[Solved] PostgreSQL: Problems inserting timestamp into table

The forum for ZeosLib 7.2 Report problems. Ask for help, post proposals for the new version and Zeoslib 7.2 features here. This is a forum that will be edited once the 7.2.x version goes into RC/stable!!

My personal intention for 7.2 is to speed up the internals as optimal a possible for all IDE's. Hope you can help?! Have fun with testing 7.2
JD
Junior Boarder
Junior Boarder
Posts: 45
Joined: 06.09.2009, 18:27

[Solved] PostgreSQL: Problems inserting timestamp into table

Postby JD » 17.12.2017, 20:13

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.php?pid=25914#p25914
You do not have the required permissions to view the files attached to this post.
Last edited by JD on 10.03.2018, 16:05, edited 1 time in total.

Fr0sT
Zeos Dev Team
Zeos Dev Team
Posts: 119
Joined: 08.05.2014, 12:08

Re: PostgreSQL: Problems inserting timestamp into table

Postby Fr0sT » 18.12.2017, 12:39

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

JD
Junior Boarder
Junior Boarder
Posts: 45
Joined: 06.09.2009, 18:27

Re: PostgreSQL: Problems inserting timestamp into table

Postby JD » 18.12.2017, 17:04

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

Fr0sT
Zeos Dev Team
Zeos Dev Team
Posts: 119
Joined: 08.05.2014, 12:08

Re: PostgreSQL: Problems inserting timestamp into table

Postby Fr0sT » 19.12.2017, 09:37

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.
Last edited by Fr0sT on 19.12.2017, 14:55, edited 1 time in total.

marsupilami
Gold Boarder
Gold Boarder
Posts: 658
Joined: 17.01.2011, 14:17

Re: PostgreSQL: Problems inserting timestamp into table

Postby marsupilami » 19.12.2017, 14:33

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.

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Re: PostgreSQL: Problems inserting timestamp into table

Postby EgonHugeist » 30.01.2018, 21:22

@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.
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

JD
Junior Boarder
Junior Boarder
Posts: 45
Joined: 06.09.2009, 18:27

Re: PostgreSQL: Problems inserting timestamp into table

Postby JD » 10.03.2018, 16:04

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


Return to “ZeosLib 7.2 Forum”

Who is online

Users browsing this forum: No registered users and 1 guest