delphi dxExpress MySQL:无效的LAST_INSERT_ID值 [英] delphi dxExpress MySQL: invalid LAST_INSERT_ID value

问题描述

我正在Delphi中开发一个应用程序，并希望将记录插入到Mysql的表中. 然后我想知道插入记录的标识值.所以我写了下面的代码.
在运行时，插入已完成并添加了记录，但标识的返回值为零！ 我的错是什么?！

I am developing an application in Delphi and want to insert records to Mysql's table. An then I want to know inserted record's identity value. So I write bellow code.
On run time , insert is done and record added but returned value for identity is zero!! what is my mistake ?!!

-- MySql  table create
CREATE TABLE Sample_Table (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  mobile_number varchar(20) DEFAULT NULL,
  message_body text,
 PRIMARY KEY (id)
);

--- Delphi code

SQLCon := TSQLConnection.Create(self);
  with SQLCon do begin
   Close;
   DriverName := 'MySQL';
   GetDriverFunc := 'getSQLDriverMYSQL';
   LibraryName := 'dbxmys.dll';
   VendorLib := 'LIBMYSQL.dll';
   LoginPrompt := false;
   Params.Values['HostName'] := '127.0.0.1';
   Params.Values['Database'] := 'sms_test';
   Params.Values['User_Name'] := 'root';
   Params.Values['Password'] := 'root';
   Open;
end;

SQLQry := TSQLQuery.Create(self);
with SQLQry do begin
  Close;
  SQLConnection := SQLCon;

  SQL.Clear;
  SQL.Add('INSERT INTO Sample_Table ');
  SQL.Add('(mobile_number, message_body) VALUES');
  SQL.Add(format('(%s, %s);',[QuotedStr('989121011689'), QuotedStr('Text1')]));
  ExecSQL();
  Close;

  SQL.Clear;
  SQL.Add('SELECT LAST_INSERT_ID() EngineRefNo;');
  Open;
  First;
  ListBox1.items.Add(FieldByName('EngineRefNo').AsString);
  Close;
end;

SQLCon.Close;

有什么建议吗?

推荐答案

您应该一次性获得ID

You should get the ID in one go

SQLQry := TSQLQuery.Create(self);
with SQLQry do begin
  SQLConnection := SQLCon;

  SQL.Add('INSERT INTO Sample_Table ');
  SQL.Add('(mobile_number, message_body) VALUES');
  SQL.Add(format('(%s, %s);',[QuotedStr('989121011689'), QuotedStr('Text1')]));

  SQL.Add('SELECT LAST_INSERT_ID() EngineRefNo;');

  Open;
  ListBox1.items.Add(FieldByName('EngineRefNo').AsString);
  Close;
end;

SQLCon.Close;

您应该考虑使用参数来防止sql注入

and you should think about using parameters to prevent sql injection

SQLQry := TSQLQuery.Create(self);
with SQLQry do begin
  SQLConnection := SQLCon;

  SQL.Add('INSERT INTO Sample_Table ');
  SQL.Add('( mobile_number, message_body ) VALUES');
  SQL.Add('( :mobile_number, :message_body );');

  SQL.Add('SELECT LAST_INSERT_ID() EngineRefNo;');

  ParamByName( 'mobile_number' ).Value := '989121011689';
  ParamByName( 'message_body' ).Value := 'Text1';

  Open;
  ListBox1.items.Add(FieldByName('EngineRefNo').AsString);
  Close;
end;

SQLCon.Close;

这篇关于delphi dxExpress MySQL:无效的LAST_INSERT_ID值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！