Page 1 of 1
InsertId is not supported
Posted: Wed 07 Jan 2009 09:21
by Thomas J.
Hello all,
I need the property TuniQuery->InsertId which was possible for TMyQuery.
Thanks
Thomas
Posted: Thu 08 Jan 2009 09:39
by Plash
In UniDAC this property is called LastInsertId.
Posted: Fri 09 Jan 2009 15:22
by Thomas J.
Thanks a lot.
But this I cannot find in documentation. At least not searching via index.
And it is also not working in case of usiong a PostgreSQL database with a field of type SERIAL.
After the post it returns in any case 0 instead of the one which is written into the field.
Why?
Thanks Thomas
Posted: Mon 12 Jan 2009 09:04
by Plash
We'll add the discription of LastInsertId to the documentation.
LastInsertId is filled only if server returns this value for a SQL statement. PostgreSQL returns only value of OID for a table with OIDs. If you have such table, you can read last inserted OID in the property.
To get the value of SERIAL, use SQL statement with RETURNING:
Code: Select all
INSERT INTO table1(f) VALUES ('hello')
RETURNING id
Posted: Mon 12 Jan 2009 10:30
by Thomas J.
Sorry but I get for each new record 0.
DROP TABLE p_cratemaker.t_test;
CREATE TABLE p_cratemaker.t_test
(
n_firmakey serial NOT NULL,
c_name character varying(40),
CONSTRAINT t_test_pkey PRIMARY KEY (n_firmakey)
);
ALTER TABLE p_cratemaker.t_test OWNER TO cratemaker;
insert into p_cratemaker.t_test (c_name) VALUES ('Paul') returning n_firmaKey;
I can alos create the table with OID this doens't matter.
This returns outside 1.
If I run my app and execute these lines.
Code: Select all
UniQuery1->Active = true;
UniQuery1->Insert();
UniQuery1->FieldByName("c_name")->AsString = "Problem";
UniQuery1->Post();
EdInsertedId->Text = IntToStr(UniQuery1->LastInsertId);
UniQuery1->LastInsertId is 0.
Thanks for you help
Posted: Mon 12 Jan 2009 13:41
by Thomas J.
Did I something wrong or is it a bug?
Thanks 4 help
Thomas
Posted: Tue 13 Jan 2009 09:09
by Plash
LastInsertId cannot be used to get the value of SERIAL field.
Assign a SQL statement like the following to the SQLInsert property of TUniQuery:
Code: Select all
INSERT INTO table1(f) VALUES (:f)
RETURNING id
Set the ReturnParams option of TUniQuery to True.
After you call Post the value of the field will be in the corresponding TField object:
Code: Select all
EdInsertedId->Text = IntToStr(UniQuery1->FieldByName("id")->AsInteger);
Posted: Tue 13 Jan 2009 09:29
by Thomas J.
Hello Plash,
why is not possible for SERIAL. postgreSQL returns the value also for SERIAL.
Anyway Thanks it is working with FieldByName("")
Thanks for your help
Thomas
Posted: Wed 14 Jan 2009 08:36
by Plash
PostgreSQL server does not have a feature of returning value of SERIAL column after insertion. So LastInsertId is not filled for PostgreSQL.