Howto get generated id/int best ?

[Terence]

If i use auto generated index values (as primary for later use as foreign reference) there are is this std approach to enter new data:

1.) run a select statment, to check if entry already exists
2a.) if entry exists, use returned id as reference
or
2b.) insert new entry and query for auto generated id afterwards.

In best case i have one select statment.
In worst case i have 2 select statments and one insert statment.

Is there approach/design i could use to have better worst case?
I mean how to solve that problem that auto generated ids are unknown after insert in the first. For apps doing mass inserting this additional selects will cost. On the other side if using client side ids (not auto generated) it comes with disadvantage providing no support for multi client access.

In mysql eg there is a method "mysql_insert_id" which shall retrurn autogen id for last insert, this will (maybe) have better perfoemance than run query (multiplied for eg 8 millions inserts).
But this is not dbms indepedent and i would like to support all by zeos supported dbms.

Any ideas,
tx for help.

[fduenas]

As a log as i know you can use the tzsequence to obtain the and sequence id you define. The only thing is that there is no TZSequence for mysql.

determining the last autogeerated is has to be implemented by ecah of the drivers Zeos implements. I thinkalsmost all protocol can optain last Autoinc Values (at leat Mysql and ADO can optain the last autogenerated id). You have to do a trick here. I think can be post to the Code samples.

If you use one Master dataset and one or more detail datasets you have to the next things:

1) Use the DataSource property kind for master-detail linking:
2) Set MasterDataSet.ClientUpdates property to false
3) Set DetaildataSetnn.ClientUpdates property to true;
4) Assign to DetailDataSetnn.Datasource property the MasterDataSet object;
5) Don't forget to specify in the DetailDataSetnn.SQL property a where clause to filter record to return only those that 'master_id' field value in detail table is equal ot 'id' field value of Master table.
for example:

Code: Select all

 SELECT * FROM Detailtable WHERE DetailTable.master_id = :id

':id' is the name of the field in the MasterDataSet that contain the unique id passed as parameter. When using Datasource linking, this value is passed automaticaly after each record change or update in the MasterDataSet. So its important you have to put the same master field name as parameter.

In MasterDataSet.OnBeforePost event

Code: Select all

 DetailDataSet01.DataSource := nil;
 DetailDataSet02.DataSource := nil;
 DetailDataSetnn.DataSource := nil;

Then in MasterDataSet.OnAfterPost

Code: Select all

 //MasterDataSet.ApplyUpdates; //<<--If you use MasterDataSet.ClientUpdates = true uncomment this line;
 with DetailDataSetnn Do
 begin
   First;
   While Not Eof do
   begin
     Edit;
     FieldByName('master_id').asInteger :=
      MasterDataSet.FieldByName('id).asInteger;
     Post;
     Next;
   end;
   ApplyUpdates;
end; 

If you use TZUpdateSQL object, i have added some new events (check lastest SVN revision) that will help you determine when (after what insert statement) to resolve the AutoInc Value. Actually this is only available for MySQL but we pretent to extent to it other protocols, but we need experts to do this

check TZUpdateSQL.OnAfterInsertStament

Regards

[Terence]

At the moment i am far away of using master and client sets, just a ZQuery. But anayway are you sure that this whole stuff will be faster and has more performance as just querying afterwards with a select?

The ZUpdateSql solution sounds better to me, in every case we should use the database specific "getlastinsertedid" method which is unqiue per connection, no more.
As far as i had the overview most dbms/ado support such method, but what about leight-weight dbs like sql lite.

For which dbs do we have the support OnAfterInsertStament returning last id ? - sure i will check myself also.

[Terence]

ahh, it seems not as difficult to epand such feature.
I found in ur code the main line
" ResultSet := Statement.ExecuteQuery('SELECT LAST_INSERT_ID()');"
which then has to be adapted db specific.
But i also noticed that the "IZCachedResolver" isn't implemented for all dbs's, although there is a Result set class for all of them, i am right?
I am just asking me what happens if accessing this being not implemented?
Would be great if you could give a short summary was has to be done here.

Implementing the
TZXXXCachedResolver = class (TZGenericCachedResolver, IZCachedResolver) for every db with "UpdateAutoIncrementFields" shouldn't be such difficult. Problems is that every db handles different, eg Firebird ins't save to query afterwards - see below.
I would search for required sql cmds for specific dbs, but if there is none we would have to use a std select query to be compatible with that method for all dbs.

Things to pay attention for:

1.) the "last inserted id" must reflect the "correct" id, we have to pay attention for multiple threadings,processes -> all dbms support per connection save values.

2.) if accepting multiple inserts, the "last inserted id" has to be queried (with sepecfic sql function) direct after each insert, else it will be lost.

3.) There are some dbs (firebird) which emulate autopinc through trigger, which generates id server side and returns it vefore insert, so it can be used to manual add it to the insert sql. In this case we can't use a "OnAfterInsert" Event". The design challenge is to enable both approaches- complety server side generated and immidiately set ids and server side genids whcih has to be set by the client side.


Database specific "last inserted id" methods

Firebird:
- we have generator, so querying afterwards isn't save

Code: Select all

SELECT GEN_ID(Generator_name, 1) AS MyVar   FROM RDB$DATABASE;

(see http://www.firebirdsql.org/index.php?op=faq#q0011.dat)

PostGresql:
- two approaches possible:

1.) before insert with

Code: Select all

new_id = execute("SELECT nextval('person_id_seq')");
execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')");

2.) after insert with

Code: Select all

execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
new_id = execute("SELECT currval('person_id_seq')");

(see http://www.postgresql.org/docs/faqs.FAQ.html#item4.11.1)

Sqllite:
- only since v3.0 supported "sqlite3_last_insert_rowid()"
(see http://www.sqlite.org/capi3ref.html#sql ... sert_rowid)

MsAccess/ADO:
- GetLastInsertID()
- does thos work for all ado driver? no idea?

Oracle:
- "create sequence" - server side genids, usable by several connections (transaction save) (see
http://download-west.oracle.com/docs/cd ... sthref5308
http://download-west.oracle.com/docs/cd ... m#i1006157)
- id is generated on server side but can be used immidiately afterwards in insert sql, e.g.

1.) first insert and query afterwards

Code: Select all

INSERT INTO employees  VALUES (employees_seq.nextval, 'John');
SELECT employees_seq.currval from  employees;

2.) other (firebird like) approach is, first query and insert afterwards:

Code: Select all

id:= SELECT employees_seq.nextval from DUAL;
INSERT INTO employees  VALUES (id, 'John');

Ms SQL:

Code: Select all

DECLARE @NewID int
INSERT INTO E (EVAL) VALUES (12345)
SELECT @NewID = @@IDENTITY

[Terence]

Carefully pushing that topic
I am interested in implementing that, it seems having optimizing features for db connection and data exchange.
Any idea how to solve that problems with need of run sql in front of insert statment? At the moment the "hook" is after the insert statmenet as implemented in Mysql only.
Btw apart from the "auto" update of the idert ids via Event, a simple wrapper method to get "lastinsertid" like applied by nearly ll dbs would be great. Then you don'T have to know how the sql cmd and everything else is done.

[Terence]

since Firebird 2 related to that topic :

The RETURNING clause syntax has been implemented for the INSERT statement, enabling the return
of a result set from the INSERT statement. The set contains the column values actually stored.
Most common usage would be for retrieving the value of the primary key generated inside a BEFORE-
trigger.
Available in DSQL and PSQL.
Syntax Pattern

Code: Select all

INSERT INTO ... VALUES (...) [RETURNING <column_list> [INTO <variable_list>]]

Example(s)
1.

Code: Select all

INSERT INTO T1 (F1, F2)
VALUES (:F1, :F2)
RETURNING F1, F2 INTO :V1, :V2;

2.

Code: Select all

INSERT INTO T2 (F1, F2)
VALUES (1, 2)
RETURNING ID INTO :PK;

Note
1. The INTO part (i.e. the variable list) is allowed in PSQL only (to assign local variables) and rejected
in DSQL.
2. In DSQL, values are being returned within the same protocol roundtrip as the INSERT itself is
executed.
3. If the RETURNING clause is present, then the statement is described as
isc_info_sql_stmt_exec_procedure by the API (instead of isc_info_sql_stmt_insert), so the existing
connectivity drivers should support this feature automagically.
4. Any explicit record change (update or delete) performed by AFTER-triggers is ignored by the
RETURNING clause.
5. Cursor based inserts (INSERT INTO ... SELECT ... RETURNING ...) are not supported.
6. This clause can return table column values or arbitrary expressions.

[apiove]

If i use auto generated index values (as primary for later use as foreign reference) there are is this std approach to enter new data:

1.) run a select statment, to check if entry already exists
2a.) if entry exists, use returned id as reference
or
2b.) insert new entry and query for auto generated id afterwards.

In best case i have one select statment.
In worst case i have 2 select statments and one insert statment.

Is there approach/design i could use to have better worst case?
I mean how to solve that problem that auto generated ids are unknown after insert in the first. For apps doing mass inserting this additional selects will cost. On the other side if using client side ids (not auto generated) it comes with disadvantage providing no support for multi client access.

In mysql eg there is a method "mysql_insert_id" which shall retrurn autogen id for last insert, this will (maybe) have better perfoemance than run query (multiplied for eg 8 millions inserts).
But this is not dbms indepedent and i would like to support all by zeos supported dbms.

Any ideas,
tx for help.

...MySQL approach is o get field value trough LAST_INSERT_ID() function (but this is a query...); the firebird approach is to get index before insert the record (see this link:http://www.fingerbird.de/generatorguide ... ore_Insert)
(but here is also necessary an other query to generator...)

So whe have two database with two manage auto generated fields a query before (firebird) and a query later (mysql). I think that is useful to implement a built in behaviour dependig from "ZConnection.Protocol" property...

Andrea.

[apiove]

Downloading Zeoslib version 6.6.0-beta is possible to enable this functionality!

Follow these steps:

1. Locate:
procedure TZMySQLCachedResolver.PostUpdates(Sender: IZCachedResultSet
into:
ZDbcMySqlResultSet.pas

2. Edit:
remove PATCH [1185969] comments

3. Build and install

4. Enjoy!

Byez,
Andrea

Thanks to developers!!!!

[apiove]

Downloading Zeoslib version 6.6.0-beta is possible to enable this functionality!

Follow these steps:

1. Locate:
procedure TZMySQLCachedResolver.PostUpdates(Sender: IZCachedResultSet
into:
ZDbcMySqlResultSet.pas

2. Edit:
remove PATCH [1185969] comments

3. Build and install

4. Enjoy!

Byez,
Andrea

Thanks to developers!!!!

Works only for TZTable component!