SQLDB - letzte Insert ID (Auto Increment)

Für Themen zu Datenbanken und Zugriff auf diese. Auch für Datenbankkomponenten.

SQLDB - letzte Insert ID (Auto Increment)

Beitragvon Socke » 14. Mai 2015, 21:53 SQLDB - letzte Insert ID (Auto Increment)

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
Socke
 
Beiträge: 2514
Registriert: 22. Jul 2008, 18:27
Wohnort: Köln
OS, Lazarus, FPC: Lazarus: SVN; FPC: svn; Win 8.1/Debian GNU/Linux/Raspbian/openSUSE | 
CPU-Target: 32bit x86 armhf
Nach oben

Beitragvon hde » 15. Mai 2015, 00:18 Re: SQLDB - letzte Insert ID (Auto Increment)

das ist Datenbank abhängig

mySQL /mariaDB : SELECT LAST_INSERT_ID();
hde
 
Beiträge: 556
Registriert: 11. Aug 2010, 01:56

Beitragvon Thomas B. » 15. Mai 2015, 13:34 Re: SQLDB - letzte Insert ID (Auto Increment)

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.
Thomas B.
 
Beiträge: 89
Registriert: 2. Nov 2007, 13:32
Wohnort: Ulm
OS, Lazarus, FPC: Win (L 1.0 FPC 2.6.0) | 
CPU-Target: 32Bit
Nach oben

Beitragvon Socke » 17. Mai 2015, 18:20 Re: SQLDB - letzte Insert ID (Auto Increment)

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
Socke
 
Beiträge: 2514
Registriert: 22. Jul 2008, 18:27
Wohnort: Köln
OS, Lazarus, FPC: Lazarus: SVN; FPC: svn; Win 8.1/Debian GNU/Linux/Raspbian/openSUSE | 
CPU-Target: 32bit x86 armhf
Nach oben

Beitragvon Michl » 17. Mai 2015, 20:04 Re: SQLDB - letzte Insert ID (Auto Increment)

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).
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
Code: Alles auswählen
type
  TLiveSelection = (lsMoney, lsChilds, lsTime);
  TLive = Array[0..1] of TLiveSelection; 
Michl
 
Beiträge: 2260
Registriert: 19. Jun 2012, 11:54
OS, Lazarus, FPC: Win7 Laz 1.7 Trunk FPC 3.1.1 Trunk | 
CPU-Target: 32Bit/64bit
Nach oben

Beitragvon Socke » 27. Mai 2015, 21:25 Re: SQLDB - letzte Insert ID (Auto Increment)

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
Socke
 
Beiträge: 2514
Registriert: 22. Jul 2008, 18:27
Wohnort: Köln
OS, Lazarus, FPC: Lazarus: SVN; FPC: svn; Win 8.1/Debian GNU/Linux/Raspbian/openSUSE | 
CPU-Target: 32bit x86 armhf
Nach oben

Beitragvon Michl » 27. Mai 2015, 22:12 Re: SQLDB - letzte Insert ID (Auto Increment)

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; 
Michl
 
Beiträge: 2260
Registriert: 19. Jun 2012, 11:54
OS, Lazarus, FPC: Win7 Laz 1.7 Trunk FPC 3.1.1 Trunk | 
CPU-Target: 32Bit/64bit
Nach oben

Beitragvon mse » 28. Mai 2015, 07:40 Re: SQLDB - letzte Insert ID (Auto Increment)

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.
mse
 
Beiträge: 1949
Registriert: 16. Okt 2008, 09: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
Nach oben

Beitragvon Socke » 28. Mai 2015, 13:33 Re: SQLDB - letzte Insert ID (Auto Increment)

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
Socke
 
Beiträge: 2514
Registriert: 22. Jul 2008, 18:27
Wohnort: Köln
OS, Lazarus, FPC: Lazarus: SVN; FPC: svn; Win 8.1/Debian GNU/Linux/Raspbian/openSUSE | 
CPU-Target: 32bit x86 armhf
Nach oben

Beitragvon mse » 28. Mai 2015, 17:41 Re: SQLDB - letzte Insert ID (Auto Increment)

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;
...
 
 
mse
 
Beiträge: 1949
Registriert: 16. Okt 2008, 09: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
Nach oben

Beitragvon hubblec4 » 23. Jul 2015, 09:41 Re: SQLDB - letzte Insert ID (Auto Increment)

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
hubblec4
 
Beiträge: 189
Registriert: 25. Jan 2014, 17:50

Beitragvon Socke » 28. Jul 2015, 09:13 Re: SQLDB - letzte Insert ID (Auto Increment)

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
Socke
 
Beiträge: 2514
Registriert: 22. Jul 2008, 18:27
Wohnort: Köln
OS, Lazarus, FPC: Lazarus: SVN; FPC: svn; Win 8.1/Debian GNU/Linux/Raspbian/openSUSE | 
CPU-Target: 32bit x86 armhf
Nach oben

• Themenende •

Zurück zu Datenbanken



Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 2 Gäste

porpoises-institution
accuracy-worried