SQLdb, Firebird und auto_increment

[Joh]

Moin,
irgendwie schafft es Lazarus immer wieder, das ich mich wie ein Anfänger fühle.

Jetzt möchte ich die Tabellen eines Projektes auf automatische id's umstellen. Firebirdseitig ziemlich klar, ein

Code: Alles auswählen

INSERT INTO TABELLE (BEZ) VALUES ('Test');

funktioniert so weit; die id wird erstellt.
Auch ein

Code: Alles auswählen

frmTest.IBConnection.ExecuteDirect('INSERT INTO TABELLE (BEZ) VALUES (''Test2'')');

funktioniert wie erwartet. Aber was zum #$&!öö% muß ich einstellen, damit ich in meiner TSQLQuery das gewünschte Verhalten bekomme. Meistens kommt die Meldung "field id is required, but not supplied"...
faRequired steht auf false, Datatype ftAutoInc oder ftInteger ändern interessiert nicht.
Ob ich im InsertSQL die id mit eintrage oder weglasse, spielt keine Rolle.
// Gefühlt würde ich sagen, da dürfte auch nur

Code: Alles auswählen

INSERT INTO TABELLE (BEZ) VALUES (:Bez)

stehen.

Was übersehe ich hier?

[charlytango]

irgendwie schafft es Lazarus immer wieder, das ich mich wie ein Anfänger fühle.

Das hat wohl weniger mit Lazarus zu tun als mit der Tatsache dass man bei Datenbankprogrammierung mehrere Bälle gleichzeitig jonglieren muss.

Die DB und deren Struktur -- die Logik wie das alles funktioniert samt deren Syntax und dann noch die Lazarus-Komponenten.

Meine Vorgehensweise:
besorg dir ein Frontend für Firebird - IMHO scheint Flamerobin populär zu sein.
Damit testest du SQL Statements direkt gegen deine Firebird-DB. Dann bistdu zumindest mal sicher dass das was du machen möchtest auch mit entsprechenden SQL Statements klappt.
Dann brauchst du das "nur" mehr Lazarus beibringen.

damit ich in meiner TSQLQuery das gewünschte Verhalten bekomme

Da solltest du uns mitteilen was das gewünschte Verhalten ist.

-- wie ist die Struktur deiner Tabelle? (bzw das Create Statement) um zu checken ob die DB ok ist. (Bzw ggfs nötige Generatoren und Trigger)

Meine SQL-Server sind MySQL, SQLite, MSSQL, MariaDB -- mit Firebird kenne ich mich nicht wirklich aus. Scheinbar ist es da nicht so ganz einfach ein autoincrement Feld zu definieren

Quelle Firebird
Noch eine Quelle die auch zeigt wie du an die neue ID heran kommst.

Konntest du das autoincrement-Feld sauber erledigen/verifizieren? Die DB selbst macht was du möchtest?

Dann ist die Frage: Was willst du erreichen/machen ?

Möglicherweise hilft dir das Wiki zu TSQLQuery unter 8.2

[gladio]

im Titel steht was von Auto_Increment.
Diese Eigenschaft/Verhalten muss bei der Definition eines Feldes zugewiesen werden.
Besonderheit bei Firebird: Firebird biete die Eigenschaft Autoincrement nicht direkt an. Da muss das Verhalten über einen Generator erzeugt werden.
Ob das jetzt in Version 4 geändert wurde, weiß ich nicht.

[Joh]

i) @gladio: Ich benutze DBeaver als SQL-Frontend.
Über dieses geniale SQL-Tool sollten wir auch noch mal sprechen, aber das sollte mindestens einen eigenen Thread notwendig machen.

ii) ich habe die Tabellenbeschreibung auf das nötigste verkürzt; in dem (Test)Fall geht es um die Tabelle Rechnungspositionen:

Code: Alles auswählen

sql := 'CREATE TABLE RechnungPositionen ('
     + 'id             int generated by default as identity primary key,'
     + 'Pos            int,'
     + 'RechnungID     int,'
     + 'Menge          decimal(9,2),'
     + 'ePreis         decimal(9,2),'
     + 'ekPreis        decimal(9,2),'
     + 'MwStProz       decimal(5,2),'
     + 'KurzBez        varchar(40),'
     + 'Bez            varchar(255))';   

Wie gesagt:
Das automatische Generieren des auto_inc-Wertes funktioniert in Firebird.
Ein Statement via SQL-Statement in DBeaver läuft.
Ein Einfügen in Lazarus als IBConnection.ExecuteDirect-Statement funktioniert.

Ich bekomme es nur nicht hin, das ganze dem TSQLQuery beizubringen.
Sprich: wo muß ich in den TSQLQuery-Einstellungen was einstellen, damit das ganze auch via SQLRechnungPositionen.ApplyUpdates funktioniert.

PS: einen Schritt weiter bin ich: Ich habe die Feldliste hinzugefügt; dort gibt es auch eine Eigenschaft required.

[gladio]

Irgendwelche speziellen Einstellungen in der TQuery sind im Normalfall nicht erforderlich.
Hast du nach dem Insert auch ein SQLRechnungPositionen.post ?

[Soner]

Ich glaube die Eigenschaft dafür ist TSQLQuery.Sequence. Ich würde TSQLQuery.Sequence.ApplyEvent zu saeOnPost ändern, damit keine Lücken bei ID entsteht und dann muss du noch bei Fieldname "id" eingeben und bei SequenceName Generatorname eingeben.
Wenn du nicht weißt, was Generatoren bei Firebird sind dann gehe zu Google "generator firebird sql".
Ich habe es nicht getestet, aber alles deutet darauf hin.

Du kannst alles auch manuell machen und bei TQSQLQuery.BeforePost selber hinzufügen.

[Joh]

Ich arbeite mit Firebird 4. Da ist Autoincrement integriert via "generated by default". Ich glaube, das mit der Sequence ist noch Firebird 2.x.

Ich werde am / nach dem Wochenende ein separates Testprojekt erstellen; Vielleicht wird dann ja einiges klarer.

[Frickler]

"generated by default as identity" gibts seit Firebird 3. Das nutzt auch einen Generator, aber einen internen, dessen Name man vielleicht herausfinden kann, aber besser nicht "von der Seite" ansprechen sollte.

Ab Firebird 4 gäbe es auch "generated always as identity". Der Unterschied: bei "by default" kann man beim INSERT auch selbst einen Wert setzen, der nicht aus der Sequenz kommt. Der hat dann Vorrang. Bei "always" wird immer der Generatorwert verwendet.

Siehe auch https://firebirdsql.org/file/documentat ... nz-de.html

[ErnstVolker]

Guten Tag,
ich stelle meine Frage mal hier, weil sie auch was mit Autoincrementen (Sequenzen in Postgres) zu tun hat. Ich bin mit Postgres unterwegs.

Ich habe eine ButtonClick-Prozedur, bei der in zwei Tabellen (Tabelle_1 und Tabelle_2) nacheinander "INSERT" durchgeführt wird.
Für den Insert in Tabelle_2 ist der Primareykey aus Tabelle_1 notwendig. Den hole ich mir mit "CURRVAL" aus der Sequenz von Tabelle_1.
Das ganze läuft nacheinander durch verschiedne SQL-Zuweisungen über eine ZEOS-TZQuery-Variable (Eingabe) die ich am Anfang initialisiere und am Ende wieder frei gebe (FreeAndNil(Eingabe)).

Die nacheinander ablaufenden Vorgänge sind in einem try-except-Block verpackt.

Tabelle_2 hat jetzt eine UNIQUE-Constraint, die nicht verletzt werden darf. Wenn sie Verletzt wird, dann tritt der except-Fall ein und eine Fehlermeldung wird geworfen. Die Änderung an Tabelle_2 findet nicht statt, wird zurückgewiesen. Allerdings zählt das Autoincrement von Tabelle_2 trotzdem hoch. Das kann man mit PGAdmin nachvollziehen.

Nach dem Zurückweisen müssten drei Dinge rückgängig gemacht werden (von hinten nach vorne):

1.) Das Increment von Tabelle_2 wieder um 1 reduzieren um den Wert nicht zu vergeuden.

2.) Den Eintrag in Tabelle_1 wieder löschen und 3.) auch dort das Increment der Sequenz um 1 reduzieren. Die wurde ja auch hochgezählt.

Ich dachte an einen Träger in Postgres, aber der müsste auf die Unique-Verletzung von Tabelle_2 reagieren. Die Trigger reagieren aber auf BEFORE/AFTER INSERT/UPDATE/DELETE

DELETE des Datensatz an Tabelle_1 müsste dann die Folge des Triggers sein. Den Datensatz löschen und beide Autoincremente um 1 reduzieren.

Die UNIQUE-Bedingung von Tabelle_2 sind eine Datumsspalte und eine Nummernspalte. Jede Nummer darf es zu jedem Datum nur einmal geben.

Wie löst man sowas?

Schönen Sonntag

Volker

[af0815]

Generell sollte man Keys nicht monoton aufsteigend erwarten im Design. Wenn eine Lücke zulässig ist, dann gibt es keine Probleme bei abgebrochenen Transaktionen. Bis zu einem gewissen Grad kann man das atomar kapseln, wenn du über das Ganze eine manuell erstellte Transaktion legst.

[charlytango]

Tabelle_2 hat jetzt eine UNIQUE-Constraint, die nicht verletzt werden darf.

Es kommt beim DB-Design immer darauf an wieviel "Intelligenz" die DB haben soll und was im Client erledigt wird.
Besonders wichtig sind solche Überlegungen wenn mehrere unterschiedliche Programme (ggfs auch von unterschiedlichen Programmierern und auch mit unterschiedlichen Systemen) schreibend gegen eine Datenbank arbeiten.
Dann muß einiges die DB erledigen um die DB-Integrität auf jeden Fall aufrecht zu erhalten.

Nachdem das bei meinen Programen so kaum der Fall ist, vermeide ich es, allzuviel Inteligenz in die DB zu packen sondern kümmere mich selbst um die DB-Integrität im Stile eines "fat client."

BTW: Was ich hier mit dem Jonglieren mehrere Bälle gesagt habe gilt nach wie vor.
Ein try/except fängt Exceptions innerhalb des Clients ab. Die DB beeindruckt das nicht. Wenn mit einem SQL-Statement ein Constraint verletzt wurde lässt sie dieses Statement nicht zu, die Zugriffskomponente erzeugt daraus eine Exception.

Jede Nummer darf es zu jedem Datum nur einmal geben.
Wie löst man sowas?

Indem du vorher mittels SELECT vorher prüfst/validierst ob die Werte die du eintragen willst auch der Constraint-Regel folgen. Das könnte zb in einem Validate-Event passieren, das ein Abschicken der Statements ggfs verhindert.

Wobei noch nicht definiert wurde welcher Art die "Nummer" ist -- irgendeine? eine eigens erzeugte und selbst vergebene?

[ErnstVolker]

Die Nummern sind einfache Ganzzahlen von 1 bis 200, wobei in der Regel nur so um die 80 benötigt werden. Sie werden aus einer DBLookUp-Combobox bereitgestellt. Der Bediener muß sie auswählen. Es kann halt passieren, dass der Bediener vergessen hat, was die letzte Nummer war. Es gibt zwar die Möglichkeit durch Aufruf eines Übersichts-Formular, was anzeigt, welche Datum-Nummern-Kombinationen bereits vergeben wurden, sich über den "Verbrauch an Nummern" zu informieren, das Eingabeformular arbeitet aber auch eigenständig, ohne die Übersicht.

[charlytango]

wenn irgendwo aus 80 Werten oder mehr etwas in einer Combobox auszusuchen ist, dann stimmt irgendetwas mit dem Design nicht. Das ist völlig unhandlich für einen Benutzer.

Aus meiner Sicht ist so ein Ansatz völlig unbenutzbar (Ohne die Businesslogik dahinter zu kennen)

Was spricht denn dagegen die Nummernvergabe zu automatisieren?

Soll das in einer Mehrbenutzerumgebung funktionieren?

zb eine Vergabetabelle mit einer Autoincrementspalte als Primary Key und einer spalte fürs Datum, sowie einer Spalte für die zu vergebende Nummer, die aus dem Primary Key mittels modulo 200 errechnet wird.
dh die Tabelle wird in Junks zu 200 aufgeteilt. Nur so als Basisidee

[ErnstVolker]

Naja, es macht für die Anwender schon Sinn es so zu machen. Der Anwender darf sich halt nicht vertun.

Die Anwendung ist eine öffentliche Versteigerung die einmal im Monat stattfindet. Am ersten Mittwoch im Monat, es sei denn es ist ein Feiertag.

Also wird am Ende des Jahres die Tabelle mit den Datumswerten händisch für das kommende Jahr gefüllt. Da im Januar und Juli keine Versteigerungen stattfinden sind das für ein Jahr 10 Datumseinträge.

Die Nummern sind die Versteigerungsnummern. Es kommt jetzt durchaus vor, dass zwei bis drei Kollegen Versteigerungsobjekte eingeben. UND DA bin ich mir mit meiner Vorgehensweise mit dem CURRVAL nicht so sicher, dass das zuverlässig funktioniert, wenn kurz hintereinander von verschiedenen Anwendern mit CURRVAL die Sequenz abgefragt wird...

Also im Januar findet keine Versteigerung statt, dann wird NACH der Dezember-Versteigerung diesen Jahres die Versteigerung für Februar 2024 befüllt.

Jetzt kann es passieren, dass Objekte am Versteigerungstag im Februar nicht versteigert, oder nicht bezahlt werden. Beides führt dazu, dass es "Stehenbleiber" gibt, die einfach in die nächste anstehende Versteigerung in den März übernommen werden. ENTWEDER mit der ursprünglichen Nummer, ODER mit neuer Nummer. Die Nummer muß deshalb ggf. gewechselt werden, weil Ende Januar in der 4. Woche Annahmeschluss für Februar ist und schon der März gefüttert wird. Also geht der März wieder mit "1" los. Wenn jetzt bis zur Versteigerung im Februar schon 20 oder 30 Objekte für März eingegeben sind und bei der Versteigerung im Februar z.B. die "15" stehen bleibt, kann sie im März nicht mehr "15" sein, weil diese bereits vergeben ist. Bei höheren Nummern, gegen Ende ist das anders. Wenn die "80" stehen bleibt, dann kann sie im nächsten Monat die "80" bleiben, weil so weit noch nicht vorgearbeitet wurde.
Für das Problem des "Stehenbleibens" habe ich die Tabelle mit dem UNIQUE gemacht. Ich will den Datensatz nicht kopieren sondern weiterverwenden wie er ist. Da hängt noch mehr dran. Die Objekte können Mängel haben, oder es gibt Bemerkungen dazu etc.

Deshalb hat der Anwender die Nummer zu vergeben. Ich bin halt Anfänger was das Beschäftigen mit Datenbanken angeht. Ich habe mit dem Buch "SQL" von Ralf Adams vor zwei Jahren angefangen um überhaupt einen Einstieg zu bekommen. Zwischenzeitlich passiert(e) dann mal mehr mal weniger. Angefangen mit Firebird, dann auf Postgres. Alles in der Freizeit. Da ich nicht der Hellste bin dauert es halt was länger...

[Joh]

Also ein Auktionsprogramm habe ich auch mal geschrieben... (Ist mit 24 Jahren gerade erst volljährig geworden^^.)
Die Nummern der neuen Versteigerung werden bei mir für "nichtversteigerte Objekte" in der nächsten Auktion automatisch vergeben. Also hinten angehängt.
Was soll es für einen Sinn ergeben, das die Mitarbeiter die Nummern bis 37 überkleben müssen, danach aber nicht mehr. Das führt doch nur zu Fehlern im Ablauf. Besser ALLE alten Nummern runter und ALLES neu bekleben. Das verstehen auch die Helfer, die nicht immer dabei sind.

PS: vor der Auktion werden bei mir die Nummern eh noch einmal neu vergeben, da die Nummern-Reihenfolge mit den aufsteigenden Auktionsgruppen (diese Reihenfolge setzt der Auktionator manuell fest) korrelieren sollen.