SQLDB - letzte Insert ID (Auto Increment)

Für Themen zu Datenbanken und Zugriff auf diese. Auch für Datenbankkomponenten.
Antworten
Socke
Lazarusforum e. V.
Beiträge: 3158
Registriert: Di 22. Jul 2008, 19:27
OS, Lazarus, FPC: Lazarus: SVN; FPC: svn; Win 10/Linux/Raspbian/openSUSE
CPU-Target: 32bit x86 armhf
Wohnort: Köln
Kontaktdaten:

SQLDB - letzte Insert ID (Auto Increment)

Beitrag von Socke »

Hallo zusammen,

wie kann ich mit SQLDB auf die im letzten INSERT-Statement automatisch vergebenen Auto-increment-Wert zugreifen?
Das SQL-Statement wird in etwa wie folgt ausgeführt:

Code: Alles auswählen

// Tabelle mytab hat die Felder f0 (auto inc) und f1
Query.SQL.Text := 'INSERT INTO mytab (f1) VALUES (1)';
Query.Open;

Die Datenbank wird über die Connection-Registry der Unit sqldb instantiiert, sodass beim Datenbankzugriff die tatsächliche Klasse nicht bekannt ist. Die konkreten Units (z.B. mysql55conn) sind dort ebenfalls nicht verfügbar. Damit ist ein Typecast ausgeschlossen.
Da die vorhandene Methode TConnectionName.GetInsertID (am Beispiel MySQL) nicht virtuell ist, kann sie auch nicht über die VMT bzw. die Methode TObject.MethodAddress nicht gefunden werden.

Habt ihr Lösungsvorschläge?

Mir fallen ein:
  • Eigene Nummernkreise in der Anwendung unabhängig von der Datenbank
  • Methoden-Registry zuzüglich der Connection-Registry
MfG Socke
Ein Gedicht braucht keinen Reim//Ich pack’ hier trotzdem einen rein

hde
Beiträge: 556
Registriert: Mi 11. Aug 2010, 02:56

Re: SQLDB - letzte Insert ID (Auto Increment)

Beitrag von hde »

das ist Datenbank abhängig

mySQL /mariaDB : SELECT LAST_INSERT_ID();

Thomas B.
Beiträge: 90
Registriert: Fr 2. Nov 2007, 13:32
OS, Lazarus, FPC: Win (L 1.0 FPC 2.6.0)
CPU-Target: 32Bit
Wohnort: Ulm

Re: SQLDB - letzte Insert ID (Auto Increment)

Beitrag von Thomas B. »

bei MySQL nutze ich

Code: Alles auswählen

Query.SQL.Text := 'SHOW TABLE STATUS WHERE Name=' + QuotedStr('Tabellenname');
Query.Open;
Query.FieldByName('Auto_increment').AsInteger; // gibt die ID, die beim nächsten Insert verwendet wird.

Bei einer Multi-User-Nutzung empfehle ich ggf. ein Lock/Unlock der Tabelle.

Socke
Lazarusforum e. V.
Beiträge: 3158
Registriert: Di 22. Jul 2008, 19:27
OS, Lazarus, FPC: Lazarus: SVN; FPC: svn; Win 10/Linux/Raspbian/openSUSE
CPU-Target: 32bit x86 armhf
Wohnort: Köln
Kontaktdaten:

Re: SQLDB - letzte Insert ID (Auto Increment)

Beitrag von Socke »

Bei SQLite gibt es ja auch die SQL-Funktion last_insert_rowid().

Wie wäre denn bei diesem Ansatz eine gute Möglichkeit, die Datenbank festzustellen? Da die Datenbankklasse dem Compiler nicht bekannt ist, müsste ich dann über den Klassennamen gehen?
MfG Socke
Ein Gedicht braucht keinen Reim//Ich pack’ hier trotzdem einen rein

Michl
Beiträge: 2505
Registriert: Di 19. Jun 2012, 12:54

Re: SQLDB - letzte Insert ID (Auto Increment)

Beitrag von Michl »

Falls man keine komplizierten Joins etc. nutzt, kann man eigentlich zum Einfügen von Daten die Datenbankkomponenten nutzen (im SELECT müsste aber die ID mit vorhanden sein). IMHO liefern dann SQLdb und Zeos automatisch die letzte ID zurück: http://zeoslib.sourceforge.net/viewtopic.php?t=2774

Eben habe ich noch SQLdb mit einer SQLite-Datenbank getestet, da funktioniert dies offensichtlich auch (zumindest bei mir):

Code: Alles auswählen

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.Append;
  SQLQuery1.FieldByName('name').AsString:='Zufallsname'+IntToStr(Random(100));
  SQLQuery1.FieldByName('nachname').AsString:='Zufallsnachname'+IntToStr(Random(100));
  SQLQuery1.Post;
  SQLQuery1.ApplyUpdates;
  ShowMessage('Letzte verwendete ID: '+SQLQuery1.FieldByName('id').AsString);
end;   


Wer es selbst mal testen will, anbei ist mein Testprojekt (SQLite.dll für Windows 32bit).
Dateianhänge
TestLastID.zip
(324.95 KiB) 84-mal heruntergeladen

Code: Alles auswählen

type
  TLiveSelection = (lsMoney, lsChilds, lsTime);
  TLive = Array[0..1] of TLiveSelection; 

Socke
Lazarusforum e. V.
Beiträge: 3158
Registriert: Di 22. Jul 2008, 19:27
OS, Lazarus, FPC: Lazarus: SVN; FPC: svn; Win 10/Linux/Raspbian/openSUSE
CPU-Target: 32bit x86 armhf
Wohnort: Köln
Kontaktdaten:

Re: SQLDB - letzte Insert ID (Auto Increment)

Beitrag von Socke »

Hallo Michel,

ich habe mir dein Beispiel angesehen.
In der Tat wäre das ein funktionierende Möglichkeit.

Wie geben ich der SQL-Query zu verstehen, dass ich ausschließlich einen Datensatz einfügen und nicht die gesamte Tabelle abrufen möchte?
Die Abfrage so zu setzen, dass keine Daten abgerufen werden (LIMIT 0), scheint mir wenig elegant zu sein (scheint aber bei SQLite3 und MariaDB 10.0.19 zu funktionieren).
MfG Socke
Ein Gedicht braucht keinen Reim//Ich pack’ hier trotzdem einen rein

Michl
Beiträge: 2505
Registriert: Di 19. Jun 2012, 12:54

Re: SQLDB - letzte Insert ID (Auto Increment)

Beitrag von Michl »

Falls es dir um Performance geht, ist die gepostete Möglichkeit sicherlich nicht die erste Wahl. Ansonsten werden bei einem normalen Select per SQLDB nur 10 Datensätze und nicht die ganze Tabelle geladen: http://wiki.freepascal.org/SqlDBHowto/d ... C3.BCck.3F (Ich hatte eben mal probiert die SQLQuery1.PacketRecords auf unter 10 zu stellen, das funktioniert nicht, scheinbar kann man diese Zahl nur erhöhen.)

Ansonsten dürfte LastItem oder ein einfaches Select des letzten, mit diesen Werten und der höchsten ID, eingefügten Datensatzes schneller sein.

Code: Alles auswählen

type
  TLiveSelection = (lsMoney, lsChilds, lsTime);
  TLive = Array[0..1] of TLiveSelection; 

mse
Beiträge: 2013
Registriert: Do 16. Okt 2008, 10:22
OS, Lazarus, FPC: Linux,Windows,FreeBSD,(MSEide+MSEgui 4.6,git master FPC 3.0.4,fixes_3_0)
CPU-Target: x86,x64,ARM

Re: SQLDB - letzte Insert ID (Auto Increment)

Beitrag von mse »

Socke hat geschrieben:Wie geben ich der SQL-Query zu verstehen, dass ich ausschließlich einen Datensatz einfügen und nicht die gesamte Tabelle abrufen möchte?

Benutze "TSqlQuery.ExecSQL()" statt "Active". Wenn du das automatisch erzeugte insert-statement verwenden möchtest, füge im SQL-query Statement eine WHERE-Klausel an welche keine Daten zurückliefert, z.B WHERE 1 = 2.

Socke
Lazarusforum e. V.
Beiträge: 3158
Registriert: Di 22. Jul 2008, 19:27
OS, Lazarus, FPC: Lazarus: SVN; FPC: svn; Win 10/Linux/Raspbian/openSUSE
CPU-Target: 32bit x86 armhf
Wohnort: Köln
Kontaktdaten:

Re: SQLDB - letzte Insert ID (Auto Increment)

Beitrag von Socke »

Michl hat geschrieben:Falls es dir um Performance geht, ist die gepostete Möglichkeit sicherlich nicht die erste Wahl. Ansonsten werden bei einem normalen Select per SQLDB nur 10 Datensätze und nicht die ganze Tabelle geladen

Wenn die Datenbank das nicht mitbekommt sondern unabhängig vom tatsächlichen Abrufen der Daten einen Full Table Scan macht, kann das doch wohl nicht das gewünschte Ziel sein?

mse hat geschrieben:Benutze "TSqlQuery.ExecSQL()" statt "Active".

Kannst du die die Variante mit ExecSQL() ein wenig ausführlicher fassen? Ich bin mit SQLdb noch nicht so vertraut.

mse hat geschrieben:Wenn du das automatisch erzeugte insert-statement verwenden möchtest, füge im SQL-query Statement eine WHERE-Klausel an welche keine Daten zurückliefert, z.B WHERE 1 = 2.

Ich hatte die Abfrage 'SELECT id, name, vorname FROM personen LIMIT 0' (um bei Michls Demo-Programm zu bleiben) verwendet. Nach meinem Empfinden ist ein solche Abfrage sinnlos und gehört gestrichen. Gibt es hier eine Möglichkeit, die SQL-Query entsprechend zu Initialisieren, sodass trotzdem INSERTs möglich sind?
An sich ist es mir egal, wo das INSERT-Statemen herkommt. Falls möglich/notwendig gebe ich das auch gerne vor.
MfG Socke
Ein Gedicht braucht keinen Reim//Ich pack’ hier trotzdem einen rein

mse
Beiträge: 2013
Registriert: Do 16. Okt 2008, 10:22
OS, Lazarus, FPC: Linux,Windows,FreeBSD,(MSEide+MSEgui 4.6,git master FPC 3.0.4,fixes_3_0)
CPU-Target: x86,x64,ARM

Re: SQLDB - letzte Insert ID (Auto Increment)

Beitrag von mse »

Socke hat geschrieben:
mse hat geschrieben:Benutze "TSqlQuery.ExecSQL()" statt "Active".

Kannst du die die Variante mit ExecSQL() ein wenig ausführlicher fassen? Ich bin mit SQLdb noch nicht so vertraut.

In MSEgui verwende ich dafür tsqlstatement. IIRC kann man bei FPC TSQLQuery in die SQL Property das gewünschte SQL-Statement eintragen (auch zur Entwurfszeit) und mit TSQLQuery.ExecSQL() ausführen lassen. Dabei wird kein Datenpuffer angelegt, auch insert durch ApplyUpdates() ist natürlich nicht möglich.
Also z.B.

Code: Alles auswählen

 
insert into personen (id,name,vorname) values (:id,:name,:vorname);
 

mse hat geschrieben:Wenn du das automatisch erzeugte insert-statement verwenden möchtest, füge im SQL-query Statement eine WHERE-Klausel an welche keine Daten zurückliefert, z.B WHERE 1 = 2.

Ich hatte die Abfrage 'SELECT id, name, vorname FROM personen LIMIT 0' (um bei Michls Demo-Programm zu bleiben) verwendet. Nach meinem Empfinden ist ein solche Abfrage sinnlos und gehört gestrichen. Gibt es hier eine Möglichkeit, die SQL-Query entsprechend zu Initialisieren, sodass trotzdem INSERTs möglich sind?
An sich ist es mir egal, wo das INSERT-Statemen herkommt. Falls möglich/notwendig gebe ich das auch gerne vor.

Die LIMIT Klausel kann ziemlichen Aufwand im Server bedeuten, da die ganze dem query entsprechende Datenmenge aufbereitet und sortiert wird. Es werden dann von der bereitgestellten Menge die gewünschte Anzahl Datensätze auf die Leitung gegeben. Ich weiss nicht, ob LIMIT 0 im Server als abgekürzter Spezialfall behandelt wird.
Die query mit dem dummy-WHERE dient dazu um im Dataset die benötigten Felder zu erzeugen und die Arbeit mit TDataset.Insert(),Post() oder den entsprechenden GUI-Aktionen und TSQLQuery.ApplyUpdates() zu ermöglichen.
Falls man nicht das automatisch erzeugte insert statement verwenden möchte kann man den gewünschten SQL-Text in TSQLQuery.SQLInsert schreiben.
Geht es hier immer noch um LAST INSERT ID? Dann kann man auch wenn die verwendete Datenbank RETURNING unterstützt

Code: Alles auswählen

 
insert into personen (id,name,vorname) values (:id,:name,:vorname) returning id;
 

in TSQLQuery.SQL schreiben, ParseSQL auf false und TSQLQuery.Active true setzen. Die von der DB erzeugte id steht dann im Feld "id" des Dataset.
MSEgui frischt Felder mit gesetztem of_refreshinsert, of_refreshinsert in optionsfield über diesen Weg automatisch auf, primary key fields mit autoinc werden ebenfalls automatisch via LAST INSERT ID aufgefrischt. AFAIK macht dies FPC Sqldb nun ebenfalls.
Socke hat geschrieben:Da die vorhandene Methode TConnectionName.GetInsertID (am Beispiel MySQL) nicht virtuell ist, kann sie auch nicht über die VMT bzw. die Methode TObject.MethodAddress nicht gefunden werden.

Habt ihr Lösungsvorschläge?

Code: Alles auswählen

 
if connection is tmysql55connection then begin
 theid:= tmysql55connection(connection).getinsertid;
end
else begin
 if connection is tmysql51connection then begin
  theid:= tmysql51connection(connection).getinsertid;
...
 
 

hubblec4
Beiträge: 341
Registriert: Sa 25. Jan 2014, 17:50

Re: SQLDB - letzte Insert ID (Auto Increment)

Beitrag von hubblec4 »

das problem hatte ich auch.

http://www.lazarusforum.de/viewtopic.php?f=17&t=8041

SQL.Query ist nicht sonderlich gut geeignet, da man dort nur einen SQL-Befehl setzen kann.
Nimm lieber SQL.Script

Socke
Lazarusforum e. V.
Beiträge: 3158
Registriert: Di 22. Jul 2008, 19:27
OS, Lazarus, FPC: Lazarus: SVN; FPC: svn; Win 10/Linux/Raspbian/openSUSE
CPU-Target: 32bit x86 armhf
Wohnort: Köln
Kontaktdaten:

Re: SQLDB - letzte Insert ID (Auto Increment)

Beitrag von Socke »

mse hat geschrieben:

Code: Alles auswählen

 
if connection is tmysql55connection then begin
 theid:= tmysql55connection(connection).getinsertid;
end
else begin
 if connection is tmysql51connection then begin
  theid:= tmysql51connection(connection).getinsertid;
... 

Diesen Code wollte ich vermeiden, da dann die konkreten Datenbank-Klassen in der Unit bekannt sein müssen. Da SQLdb aber einen schönen Factory-Ansatz mitliefert, sind die Klassen in der nutzenden Unit gar nicht bekannt.

mse hat geschrieben:Geht es hier immer noch um LAST INSERT ID? Dann kann man auch wenn die verwendete Datenbank RETURNING unterstützt

Ja, Insert und LAST INSERT ID stehen in unmittelbarem Zusammenhang - daher die Diskussion.
Mein bisheriger Favourit ist die Abfrage per SELECT. Diese benötigt keine weiteren Einstellungen und unterscheidet sich zwischen den Datenbanken nur in dem genauen SQL-Befehl.

hubblec4 hat geschrieben:SQL.Query ist nicht sonderlich gut geeignet, da man dort nur einen SQL-Befehl setzen kann.
Nimm lieber SQL.Script

Vielen Dank für den Hinweis und den Link!
MfG Socke
Ein Gedicht braucht keinen Reim//Ich pack’ hier trotzdem einen rein

Antworten