[fpc-pascal] Re: How to insert a record and get the primary key with sqldb?
On 22-10-2011 15:33, michael.vancanneyt-0is9kj9s...@public.gmane.org wrote: On Sat, 22 Oct 2011, Marco van de Voort wrote: In our previous episode, Felipe Monteiro de Carvalho said: Ok, now I want to insert a record in my table and I would like to obtain the auto-generated PrimaryKey This is a classic problem, since SQL simply doesn't support this. So all DBs do something else, for postgresql there are sequence objects that can be queried, while other allow to return the id of the autogenerated fields. Afaik sqldb does not abstract this yet. That's because it can't be abstracted correctly. There are 2 incompatible mechanisms. 1. autogenerated fields (mysql, MS SQL server) which must be retrieved after the insert using a special API. 2. or sequences, which must be generated manually before the insert (DB2, Oracle, Firebird) using a special API, but which may or may not be generated in an AFTER INSERT trigger. In which case it's impossible to retrieve the sequence value after the insert except by re-reading the record. Correct, but for option 2. - at least in Firebird - BEFORE INSERT triggers are often used, not AFTER INSERT. See eg http://www.firebirdfaq.org/faq29/ Also, using INSERT...RETURNING it's very well possible to get the sequence value directly when inserting the data. What's more incompatible to me is the difference between: a. manually having to specify a PK, either totally manually or using a sequence b. having the DB do it for you, either via an autonumber (your option 1) or a trigger with a sequence. The problem is that I think it's very difficult to SQLDB to figure out whether it's in situation a or b. If in a and the DB supports sequences, SQLDB will still need to be told which sequence to use. If in b, we face all the various ways of getting an autonumber or auto-generated PKs back, see e.g. https://secure.wikimedia.org/wikipedia/en/wiki/Insert_%28SQL%29#Retrieving_the_key When in b, we then get the issue of getting the primary key: Doing it in one statement: INSERT RETURNING for Firebird, Postgresql, Oracle, ? ... In two: SCOPY_IDENTITY() for SQL Server 2000 and earlier, or if you don't/can't use OUTPUT LAST_INSERT_ID() Special select statement for DB2 ;) ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Re: How to insert a record and get the primary key with sqldb?
On Mon, 24 Oct 2011, Reinier Olislagers wrote: On 22-10-2011 15:33, michael.vancanneyt-0is9kj9s...@public.gmane.org wrote: On Sat, 22 Oct 2011, Marco van de Voort wrote: In our previous episode, Felipe Monteiro de Carvalho said: Ok, now I want to insert a record in my table and I would like to obtain the auto-generated PrimaryKey This is a classic problem, since SQL simply doesn't support this. So all DBs do something else, for postgresql there are sequence objects that can be queried, while other allow to return the id of the autogenerated fields. Afaik sqldb does not abstract this yet. That's because it can't be abstracted correctly. There are 2 incompatible mechanisms. 1. autogenerated fields (mysql, MS SQL server) which must be retrieved after the insert using a special API. 2. or sequences, which must be generated manually before the insert (DB2, Oracle, Firebird) using a special API, but which may or may not be generated in an AFTER INSERT trigger. In which case it's impossible to retrieve the sequence value after the insert except by re-reading the record. Correct, but for option 2. - at least in Firebird - BEFORE INSERT triggers are often used, not AFTER INSERT. See eg http://www.firebirdfaq.org/faq29/ Typo on my part, sorry. Also, using INSERT...RETURNING it's very well possible to get the sequence value directly when inserting the data. Yes, but not all DBs support this. We use Firebird a lot, and it didn't support that construct until version 2.1 or so. In each case: it's hard to abstract correctly, but nevertheless we'll try and make managing this easier. Martin's ideas for this are useful. What's funny is that I never understood how the MySQL/SQL-Server way could correctly work. For example I have 2 tables, both with an auto-incremental field. Table 1 has an after insert trigger that does an extra insert in table 2. When I do an insert in table 1 , what does 'last_insert_id' return ? The value for table 2 or table 1 ? The last inserted id for your connection is the one for table 2, but you need/expect the last id for table 1 :-) Michael. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Re: How to insert a record and get the primary key with sqldb?
On 24-10-2011 11:14, michael.vancann...@wisa.be wrote: On Mon, 24 Oct 2011, Reinier Olislagers wrote: Also, using INSERT...RETURNING it's very well possible to get the sequence value directly when inserting the data. Yes, but not all DBs support this. We use Firebird a lot, and it didn't support that construct until version 2.1 or so. Yep, it's one of many really nice cross-db incompatibilities. Long live SQL ;) In each case: it's hard to abstract correctly, but nevertheless we'll try and make managing this easier. Martin's ideas for this are useful. Yes, he's got experience with it already - more than you can say of some armchair theorists like me ;) What's funny is that I never understood how the MySQL/SQL-Server way could correctly work. For example I have 2 tables, both with an auto-incremental field. Table 1 has an after insert trigger that does an extra insert in table 2. When I do an insert in table 1 , what does 'last_insert_id' return ? The value for table 2 or table 1 ? The last inserted id for your connection is the one for table 2, but you need/expect the last id for table 1 :-) True. Fortunately, I've never needed to do that ;) Regards, Reinier ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Re: How to insert a record and get the primary key with sqldb?
On 2011-10-24 14:43, michael.vancann...@wisa.be wrote: We will of course support GUID PKs, although in my opinion, there is precious little to support as it will in general be client code that generates the GUID (unless I am much mistaken in what is common practise) ? That is indeed the case. There is nothing extra that needs to be added to SqlDB. Our primary key fields are simply a varchar(36), and we all know a string value / varchar() is already well supported. ;-) Regards, - Graeme - -- fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal http://fpgui.sourceforge.net/ ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
[fpc-pascal] Re: How to insert a record and get the primary key with sqldb?
Hello, Ok, searching some more I see that select scope_identity() could return the generated primary key. But how to get the value? DBComm.PQConnection.ExecuteDirect(SQLText); The method above has no return value =( -- Felipe Monteiro de Carvalho ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Re: How to insert a record and get the primary key with sqldb?
On Sat, 22 Oct 2011, Felipe Monteiro de Carvalho wrote: Hello, Ok, searching some more I see that select scope_identity() could return the generated primary key. But how to get the value? DBComm.PQConnection.ExecuteDirect(SQLText); The method above has no return value =( Use a TSQLQuery with SQL select scope_identity() as theid and read field 0. I repeat, do not use ExecuteDirect. Michael.___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Re: How to insert a record and get the primary key with sqldb?
Ok, searching some more I see that select scope_identity() could return the generated primary key. But how to get the value? Better use the RETURNING* clause** as it clearly state the column value you are expecting; The scope_identity() may be a source of subtle bugs that are difficult to resolve, for example : what if the table make use of two sequences (yes sometime this is needed) what if the table's insert trigger generates a insert query(or stored procedure) into another table which make use of sequence (*) http://www.postgresql.org/docs/8.3/interactive/sql-insert.html (**) this clause is at least supported by Firebird, MS SQL SERVER, ORACLE -- Inoussa O. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Re: How to insert a record and get the primary key with sqldb?
On Sat, Oct 22, 2011 at 11:35 AM, Inoussa OUEDRAOGO inouss...@gmail.com wrote: Ok, searching some more I see that select scope_identity() could return the generated primary key. But how to get the value? Better use the RETURNING* clause** as it clearly state the column value you are expecting; The scope_identity() may be a source of subtle bugs that are difficult to resolve, for example : what if the table make use of two sequences (yes sometime this is needed) what if the table's insert trigger generates a insert query(or stored procedure) into another table which make use of sequence (*) http://www.postgresql.org/docs/8.3/interactive/sql-insert.html (**) this clause is at least supported by Firebird, MS SQL SERVER, ORACLE Talking about MSSQL Server, the scope_identity() return the ID inside the scope, ie, return the ID from your INSERT instruction, ignoring Triggers, for example. If you use @@IDENTITY then you get the last ID inserted, ie, if you have triggers you do not know which ID belongs which table. Marcos Douglas ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Re: How to insert a record and get the primary key with sqldb?
ugh, any ideas how I can obtain the next sequence value using sqldb and postgres then? I have never used sequence values before, and while I am googling I only found either answers which are specific for a particular framework (java, djambo, whatever) or people claiming that it should generate the index automatically! o.O Which clearly it is not doing... I couldn't yet get a generic answer using SQL supported by Postgres, or whatever thing which I could reuse in sqldb. thanks, -- Felipe Monteiro de Carvalho -- Felipe Monteiro de Carvalho ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Re: How to insert a record and get the primary key with sqldb?
On Sat, Oct 22, 2011 at 1:33 PM, Felipe Monteiro de Carvalho felipemonteiro.carva...@gmail.com wrote: ugh, any ideas how I can obtain the next sequence value using sqldb and postgres then? I have never used sequence values before, and while I am googling I only found either answers which are specific for a particular framework (java, djambo, whatever) or people claiming that it should generate the index automatically! o.O Which clearly it is not doing... I couldn't yet get a generic answer using SQL supported by Postgres, or whatever thing which I could reuse in sqldb. You can use: - Stored Procedures (SP): one for each table; - Dynamic SP: pass the generic SQL to the SP and return the scope_identity(); - GUID: Use a column to save a GUID. The real ID can be autoincrement but you do not need it. You can found the register using the GUID. Marcos Douglas ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Re: How to insert a record and get the primary key with sqldb?
On Sat, 22 Oct 2011, Felipe Monteiro de Carvalho wrote:
ugh, any ideas how I can obtain the next sequence value using sqldb
and postgres then?
I have never used sequence values before, and while I am googling I
only found either answers which are specific for a particular
framework (java, djambo, whatever) or people claiming that it should
generate the index automatically! o.O Which clearly it is not doing...
I couldn't yet get a generic answer using SQL supported by Postgres,
or whatever thing which I could reuse in sqldb.
Just a use a TSQLQuery:
With TSQLQuery.Create(Self) do
try
Database:=MyDatabase;
Transaction:=MyTransaction;
// Replace sequencename with the correct name of the sequence
SQL.Text:='SELECT nextval(''sequencename'')';
Open;
TheNewID:=(Fields[0] as TLargeIntField).AsLargeInt;
close;
finally
free;
end;
That's it.
Michael.
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Re: How to insert a record and get the primary key with sqldb?
On Saturday 22 October 2011 17.33:11 Felipe Monteiro de Carvalho wrote:
ugh, any ideas how I can obtain the next sequence value using sqldb
and postgres then?
I have never used sequence values before, and while I am googling I
only found either answers which are specific for a particular
framework (java, djambo, whatever) or people claiming that it should
generate the index automatically! o.O Which clearly it is not doing...
I couldn't yet get a generic answer using SQL supported by Postgres,
or whatever thing which I could reuse in sqldb.
thanks,
Postgres documenatation is here:
http://www.postgresql.org/docs/manuals/
http://www.postgresql.org/docs/8.3/static/functions-sequence.html
MSEgui calls getsqlresult() with the statement
function tmsepqconnection.readsequence(const sequencename: string): string;
begin
result:= 'select nextval(''' +sequencename+''') as res;';
end;
returns. Instead of the MSEgui getsqlresult() function use a tsqlquery in
sqldb.
Martin
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
