Abfrage nach Last_Insert_ID() funkt. nicht

[TUX_der_Pinguin]

Ich greife auf meine Datenbank via dbExpress zu, jetzt will ich von einem gerade hinzugefügten Datensatz den Auto Inc.
Wert ermitteln, eigentlich keine große Sache es gibt ja 'SELECT Last_Insert_ID...' nur erhalte ich da jedesmal nur "0".

Irgendwie bin ich verwirrt, da es sonst mit Turbo Delphi und dbExpress (Open dbExpress Driver für MySQL 5) immer
geklappt hat, genauso wie von Hand in der MySQL Console.

Ich habe eine Komponente 'TSQLConnection' auf meinem Hauptformular liegen worüber ich die Verbindung herstelle und egal ob
ich jetzt die Komponente TSQLQuery auf meinem Dialog verwende oder das Object zur laufzeit erstelle wie in dem folgenden
Auszug aus dem Quelltext ich erhalte immer als Ergebnis "0" was totaler quatsch ist. :gruebel: :wall:

Delphi-Quellcode:

procedure TfrmDialog.btnSaveClick(Sender: TObject);
var
  SQLQuery : TSQLQuery;

begin
  //init
  SQLQuery       := TSQLQuery.Create(Self);
  SQLQuery.SQLConnection := frmMain.SQLCon;

  SQLQuery.SQL.Clear;
  SQLQuery.SQL.Text := 'INSERT INTO tb_test (Datum, Benutzer) VALUES (:Date, :User)';
  SQLQuery.ParamByName('Date').AsDate    := dtDate;
  SQLQuery.ParamByName('User').AsString := strUserName;
  SQLQuery.ExecSQL;

  if SQLQuery.RowsAffected > 0 then begin
    SQLQuery.SQL.Clear;
    SQLQuery.SQL.Text := 'SELECT LAST_INSERT_ID() AS Last_Insert_ID FROM tb_test LIMIt 1';
    SQLQuery.Open;

    if SQLQuery.RecordCount > 0 then begin
      ShowMessage(SQLQuery.FindField('Last_Insert_Id').AsString);
    end;
   
  end;


  //deinit
  SQLQuery.Close;
  SQLQuery.Free;

end;

[Nuclear-Ping]

Gibts SQLQuery.LastAutoIncVal?

[TUX_der_Pinguin]

Zitat:

Zitat von Nuclear-Ping

Gibts SQLQuery.LastAutoIncVal?

Nein eine solche Methode gibt es bei TSQLQuery nicht.

[Nuclear-Ping]

Reicht nicht einfach SELECT LAST_INSERT_ID()?

[edit]
Grad getestet, als direkte SQL Abfrage.

SQL-Code:

insert into customers(name, regid, unlockid, registratorid, added) values('test', 'test', 'test', 0, NULL);
select last_insert_id();

Lieferte mir 25, was stimmt.

[TUX_der_Pinguin]

Zitat:

Zitat von Nuclear-Ping

Reicht nicht einfach SELECT LAST_INSERT_ID()?

[edit]
Grad getestet, als direkte SQL Abfrage.

SQL-Code:

insert into customers(name, regid, unlockid, registratorid, added) values('test', 'test', 'test', 0, NULL);
select last_insert_id();

Lieferte mir 25, was stimmt.

@Nuclear-Ping: Du hast meinen Beitrag komplett gelesen?

Mit anderen Worten klar würde das reichen wenn es den klappen würde was es nämlich NICHT tut und ich nicht weiß warum.

[DeddyH]

Zitat:

LAST_INSERT_ID(), LAST_INSERT_ID(expr)

For MySQL 5.1.12 and later, LAST_INSERT_ID() (no arguments) returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.

For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:

mysql> SELECT LAST_INSERT_ID();
-> 195

In MySQL 5.1.11 and earlier, LAST_INSERT_ID() (no arguments) returns the first automatically generated value if any rows were successfully inserted or updated. This means that the returned value could be a value that was not successfully inserted into the table. If no rows were successfully inserted, LAST_INSERT_ID() returns 0.

The value of LAST_INSERT_ID() will be consistent across all versions if all rows in the INSERT or UPDATE statement were successful.

The currently executing statement does not affect the value of LAST_INSERT_ID(). Suppose that you generate an AUTO_INCREMENT value with one statement, and then refer to LAST_INSERT_ID() in a multiple-row INSERT statement that inserts rows into a table with its own AUTO_INCREMENT column. The value of LAST_INSERT_ID() will remain stable in the second statement; its value for the second and later rows is not affected by the earlier row insertions. (However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined.)

If the previous statement returned an error, the value of LAST_INSERT_ID() is undefined. For transactional tables, if the statement is rolled back due to an error, the value of LAST_INSERT_ID() is left undefined. For manual ROLLBACK, the value of LAST_INSERT_ID() is not restored to that before the transaction; it remains as it was at the point of the ROLLBACK.

Within the body of a stored routine (procedure or function) or a trigger, the value of LAST_INSERT_ID() changes the same way as for statements executed outside the body of these kinds of objects. The effect of a stored routine or trigger upon the value of LAST_INSERT_ID() that is seen by following statements depends on the kind of routine:

*

If a stored procedure executes statements that change the value of LAST_INSERT_ID(), the changed value will be seen by statements that follow the procedure call.
*

For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements will not see a changed value.

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

The value of LAST_INSERT_ID() is not changed if you set the AUTO_INCREMENT column of a row to a non-“magic” value (that is, a value that is not NULL and not 0).
Important

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

For example:

mysql> USE test;
Database changed
mysql> CREATE TABLE t (
-> id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> name VARCHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t VALUES (NULL, 'Bob');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
+----+------+
1 row in set (0.01 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t VALUES
-> (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
| 2 | Mary |
| 3 | Jane |
| 4 | Lisa |
+----+------+
4 rows in set (0.01 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)

Although the second INSERT statement inserted three new rows into t, the ID generated for the first of these rows was 2, and it is this value that is returned by LAST_INSERT_ID() for the following SELECT statement.

If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter is not incremented and LAST_INSERT_ID() returns 0, which reflects that no row was inserted.

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

1.

Create a table to hold the sequence counter and initialize it:

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

2.

Use the table to generate sequence numbers like this:

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();

The UPDATE statement increments the sequence counter and causes the next call to LAST_INSERT_ID() to return the updated value. The SELECT statement retrieves that value. The mysql_insert_id() C API function can also be used to get the value. See Section 24.2.3.37, “mysql_insert_id()”.

You can generate sequences without calling LAST_INSERT_ID(), but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value. It is multi-user safe because multiple clients can issue the UPDATE statement and get their own sequence value with the SELECT statement (or mysql_insert_id()), without affecting or being affected by other clients that generate their own sequence values.

Note that mysql_insert_id() is only updated after INSERT and UPDATE statements, so you cannot use the C API function to retrieve the value for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or SET.

Quelle: http://dev.mysql.com/doc/refman/5.1/...functions.html

[Nuclear-Ping]

Zitat:

Zitat von TUX_der_Pinguin

@Nuclear-Ping: Du hast meinen Beitrag komplett gelesen?

Mit anderen Worten klar würde das reichen wenn es den klappen würde was es nämlich NICHT tut und ich nicht weiß warum.

Ja, hab ich. Aber wenn das Konstrukt von dir die Query ist, die du verwendest, würde ich sie vereinfachen, da es laut SQL-Standard schon reicht. Es benötigt weder FROM noch LIMIT.
Ich will dir ja nichts in den Mund legen, aber du hast bisher nicht gesagt, dass du's mit der einfachen Query so im Programm probiert hast. ^^
Die drei Punkte bei der Query in deinem Anfangssatz können für alles mögliche stehen.

[TUX_der_Pinguin]

Ich habe das noch mal ganz kurz auf die schnelle abgecheckt und bekomme eine Fehlermeldung wenn ich versuche
'SELECT Last_Insert_ID()' abzufragen... ich muß mir das Morgen oder die Tage noch mal näher ansehen.
Vielleicht Update ich auch mal den MySQL Server auf 5.1.x .. meine Vermutung liegt bei der Session das der
irgendwie nicht rafft das das die gleiche Session ist oder aber wie im Auszug aus der Doku beschrieben das
das Insert Statement nicht korrekt abgeschlossen wurde und somit '0' zurück gegeben wird.

[Nuclear-Ping]

Ich habs mit MySQL 4 versucht.

Kannst du mehrere Anweisungen in eine reinpacken?

Delphi-Quellcode:

  SQLQuery.SQL.Clear;
  SQLQuery.SQL.Add ('INSERT INTO tb_test (Datum, Benutzer) VALUES (:Date, :User);');
  SQLQuery.SQL.Add ('SELECT LAST_INSERT_ID() AS LID;');
  SQLQuery.ParamByName('Date').AsDate    := dtDate;
  SQLQuery.ParamByName('User').AsString := strUserName;
  SQLQuery.Open;

  if SQLQuery.RecordCount > 0 then begin
    ShowMessage(SQLQuery.FindField('LID').AsString);
  end;

[x000x]

Moin moin,

evtl. schließt SQLQuery.ExecSQL die Connection nach Ausführung?!
Kannst du die Connection mal explizit öffnen?
Halt nur ne Vermutung...

[TUX_der_Pinguin]

Zitat:

Zitat von x000x

evtl. schließt SQLQuery.ExecSQL die Connection nach Ausführung?!
Kannst du die Connection mal explizit öffnen?
Halt nur ne Vermutung...

Ob es jetzt das SQLQuery.ExecSQL ist oder was anderes aber das Problem liegt wirklich daran das die Verbindung
unterbrochen wird und bei einer neuen Abfrage eine neue Session erstellt wird, das verhindert natürlich die
korrekte ausgabe des letzten auto inc. wertes.

Ich habe das ganze mal mit einer Abfrage nach 'CONNECTION_ID()' getester vor dem INSERT Statement hatte ich die ID 86
und danach erhielt ich 87 bevor ich dann nach Last_Insert_ID abgefragt habe, ich schau mal ob ich irgendwo was finden
kann damit die Verbindung aufrecht erhalten wird. TSQLConnection.KeepConnection steht auf True.

Alles etwas verwirrend, aber ich komm dem noch auf die spur, die Hoffnung stirb zu letzt. :coder2:


Edit:
Ich habe das noch mal etwas genauer getestet, selbst wenn ich zwei mal hintereinander nur nach der CONNECTION_ID()
abfrage bekomme ich jedesmal eine andere, also kann es nicht am ExecSQL() liegen sondern ein allgemeines Problem
liegt vor das der für jede Abfrage eine neue Session eröffnet.

[x000x]

Moin moin,

Delphi-Quellcode:

  //init
  SQLQuery       := TSQLQuery.Create(Self);
  SQLQuery.SQLConnection := frmMain.SQLCon;
  SQLQuery.SQLConnection.Open; //bzw. .Active := True;
  //...
  SQLQuery.ExecSQL;
  //...
  SQLQuery.SQLConnection.Close;  //bzw. .Active := False;

funktioniert das so auch nicht?

[TUX_der_Pinguin]

So jetzt habe ich das ganze noch mal manuell und ohne Komponenten ausprobiert und nach wie vor immer noch der gleiche Fehler. :-(

Delphi-Quellcode:

procedure TfrmLogEntryDlg.btnSaveClick(Sender: TObject);
var
  i        : Integer;
  SQLCon   : TSQLConnection;
  SQLQuery : TSQLQuery;

begin
  //init
  SQLCon                     := TSQLConnection.Create(Self);
  SQLCon.Connected           := False;
  SQLCon.ConnectionName      := 'MySQLConnection';
  SQLCon.DriverName          := 'MySQL';
  SQLCon.GetDriverFunc       := 'getSQLDriverMYSQL';
  SQLCon.KeepConnection      := True;
  SQLCon.LibraryName         := 'dbxmys30.dll';
  SQLCon.LoadParamsOnConnect := False;
  SQLCon.LoginPrompt         := False;
  SQLCon.Params.Clear;
  SQLCon.Params.Add('DriverName=MySQL');
  SQLCon.Params.Add('HostName=localhost');
  SQLCon.Params.Add('Database=cbase');
  SQLCon.Params.Add('User_Name=root');
  SQLCon.Params.Add('Password=xxxx');
  SQLCon.Params.Add('BlobSize=-1');
  SQLCon.Params.Add('ErrorResourceFile=');
  SQLCon.Params.Add('LocaleCode=0000');
  SQLCon.Params.Add('Compressed=False');
  SQLCon.Params.Add('Encrypted=False');
  SQLCon.VendorLib           := 'LIBMYSQL.DLL';

  //Verbindung öffnen
  SQLCon.Open;

  SQLQuery := TSQLQuery.Create(Self);
  SQLQuery.SQLConnection := frmMain.SQLCon;

  //Datensatz Speichern

  if bNewEntry then begin
    SQLQuery.SQL.Clear;
    SQLQuery.SQL.Text := 'SELECT CONNECTION_ID() FROM tb_routes LIMIT 1';
    SQLQuery.Open;
    //z.B. Connection ID = 200
    if SQLQuery.RecordCount > 0 then ShowMessage('Connection ID:'+SQLQuery.FindField('Connection_ID()').AsString);

    //Datensatz anlegen
    SQLQuery.SQL.Clear;
    SQLQuery.SQL.Text := 'INSERT INTO tb_routes (Date, CarID) VALUES (:Date, :CarID)';
    SQLQuery.ParamByName('Date').AsDate     := dtpDate.Date;
    SQLQuery.ParamByName('CarID').AsInteger := Integer(cmbCar.Items.Objects[cmbCar.ItemIndex]);
    SQLQuery.ExecSQL;

    if SQLQuery.RowsAffected > 0 then begin
      SQLQuery.SQL.Clear;
      SQLQuery.SQL.Text := 'SELECT CONNECTION_ID() FROM tb_routes LIMIT 1';
      SQLquery.Open;
      //z.B. Connection ID = 205
      if SQLQuery.RecordCount > 0 then ShowMessage('Connection ID:'+SQLQuery.FindField('Connection_ID()').AsString);

      SQLQuery.SQL.Clear;
      SQLQuery.SQL.Text := 'SELECT LAST_INSERT_ID() FROM tb_routes';
      SQLQuery.Open;

      //Sollte den letzten Auto Inc Wert ausgeben, gibt aber immer '0' aus...
      if SQLQuery.RecordCount > 0 then ShowMessage(SQLQuery.FindField('Last_Insert_Id()').AsString);
    end;{if}
  end;{if}

  //deinit
  SQLCon.Close;
  SQLCon.Free;
  SQLQuery.Close;
  SQLQuery.Free;

end;

Entweder bin ich so blind und überseh die ganze Zeit etwas, oder dieser dBExpress müll ist total verbuggt.

[Nuclear-Ping]

Das mal probiert: http://www.delphipraxis.net/internal...=842059#842059 :?:

[omata]

Hallo TUX_der_Pinguin,

ich habe folgendes gerade mal getestet und das funktioniert bestens!

Delphi-Quellcode:

  SQLQuery.SQLConnection:=SQLConnection;
  SQLQuery.SQL.Text:='INSERT INTO tabelle (feld) VALUES (''inhalt'')';
  SQLQuery.ExecSQL;
  SQLQuery.SQL.Text:='SELECT LAST_INSERT_ID() as id';
  SQLQuery.Open;
  ShowMessage(SQLQuery.fieldByName('id').AsString);
  SQLQuery.Close;

Ich habe das mit folgendem Treiber ausprobiert hier

Gruss
Thorsten

[TUX_der_Pinguin]

Ich weiß das der Treiber funktioniert den hatte ich im Zusammenspiel mit Turbo Delphi Explorer verwendet,
aber wo jetzt auch ein recht aktueller Treiber in Delphi 2007 beiliegt wollte ich den auch verwenden, wobei
der OpenSource treiber auch einige Bugs hat bzw. Gespeicherte Prozeduren nicht unterstütz und ich gehofft hatte
mit dem Treiber von CodeGear das nun klappen könnte, damit ich mich mal mehr mit dem Thema Gespeicherte
Prozeduren beschäftigen kann.

Es muß doch eine Lösung geben oder benutzt niemand mehr den dbExpress Treiber bzw. dbExpress generell. :wall: :gruebel:

[Nuclear-Ping]

Zum dritten mal: Hast du mal versucht, ob du mehrere Anweisungen in eine packen kannst (Beitrag #9 von mir)?

Warum muß es denn der Treiber sein?

[TUX_der_Pinguin]

Der Test das ich beide Abfragen gleichzeitig abschicke, wird schon direkt abgebrochen mit einer Exception.

Delphi-Quellcode:

SQLQuery.SQL.Clear;
SQLQuery.SQL.Add('INSERT INTO tb_routes (Date, CarID) VALUES (:Date, :CarID);');
SQLQuery.SQL.Add('SELECT LAST_INSERT_ID() FROM tb_routes Limit 1;');
SQLQuery.ParamByName('Date').AsDate     := dtpDate.Date;
SQLQuery.ParamByName('CarID').AsInteger := Integer(cmbCar.Items.Objects[cmbCar.ItemIndex]);
SQLQuery.Open;

if SQLQuery.RecordCount > 0 then begin
  ShowMessage('Last ID:'+SQLQuery.FindField('Last_Insert_ID()').AsString);
end;{if}

Exception:

Zitat:

Im Projekt Test.exe ist eine Exception der Klasse TDBXError mit der Meldung
'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 ';
SELECT LAST_INSERT_ID() FROM tb_routes Limit 1' at line 1' aufgetreten.

Des weiteren was in einigen postings vorher schon angemerkt wurde ob man nicht nur nach 'SELECT Last_Insert_ID()' abfragen
kann, funktioniert auch nicht, das wirft auch eine Exception aus. Daher immer das 'From tabelle' und dann auch 'LIMIT 1' da
er sonst so viele Zeilen zurück gibt, wie Datensätze in der Tabelle stehen.


So jetzt habe ich mir mal den "Spaß" erlaubt die ZEOSLib (zeosdbo-6.6.2-rc) zu testen, ich habe vom Prinzip nichts geändert
und plötzlich funktioniert alles wunderbar. Hier noch mal der Quellcode mit ZEOS Komponenten.

Delphi-Quellcode:

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ZAbstractRODataset, ZAbstractDataset,
  ZDataset, ZConnection;

type
  TForm1 = class(TForm)
    SQLCon: TZConnection;
    SQLQuery: TZQuery;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin

  SQLCon.Connect;

  ShowMessage(SQLCon.ServerVersionStr); //5.0.51a

  SQLQuery.SQL.Clear;
  SQLQuery.SQL.Text := 'SELECT Connection_ID()';  
  SQLQuery.Open;

  if SQLQuery.RecordCount > 0 then ShowMessage('ConID:'+SQLQuery.FindField('Connection_ID()').AsString);  //ID: 13

  SQLQuery.SQL.Text := 'INSERT INTO tb_routes (Date, CarID) VALUES (:Date, :CarID)';
  SQLQuery.ParamByName('Date').AsDate     := Now;
  SQLQuery.ParamByName('CarID').AsInteger := 105;
  SQLQuery.ExecSQL;

  if SQLQuery.RowsAffected > 0 then begin
    SQLQuery.SQL.Clear;
    SQLQuery.SQL.Text := 'SELECT Last_Insert_ID()';
    SQLQuery.Open;

    if SQLQuery.RecordCount > 0 then ShowMessage('Last ID:'+SQLQuery.FindField('Last_Insert_Id()').AsString); //Last Auto Inc. 21
  end;

  SQLQuery.SQL.Clear;
  SQLQuery.SQL.Text := 'SELECT Connection_ID()'; 
  SQLQuery.Open;

  if SQLQuery.RecordCount > 0 then ShowMessage('ConID:'+SQLQuery.FindField('Connection_ID()').AsString); //ID: 13

  SQLCon.Disconnect;

end;

end.

Und all diese Tests zeigen für mich das der dBExpress Treiber von Delphi 2007 verbuggt ist oder man noch irgendwas einstellen
kann um das Verhalten zuändern, die Einstellung finde ich aber nicht. Auch wenn mein Fazit nicht ganz sachlich ist aber
dbExpress von Delphi 2007 für Win32 R2 SUCKS ! Ich überlege mir jetzt das Projekt mit der ZEOSLib zurealisieren.

Aber dennoch vielen dank für all die Tips und Ideen um das Problem zubeheben.

[NormanNG]

Hi,

Zitat:

'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 ';

meint ja evtl., das die Semikolons am Ende der SQL-Zeilen einfach da nicht hingehören?

SQL-Code:

SQLQuery.SQL.Add('INSERT INTO tb_routes (Date, CarID) VALUES (:Date, :CarID);');
SQLQuery.SQL.Add('SELECT LAST_INSERT_ID() FROM tb_routes Limit 1;');

[TUX_der_Pinguin]

Auch das klappt nicht, wenn ich das Semikolon beim ersten oder zweiten oder bei beiden weglasse, wird nur eine
andere Exception ausgelöst, ich bau das ganze jetzt einfach mit ZEOS um mit der Hoffnung das das ganze jetzt
gescheit läuft und keine Merkwürdigkeiten mehr auftreten.