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.

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 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 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 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...