Page 1 of 1
PostgreSQL Timestamp problem
Posted: Thu 31 Mar 2011 02:06
by Gigasoft
I'm trying to insert a number rows into a PostgreSQL table.
I've got an insert statement in a TUniQuery which includes the following timestamp value '20/05/2010 8:09:04 PM'
Whenever I try to execute it, I get an error back saying "Invalid input syntax for timestamp '20/05/2010 8$1$2 PM'"
I think the component is interpreting :09 and :04 as parameters.
Strange thing is, this is the 19th record to be inserted into the table this way, and the first to have any errors. (The second insert actually has a timestamp of "20/05/2010 8:09:05 PM" - just one second different, which works perfectly)
I've run the exact same query in PgAdmin III (the GUI system that came with my PostgreSQL server install) and it works without issue.
Is there another way I can format the timestamp to not include the colons? Or to force the component into not interpreting the string as having parameters?
Posted: Thu 31 Mar 2011 08:17
by AlexP
Hello,
I could not reproduce the problem.
Please specify the exact version of UniDAC.
Also please specify the script to create a table, and the SQL text wich you are using to insert data.
Posted: Thu 31 Mar 2011 21:52
by Gigasoft
UniDAC7 Trial V3.60.0.16 10-Mar-11
Table:
Code: Select all
CREATE TABLE cde_master
(
"MasterSystemCode" character varying( 38 ) NOT NULL DEFAULT ''::character varying,
"MasterUserCode" character varying( 30 ),
"Title" character varying( 10 ),
"Name" character varying( 120 ),
"Firstname" character varying( 40 ),
"MiddleName" character varying( 40 ),
"Surname" character varying( 40 ),
"LegalName" character varying( 120 ),
"POBox" character varying( 60 ),
"Street" character varying( 60 ),
"Other" character varying( 60 ),
"Suburb" character varying( 60 ),
"State" character varying( 40 ),
"Postcode" character varying( 4 ),
"Phone" character varying( 15 ),
"Fax" character varying( 15 ),
"Mobile" character varying( 15 ),
"eMail" character varying( 120 ),
"Creditor" boolean DEFAULT FALSE,
"Debtor" boolean DEFAULT FALSE,
"Employee" boolean DEFAULT FALSE,
"TradingName" character varying( 120 ),
"ABN" character varying( 20 ),
"ACN" character varying( 20 ),
"RedundantC" boolean DEFAULT FALSE,
"RedundantD" boolean DEFAULT FALSE,
"RedundantE" boolean DEFAULT FALSE,
"MinimumOrder" numeric(10,2),
"PostedUser" character varying( 20 ) NOT NULL DEFAULT ''::character varying,
"PostedSystemDate" date NOT NULL DEFAULT NULL,
"PostedDT" timestamp NOT NULL DEFAULT NULL,
"PostedMachine" character varying( 60 ) NOT NULL DEFAULT ''::character varying,
"PostedCompany" character varying( 80 ) NOT NULL DEFAULT ''::character varying,
"LastUpdatedUser" character varying( 20 ) NOT NULL DEFAULT ''::character varying,
"LastUpdatedSystemDate" date NOT NULL DEFAULT NULL,
"LastUpdatedDT" timestamp NOT NULL DEFAULT NULL,
"LastUpdatedMachine" character varying( 60 ) NOT NULL DEFAULT ''::character varying,
"LastUpdatedCompany" character varying( 80 ) NOT NULL DEFAULT ''::character varying,
CONSTRAINT cde_master_pkey PRIMARY KEY ("MasterSystemCode")
);
Insert: (Edited to preserve privacy)
Code: Select all
INSERT INTO cde_master VALUES(E'{00FF7505-9DD3-4CD5-A4AF-035A42E0023A}', E'OKEST', NULL, E'STEPHEN O\KEEFE', NULL, NULL, NULL, NULL, NULL, E'--------------', NULL, E'-----------', E'------------', E'', E'---------', E'--------', E'------------', E'----------------------------', FALSE, TRUE, FALSE, E'', NULL, NULL, FALSE, FALSE, FALSE, NULL, E'DARYL', '20/05/2010', TIMESTAMP '20/05/2010 8:09:04 PM', E'GIGASOFT1\_Daryl', E'WHIP', E'DARYL', '20/05/2010', TIMESTAMP '20/05/2010 8:09:04 PM', E'GIGASOFT1\_Daryl', E'WHIP');
Posted: Fri 01 Apr 2011 07:02
by AlexP
Hello,
Thank you for the information.
We have reproduced the problem.
We will notify you as soon as we have any results.
Posted: Fri 01 Apr 2011 08:22
by AlexP
Hello,
Now you can use the following workaround - set '' (two single quotes) instead of \ like
....
E'STEPHEN O''KEEFE',
.....
Posted: Tue 05 Apr 2011 06:43
by Gigasoft
Thank you very much.
That has solved the problem.
Posted: Tue 05 Apr 2011 07:05
by AlexP
Hello,
It is good to see that this problem was solved.
We have fixed the problem.
This fix will be included in the next build.
If any other questions come up, please contact us.