Page 1 of 1
TZQuery & LAST_INSERT_ID()
Posted: 02.03.2006, 19:19
by apiove
Hello folks,
I'm using Delphi 7, Mysql 4, and ZeosDBO 6.1.5
Is possible to have auto generated field values using TZQuery and TZUpdateSQL after an insert?
Any suggest?
Thanks a lot.
PS: I can't use TZTable because I'have to fetch only few records from a big table that contains thousand of record.
--- What I've tried to do: ---
I've tried using this SQL code for TZQuery:
[syntax="sql"]
SELECT * FROM mytable
WHERE cat = :cat;
[/syntax]
and auto generated SQL code for TZUpdateSQL:
Posted: 03.03.2006, 09:46
by pol
Hi!
It's very easy. As you wrote in the subject, it's last_insert_id(). Use a new ZReadOnlyQuery with the SQL 'select last_insert_id() as LID'. After you have done a Post and ApplyUpdates (I'm not sure if the last one is really necessary) of your Insert, close and open the LID query. Then you have the last insert id as for example Query2LID.AsInteger.
Posted: 03.03.2006, 12:41
by apiove
pol wrote:Hi!
It's very easy. As you wrote in the subject, it's last_insert_id(). Use a new ZReadOnlyQuery with the SQL 'select last_insert_id() as LID'. After you have done a Post and ApplyUpdates (I'm not sure if the last one is really necessary) of your Insert, close and open the LID query. Then you have the last insert id as for example Query2LID.AsInteger.
Ok, I know... but isn't the right way...
Example: I've a TDBTreeView component that build a tree reading data from the query (it use autogenetated field), after a post it rescans the dataset to rebuild the tree but (after an insert) it find some record without the primery key field, so the tree can't put TTreeNodes in the right position.
So, I need that the value is written into the field before the post operation ends, like TZTable do...
Tanks in advance.
Andrea.
Here you are my tests
Posted: 07.03.2006, 18:36
by apiove
I've modified TZUpdateSQL component adding a LastInsertIdSQL and AutoColumnIndex properties trying to emulate TZTable behaviour.
I've added after normal PostUpdates operation a query execution to get new field index from database.
But now I've a problem finding a connection to make the query, how can i do?
Please developers help me!!! I don't want a solution, but only a trick!!!
PS: I've tried pubblishing and using FConnection field (into DbcConnection property), but on this line I receive an access violation exception:
con := (TZAbstractRODataset(TDataSet).Connection).DbcConnection;
Re: Here you are my tests
Posted: 07.03.2006, 18:49
by apiove
apiove wrote:I've modified TZUpdateSQL component adding a LastInsertIdSQL and AutoColumnIndex properties trying to emulate TZTable behaviour.
I've added after normal PostUpdates operation a query execution to get new field index from database.
But now I've a problem finding a connection to make the query, how can i do?
Please developers help me!!! I don't want a solution, but only a trick!!!
PS: I've tried pubblishing and using FConnection field (into DbcConnection property), but on this line I receive an access violation exception:
con := (TZAbstractRODataset(TDataSet).Connection).DbcConnection;
I'm sorry guys, I'm a pig!!!
Code: Select all
con := (TZAbstractRODataset(TDataSet).Connection).DbcConnection;
^ = FDataset not TDataset
I've attached the new code:
extract files into component folder and rebuild component package.