[Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)

[Michael Van Canneyt]

On Fri, 14 Nov 2014, Reimar Grabowski wrote:

> On Fri, 14 Nov 2014 14:50:20 +0100
> Reimar Grabowski <reimgrab at web.de> wrote:
>
>> On Fri, 14 Nov 2014 14:15:08 +0100 (CET)
>> Michael Van Canneyt <michael at 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.