TZSQLProcessor, LAST_INSERT_ID(), MySQL

[vejrous]

Hi, I am trying to get last inserted ID. Is something like below supossed to work?

Code: Select all

const
  LastStatusID: string = 'last_status_id'
begin
  ZSQLProcessor.Connection := Connection1;
  ZSQLProcessor.ParamChar := '@';
  ZSQLProcessor.Script.Clear;
  ZSQLProcessor.Script.Add('START TRANSACTION;');
  ZSQLProcessor.Script.Add('INSERT INTO table1 (field1, field2) VALUES (value1, value2);');
  { Remember last ID for next staments (this works) }
  ZSQLProcessor.Script.Add('SET ' + ZSQLProcessor.ParamChar + LastStatusID + ' = LAST_INSERT_ID();');
  { Select last ID for param? }
  ZSQLProcessor.Script.Add('SELECT ' + ZSQLProcessor.ParamChar + LastStatusID + ';');

  { Multiple more stements (using last ID, works) }
  ZSQLProcessor.Script.Add( ... );');
  ZSQLProcessor.Script.Add('COMMIT;');
  ZSQLProcessor.Execute;

  { Tris retturns always 0 }
  Result := ZSQLProcessor.ParamByName(LastStatusID).AsInteger;

[Fr0sT]

Parameters are input, output are fields. Probably that's the reason

[marsupilami]

Hello vejrous,

TZSQLProcessor is meant to be more like an SQL script runner. I am pretty sure it isn't ment to return parameter values, so I doubt that your idea is expected to work. But I seem to rememebr that TZSQLProcessor has an Open-Method, so something like "select LAST_INSERT_ID()" as the last line in your script might work.

There is one more thing about your script: You use "START TRANSACTION" and "COMMIT" in your script. Don't do that. If things go wrong, it will fuck up the Zeos transaction management. Even if things are good in your script, it might fuck up the transaction management. Use ZConnection.StartTransaction and ZConnection.Commit instead.

Best regards,

Jan

[vejrous]

Finally got back to this. Thank you for help.
I used LAST_INSERT_ID directly in the script as variable.