[Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)
Marcos Douglas
md at delfire.net
Fri Nov 14 14:21:57 CET 2014
On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt
<michael at freepascal.org> wrote:
>
>
> On Fri, 14 Nov 2014, Reimar Grabowski wrote:
>
>> On Thu, 13 Nov 2014 13:26:32 -0300
>> Marcos Douglas <md at 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
More information about the Lazarus
mailing list