mORMot Open Source

JD

Member

Registered: 2015-08-20

Posts: 101

Problems inserting timestamp into PostgreSQL table using Zeos

Hi there everyone,

I'm trying to update some fields in an external PostgreSQL database that I connect to using mORMot/Zeos. The fields that are causing problems are of type timestamp. The DateTimeToSQL function is not working for me.

A client sends a timestamp value of say "2017-12-11T15:30:00" to my mORMot server. I try to insert it into the PostgreSQL table using

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

I got an error saying "2017-12-11T15:30:00" is not a valid time

I then used StringReplace to remove the 'T' and replace it with a blank space. I tried again with

    aServer.fDbProps.ExecuteNoResult(
        'INSERT INTO %s.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])))]);

This time, I got an error saying "2017-12-11" is not a valid date format

What am I doing wrong?

Thanks,

JD

Last edited by JD (2017-12-10 22:35:25)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,231

Website

Re: Problems inserting timestamp into PostgreSQL table using Zeos

I guess this is because you are using Delphi RTL's StrToDateTime() which does not support ISO-8601 encoded date time.

Try with SynCommons' Iso8601ToDateTime() instead.
Or, for your specific case, directly the Iso8601ToSQL() function.

JD

Member

Registered: 2015-08-20

Posts: 101

Re: Problems inserting timestamp into PostgreSQL table using Zeos

I guess this is because you are using Delphi RTL's StrToDateTime() which does not support ISO-8601 encoded date time.

Try with SynCommons' Iso8601ToDateTime() instead.
Or, for your specific case, directly the Iso8601ToSQL() function.

Where is the Iso8601ToDateTime() function? It does not seem to be in SynCommons.pas. It is only mentioned on line 357 in the statement below:

  - new DateToSQL(), DateTimeToSQL() and Iso8601ToSQL() functions, returning
    a string with a JSON_SQLDATE_MAGIC prefix and proper UTF-8/ISO-8601 encoding
    to be inlined as ? bound parameter in any SQL query (allow binding of
    date/time parameters as request by some external database engine
    which does not accept ISO-8601 text in this case)

Thanks,

JD

Last edited by JD (2017-12-11 13:00:28)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,231

Website

Re: Problems inserting timestamp into PostgreSQL table using Zeos

Iso8601ToDateTime() is part of SynCommons.pas.

Ensure you have the latest unstable version of the framework, i.e. 1.18.4068.

JD

Member

Registered: 2015-08-20

Posts: 101

Re: Problems inserting timestamp into PostgreSQL table using Zeos

Iso8601ToDateTime() is part of SynCommons.pas.

Ensure you have the latest unstable version of the framework, i.e. 1.18.4068.

Thanks for your reply ab. Sorry, I meant to say Iso8601ToSQL() function.

I changed StrToDateTime() to Iso8601ToDateTime() and tried again with

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

This time I got the following exception which was thrown on line 705 of ZDbcPostgreSqlUtils.pas

EZSQLException with message: SQL Error: ERROR invalid entry syntax for the type timestamp << >>

I then used ShowMessage to see what the DateTimeToSQL functions above were returning and I saw the following

□2017-12-11T14:00:00
□2017-12-11T16:00:00

The function is returning text with small rectangles in fromt of the text! The decimal 9633 (or the hexadecimal u25A1).

How do I fix this?

JD

Last edited by JD (2017-12-11 13:15:04)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,231

Website

Re: Problems inserting timestamp into PostgreSQL table using Zeos

This is as expected. Check the doc: there is a "magic" character at the beginning of the field.
Then, when it is bound to the statement, it will use BindDateTime() with the raw value.

I have added Iso8601ToSQL() function yesterday, so you need to update the mORMot source code.

JD

Member

Registered: 2015-08-20

Posts: 101

Re: Problems inserting timestamp into PostgreSQL table using Zeos

This is as expected. Check the doc: there is a "magic" character at the beginning of the field.
Then, when it is bound to the statement, it will use BindDateTime() with the raw value.

I have added Iso8601ToSQL() function yesterday, so you need to update the mORMot source code.

Hi there ab,

Thanks for your suggestions. I'm afraid it still does not work. I now keep getting this error
SQL Error: ERROR: invalid input syntax for type timestamp: “ ”

The input is still the same, it is a timestamp like the following

2017-12-11T14:00:00
2017-12-11T16:00:00

I have even replaced the 'T' with a blank space, to no avail. I keep getting the same error.

Please help!!!

JD