InsertId is not supported

[Thomas J.]

Hello all,

I need the property TuniQuery->InsertId which was possible for TMyQuery.

Thanks
Thomas

[Plash]

In UniDAC this property is called LastInsertId.

[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

[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

[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

[Thomas J.]

Did I something wrong or is it a bug?
Thanks 4 help
Thomas

[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);

[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

[Plash]

PostgreSQL server does not have a feature of returning value of SERIAL column after insertion. So LastInsertId is not filled for PostgreSQL.