Gelöst: autoincrement, generator, trigger

[petermännchen]

Eine Frage hätte ich noch in diesem Jahr:

unter Delphi gab es die Möglichkeit, den Generator anzugeben und somit ein autoincrement automatisch füllen zu lassen.
Bei der TSQLQuery finde ich nichts entsprechendes. Ich hab eine Tabelle erstellt und einen Triger dazu.

Dieser sollte doch eigentlich nach dem Erzeugen eines neuen Datensatze automatisch ausgeführt werden? Klappt leider nicht bei mir.

Hier die Tabelle und der Trigger:

ZQuery1.SQL.add('CREATE TABLE "MANDANT" ');
ZQuery1.SQL.add('( ');
ZQuery1.SQL.add(' "ID" INTEGER NOT NULL, ');
ZQuery1.SQL.add(' "NR" INTEGER, ');
..
..
ZQuery1.SQL.add(' PRIMARY KEY ("ID") ');
ZQuery1.SQL.add(') ');
ZQuery1.execSQl;
ZQuery1.sql.clear;
Memo1.lines.add(' - Trigger');
ZQuery1.SQL.add('CREATE TRIGGER "SET_MANDANTID" FOR "MANDANT" ');
ZQuery1.SQL.add('ACTIVE BEFORE INSERT POSITION 0 ');
ZQuery1.SQL.add('as ');
ZQuery1.SQL.add('begin ');
ZQuery1.SQL.add(' if (new.ID is null) then new.ID = gen_id(gen_mandant, 1); ');
ZQuery1.SQL.add(' if (new.ID = ''0'') then new.ID = gen_id(gen_mandant, 1); ');
ZQuery1.SQL.add(' if (new.WJTag is Null) then new.WJTag = ''1''; ');
ZQuery1.SQL.add(' if (new.WJMonat is Null) then new.WJMonat = ''1''; ');
ZQuery1.SQL.add(' if (new.Abrechtag is Null) then new.Abrechtag = ''1''; ');
ZQuery1.SQL.add(' if (new.AAdeckeln is Null) then new.AAdeckeln = ''0''; ');
ZQuery1.SQL.add(' if (new.AEdeckeln is Null) then new.AEdeckeln = ''0''; ');
ZQuery1.SQL.add(' if (new.PAdeckeln is Null) then new.PAdeckeln = ''0''; ');
ZQuery1.SQL.add(' if (new.PEDeckeln is Null) then new.PEdeckeln = ''0''; ');
ZQuery1.SQL.add(' if (new.DAdeckeln is Null) then new.DAdeckeln = ''0''; ');
ZQuery1.SQL.add(' if (new.DEdeckeln is Null) then new.DEdeckeln = ''0''; ');
ZQuery1.SQL.add(' if (new.P1komplett is Null) then new.P1komplett = ''0''; ');
ZQuery1.SQL.add(' if (new.P2komplett is Null) then new.P2komplett = ''0''; ');
ZQuery1.SQL.add(' if (new.MinPause is Null) then new.MinPause = ''1''; ');
ZQuery1.SQL.add(' if (new.UrlinStunden is Null) then new.UrlinStunden = ''0''; ');
ZQuery1.SQL.add('end ');
ZQuery1.execSQl;

Tabelle wird mit TSQLQuery geöffnet und mit dem DBNavigator ein neuer Datensatz erzeugt. Beim Speichern kommt dann "Field ID is required, but not supplied".

Danke noch einmal und guten Rutsch wünsche ich..

peter

[Heinrich Wolf]

...

ZQuery1.SQL.add('CREATE TABLE "MANDANT" ');
ZQuery1.SQL.add('( ');
ZQuery1.SQL.add(' "ID" INTEGER NOT NULL, ');
ZQuery1.SQL.add(' "NR" INTEGER, ');
..
..
ZQuery1.SQL.add(' PRIMARY KEY ("ID") ');
ZQuery1.SQL.add(') ');
ZQuery1.execSQl;
...

Tabelle wird mit TSQLQuery geöffnet und mit dem DBNavigator ein neuer Datensatz erzeugt. Beim Speichern kommt dann "Field ID is required, but not supplied".

Danke noch einmal und guten Rutsch wünsche ich..

peter

Hallo,

ich weiß nicht, welchen Datenbankserver Du einsetzt und welche Spezialitäten dieser bietet. Ich bin Informix Dynamic Server gewohnt. Wenn ich darin eine ID erstellen möchte, die beim Insert automatisch hochgezählt wird, dann muss ich deren Typ nicht als Integer, sondern als Serial spezifizieren. Einen Trigger braucht der Informix Dynamic Server dafür nicht. Ansonsten kann man beim Informix Dynamic Server Default Werte beim Create Table angeben, welche automatisch die NULLs ersetzen.

Ich hab auch noch ein wenig gegurgelt:
http://www.firebirdfaq.org/faq29/
Demnach scheint Dein Integer mit Trigger ja für Firebird richtig zu sein.

Daraus sehe ich, dass SQL in diesem Punkt je nach Datenbankserver ziemlich unterschiedlich sein kann.

Guten Rutsch!
Heiner

[petermännchen]

Ich hab die Lösung gefunden, ganz zufrieden bin ich aber noch nicht.

Nach dem Erzeugen eines neuen Datensatzes bin ich leichtsinnigerweise davon ausgegangen, das ID = NULL ist. Daher auch die Anweisung im Trigger, wenn NULL dann neue ID vom Generator abholen. Zur Sicherheit hab ich ja auch die 0 abgefragt.

Automatisch hat das nicht geholfen, also ist in der ID irgendetwas anderes enthalten. Seitdem ich im Ereignis "after insert" die ID auf 0 setze klappt der Trigger.

Die Lösung in Delphi ist da sehr viel komfortabler, wird aber auf diese Weise zufriedenstellend nachgebaut. Da ja oft Felder vorbelegt werden müssen, ist es nicht wirklich Mehraufwand.

Gruß

Peter

P.S.: Firebird embedded 2.5 unter Win7/32

[Heinrich Wolf]

Das verstehe ich nicht.
Before insert ist schon etwas in der ID? Wo kommt das her?
After insert setzt Du ID auf 0? Löscht Du damit nicht die hochgezählte ID?

[petermännchen]

Hallo Heiner,

ich hab keine Ahnung, was beim Erzeugen des Datensatzes in der ID enthalten ist. Ist aber glaub ich auch egal.

Der Trigger scheint jetzt beim Speichern ausgelöst zu werden.

Ich erklär mir das so:
Beim Aufrufen der Bildschirmmaske ist die Datenmenge im EDIT- Modus. Erster Datensatz wird angezeigt falls vorhanden (select * from mytable where ID like :ID params[0] = '%') oder eben ein leerer erzeugt. Diese Datenmenge ist nur in meiner Sicht der Datenbank vorhanden, also der Datenbank selbst noch nicht bekannt. Das kommt erst mit dem post- statement. Jetzt wird die Datenmenge erzeugt, geprüft und temporär zwischengespeichert. Somit greift hier auch der Trigger.
Mit applyupdates und commit(retaining) mach ich das Ding dann fest.

Die 0 überschreibt jedenfalls nichts. Mit IBOConsole kontrolliert erscheint brav die 1.

Falls das nicht stimmt, kann das jemand korrigieren?

Gruß

Peter

[khh]

hallo petermännchen,
im Falle von Firebird gehört zum Trigger noch ein Generator(datenbankseitig)
Damit ist es völlig egal auf welche Weise die Daten in die Datenbank gelangen.

Das entsprechende Feld wird automatisch incrementiert.

Gruss KHH

[petermännchen]

hallo KHH,

einen Generator sowie die passende procedure hab ich natürlich auch.

In dem geposteten Quelltext wird dieser auch angesprochen (gen_Mandant)

Gruß

Peter

[ErnstVolker]

Guten Abend,

ich hänge mich mal hier an.
Ich versuche mir etwas Datenbankprogrammierung anzueignen.

Ich bin auf Postgresql unterwegs und habe eine Tabelle mit Primärschlüssel der automatisch hochgezählt wird.
Gebe ich in PGAdmin in die Tabelle etwas ein, dann wird der PK automatisch hochgezählt.

Wenn ich bei Lazarus den DBNavigator verwende und Insert drücke, dann erweitert sich die Tabelle um eine Zeile. Gebe ich hier den PK händisch vor, werden beim Klick auf den "Post"-Knopf die Werte in die Tabelle geschrieben. Lasse ich den PK weg, in der Hoffnung, dass Postgres ihn selber hochzählt, dann kommt eine Fehlermeldung.

Wie teile ich über die ZQuery mit, dass der PK automatisch hochgezählt wird?

Vielen Dank!

[charlytango]

Hi,

früher (also -20 Jahre oder so) gab es mit autoincrement-Feldern immer wieder mal Probleme.
Mittlerweile sind diese Feldtypen sauber, verlässlich und Sessionsicher implementiert. Da kann man sich drauf verlassen.

Das ist eine Eigenschaft/Funktion der Datenbank. Die Query/SQLQuery/TZQuery hat damit nichts zu tun.
Die Inserts werden demnach ohne den Primary Key im Statement formuliert.

Jede der Datenbanken hat allerdings ihre eigene Möglichkeit einen soeben erstellten Primary Key abzufragen um ihn zb in einer anderen Tabelle weiter zu verwenden.

Für SQLite: SELECT last_insert_rowid()
Für MySQL/MariaDB: SELECT SCOPE_IDENTITY()
etc.

Ich würde das Erstellen eines PK der Datenbank überlassen und da nicht reinpfuschen.

[ErnstVolker]

Das würde ich ja gerne, aber es funktioniert über den DBNavigator nicht.

Ich muß dazu sagen, dass die Inhalte der Zieltabelle um die es geht nur Fremdschlüssel anderer 4 Tabellen sind.

Die Datasourcen dieser 4 Tabellen sind als ListSource in LookUpComboboxen verknüpft und die Datasource der Zieltabelle ist DataSource der LookUpComboboxen.

Die Datasource der Zieltabelle ist auch mit einem DBGrid verbunden (ich will ja sehen was passiert) und mit einem DBNavigator.

Drücke ich am Navigator "Insert" erscheint im DBGrid eine neue Zeile, die sich dann auch aus den LookupComboboxen füllen lässt. der PK in der neuen Zeile bleibt leer, denn den soll ja Postgres selbst hochzuholen. Beim drücken auf den Butten "Post" am DBNavigator funktioniert aber genau das nicht. Der neue Datensatz wird nicht übernommen sondern das Ganze scheitert mit der Fehlermeldung, dass die Bedingung für das Feld des PK (Not NULL) verletzt wird. Editiere ich auch den PK und gebe einen Wert vor, dann funktioniert das Drücken auf "Post" des DBNavigator. Das ist mein Problem.

Wie bekomme ich es von Lazarus aus hin, dass Postgres bei "Post" am DBNavigator den PK selbst verwaltet?

[charlytango]

Wie bekomme ich es von Lazarus aus hin, dass Postgres bei "Post" am DBNavigator den PK selbst verwaltet?

Falls du in der Tabellendefinition Postgres mitgeteilt hast, dass dein PK automatisch hochgezählt werden soll, wird es das wohl auch tun.

Ich vermute dass dein Problem eher darin liegt dass die Kommunikation mit einem beliebigen SQL Server ein Client-Server System darstellt in dem die Daten zu unterschiedlichen Zeitpunkten in unterschiedlichen Pufferschichten aufgehoben werden.

Ich empfehle die Lektüre von https://wiki.freepascal.org/SqlDBHowto/ ... C3.A4ndern und den restlichen Artikel.

Du kannst auch in den bei Lazarus mitgelieferten Beispielen das stichwort "mushroom" verwenden um eine Beispielapplikation zu finden. Dort findest du folgenden Code:

Code: Alles auswählen

procedure TForm1.DBNavigator1BeforeAction(Sender: TObject; Button: TDBNavButtonType);
begin
  if Button = nbRefresh then
  begin
    SQLQuery1.ApplyUpdates;
    SQLTransaction1.CommitRetaining;
  end;
end; 

Deine geänderten Daten (also ohne PK) werden mit SQLQuery1.Post in den lokalen Puffer von SQLQuery1 geschrieben. Deswegen sind sie auch in allen datenbanksensitiven Controls und Grids sichtbar.
Davon weiß zu diesem Zeitpunkt aber die DB noch nix, deswegen hast du auch noch keinen neuen PK. Erst SQLQuery1.ApplyUpdates schiebt die Daten zur DB die daraufhin mit der Vergabe des neuen PK reagiert. Das ist aber nur dann in der DB fixiert wenn die dazu nötige Transaktion (SQLTransaction1) die ausstehenden Aktionen in der DB auch festschreibt (nennt sich Commit).
TSQLTransaction kann man auch so einstellen dass sie im Zusammenspiel mit TSQLQuery solche Commits auch automatisch machen.

Nun ist in der DB alles in Ordnung aber dein SQLQuery1 hat noch keine Ahnung dass die DB einen neuen PK vergeben hat und die Daten gespeichert hat.
Deswegen ist ein "Refresh" der Daten nötig um den aktuellen Zustand anzeigen zu können. Der obige Code bündelt das alles in das Event des DBNavigators.

Dieser Refresh bringt die Daten erneut ins SQLQuery1 und zeigt sie über die DataControls an.

Genau wegen dieser ablaufenden Strukturen empfiehlt es sich die angezeigten und vor allem abgeholten Daten auf das nötige zu begrenzen um die Programme performant zu halten. SELECT * FROM scheidet da dann schnell mal aus

Falls du deine Ansicht der Daten zum Zustand nach dem Post bringen möchtest sind
SQLQuery1.DisableControls und SQLQuery1.Bookmark deine Freunde.

[ErnstVolker]

Guten Morgen,

vielen Dank charlytango für Deine ausführlichen Informationen!

Ich war mit ZEOS unterwegs und habe mein Beispiel zur Nachverfolgbarkeit auf die Lazarus-SQLdb-Komponenten umgebaut. Zu PostgreSQL muß ich anführen, dass ich auf'm Mac mit der Postgres.App von https://eggerapps.at unterwegs bin. Das erschien mir bei der Installation sehr einfach und man hat eine GUI zum stoppen des Servers etc.

Wenn man sich den Paketinhalt der App anzeigen lässt, kann man libpq.dylib als Pfad kopieren und in den SQLDBLibraryloader einfügen und hat den DB-Zugang, zur Design-Zeit! Wenn das Programm kompiliert ist, wird die "libpq.dylib" nicht gefunden. Das macht keinen Spaß!

Deshalb kann ich bis jetzt noch nicht berichten ob's erfolgreich war.

[charlytango]

Ich war mit ZEOS unterwegs und habe mein Beispiel zur Nachverfolgbarkeit auf die Lazarus-SQLdb-Komponenten umgebaut. Zu PostgreSQL muß ich anführen, dass ich auf'm Mac mit der Postgres.App von https://eggerapps.at unterwegs bin. Das erschien mir bei der Installation sehr einfach und man hat eine GUI zum stoppen des Servers etc.

Wenn man sich den Paketinhalt der App anzeigen lässt, kann man libpq.dylib als Pfad kopieren und in den SQLDBLibraryloader einfügen und hat den DB-Zugang, zur Design-Zeit! Wenn das Programm kompiliert ist, wird die "libpq.dylib" nicht gefunden. Das macht keinen Spaß!

Deshalb kann ich bis jetzt noch nicht berichten ob's erfolgreich war.

ZEOS benutze ich seit vielen Jahren SQLdb erst seit kurzem

Du kannst dir ansehen wie ich das Datenmodul hier gebaut habe, oder du verwendest das komplette Teil einfach as is.

Die Datenanzeige zur Designzeit ist ein nettes Feature das ist seit Jahren nicht mehr verwende. Bei den geringen Kompilierzeiten von Lazarus sehe ich mir die FDaten lieber in der gelinkten Version an als Probleme zu riskieren weil ich etwas dummes eingestellt habe. Je mehr du programmierst umso mehr verlagert sich die Einstellung auch in den Code.

Wenn du TSQLConnector und TSQLLIbraryloader benutzt würde ich erstmal keine Bibliothek angeben sondern nur den Connector auf 'PostgreSQL' stellen, der Rest wird eigentlich auf das Betriebssystem angepasst (zumindest bei Windows und Linux, also sollte MacOS auch funktionieren)

Wenn du die Zugriffsbibibliotheken nicht kopierst sondern korrekt installierst sollten sie auch gefunden werden.

[Sieben]

Ich hätte da noch eine Zwischen- oder Randbemerkung, die sich allerdings eher auf den älteren Teil des Threads bezieht. Wenn ich einen Record clientseitig initialisieren, also Felder eines neuen Datensatzes in meiner Software durch irgendwelche, wie auch immer gewonnenen Werte vorbelegen möchte - das können zB bei einer kontinuierlichen Datenerfassung auch Werte des vorher eingegebenen Datensatzes sein - würde ich dafür nicht AfterInsert, sondern besser OnNewRecord nehmen.

Der Unterschied besteht darin, dass nach OnNewRecord das Modified-Flag nicht gesetzt ist, was wiederum unmittelbare Auswirkungen auf das Verhalten des Cancel-Befehls hat - der neue Datensatz 'verschwindet' einfach, sofern der User noch keine weiteren Daten eingegeben oder die vorgegebenen verändert hat, nach AfterInsert bliebe er bestehen und müsste explizit gelöscht werden.

Und natürlich ist auch das Modfied-Flag selbst für die eigene Anwendung nur so wirklich aussagekräftig. Zu beachten ist natürlich auch, dass Modified immer nur für den jeweils aktuellen Datensatz gilt, nicht für den gesamten Datenbestand eines DataSet.

[charlytango]

Da ja oft Felder vorbelegt werden müssen, ist es nicht wirklich Mehraufwand.

Im Sinne der Trennung von Datenhaltung, Daten und GUI ist immer zu bedenken welche Einheit welchen Job erledigt.

Alles was Keymanagement und Standardvorgaben für Felder betrifft würde ich in jedem Fall die Datenbank machen lassen.
Create Statements und Basis-Datenbestückung (zb für Auswahlfeler etc) sammle ich in einer oder mehrerer Dateien, die ich bei Bedarf mittels TSQLScript (oder dem ZEOS Äquivalent) bei Bedarf ausführen lasse. Moderne SQL Dialekte erlauben in solchen Scripts auch Logiken wie

Code: Alles auswählen

DROP TABLE IF EXISTS Album;
CREATE TABLE IF NOT EXISTS Album
(
    AlbumId INTEGER  NOT NULL,
    Title VARCHAR(160)  NOT NULL,
    ArtistId INTEGER  NOT NULL,
    CONSTRAINT PK_Album PRIMARY KEY  (AlbumId)
);

Dann braucht es keine Schreibübung

Code: Alles auswählen

create table t1 (
   id integer generated by default as identity primary key
)

So ein Sprachkonstrukt ist unter Firebird 3 bzw 4 möglich

Ich würde auf die neueste Version updaten und alles der DB überlassen, dafür ist sie da.