[Solved] PostgreSQL: Problems inserting timestamp into table

[JD]

Hi there everyone,

I am using Zeos 7.2.1rc and I have a serious problem that has been bugging me for over two weeks. I am using NewPascal mORMot server to update a PostgreSQL 9.5 database. I've even used the same mORMot server to update another PostgreSQL 10 database and everything is fine so far.

However a serious problem has arisen. I am trying to update a table with timestamp fields. I want to insert a value like 2017-12-11T15:30:00 into the table. I have used the following code one after the other and it all failed

Code: Select all

    aServer.fDbProps.ExecuteNoResult(
        'INSERT INTO public.tvp_event (starttime, endtime) ' +
        'VALUES (?,?) ',
        [DateTimeToSQL(StrToDateTime(VariantToUTF8(vJSEvents.Value(0).StartTime))),
         DateTimeToSQL(StrToDateTime(VariantToUTF8(vJSEvents.Value(0).EndTime)))]);

Error: "2017-12-11T15:30:00" is not a valid time

I then removed the 'T' and tried the following code

Code: Select all

    aServer.fDbProps.ExecuteNoResult(
        'INSERT INTO public.tvp_event (starttime, endtime) ' +
        'VALUES (?,?) ',
        [DateTimeToSQL(StrToDateTime(StringReplace(VariantToUTF8(vJSEvents.Value(0).StartTime), 'T', ' ', [rfReplaceAll]))),
         DateTimeToSQL(StrToDateTime(StringReplace(VariantToUTF8(vJSEvents.Value(0).EndTime), 'T', ' ', [rfReplaceAll])))]);

Error: "2017-12-11" is not a valid date format

Code: Select all

     aServer.fDbProps.ExecuteNoResult(
              'INSERT INTO public.tvp_event (debut, fin) ' +
              'VALUES (?,?) ',
              [VariantToUTF8(vJSEvents.Value(0).StartTime),
               VariantToUTF8(vJSEvents.Value(0).EndTime)]);

SQL Error: ERROR: invalid input syntax for type timestamp: “ ”

Code: Select all

    aServer.fDbProps.ExecuteNoResult(
             'INSERT INTO association_la_chorba.tvp_event (starttime, endtime) ' +
             'VALUES (?,?) ',
             [Iso8601ToSQL(VariantToUTF8(vJSEvents.Value(0).StartTime)),
              Iso8601ToSQL(VariantToUTF8(vJSEvents.Value(0).EndTime))]);

SQL Error: ERROR: invalid input syntax for type timestamp: “ ”.

The screencap of the error is as follows:

PostgreSQL Timestamp error.png

As you can see, the error is from line 705 in ZDbcPostgreSqlUtils.pas

Can anyone help me?

Thanks,

JD

P.S I have also posted this onto the mORMot forum in case anyone wonders. Here is the link https://synopse.info/forum/viewtopic.ph ... 914#p25914

[Fr0sT]

It's not an answer but couldn't you just use TDateTime parameters in query?

[JD]

Not in the way the query is set up as it does not use TDataSet components.

[Fr0sT]

TDataSet components are of no use here. In ExecuteNoResult you already use parameters. So just call it with TDateTime values instead of string

No matter if the subject is a bug or not, using timestamp values converted to string is always prone to errors and of course slow.

[marsupilami]

Hello JD, could you please create a log using a TZSQLMonitor component to see what gets sent to the database? I think this could help finding the problem.

[EgonHugeist]

@JD

your using pure dbc comming from mORMot.

have a look at TZDbcPostgesStatement.pas.
your conversion of the DateTime value is not correct. IIRC you should use SQLToDateTime(VariantToUTF8(vJSEvents.Value(0).StartTime)) if Arnauds "magic" byte has been set. Look to SynCommons.pas. Note that SynCommons.VariantToUTF8 is a bit ... wrong/inconsistent ..(my POV) it returns 'null' if varType in [vtNull,vtEmpty] and says conversion successfull. Same like other Conversions of VariantToXXXX too. So be carefull.

You need a native TDate/TTime/TDateTime value and Zeos will do the job.
Next issue might be the array of const..

This is mORMot related please ask AB for help.

[JD]

Thanks to everyone for their suggestions. As Michael pointed out the problem was with mORMot or rather my understanding of how mORMot works.

I've cleaned up the code, used native pascal Date/Time handling functions and it now works properly.

Cheers,

JD