Re: [Lazarus] TSQLQuery
you mean something like this :
If Length(st) 0 Then Begin
SQLQuery1.SQL.Text := st;
End
Else Begin
exit;
End;
Try
SQLQuery1.Open;
Except
On e: Exception Do Begin
ShowMessage('Fehler ungültige Eingabe.'#13'Fehlermeldung:'#13 +
e.Message);
exit;
End;
End;
On 07/30/2015 04:45 PM, Terry A. Haimann wrote:
After executing a query, is there a way to test if there were any
errors?
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
--
Auf meiner Homepage www.Corpsman.de ist immer was los, ständig wird sie
aktualisiert und erweitert. Da ist für jeden was dabei.
--
Schütze deine Privatsphäre, nutze E-mail Verschlüsselung.
Wie das geht steht z.B. hier :
https://support.mozilla.org/en-US/kb/digitally-signing-and-encrypting-messages#w_installing-gpg-and-enigmail
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
[Lazarus] TSQLQuery
After executing a query, is there a way to test if there were any errors? -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 09:41:27PM -0300, silvioprog wrote: Return the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it fetches the value of the last sequence used by nextval in the current session. It is an error to call lastval if nextval has not yet been called in the current session. And PostgreSQL provides the RETURNING feature too. =) Requires DEFAULT in the corresponding VALUES field though, I don't know if that is cross-db. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On 2014-11-13 14:07, Reimar Grabowski wrote: It seems to work but it's IMHO not very nice. Ideally I'd like to have TSQLQuery do it for me and offer me some property like SQLQuery.LastInsertID or something along those lines. Is there any way to get the new PK value without manually querying for the inserted record? Do you have control over the database structure? If so, simply change the auto-increment fields to standard Int or Int64 data type then use the well known Ambler High/Low method to generate your identify values. This method is also multi-user and multi-insert (multiple inserts one after the other) friendly. See tiOPF's Options/tiOIDInteger.pas unit for an implementation. tiOPF is available on SourceForge. Alternative (my preferred solution) is to use a GUID as your identify value. In is table and database unique, so opens up a world of opportunity. There are SO MANY benefits to generating your own identify value BEFORE you insert a record. I have worked for more years than I care to remember with databases, and even with something as hugely popular as MS SQL Server there is inherent issues with auto-increment fields. My advice, avoid auto-increment as much as possible! Regards, - Graeme - -- fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal http://fpgui.sourceforge.net/ -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Thu, 13 Nov 2014 11:33:58 -0300 silvioprog silviop...@gmail.com wrote: I use an own TPreparedStatement (equivalent to TSQLQuery prepared + Params, but my structure does not uses TDataset) that I get the last inserted ID with (pseudo codes): snip Sorry, you lost me there, but not a problem (see my response to Marcos). Thanks anyway R. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Thu, 13 Nov 2014 13:26:32 -0300 Marcos Douglas m...@delfire.net wrote: Hi, Take a look in Greyhound project: https://github.com/mdbs99/Greyhound I did and could not see how it helps. Here you can see an example using last id: https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285 Yes, if you are using Greyhound, which I am not. So it looks like there's nothing in TSQLQuery that does the job for me. I now just use another TSQLQuery with the following SQL: SELECT LAST_INSERT_ID() AS SomeNameIChoose; Seems to work well, reduces the lines of code as I don't have to set parameters (my primary goal), can be reused for other inserts (being not table dependent) and took about 1 Minute to implement. Thanks anyways. R. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 10:05 AM, Reimar Grabowski reimg...@web.de wrote: On Thu, 13 Nov 2014 13:26:32 -0300 Marcos Douglas m...@delfire.net wrote: Hi, Take a look in Greyhound project: https://github.com/mdbs99/Greyhound I did and could not see how it helps. It has a code that working with auto-inc. Here you can see an example using last id: https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285 Yes, if you are using Greyhound, which I am not. Yes, I use everyday. I'm the author. ;-) So it looks like there's nothing in TSQLQuery that does the job for me. I now just use another TSQLQuery with the following SQL: SELECT LAST_INSERT_ID() AS SomeNameIChoose; Seems to work well, reduces the lines of code as I don't have to set parameters (my primary goal), can be reused for other inserts (being not table dependent) and took about 1 Minute to implement. Thanks anyways. Best regards, Marcos Douglas -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014, Reimar Grabowski wrote: On Thu, 13 Nov 2014 13:26:32 -0300 Marcos Douglas m...@delfire.net wrote: Hi, Take a look in Greyhound project: https://github.com/mdbs99/Greyhound I did and could not see how it helps. Here you can see an example using last id: https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285 Yes, if you are using Greyhound, which I am not. So it looks like there's nothing in TSQLQuery that does the job for me. I now just use another TSQLQuery with the following SQL: SELECT LAST_INSERT_ID() AS SomeNameIChoose; Seems to work well, reduces the lines of code as I don't have to set parameters (my primary goal), can be reused for other inserts (being not table dependent) and took about 1 Minute to implement. Connections for databases that have lastinsertID you can call the method created for this: Function GetInsertID: int64; There is a plan to let SQLDB retrieve the value of some fields fro the DB, but it is not implemented yet. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt mich...@freepascal.org wrote: On Fri, 14 Nov 2014, Reimar Grabowski wrote: On Thu, 13 Nov 2014 13:26:32 -0300 Marcos Douglas m...@delfire.net wrote: Hi, Take a look in Greyhound project: https://github.com/mdbs99/Greyhound I did and could not see how it helps. Here you can see an example using last id: https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285 Yes, if you are using Greyhound, which I am not. So it looks like there's nothing in TSQLQuery that does the job for me. I now just use another TSQLQuery with the following SQL: SELECT LAST_INSERT_ID() AS SomeNameIChoose; Seems to work well, reduces the lines of code as I don't have to set parameters (my primary goal), can be reused for other inserts (being not table dependent) and took about 1 Minute to implement. Connections for databases that have lastinsertID you can call the method created for this: Function GetInsertID: int64; There is a plan to let SQLDB retrieve the value of some fields fro the DB, but it is not implemented yet. Hi Michael, To do that is need to implement a GetInsertID method in all drivers of SQLdb and include a code, like bellow, in TSQLQuery.ApplyRecUpdate: var I: Integer; LastId: NativeInt; Fld: TField; begin inherited; if UpdateKind ukInsert then Exit; for I := 0 to Fields.Count -1 do begin Fld := Fields.Fields[I]; if Fld.IsNull and ((Fld.DataType = ftAutoInc) or (LowerCase(Fld.FieldName) = 'id') and (Fld is TNumericField)) then begin LastId := FLib.GetLastAutoIncValue; if LastId = 0 then Exit; if Fld.ReadOnly then Fld.ReadOnly := False; Edit; Fields[I].SetData(@LastId); Post; Exit; end; end; end; https://github.com/mdbs99/Greyhound/blob/master/src/ghsqldblib.pas#L157 Marcos Douglas -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 9:53 AM, Reimar Grabowski reimg...@web.de wrote: On Thu, 13 Nov 2014 11:33:58 -0300 silvioprog silviop...@gmail.com wrote: I use an own TPreparedStatement (equivalent to TSQLQuery prepared + Params, but my structure does not uses TDataset) that I get the last inserted ID with (pseudo codes): snip Sorry, you lost me there, but not a problem (see my response to Marcos). Thanks anyway I saw. The problem which I see is just: Query.SQL.Text := 'SELECT LAST_INSERT_ID() AS SomeNameIChoose;'; Query.Open; here ID := Query.Fields[0].AsInt64; This approach is ugly IMHO. So I use INSERT X (FIELDS) VALUES (PARAMS) RETURNING PK, returning the generated ID in same SQL cursor which I used to prepare my statement. This idea is from JDBC and I'm using it daily. Works fine in all popular SGBDs. =) Even when the SGBD does not provides the RETURNING feature, I use the SQL cursor from the database driver to generate it without TDataset.Open. My complete code works only in FPC 2.7.1+, but in short words I get the generated ID via buffer (pointer) on database driver. -- Silvio Clécio My public projects - github.com/silvioprog -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt mich...@freepascal.org wrote: [...] Connections for databases that have lastinsertID you can call the method created for this: Function GetInsertID: int64; There is a plan to let SQLDB retrieve the value of some fields fro the DB, but it is not implemented yet. Awesome news! =) Some branch to follow this new implementations? (trunk?) Thanks! -- Silvio Clécio My public projects - github.com/silvioprog -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014, Marcos Douglas wrote: On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt mich...@freepascal.org wrote: On Fri, 14 Nov 2014, Reimar Grabowski wrote: On Thu, 13 Nov 2014 13:26:32 -0300 Marcos Douglas m...@delfire.net wrote: Hi, Take a look in Greyhound project: https://github.com/mdbs99/Greyhound I did and could not see how it helps. Here you can see an example using last id: https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285 Yes, if you are using Greyhound, which I am not. So it looks like there's nothing in TSQLQuery that does the job for me. I now just use another TSQLQuery with the following SQL: SELECT LAST_INSERT_ID() AS SomeNameIChoose; Seems to work well, reduces the lines of code as I don't have to set parameters (my primary goal), can be reused for other inserts (being not table dependent) and took about 1 Minute to implement. Connections for databases that have lastinsertID you can call the method created for this: Function GetInsertID: int64; There is a plan to let SQLDB retrieve the value of some fields fro the DB, but it is not implemented yet. Hi Michael, To do that is need to implement a GetInsertID method in all drivers of SQLdb and include a code, like bellow, in TSQLQuery.ApplyRecUpdate: No. Not all SQL engines support lastID. Postgres and Firebird don't. A more general mechanism is needed. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014, silvioprog wrote: On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt mich...@freepascal.org wrote: [...] Connections for databases that have lastinsertID you can call the method created for this: Function GetInsertID: int64; There is a plan to let SQLDB retrieve the value of some fields fro the DB, but it is not implemented yet. Awesome news! =) Some branch to follow this new implementations? (trunk?) It will be implemented in trunk. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014 14:15:08 +0100 (CET) Michael Van Canneyt mich...@freepascal.org wrote: Connections for databases that have lastinsertID you can call the method created for this: Function GetInsertID: int64; That is actually exactly what I was looking for. I was just too dense to realize that it belongs to the connection, not the query and didn't look for it there. Shame on me. Thanks a lot R. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 10:39 AM, Michael Van Canneyt mich...@freepascal.org wrote: On Fri, 14 Nov 2014, Marcos Douglas wrote: On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt mich...@freepascal.org wrote: On Fri, 14 Nov 2014, Reimar Grabowski wrote: On Thu, 13 Nov 2014 13:26:32 -0300 Marcos Douglas m...@delfire.net wrote: Hi, Take a look in Greyhound project: https://github.com/mdbs99/Greyhound I did and could not see how it helps. Here you can see an example using last id: https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285 Yes, if you are using Greyhound, which I am not. So it looks like there's nothing in TSQLQuery that does the job for me. I now just use another TSQLQuery with the following SQL: SELECT LAST_INSERT_ID() AS SomeNameIChoose; Seems to work well, reduces the lines of code as I don't have to set parameters (my primary goal), can be reused for other inserts (being not table dependent) and took about 1 Minute to implement. Connections for databases that have lastinsertID you can call the method created for this: Function GetInsertID: int64; There is a plan to let SQLDB retrieve the value of some fields fro the DB, but it is not implemented yet. Hi Michael, To do that is need to implement a GetInsertID method in all drivers of SQLdb and include a code, like bellow, in TSQLQuery.ApplyRecUpdate: No. Not all SQL engines support lastID. Postgres and Firebird don't. I know. For these the method returns 0 or the user need to code some more to work. If a DMBS does not support transaction, for example, I can't implement transactions on SQLdb? Is better to implement transactions for all DMBS that works and do nothing in few cases that not supported. The same for auto-inc, IMHO. A more general mechanism is needed. The code I posted is general, only in TSQLQuery.ApplyRecUpdate method. In my example before, FLib instance (Greyhound's code) would be a driver for SQLdb that will call GetLastAutoIncValue. A code for each DBMS driver. Marcos Douglas -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 10:40 AM, Michael Van Canneyt mich...@freepascal.org wrote: On Fri, 14 Nov 2014, silvioprog wrote: On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt mich...@freepascal.org wrote: [...] Connections for databases that have lastinsertID you can call the method created for this: Function GetInsertID: int64; There is a plan to let SQLDB retrieve the value of some fields fro the DB, but it is not implemented yet. Awesome news! =) Some branch to follow this new implementations? (trunk?) It will be implemented in trunk. If you do not put the value, automatically, on register that you included... what the point? IMHO this is not a general code. The user can implements the same but he will need to put the value on record... manually. The code I showed before works in any DMBS. Just test it. Best regards, Marcos Douglas -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014, Marcos Douglas wrote: On Fri, Nov 14, 2014 at 10:39 AM, Michael Van Canneyt mich...@freepascal.org wrote: On Fri, 14 Nov 2014, Marcos Douglas wrote: On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt mich...@freepascal.org wrote: On Fri, 14 Nov 2014, Reimar Grabowski wrote: On Thu, 13 Nov 2014 13:26:32 -0300 Marcos Douglas m...@delfire.net wrote: Hi, Take a look in Greyhound project: https://github.com/mdbs99/Greyhound I did and could not see how it helps. Here you can see an example using last id: https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285 Yes, if you are using Greyhound, which I am not. So it looks like there's nothing in TSQLQuery that does the job for me. I now just use another TSQLQuery with the following SQL: SELECT LAST_INSERT_ID() AS SomeNameIChoose; Seems to work well, reduces the lines of code as I don't have to set parameters (my primary goal), can be reused for other inserts (being not table dependent) and took about 1 Minute to implement. Connections for databases that have lastinsertID you can call the method created for this: Function GetInsertID: int64; There is a plan to let SQLDB retrieve the value of some fields fro the DB, but it is not implemented yet. Hi Michael, To do that is need to implement a GetInsertID method in all drivers of SQLdb and include a code, like bellow, in TSQLQuery.ApplyRecUpdate: No. Not all SQL engines support lastID. Postgres and Firebird don't. I know. For these the method returns 0 or the user need to code some more to work. If a DMBS does not support transaction, for example, I can't implement transactions on SQLdb? Is better to implement transactions for all DMBS that works and do nothing in few cases that not supported. The same for auto-inc, IMHO. No worries. We will implement it, but simply not in the way you suggest. A more general mechanism is needed. The code I posted is general, only in TSQLQuery.ApplyRecUpdate method. In my example before, FLib instance (Greyhound's code) would be a driver for SQLdb that will call GetLastAutoIncValue. A code for each DBMS driver. By the very nature of the problem: GetLastAutoIncValue simply cannot be implemented in general. In firebird, postgres, these values are generated using a generator/sequence. You don't know the name of the generator, and even if you did, it is impossible to retrieve the value that was used to fill your particular record because the sequence/generator may have been updated several thousands of times by the time your second statement arrives. The only way to do this correctly is using a RETURNING clause when doing the insert. The idea is to add [pfRefresh] to providerflags. When doing an insert, the fields with this flag will be added to the RETURNING clause and used to update the buffer. Fields of type ftautoInc can be added to this list (i.e. they automatically get pfRefresh in their providerflags) This approach will work on all SQL databases that support RETURNING. (That should include any SQL database available today) Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 10:50 AM, Reimar Grabowski reimg...@web.de wrote: On Fri, 14 Nov 2014 14:15:08 +0100 (CET) Michael Van Canneyt mich...@freepascal.org wrote: Connections for databases that have lastinsertID you can call the method created for this: Function GetInsertID: int64; That is actually exactly what I was looking for. I was just too dense to realize that it belongs to the connection, not the query and didn't look for it there. Shame on me. Thanks a lot +1 ID/Sequence generation must work out of transactions. It will be a nice new feature in SQLdb! =) Currently I use it via cursor from the database driver. It is very very fast and is transaction independent. =) -- Silvio Clécio My public projects - github.com/silvioprog -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 10:40 AM, Michael Van Canneyt mich...@freepascal.org wrote: On Fri, 14 Nov 2014, silvioprog wrote: On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt mich...@freepascal.org wrote: [...] Connections for databases that have lastinsertID you can call the method created for this: Function GetInsertID: int64; There is a plan to let SQLDB retrieve the value of some fields fro the DB, but it is not implemented yet. Awesome news! =) Some branch to follow this new implementations? (trunk?) It will be implemented in trunk. Thanks dude. I'll follow it there! (y) -- Silvio Clécio My public projects - github.com/silvioprog -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Friday 14 November 2014 15:11:30 Michael Van Canneyt wrote: The idea is to add [pfRefresh] to providerflags. When doing an insert, the fields with this flag will be added to the RETURNING clause and used to update the buffer. I suggest [pfRefreshInsert,pfRefreshUpdate] as in MSEgui. MSEgui (and AFAIK Zeos) also automatically updates a simple master key field by LASTINSERTID if the DB supports it. Martin -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 11:11 AM, Michael Van Canneyt mich...@freepascal.org wrote: On Fri, 14 Nov 2014, Marcos Douglas wrote: On Fri, Nov 14, 2014 at 10:39 AM, Michael Van Canneyt mich...@freepascal.org wrote: On Fri, 14 Nov 2014, Marcos Douglas wrote: On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt mich...@freepascal.org wrote: On Fri, 14 Nov 2014, Reimar Grabowski wrote: On Thu, 13 Nov 2014 13:26:32 -0300 Marcos Douglas m...@delfire.net wrote: Hi, Take a look in Greyhound project: https://github.com/mdbs99/Greyhound I did and could not see how it helps. Here you can see an example using last id: https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285 Yes, if you are using Greyhound, which I am not. So it looks like there's nothing in TSQLQuery that does the job for me. I now just use another TSQLQuery with the following SQL: SELECT LAST_INSERT_ID() AS SomeNameIChoose; Seems to work well, reduces the lines of code as I don't have to set parameters (my primary goal), can be reused for other inserts (being not table dependent) and took about 1 Minute to implement. Connections for databases that have lastinsertID you can call the method created for this: Function GetInsertID: int64; There is a plan to let SQLDB retrieve the value of some fields fro the DB, but it is not implemented yet. Hi Michael, To do that is need to implement a GetInsertID method in all drivers of SQLdb and include a code, like bellow, in TSQLQuery.ApplyRecUpdate: No. Not all SQL engines support lastID. Postgres and Firebird don't. I know. For these the method returns 0 or the user need to code some more to work. If a DMBS does not support transaction, for example, I can't implement transactions on SQLdb? Is better to implement transactions for all DMBS that works and do nothing in few cases that not supported. The same for auto-inc, IMHO. No worries. We will implement it, but simply not in the way you suggest. A more general mechanism is needed. The code I posted is general, only in TSQLQuery.ApplyRecUpdate method. In my example before, FLib instance (Greyhound's code) would be a driver for SQLdb that will call GetLastAutoIncValue. A code for each DBMS driver. By the very nature of the problem: GetLastAutoIncValue simply cannot be implemented in general. In firebird, postgres, these values are generated using a generator/sequence. ...as well as could have a trigger for these cases, encapsulating the problem in database... but the code in these cases should be different. You don't know the name of the generator, and even if you did, it is impossible to retrieve the value that was used to fill your particular record because the sequence/generator may have been updated several thousands of times by the time your second statement arrives. If you use a sequence/generator, you should call it before INSERT. If you use auto-inc, the DMBS have support to return the last id on your session. The only way to do this correctly is using a RETURNING clause when doing the insert. This RETURNING clause works for all DBMS? The idea is to add [pfRefresh] to providerflags. When doing an insert, the fields with this flag will be added to the RETURNING clause and used to update the buffer. Fields of type ftautoInc can be added to this list (i.e. they automatically get pfRefresh in their providerflags) This approach will work on all SQL databases that support RETURNING. (That should include any SQL database available today) Nice. But this is only a part of code. How do you will get the last ID and put in record? Marcos Douglas -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 11:11 AM, Michael Van Canneyt mich...@freepascal.org wrote: [...] By the very nature of the problem: GetLastAutoIncValue simply cannot be implemented in general. In firebird, postgres, these values are generated using a generator/sequence. You don't know the name of the generator, and even if you did, it is impossible to retrieve the value that was used to fill your particular record because the sequence/generator may have been updated several thousands of times by the time your second statement arrives. The only way to do this correctly is using a RETURNING clause when doing the insert. Indded. The idea is to add [pfRefresh] to providerflags. When doing an insert, the fields with this flag will be added to the RETURNING clause and used to update the buffer. Fields of type ftautoInc can be added to this list (i.e. they automatically get pfRefresh in their providerflags) This approach will work on all SQL databases that support RETURNING. (That should include any SQL database available today) Just a suggestion: it would be nice to implement it using interface or abstract class (like ConnectionDef). So if a database X does not provide the RETURNING support, a mortal programmer could do that by himself on his own project. =D E.g: // implemented in FPC PQConnection.pas, using the RETURNING feature TPGSequence = class(..., ISQLSequence) .. function GetLastId: int64; // implemented in programmer project, using X feature TXSequence = class(..., ISQLSequence) .. function GetLastId: int64; So: PQConnector1.SetSequencer(TPGSequence.Create); or: XYZConnector1.SetSequencer(TXSequence.Create); -- Silvio Clécio My public projects - github.com/silvioprog -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014, Martin Schreiber wrote: On Friday 14 November 2014 15:11:30 Michael Van Canneyt wrote: The idea is to add [pfRefresh] to providerflags. When doing an insert, the fields with this flag will be added to the RETURNING clause and used to update the buffer. I suggest [pfRefreshInsert,pfRefreshUpdate] as in MSEgui. MSEgui (and AFAIK Zeos) also automatically updates a simple master key field by LASTINSERTID if the DB supports it. No problem. I didn't know MSEGui has it, and the idea to use 2 different flags for insert/update is good :) Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014, Marcos Douglas wrote: By the very nature of the problem: GetLastAutoIncValue simply cannot be implemented in general. In firebird, postgres, these values are generated using a generator/sequence. ...as well as could have a trigger for these cases, encapsulating the problem in database... but the code in these cases should be different. I know. RETURNING will handle this case correctly. You don't know the name of the generator, and even if you did, it is impossible to retrieve the value that was used to fill your particular record because the sequence/generator may have been updated several thousands of times by the time your second statement arrives. If you use a sequence/generator, you should call it before INSERT. That is one way, but not obligatory. Mostly it is done in triggers. This is not a problem. RETURNING handles all cases. If you use auto-inc, the DMBS have support to return the last id on your session. I know. The only way to do this correctly is using a RETURNING clause when doing the insert. This RETURNING clause works for all DBMS? AFAIK Only sqlite does not (maybe it does today). But that is not a DBMS :-) The idea is to add [pfRefresh] to providerflags. When doing an insert, the fields with this flag will be added to the RETURNING clause and used to update the buffer. Fields of type ftautoInc can be added to this list (i.e. they automatically get pfRefresh in their providerflags) This approach will work on all SQL databases that support RETURNING. (That should include any SQL database available today) Nice. But this is only a part of code. How do you will get the last ID and put in record? This is the complete code, because the returning clause will contain the ID. Your remarks sound like maybe you don't understand/know exactly what RETURNING does ? It was invented exactly to deal with such things. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
2014-11-14 11:43 GMT-03:00 Michael Van Canneyt mich...@freepascal.org:
On Fri, 14 Nov 2014, Marcos Douglas wrote:
This RETURNING clause works for all DBMS?
AFAIK Only sqlite does not (maybe it does today). But that is not a DBMS
:-)
MySQL also does not implement it
This is the workaround i use today:
var
Info: TSQLStatementInfo;
ActualConnection: TSQLConnection;
InsertQuery: TSQLQuery;
begin
Result := '';
if (FConnection is TSQLConnector) then
ActualConnection := TSQLConnectorAccess(FConnection).Proxy
else
ActualConnection := FConnection;
Info :=
TSQLConnectionAccess(ActualConnection).GetStatementInfo(Query.SQL.Text,
True, stNoSchema);
InsertQuery := CreateInsertQuery(Query, Info.TableName,
ActualConnection.FieldNameQuoteChars);
try
if (ActualConnection is TPQConnection) or (ActualConnection is
TIBConnection) then
begin
InsertQuery.SQL.Add(Format('Returning %s', [FPrimaryKey]));
InsertQuery.Open;
if InsertQuery.RecordCount 0 then
Result := InsertQuery.Fields[0].AsString;
ActualConnection.Transaction.CommitRetaining;
end
else
begin
InsertQuery.ExecSQL;
ActualConnection.Transaction.CommitRetaining;
if (ActualConnection is TSQLite3Connection) then
Result := IntToStr(TSQLite3Connection(ActualConnection).GetInsertID)
else if (ActualConnection is TConnectionName{MySql}) then
Result := IntToStr(TConnectionName(ActualConnection).GetInsertID);
end;
finally
InsertQuery.Destroy;
end;
end;
Luiz
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014, silvioprog wrote: On Fri, Nov 14, 2014 at 11:11 AM, Michael Van Canneyt mich...@freepascal.org wrote: [...] By the very nature of the problem: GetLastAutoIncValue simply cannot be implemented in general. In firebird, postgres, these values are generated using a generator/sequence. You don't know the name of the generator, and even if you did, it is impossible to retrieve the value that was used to fill your particular record because the sequence/generator may have been updated several thousands of times by the time your second statement arrives. The only way to do this correctly is using a RETURNING clause when doing the insert. Indded. The idea is to add [pfRefresh] to providerflags. When doing an insert, the fields with this flag will be added to the RETURNING clause and used to update the buffer. Fields of type ftautoInc can be added to this list (i.e. they automatically get pfRefresh in their providerflags) This approach will work on all SQL databases that support RETURNING. (That should include any SQL database available today) Just a suggestion: it would be nice to implement it using interface or abstract class (like ConnectionDef). So if a database X does not provide the RETURNING support, a mortal programmer could do that by himself on his own project. =D I was planning some fallback mechanism, yes. And a class function SupportReturningClause : Boolean or something similar. Or maybe TDBCapability = (lastID,returningclause); TDBCapabilities = set of TDBCapability; and Class function DatabaseCapabilities : TDBCapabilities; I don't really like using interfaces when things can be done more simple. Michael.-- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014, luiz americo pereira camara wrote: 2014-11-14 11:43 GMT-03:00 Michael Van Canneyt mich...@freepascal.org: On Fri, 14 Nov 2014, Marcos Douglas wrote: This RETURNING clause works for all DBMS? AFAIK Only sqlite does not (maybe it does today). But that is not a DBMS :-) MySQL also does not implement it Hm. I thought it did. Well, it shows that MySQL is even less of a RDBMS than sqlite. People trusting their data to these databases don't care about their data :) No problem: Like I said, there will be a fallback mechanism. I understand sometimes you don't have a choice what database to use, so we must cater for that as well. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 11:43 AM, Michael Van Canneyt
mich...@freepascal.org wrote:
[...]
AFAIK Only sqlite does not (maybe it does today). But that is not a DBMS
:-)
Yes. It would be nice execute the last_insert_rowid() to return it in the
same cursor (AFAIK, Android SDK use this mechanism in the
nativeExecuteForLastInsertedRowId). So it can be same to the MySQL
(LAST_INSERT_ID()). =)
And you can use the TSQLite3Connection.getinsertid method, no? =/
E.g in a simple raw query in MySQL:
===
INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();
This will get you back the PRIMARY KEY value of the last row that *you*
inserted:
The ID that was generated is maintained in the server on a *per-connection
basis*. This means that the value returned by the function to a given
client is the first AUTO_INCREMENT value generated for most recent
statement affecting an AUTO_INCREMENT column *by that client*.
So this is unaffected by other queries that might be running on the server
from other users.
S:
http://stackoverflow.com/questions/17112852/get-the-new-record-primary-key-id-from-mysql-insert-query
===
--
Silvio Clécio
My public projects - github.com/silvioprog
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 11:47 AM, Michael Van Canneyt mich...@freepascal.org wrote: [...] I was planning some fallback mechanism, yes. And a class function SupportReturningClause : Boolean or something similar. Or maybe TDBCapability = (lastID,returningclause); TDBCapabilities = set of TDBCapability; and Class function DatabaseCapabilities : TDBCapabilities; I don't really like using interfaces when things can be done more simple. Good too dude. =) Protected virtual methods would be welcome too. =) -- Silvio Clécio My public projects - github.com/silvioprog -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 12:49 PM, silvioprog silviop...@gmail.com wrote:
On Fri, Nov 14, 2014 at 11:43 AM, Michael Van Canneyt
mich...@freepascal.org wrote:
[...]
AFAIK Only sqlite does not (maybe it does today). But that is not a DBMS
:-)
Yes. It would be nice execute the last_insert_rowid() to return it in
the same cursor (AFAIK, Android SDK use this mechanism in the
nativeExecuteForLastInsertedRowId). So it can be same to the MySQL
(LAST_INSERT_ID()). =)
And you can use the TSQLite3Connection.getinsertid method, no? =/
It works fine in SQLite (my test table have ten records and test2 one
record):
procedure TForm1.Button1Click(Sender: TObject);
var
id1, id2: Int64;
begin
SQLQuery1.Close;
SQLQuery1.SQL.Text := 'insert into test (foo) values (''TEST'')';
SQLQuery1.ExecSQL;
id1 := SQLite3Connection1.GetInsertID;
SQLQuery2.Close;
SQLQuery2.SQL.Text := 'insert into test2 (foo) values (''TEST'');';
SQLQuery2.ExecSQL;
id2 := SQLite3Connection1.GetInsertID;
ShowMessageFmt('ID1: %d; ID2: %d', [id1, id2]);
end;
[Window Title]
project1
[Content]
ID1: 11; ID2: 2
[OK]
I'll test it in MySQL ...
--
Silvio Clécio
My public projects - github.com/silvioprog
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014 12:49:44 -0300 silvioprog silviop...@gmail.com wrote: snip This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column *by that client*. Just to clarify, the 'first' here is important as it means that if you insert multiple rows in one statement you will get the first generated id and not the last one as some might expect (see http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id). R. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 11:43 AM, Michael Van Canneyt mich...@freepascal.org wrote: On Fri, 14 Nov 2014, Marcos Douglas wrote: By the very nature of the problem: GetLastAutoIncValue simply cannot be implemented in general. In firebird, postgres, these values are generated using a generator/sequence. ...as well as could have a trigger for these cases, encapsulating the problem in database... but the code in these cases should be different. I know. RETURNING will handle this case correctly. You don't know the name of the generator, and even if you did, it is impossible to retrieve the value that was used to fill your particular record because the sequence/generator may have been updated several thousands of times by the time your second statement arrives. If you use a sequence/generator, you should call it before INSERT. That is one way, but not obligatory. Mostly it is done in triggers. This is not a problem. RETURNING handles all cases. If you use auto-inc, the DMBS have support to return the last id on your session. I know. The only way to do this correctly is using a RETURNING clause when doing the insert. This RETURNING clause works for all DBMS? AFAIK Only sqlite does not (maybe it does today). But that is not a DBMS :-) Of course. :-) The idea is to add [pfRefresh] to providerflags. When doing an insert, the fields with this flag will be added to the RETURNING clause and used to update the buffer. Fields of type ftautoInc can be added to this list (i.e. they automatically get pfRefresh in their providerflags) This approach will work on all SQL databases that support RETURNING. (That should include any SQL database available today) Nice. But this is only a part of code. How do you will get the last ID and put in record? This is the complete code, because the returning clause will contain the ID. Your remarks sound like maybe you don't understand/know exactly what RETURNING does ? It was invented exactly to deal with such things. AFAIK RETURNING is a SQL clause. I know that but I never used before. You tell me SQLdb will use RETURNING clause if the DMBS have support, right? Well, AFAIK MSSQL does not have support for this... but MSSQL have IDENTITY (auto-inc) fields. How I will use RETURNING on MSSQL? If this clause don't exists -- if already exists, sorry -- how I can get the ID auto-inc? Please, don't tell me that will be implemented a parser to search a RETURNING clause on SQL, cut this part, execute, do other things to get the ID and paste the RETURNING again. :) Marcos Douglas -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014, Marcos Douglas wrote: Your remarks sound like maybe you don't understand/know exactly what RETURNING does ? It was invented exactly to deal with such things. AFAIK RETURNING is a SQL clause. I know that but I never used before. You tell me SQLdb will use RETURNING clause if the DMBS have support, right? Well, AFAIK MSSQL does not have support for this... but MSSQL have IDENTITY (auto-inc) fields. How I will use RETURNING on MSSQL? If this clause don't exists -- if already exists, sorry -- how I can get the ID auto-inc? Please, don't tell me that will be implemented a parser to search a RETURNING clause on SQL, cut this part, execute, do other things to get the ID and paste the RETURNING again. :) Nono. What I have in mind is the following: Add pfRefreshUpdate/pfRefreshInsert for ProviderFLags Then: a) If the DB engine supports RETURNING clause (it seems 3 engines do, 3 don't), that will be used to fetch the values. This is the most optimal approach. b) For engines that do not support returning there is the option to specify a refresh SQL statement. (coincidentally: these are the engines that use autoInc type fields instead of sequences) the 'last_insert_id' parameter for this statement will be detected (using the reserved name or the name of the ftAutoInc field) and filled with the contents of the last generated ID. This if the engine supports getting the last insert id, this refresh statement will be constructed and automatically used in 2 cases: 1) if pfRefreshUpdate/pfRefreshInsert is found in the list of fields. 2) if no pfRefreshUpdate/pfRefreshInsert is found in the list of fields, but there is 1 ftAutoInc field and the database engine supports getting the last generated ID. That should cover most, if not all, cases where a refresh is needed, with minimal interference of the programmer... Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 2:11 PM, Michael Van Canneyt mich...@freepascal.org wrote: On Fri, 14 Nov 2014, Marcos Douglas wrote: Your remarks sound like maybe you don't understand/know exactly what RETURNING does ? It was invented exactly to deal with such things. AFAIK RETURNING is a SQL clause. I know that but I never used before. You tell me SQLdb will use RETURNING clause if the DMBS have support, right? Well, AFAIK MSSQL does not have support for this... but MSSQL have IDENTITY (auto-inc) fields. How I will use RETURNING on MSSQL? If this clause don't exists -- if already exists, sorry -- how I can get the ID auto-inc? Please, don't tell me that will be implemented a parser to search a RETURNING clause on SQL, cut this part, execute, do other things to get the ID and paste the RETURNING again. :) Nono. :) What I have in mind is the following: Add pfRefreshUpdate/pfRefreshInsert for ProviderFLags Then: a) If the DB engine supports RETURNING clause (it seems 3 engines do, 3 don't), that will be used to fetch the values. This is the most optimal approach. OK b) For engines that do not support returning there is the option to specify a refresh SQL statement. (coincidentally: these are the engines that use autoInc type fields instead of sequences) This option, to specify a SQL statement, is the programmer that write the SQL? This should come from driver, don't? the 'last_insert_id' parameter for this statement will be detected (using the reserved name or the name of the ftAutoInc field) and filled with the contents of the last generated ID. This if the engine supports getting the last insert id, this refresh statement will be constructed and automatically used in 2 cases: 1) if pfRefreshUpdate/pfRefreshInsert is found in the list of fields. 2) if no pfRefreshUpdate/pfRefreshInsert is found in the list of fields, but there is 1 ftAutoInc field and the database engine supports getting the last generated ID. OK That should cover most, if not all, cases where a refresh is needed, with minimal interference of the programmer... Looks good. Marcos Douglas -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014 14:50:20 +0100
Reimar Grabowski reimg...@web.de wrote:
On Fri, 14 Nov 2014 14:15:08 +0100 (CET)
Michael Van Canneyt mich...@freepascal.org wrote:
Connections for databases that have lastinsertID you can call the method
created for this:
Function GetInsertID: int64;
Unfortunately it does not work and returns only 0, while my custom query using
LAST_INSERT_ID() works as expected.
Debugging this is very hard as Lazarus crashes on me left and right when trying
to step through the code and an exception is encountered.
I have one connection, one transaction and some queries bound to them on a
FPWebModule.
ID1:=MySQL55Connection1.GetInsertID;
SQLQuery1.Open;
ID2:=SQLQuery1.FieldByName('LastInsertID').AsLargeInt;
ID1 is 0 and ID2 the correct id value.
Any ideas what I could be doing wrong?
R.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014, Reimar Grabowski wrote:
On Fri, 14 Nov 2014 14:50:20 +0100
Reimar Grabowski reimg...@web.de wrote:
On Fri, 14 Nov 2014 14:15:08 +0100 (CET)
Michael Van Canneyt mich...@freepascal.org wrote:
Connections for databases that have lastinsertID you can call the method
created for this:
Function GetInsertID: int64;
Unfortunately it does not work and returns only 0, while my custom query using
LAST_INSERT_ID() works as expected.
Debugging this is very hard as Lazarus crashes on me left and right when trying
to step through the code and an exception is encountered.
I have one connection, one transaction and some queries bound to them on a
FPWebModule.
ID1:=MySQL55Connection1.GetInsertID;
SQLQuery1.Open;
ID2:=SQLQuery1.FieldByName('LastInsertID').AsLargeInt;
ID1 is 0 and ID2 the correct id value.
Any ideas what I could be doing wrong?
This is what it does:
function TConnectionName.GetInsertID: Int64;
begin
CheckConnected;
Result:=mysql_insert_id(GetHandle);
end;
a) Did you do an insert right before the call to GetInsertID ?
b) Is the transaction active after the insert ?
c) The connection transaction should be the same as the transaction of the insert.
Michael.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014, Marcos Douglas wrote: specify a refresh SQL statement. (coincidentally: these are the engines that use autoInc type fields instead of sequences) This option, to specify a SQL statement, is the programmer that write the SQL? This should come from driver, don't? If the driver can create it: yes. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 3:23 PM, Michael Van Canneyt mich...@freepascal.org
wrote:
On Fri, 14 Nov 2014, Reimar Grabowski wrote:
On Fri, 14 Nov 2014 14:50:20 +0100
Reimar Grabowski reimg...@web.de wrote:
On Fri, 14 Nov 2014 14:15:08 +0100 (CET)
Michael Van Canneyt mich...@freepascal.org wrote:
Connections for databases that have lastinsertID you can call the
method created for this:
Function GetInsertID: int64;
Unfortunately it does not work and returns only 0, while my custom query
using LAST_INSERT_ID() works as expected.
Debugging this is very hard as Lazarus crashes on me left and right when
trying to step through the code and an exception is encountered.
I have one connection, one transaction and some queries bound to them on
a FPWebModule.
ID1:=MySQL55Connection1.GetInsertID;
SQLQuery1.Open;
ID2:=SQLQuery1.FieldByName('LastInsertID').AsLargeInt;
ID1 is 0 and ID2 the correct id value.
Any ideas what I could be doing wrong?
This is what it does:
function TConnectionName.GetInsertID: Int64;
begin
CheckConnected;
Result:=mysql_insert_id(GetHandle);
end;
a) Did you do an insert right before the call to GetInsertID ?
b) Is the transaction active after the insert ? c) The connection
transaction should be the same as the transaction of the insert.
Work fine here (MySQL 5.5 32 bits / Lazarus 1.2.6 r46529 FPC 2.6.4
i386-win32-win32/win64 / Windows 7 64 bits):
unit1.lfm:
object Form1: TForm1
Left = 238
Height = 240
Top = 144
Width = 320
Caption = 'Form1'
ClientHeight = 240
ClientWidth = 320
LCLVersion = '1.2.6.0'
object Button1: TButton
Left = 10
Height = 25
Top = 8
Width = 75
Caption = 'Button1'
OnClick = Button1Click
TabOrder = 0
end
object MySQL55Connection1: TMySQL55Connection
Connected = False
LoginPrompt = False
DatabaseName = 'test'
KeepConnection = False
Password = 'root'
Transaction = SQLTransaction1
UserName = 'root'
HostName = '127.0.0.1'
LogEvents = []
left = 40
top = 16
end
object SQLQuery1: TSQLQuery
FieldDefs =
Database = MySQL55Connection1
Transaction = SQLTransaction1
Params =
left = 88
top = 116
end
object SQLTransaction1: TSQLTransaction
Active = False
Database = MySQL55Connection1
left = 147
top = 45
end
end
...
unit1.pas:
unit Unit1;
{$mode objfpc}{$H+}
interface
uses
SysUtils, mysql55conn, sqldb, Forms, Dialogs, StdCtrls;
type
{ TForm1 }
TForm1 = class(TForm)
Button1: TButton;
MySQL55Connection1: TMySQL55Connection;
SQLQuery1: TSQLQuery;
SQLTransaction1: TSQLTransaction;
procedure Button1Click(Sender: TObject);
end;
var
Form1: TForm1;
implementation
{$R *.lfm}
{ TForm1 }
procedure TForm1.Button1Click(Sender: TObject);
begin
SQLQuery1.SQL.Text := 'insert into test (foo) values (:foo)';
SQLQuery1.Params.ParamByName('foo').AsString := DateTimeToStr(Now);
SQLQuery1.ExecSQL;
ShowMessageFmt('%d', [MySQL55Connection1.GetInsertID]);
end;
end.
Result after three clicks:
[Window Title]
project1
[Content]
4
[OK]
SQL:
-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: 14-Nov-2014 às 17:09
-- Versão do servidor: 5.5.40
-- PHP Version: 5.3.21
SET SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
SET time_zone = +00:00;
--
-- Database: `test`
--
--
--
-- Estrutura da tabela `test`
--
CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL,
`foo` varchar(20) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `test`
--
ALTER TABLE `test`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `test`
--
ALTER TABLE `test`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;
--
Silvio Clécio
My public projects - github.com/silvioprog
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 03:11:30PM +0100, Michael Van Canneyt wrote: By the very nature of the problem: GetLastAutoIncValue simply cannot be implemented in general. In firebird, postgres, (and Oracle afaik) these values are generated using a generator/sequence. You don't know the name of the generator, and even if you did, it is impossible to retrieve the value that was used to fill your particular record because the sequence/generator may have been updated several thousands of times by the time your second statement arrives. IIRC the currval from a sequence is per session, so if you didn't insert again in the same transaction that is ok too. Moverover there is lastval to avoid knowing the sequence name from http://www.postgresql.org/docs/8.4/static/functions-sequence.html lastval Return the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it fetches the value of the last sequence used by nextval in the current session. It is an error to call lastval if nextval has not yet been called in the current session. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, Nov 14, 2014 at 8:32 PM, Marco van de Voort mar...@stack.nl wrote: On Fri, Nov 14, 2014 at 03:11:30PM +0100, Michael Van Canneyt wrote: By the very nature of the problem: GetLastAutoIncValue simply cannot be implemented in general. In firebird, postgres, (and Oracle afaik) these values are generated using a generator/sequence. You don't know the name of the generator, and even if you did, it is impossible to retrieve the value that was used to fill your particular record because the sequence/generator may have been updated several thousands of times by the time your second statement arrives. IIRC the currval from a sequence is per session, so if you didn't insert again in the same transaction that is ok too. Moverover there is lastval to avoid knowing the sequence name from http://www.postgresql.org/docs/8.4/static/functions-sequence.html lastval Return the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it fetches the value of the last sequence used by nextval in the current session. It is an error to call lastval if nextval has not yet been called in the current session. And PostgreSQL provides the RETURNING feature too. =) -- Silvio Clécio My public projects - github.com/silvioprog -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Fri, 14 Nov 2014 19:23:50 +0100 (CET)
Michael Van Canneyt mich...@freepascal.org wrote:
a) Did you do an insert right before the call to GetInsertID ?
b) Is the transaction active after the insert ?
c) The connection transaction should be the same as the transaction of the
insert.
Got it working.
MySQL55Connection1.Connected:=True;
SQLTransaction1.Active:=True;
SQLQuery.Active:=True;
SQLQuery.Insert;
SQLQuery.FieldByName(...);
...
SQLQuery.Post;
SQLQuery.ApplyUpdates;
writeln('ID1: ', MySQL55Connection1.GetInsertID);
SQLTransaction1.Commit;
writeln('ID2: ', MySQL55Connection1.GetInsertID);
SQLQuery1.Open;
writeln('ID3: ', SQLQuery1.FieldByName('LastInserID').AsLargeInt);
ID1 and ID3 are correct but ID2 = 0
So GetInsertID works only before the commit.
Thanks
R.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
[Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
Hi,
first of all I am new to using Lazarus/FPC for database communication.
I have set up my database connection, transaction and queries via the Object
Inspector and all is working as it should. But I have a little problem getting
the PK value of a newly inserted record (the PK field is an auto-incremented
one).
Actually it's not really a problem, I just don't like the way I do it and found
no better one.
Here is some code:
SQLQuery.Insert;
SQLQuery.FieldByName('SomeField').AsString:=SomeValue;
SQLQuery.FieldByName('AnotherField').AsString:=AnotherValue;
... (setting more fields)
SQLQuery.Post;
SQLQuery.ApplyUpdates;
SQLTransaction.Commit;
// Now the part I don't like
SQLQuery.Close;
SQLQuery.Params.ParamByName('SomeField').AsString:=SomeValue;
SQLQuery.Params.ParamByName('AnotherField').AsString:=AnotherValue;
... (setting more params, the same as the fields in the insert)
SQLQuery.Open;
NewID:=SQLQuery.FieldByName('AutoIncrementedPrimaryKey').AsInteger;
It seems to work but it's IMHO not very nice.
Ideally I'd like to have TSQLQuery do it for me and offer me some property like
SQLQuery.LastInsertID or something along those lines.
Is there any way to get the new PK value without manually querying for the
inserted record?
BTW the wiki (http://wiki.freepascal.org/Working_With_TSQLQuery) mentions that
you should send 0 for the ID and add an appropriate 'ON DUPLICATE KEY' phrase
to your SQL. I did not do this. I just omitted the ID in my SQLQuery.InsertSQL
and AFAICT it works flawlessly.
R.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Thu, Nov 13, 2014 at 11:07 AM, Reimar Grabowski reimg...@web.de wrote:
Hi,
first of all I am new to using Lazarus/FPC for database communication.
I have set up my database connection, transaction and queries via the
Object Inspector and all is working as it should. But I have a little
problem getting the PK value of a newly inserted record (the PK field is an
auto-incremented one).
Actually it's not really a problem, I just don't like the way I do it and
found no better one.
Here is some code:
SQLQuery.Insert;
SQLQuery.FieldByName('SomeField').AsString:=SomeValue;
SQLQuery.FieldByName('AnotherField').AsString:=AnotherValue;
... (setting more fields)
SQLQuery.Post;
SQLQuery.ApplyUpdates;
SQLTransaction.Commit;
// Now the part I don't like
SQLQuery.Close;
SQLQuery.Params.ParamByName('SomeField').AsString:=SomeValue;
SQLQuery.Params.ParamByName('AnotherField').AsString:=AnotherValue;
... (setting more params, the same as the fields in the insert)
SQLQuery.Open;
NewID:=SQLQuery.FieldByName('AutoIncrementedPrimaryKey').AsInteger;
It seems to work but it's IMHO not very nice.
Ideally I'd like to have TSQLQuery do it for me and offer me some property
like SQLQuery.LastInsertID or something along those lines.
Is there any way to get the new PK value without manually querying for the
inserted record?
BTW the wiki (http://wiki.freepascal.org/Working_With_TSQLQuery) mentions
that you should send 0 for the ID and add an appropriate 'ON DUPLICATE KEY'
phrase to your SQL. I did not do this. I just omitted the ID in my
SQLQuery.InsertSQL and AFAICT it works flawlessly.
R.
I use an own TPreparedStatement (equivalent to TSQLQuery prepared +
Params, but my structure does not uses TDataset) that I get the last
inserted ID with (pseudo codes):
[code]
uses
Core.SQL.PreparedStatement, ...
VStmt := TPreparedStatementTMyObject.Create;
ID := VStmt.Execute; // get last inserted ID instead of rowsaffected
[/code]
or:
[code]
VStmt := TPreparedStatementTMyObject.Create;
ID := VStmt.Execute.Values.ID; // get last inserted ID
[/code]
or:
[code]
uses
Core.SQL.ConnectionFactory, ...
PK := TConnectionFactory.GetInstance(Context).Insert(MyObject);
[/code]
or:
[code]
TConnectionFactory.GetInstance(Context).Insert(MyObject);
WriteLn('The generated ID is: ', MyObject.Id);
[/code]
or with on conflict:
[code]
TConnectionFactory.GetInstance(Context).UpdateWithOnConflict(MyObject,
TConnection.CONFLICT_REPLACE);
WriteLn('The generated or existing ID is: ', MyObject.Id);
[/code]
I'll send it soon to the Github. =)
--
Silvio Clécio
My public projects - github.com/silvioprog
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
On Thu, Nov 13, 2014 at 11:07 AM, Reimar Grabowski reimg...@web.de wrote: Hi, first of all I am new to using Lazarus/FPC for database communication. I have set up my database connection, transaction and queries via the Object Inspector and all is working as it should. But I have a little problem getting the PK value of a newly inserted record (the PK field is an auto-incremented one). Actually it's not really a problem, I just don't like the way I do it and found no better one. [snip] Hi, Take a look in Greyhound project: https://github.com/mdbs99/Greyhound Here you can see an example using last id: https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285 Regards, Marcos Douglas -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
[Lazarus] TSQLQuery Error Database not assigned.
I'm trying to familiarize with MySql components, using mysql 5.1.
I downloaded the TryMySql example linked from the Wiki page, I've
changed TMySQL50Connection to TMySQL51Connection, and set in SQLQuery
ParseSQL=False and ReadOnly=true, as suggested, but when I try to run
it, the connect to server fails with the error Database not assigned!.
The code I'm using is:
MySQLConnection1.HostName := HostEdit.Text;
MySQLConnection1.UserName := UserEdit.Text;
MySQLConnection1.Password := PasswdEdit.Text;
MySQLConnection1.DatabaseName := 'mysql'; // MySQL is allways there!
ShowString('Opening a connection to server: ' + HostEdit.Text);
MySQLConnection1.Open;
// First lets get a list of available databases.
if MySQLConnection1.Connected then begin
ShowString('Connected to server: ' + HostEdit.Text);
ShowString('Retreiving list of available databases.');
SQLQuery1.SQL.Text := 'show databases';
SQLQuery1.Open; Fails here
This occurs both with Lazarus 1.02 and with Lazarus svn (rev.39655), and
fpc 2.6.0, in Linux (FC14) environment.
Useless to say that mysql from command line, with identical parameters
works as expected.
Am I doing something wrong, should I use fpc 2.6.1, set some extra
parameter, or what?
Any help is welcome!
Giuliano
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery Error Database not assigned.
On Wed, 26 Dec 2012, Giuliano Colla wrote:
I'm trying to familiarize with MySql components, using mysql 5.1.
I downloaded the TryMySql example linked from the Wiki page, I've changed
TMySQL50Connection to TMySQL51Connection, and set in SQLQuery ParseSQL=False
and ReadOnly=true, as suggested, but when I try to run it, the connect to
server fails with the error Database not assigned!.
The code I'm using is:
MySQLConnection1.HostName := HostEdit.Text;
MySQLConnection1.UserName := UserEdit.Text;
MySQLConnection1.Password := PasswdEdit.Text;
MySQLConnection1.DatabaseName := 'mysql'; // MySQL is allways there!
ShowString('Opening a connection to server: ' + HostEdit.Text);
MySQLConnection1.Open;
// First lets get a list of available databases.
if MySQLConnection1.Connected then begin
ShowString('Connected to server: ' + HostEdit.Text);
ShowString('Retreiving list of available databases.');
SQLQuery1.SQL.Text := 'show databases';
SQLQuery1.Open; Fails here
Did you set
SQLQuery1.Database:=MySQLConnection1;
?
Also 'Show databases' will - to my knowlegde - not work. It is not valid SQL.
as far as I know, it's a metadata statement that is handled by the mysql
client which translates it to an API call.
Michael.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery Error Database not assigned.
Another point is add a transaction on your project and assign it to
sqlconnection...
At least, i never connected without a transaction
Em 26/12/2012 12:37, Michael Van Canneyt mich...@freepascal.org
escreveu:
On Wed, 26 Dec 2012, Giuliano Colla wrote:
I'm trying to familiarize with MySql components, using mysql 5.1.
I downloaded the TryMySql example linked from the Wiki page, I've
changed TMySQL50Connection to TMySQL51Connection, and set in SQLQuery
ParseSQL=False and ReadOnly=true, as suggested, but when I try to run it,
the connect to server fails with the error Database not assigned!.
The code I'm using is:
MySQLConnection1.HostName := HostEdit.Text;
MySQLConnection1.UserName := UserEdit.Text;
MySQLConnection1.Password := PasswdEdit.Text;
MySQLConnection1.DatabaseName := 'mysql'; // MySQL is allways there!
ShowString('Opening a connection to server: ' + HostEdit.Text);
MySQLConnection1.Open;
// First lets get a list of available databases.
if MySQLConnection1.Connected then begin
ShowString('Connected to server: ' + HostEdit.Text);
ShowString('Retreiving list of available databases.');
SQLQuery1.SQL.Text := 'show databases';
SQLQuery1.Open; Fails here
Did you set
SQLQuery1.Database:=MySQLConnection1;
?
Also 'Show databases' will - to my knowlegde - not work. It is not valid
SQL.
as far as I know, it's a metadata statement that is handled by the mysql
client which translates it to an API call.
Michael.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery Error Database not assigned.
Il 26/12/2012 15:53, William Oliveira Ferreira ha scritto: Another point is add a transaction on your project and assign it to sqlconnection... At least, i never connected without a transaction Did you set SQLQuery1.Database:=MySQLConnection1; ? Also 'Show databases' will - to my knowlegde - not work. It is not valid SQL. as far as I know, it's a metadata statement that is handled by the mysql client which translates it to an API call. Thanks to both of you. Filling up SQLQuery and SQLTansaction properties (which most likely had been cleared from the example when changing TConnection from 5.0 to 5.1) now it works. BTW show databases appears to work, because databases are shown. Giuliano. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
[Lazarus] TSQLQuery limited to 255 characters in Unicode
Hi all, I'm using TSQLQuery on SQLite in order to manipulation very long unicode strings (in tibetan). On inserting there is no problem, everything in inserted correctly in the database, using this code, containing a SQLquery : procedure ExecuteSQL(pRequete: string; pCommit: boolean); var conn: TSQLConnection; query: TSQLQuery; begin query := TSQLQuery.Create(nil); try try conn := getConnection(_TypeConnection); query.DataBase := conn; query.UsePrimaryKeyAsKey:=false; query.SQL.clear; query.SQL.Add(pRequete); query.ExecSQL; // COMMIT de la transaction et ouvre une nouvelle transaction if (pCommit = true) then conn.Transaction.CommitRetaining; query.Close; finally query.Free; //conn.Free; end; except on E: Exception do ShowMessage(E.message); end; end; BUT when I try to get the values, also with a SQLQuery, if the value is not unicode (ascii characters) I can get more than 255 chars, but if it's unicode (tibetan) the returned value is always cut to 255 characters. conn: TSQLConnection; query: TSQLQuery; str: widestring; i, n: integer; begin query := TSQLQuery.Create(nil); try try conn := getConnection(_TypeConnection); query.DataBase := conn; query.UsePrimaryKeyAsKey:=false; query.SQL.Text := pRequest; query.Open; query.Last; pGrid.Clean; n := 1; pGrid.RowCount := query.RecordCount + 1; pGrid.ColCount := pHeaders.Count; pGrid.FixedCols := pNbFixedCols; for i := 0 to pHeaders.Count - 1 do pGrid.Cells[i,0] := pHeaders.ValueFromIndex[i]; query.First; while not query.EOF do begin for i := 0 to pAttrs.Count - 1 do begin // HERE str is always cut str := query.FieldByName(pAttrs.ValueFromIndex[i]).AsWideString; pGrid.Cells[i,n] := str; end; query.Next; n := n + 1; end; // ajuste les colonnes au contenu pGrid.AutoAdjustColumns; finally query.Free; //conn.Free; end; except on E: Exception do ShowMessage(E.message); end; Would somebody explain my why the value is always cut and give me a solution ? because didn't find any explaination of the internet, and as it's working in one way but not the other. Thank you very much for your answer. Hugues MOISY -- Utilisant le logiciel de courrier révolutionnaire d'Opera : http://www.opera.com/mail/ -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
[Lazarus] TSQLQuery state doesn't change after TSQLQuery.Open
Does it heappens to anyone? here, any of mysql TSQLQuery.Open changed TSQLQuery.State William de Oliveira Ferreira Bacharel em Sistemas de Informação -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery and creating a new record
On Mon, 2010-05-17 at 12:49 -0500, Albert Zuurbier wrote: would like to discuss (and hopefully learn) about other things I found. It always amazes me how people can have huge problems with the most simple things. It probably means that there has to be something wrong, somewhere. (Documentation?) Are you familiar with datasets in Delphi? It always amazes me how sharp developers/owners/creators react to innocent, dumb and (in the light of your reaction) arrogant emails. When will I ever learn to hit the right tone in my emails? And you are right offcourse that a InsertSQL property would be useless without parameters. If you want to provide your own InsertSQL (instead the one generated by sqldb) try: 'insert into table(field1,field2) values (:field1,:field2)' Updatesql: 'update table set field=:field, field2=:field2 where (field1=:old_field1) and (field2=:old_field2)' A delete statement should be easy now. IT WORKS! I definitively vote for documentation. Now, documentation is where I can do something. I cannot add anything to the development of TSQLQuery itself, so much is made more than clear. But, I am happy that the component works the way I expected it to work. Glad to hear. But if you're not doing something strange, it should also work with the default queries. Any idea why that doesn't work? Joost. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery and creating a new record
IT WORKS! I definitively vote for documentation. Now, documentation is where I can do something. I cannot add anything to the development of TSQLQuery itself, so much is made more than clear. But, I am happy that the component works the way I expected it to work. Glad to hear. But if you're not doing something strange, it should also work with the default queries. Any idea why that doesn't work? Well... After it properly worked (inserted a ApplyUpdates and a Refresh), I removed the SQL I created myself. And yes, it works. What I think happened is that because the id wasn't shown, my impression was that things didn't work. I also want the new record to show in a form. I found information on using SQL statements, which set me on the wrong path. Eventually I found the bug report and saw in it the confirmation that I wasn't the only one with the problem. Making the new autoincrement id visible immediately after inserting or appending a record, would solve confusion. However, I.understand the difficulty in implementing that. Albert -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TSQLQuery and creating a new record
On Sun, 2010-05-16 at 15:46 -0500, Albert Zuurbier wrote: Over the past few days I have tried creating and editing new records with the TSQLQuery component. I succeeded, but with quite a workaround. As stated in bug report 0016076 (not authored by me) TSQLQuery gives errors if you try to create a new record and think TSQLQuery would all handle it. The issue addressed in the bug report is that all databases address making new records in different ways. The suggestion is to provide additional SQL lines to gain control over that process. Here I would like to discuss (and hopefully learn) about other things I found. It always amazes me how people can have huge problems with the most simple things. It probably means that there has to be something wrong, somewhere. (Documentation?) Are you familiar with datasets in Delphi? The cited bug report is only slightly related to your problem, I think. The bug-report is about autoincrement fields. Those weren't detected properly. The problem that's not solved yet is that the generated number is not set in the current dataset, only when the dataset is fetched again. 1) TSQLQuery doesn't really create a new record at insert or append. Instead, the only thing that is done is adding a row in the dataset [InsertInternal doesn't do anything]. The insert is not done and at ApplyUpdates the insert is lost because the dataset doesn't know the difference between updates and inserts. The insert is lost, because the new row is used to try to update a record that doesn't exist result in a fail. However, the InsertSQL line is executed which leads to a new record and the next observation. If this doesn't work, you have to provide an example in which case it doesn't. Normally it does. 2) The InsertSQL is executed, but since these are fixed strings I cannot put my new values into the strings. Trying to change the InsertSQL leads to an error because you cannot change the InsertSQL on an active dataset (rightfully so). So, the InsertSQL can create a new record but not with the right values. What I would like to see here is a insert statement with parameters and a possibility to provide parameters. Is that already there? This contradicts the first point? In your first point InsertSQL wasn't executed? And you are right offcourse that a InsertSQL property would be useless without parameters. If you want to provide your own InsertSQL (instead the one generated by sqldb) try: 'insert into table(field1,field2) values (:field1,:field2)' Updatesql: 'update table set field=:field, field2=:field2 where (field1=:old_field1) and (field2=:old_field2)' A delete statement should be easy now. 3) At ApplyUpdates the dataset is forwarded to the database as a list of updates. Which doesn't necessarily have to be true. What I would suggest here is an implementation of the Unit of Work pattern (http://martinfowler.com/eaaCatalog/unitOfWork.html). Using the Unit of Work pattern you can make a difference between update, insert and delete actions. The pattern also allows to make the processing of the dataset one compact unit with some favorable effects on atomicity and concurrency. That's in principle how it is working. Only heavily optimized for the job at hand (cached-updates in a dataset) and with a lot of extra functionality and flexibility. 4) Creating an ID for a new record can be done at two moments a) at the moment you want the new record b) at the moment you really save the new record. With (a) you can use the ID in references immediately without too much complexity, with (b) you don't waist IDs at the moment you are not sure you will ever save the record. So, it would be nice to have a property to choose when the InsertSQL is executed. What's more flexible then letting you call ApplyUpdates whenever you want? It also depends on the used database-engine if you can use a or b. And a is always the best option. But there are a few database-engines from which the developers weren't that good at database design when they made their first implementation. (Access, MS SQL, MySQL) So they added the b design. But nowadays they all can use a also. (Except for Access, but, hey, that's not a serious database, and they don't pretend to be one too.) Nice thing with the TSQLQuery design is that you can use both. But option b needs some extra code in some cases. (Unless we can think about some way to do this automatically, see the bug report you started with) Of course, I am not experienced enough (with FreePascal) to implement this or even try to. But I hope my suggestions will help the development of TSQLQuery. I noticed there is quite a bit of discussion about the database components lately, but I don't know the development plans. Also, if I have overlooked anything I am happy to learn. There is always discussion about the database components. That will never stop. ;) But you didn't add any new information to the discussion. (Most people don't)
Re: [Lazarus] TSQLQuery and creating a new record
would like to discuss (and hopefully learn) about other things I found. It always amazes me how people can have huge problems with the most simple things. It probably means that there has to be something wrong, somewhere. (Documentation?) Are you familiar with datasets in Delphi? It always amazes me how sharp developers/owners/creators react to innocent, dumb and (in the light of your reaction) arrogant emails. When will I ever learn to hit the right tone in my emails? And you are right offcourse that a InsertSQL property would be useless without parameters. If you want to provide your own InsertSQL (instead the one generated by sqldb) try: 'insert into table(field1,field2) values (:field1,:field2)' Updatesql: 'update table set field=:field, field2=:field2 where (field1=:old_field1) and (field2=:old_field2)' A delete statement should be easy now. IT WORKS! I definitively vote for documentation. Now, documentation is where I can do something. I cannot add anything to the development of TSQLQuery itself, so much is made more than clear. But, I am happy that the component works the way I expected it to work. Thanks, Albert -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
[Lazarus] TSQLQuery and creating a new record
Over the past few days I have tried creating and editing new records with the TSQLQuery component. I succeeded, but with quite a workaround. As stated in bug report 0016076 (not authored by me) TSQLQuery gives errors if you try to create a new record and think TSQLQuery would all handle it. The issue addressed in the bug report is that all databases address making new records in different ways. The suggestion is to provide additional SQL lines to gain control over that process. Here I would like to discuss (and hopefully learn) about other things I found. 1) TSQLQuery doesn't really create a new record at insert or append. Instead, the only thing that is done is adding a row in the dataset [InsertInternal doesn't do anything]. The insert is not done and at ApplyUpdates the insert is lost because the dataset doesn't know the difference between updates and inserts. The insert is lost, because the new row is used to try to update a record that doesn't exist result in a fail. However, the InsertSQL line is executed which leads to a new record and the next observation. 2) The InsertSQL is executed, but since these are fixed strings I cannot put my new values into the strings. Trying to change the InsertSQL leads to an error because you cannot change the InsertSQL on an active dataset (rightfully so). So, the InsertSQL can create a new record but not with the right values. What I would like to see here is a insert statement with parameters and a possibility to provide parameters. Is that already there? 3) At ApplyUpdates the dataset is forwarded to the database as a list of updates. Which doesn't necessarily have to be true. What I would suggest here is an implementation of the Unit of Work pattern ( http://martinfowler.com/eaaCatalog/unitOfWork.html). Using the Unit of Work pattern you can make a difference between update, insert and delete actions. The pattern also allows to make the processing of the dataset one compact unit with some favorable effects on atomicity and concurrency. 4) Creating an ID for a new record can be done at two moments a) at the moment you want the new record b) at the moment you really save the new record. With (a) you can use the ID in references immediately without too much complexity, with (b) you don't waist IDs at the moment you are not sure you will ever save the record. So, it would be nice to have a property to choose when the InsertSQL is executed. Of course, I am not experienced enough (with FreePascal) to implement this or even try to. But I hope my suggestions will help the development of TSQLQuery. I noticed there is quite a bit of discussion about the database components lately, but I don't know the development plans. Also, if I have overlooked anything I am happy to learn. Regards, Albert -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
