select last_insert_id() yelds error
lazarus / 16-Databases

----------------------------------------------------------------------
ciuly · 2007-12-10 19:46

I'm having a more complex program in which at one time I need to get
the last (just) inserted ID and it goes like this:

query1.sql.text:='insert into blabla';
query1.execsql;
query1.close;
query1.sql.text:='select last_insert_id()';
query1.open;
blabla

on the qyer1.open I get "EDatabaseError  : Error executing query: You
have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '' at line
1 "

I'm using (latest now) mysql server version 5.0.45 and a libmysql.dll
from mysql server 5.0.27 (my old, and initial one) and 5.0.45 after I
updated the server.
I'm getting that error in both cases.

I also tried to get the resutl from the mysql administrator, but it
always returned 0. I tracked that down at beeing a connection problem
as the function works only if the queries are executed by the same
thread in the same connection. I tested from the command line client
and that works fine, so its not a server issue.

I also tried "select last_insert_id() as blabla' but that didn't work
either (same behaviour).

what am I doing wrong?

----------------------------------------------------------------------
piper62 · 2007-12-11 04:52

Hi,
this code works with ZeosLib:
---------------------------------------------------
make some insert before...
  ZQuery1.SQL.Clear;
  ZQuery1.SQL.Add('select last_insert_id() as last_id');
  ZQuery1.Open;
  label1.caption:=ZQuery1.FieldByName('last_id').asString;
  ZQuery1.Close;
---------------------------------------------------

probably that helps.

Regards,
Tibor

----------------------------------------------------------------------
ciuly · 2007-12-11 07:53

not much since that would mean to change the db component all over the
application  but it's a workaround in case I can't get this done
otehrwise.
are there any values of the connection you specifically set? I'll
check the defaults myself.

----------------------------------------------------------------------
ciuly · 2007-12-11 09:05

I think it's a bug in the query component. I configured the server to
log the queries and even though all I do is
- on insert
- on select last_insert_id()
in the logs, there are 3 queries. the above 2 and a 3rd, following
last_insert_id()
"show index from"
which would explain the error I received.

now I have to track down where that is happening and fix it.

----------------------------------------------------------------------
ciuly · 2007-12-11 09:34

I have found and fixed the problem. this is how the procedure
TSQLQuery.InitUpdates(ASQL : string); from sqldb.pas should look like:

Code: [Select]
procedure TSQLQuery.InitUpdates(ASQL : string);
begin
  if FFromPart='' then
  begin
    FUpdateable := False; // @ciuly [11.12.2007] : probably a
function, like last_insert_id which does nto require a table and is
not updateable
    exit;
  end;
  if pos(',',FFromPart) > 0 then
    FUpdateable := False // select-statements from more then one table
are not updateable
  else
    begin
    FUpdateable := True;
    FTableName := FFromPart;
    end;
end;

----------------------------------------------------------------------
ciuly · 2007-12-11 09:39

I have submitted a bug with the tracker with proposed fix.