PostgreSQL: Problems inserting timestamp into table

The BETA tester's 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: 43
Joined: 06.09.2009, 18:27

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.

Fr0sT
Senior Boarder
Senior Boarder
Posts: 65
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: 43
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
Senior Boarder
Senior Boarder
Posts: 65
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: 604
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.


Return to “ZeosLib 7.2 Betatest Forum”

Who is online

Users browsing this forum: No registered users and 1 guest