Recent

Author Topic: Why does GetInsertID always return 0?  (Read 893 times)

kinlion

  • New member
  • *
  • Posts: 39
  • I Love Lazarus
Why does GetInsertID always return 0?
« on: January 11, 2019, 02:36:14 am »
Hi, guys,

I'm using TMySQL56Connection, connect to a MariaDB server,  which version is 10.2.18-MariaDB-10.2.18+maria~xenial-log.
Everything is OK except when I trying to get last insert id.

I insert a record, then if I using a query 'select last_insert_id()', the result is correct.
But if I use TConnectionName(TheQuery.Database).GetInsertID, it returns 0.
What's wrong?

« Last Edit: January 11, 2019, 03:35:59 am by kinlion »
Lazarus 1.8.4 / FPC 3.0.4 / SVN 57972
On Win10 X64, have to compile for Win32

madref

  • Hero Member
  • *****
  • Posts: 565
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Why does GetInsertID always return 0?
« Reply #1 on: January 16, 2019, 10:19:45 am »
this is an assumption. But if Last_ID is auto generated it is also the Max_ID.
Can't you try and get that one?
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.0 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

Leledumbo

  • Hero Member
  • *****
  • Posts: 8022
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Why does GetInsertID always return 0?
« Reply #2 on: January 17, 2019, 10:38:29 am »
I dig in the implementation, it seems correct, but I'm on trunk. No idea if it was already implemented in stable.
Code: Pascal  [Select]
  1. // $(fpcsrc)/packages/fcl-db/src/sqldb/mysql/mysqlconn.inc:378
  2. Function TConnectionName.GetInsertID: int64;
  3. begin
  4.   CheckConnected;
  5.   Result:=mysql_insert_id(GetHandle);
  6. end;
  7.  

kinlion

  • New member
  • *
  • Posts: 39
  • I Love Lazarus
Re: Why does GetInsertID always return 0?
« Reply #3 on: January 18, 2019, 02:24:08 am »
this is an assumption. But if Last_ID is auto generated it is also the Max_ID.
Can't you try and get that one?

Max_ID maybe not what I want if multiple connections do Insert at same time.
Lazarus 1.8.4 / FPC 3.0.4 / SVN 57972
On Win10 X64, have to compile for Win32

kinlion

  • New member
  • *
  • Posts: 39
  • I Love Lazarus
Re: Why does GetInsertID always return 0?
« Reply #4 on: January 18, 2019, 02:28:37 am »
I dig in the implementation, it seems correct, but I'm on trunk. No idea if it was already implemented in stable.
Code: Pascal  [Select]
  1. // $(fpcsrc)/packages/fcl-db/src/sqldb/mysql/mysqlconn.inc:378
  2. Function TConnectionName.GetInsertID: int64;
  3. begin
  4.   CheckConnected;
  5.   Result:=mysql_insert_id(GetHandle);
  6. end;
  7.  

Yes, I also see the implementation.
I think there must be some bad code in my project because I have used it before, and the result was right.
Now, I use 'select last_insert_id()'.
I will dig it when have time.

Thanks Leledumbo :)
« Last Edit: January 18, 2019, 02:30:21 am by kinlion »
Lazarus 1.8.4 / FPC 3.0.4 / SVN 57972
On Win10 X64, have to compile for Win32