Firebird: Generator mit dynamischen Namen

[MrSpock]

Hallo,

kann ich in einer Stored Procedure in Firebird einen Generator mit "dynamischen Namen" erzeugen, also etwa so:

Code:

 gname =  'ERFN' || jahr || monat;
    create sequence "gname";
    erfnr = gen_id("gname", 1)

[mkinzler]

Notfalls das Statement in einem String zusammenbauen und dann per EXECUTE Block aufrufen.

[MrSpock]

Ja, danke, funktioniert:

Code:

 execute statement 'create sequence erfnr' || jahr || monat;

[Dejan Vu]

Geht das in Richtung 'Sprechende ID'? Wird also das Ergebnis als PK verwendet?

Nur der Vollständigkeit wegen: Wenn ja, dann: DON'T! Das ist ein Anti-Pattern.

[mkinzler]

Was hat der Name eines Generator mit desen Inhalt zu tun?

[MrSpock]

Nein, keine sprechende ID. Ich benötige einen Generator, der eine ID (Erfassungsnummer) hochzählt, diese Nummer fängt jede Woche, jedes Jahres wieder bei 1 an. Ich habe deshalb vor, einen Generator dynamisch zu erzeugen: z.B. ERFNR1501 für Erfassungsnummern in der ersten Kalenderwoche des Jahres 2015. Wenn der generator noch nicht existiert, wird er neu angelegt, ansonsten wird mit GEN_ID die nächste Nummer bei der DB gezogen.

[Blup]

Generatoren zählen unabhängig von Transaktionen. Das Anlegen einer neuen Erfassungsnummer ist zumindest eine fachliche Transaktion.
Eine Tabelle, in der für jedes Jahr/Woche, die letzte Erfassungsnummer abgelegt wird, scheint mir die bessere Lösung.

[Lemmy]

Zitat:

Zitat von MrSpock (Beitrag 1313953)

kann ich in einer Stored Procedure in Firebird einen Generator mit "dynamischen Namen" erzeugen, also etwa so:

wie es geht wurde schon geschrieben, Aber:

http://www.firebirdsql.org/refdocs/l...-execstat.html

Zitat:

Warning

Although this form of EXECUTE STATEMENT can also be used with all kinds of DDL strings (except CREATE/DROP DATABASE), it is generally very, very unwise to use this trick in order to circumvent the no-DDL rule in PSQL.

[MrSpock]

Hallo Lemmy,

danke für die Info. Hast du eine Ahnung, warum diese Warnung ausgesprochen wird?

[jobo]

Ich verstehe den Sinn nicht, ein aufrufendes Programm (oder auch Trigger) muss dann ebenfalls noch umgeschrieben werden, um den neuen Generatornamen zu beherrschen?
Wenn schon dynamisches DDL, warum dann nicht ein Drop bzw. Reset des bestehenden Generators unter gleichem Namen?

[MrSpock]

Hallo jobo,

weil der User auch nachträglich noch in eine vorherige Erfassungswoche wechseln kann. Im Moment benutzt das Programm eine ini-Datei, umden aktuellen Wert der Erfassungsnummer pro Woche/Jahr einzulesen und zu speichern. Das Programm läuft seid fast 10 Jahren so ohne Probleme. Der Kunde möchte jetzt aber Mehrbenutzerbetrieb und da will ich die Nummer durch die DB erzeugen lassen.

[jobo]

Zitat:

Zitat von MrSpock (Beitrag 1313988)

weil der User auch nachträglich noch in eine vorherige Erfassungswoche wechseln kann.

Ok, wenn das nicht verkürzt dargestellt ist, wären eigentlich nur 2 Generatoren nötig.

Wäre es nicht am hübschesten, eine Funktion zu definieren, die das anhand eines Datumsparameters (oder KW Parameter) richtig ausspukt?

Zu dem Zitat von Jumpy:
Ich kann nur eine Idee anhand des Verhaltens von Oracle geben. Hier ist es so, dass DDL implizit ein Commit durchführt. Wenn also innerhalb ein Oracle Code Blocks ein DDL Statement aufgerufen wird, passiert "nichts schlimmes" außer halt ein Commit.
Wenn alles gut geht ist es wunderbar, wenn nicht hat man ein riesen Problem, weil ein Fehler oder ein explizites Roleback nur noch einen Teil der Transaktion zurückdreht. Vielleicht ist es bei FB ähnlich.
Deswegen gibt es diese Regel bei uns intern genauso wie bei Firebird. Wer erwischt wird, muss stramm stehen und sich wüßt beschimpfen lassen, bekommt die Haare abrasiert (wenn er noch keine Glatze hat) und muss für die Kollegen eine Woche Kaffee ausschenken.

[p80286]

Zitat:

Zitat von MrSpock (Beitrag 1313985)

Hallo Lemmy,

danke für die Info. Hast du eine Ahnung, warum diese Warnung ausgesprochen wird?

aus dem gleichen Grund aus dem man ein Messer nicht ablecken sollte......

Wenn der Kunde diesen Generator will.. ok, wenn er will, trotzdem werde ich das Gefühl nicht los, daß da irgendein Datenbank-Voodoo veranstaltet wird. Meiner Meinung nach sollte ein "ErfassungsStartdatum" das vom Benutzer eingegeben/bestätigt wird, ausreichen um die Akte/den Vorgang in die richtige Woche zu verschieben.

Gruß
K-H

[Hansa]

Die Warnung ist doch klar : ein Generator eignet sich in keinster Weise dazu, Informationen ausser dem Generatorwert an sich, zu speichern. Es gibt noch nicht mal Transaktionssteuerung. Den lässt man einfach laufen und sonst in Ruhe. Hier wird jetzt versucht, dem Generator über seinen eigenen Namen irgendwelche Zusatzinformationen zu verschaffen.

Hier wurde auch schon mal empfohlen, einen Generator zur Erzeugung von fortlaufenden Nummern zu erzeugen. Das geht in ähnliche Richtung. Die Nr. kann ich doch auch in Tabelle speichern. Da steht dann eben nur ein Datensatz drin mit der Rechnungsnummer. Wen interessiert das denn ? Ich kann dann zumindest mal eine total falsche Rechnung schreiben, merke das, mache ein Rollback und fertig. Über Generator müsste ich die Datenbank umschreiben.

Roter Kasten sagt gerade : Mehrplatzbetrieb ! Um Gottes Willen, dann dreimal Finger weg von Generatoren ! *haarsträub*

[mkinzler]

Zitat:

Mehrplatzbetrieb ! Um Gottes Willen, dann dreimal Finger weg von Generatoren ! *haarsträub*

Mehrplatzbetrieb ist kein Problem für Sequenzen, wen GEN_ID() verwendet wird.

[Lemmy]

Zitat:

Zitat von MrSpock (Beitrag 1313985)

danke für die Info. Hast du eine Ahnung, warum diese Warnung ausgesprochen wird?

Ich denke aus dem selben Grund weshalb man bei Firebird (und anderen) nicht in den Systemtabellen manuell rumwursteln soll:
Weil Dinge schief gehen können. Das Beispiel mit dem Messer ablecken finde ich sehr treffend ;-)

Beispiel: Kollege wollte einen Float vergrößern (Numeric 4,3 auf 6,4) - und weil es schnell gehen musste und man auf die schnelle den Befehl nicht auswendig kannte, griff man die Systemtabellen an und hat Länge und Genauigkeit verwechselt - mit dem Erfolg dass nach dem "Update" nichts mehr ging: Keine Datensicherung, Programm nicht mehr usw....

Mit einem Alter Domain wäre man gar nicht in Versuchung geraten die Zahlen falsch rum einzutragen und wäre zudem von den Firebird-Internas geschützt geworden.

Aber: Keine Frage, dass in manchen Situationen der Zugriff auf die Systemtabellen hilfreich ist....

Ich würde jetzt auch nicht annehmen, dass die Erstellung von Generatoren durch eine StoredProcedure Probleme nach sich zieht, aber ich denke das Konzept ist dennoch nicht gut, weil die Anzahl der Generatoren in Firebird begrenzt ist:

http://www.firebirdsql.org/manual/de...sqlsyntax.html

auch dann, wenn es aktuell danach aussieht, dass die gut 600 Jahre ausreichen würden.

[mkinzler]

Ab FireBird 3 kann man die Systemtabellen nicht mehr direkt manipulieren.

[Hansa]

Zitat:

Mehrplatzbetrieb ist kein Problem für Sequenzen, wen GEN_ID() verwendet wird.

So meine ich das nicht. GEN_ID verschafft doch keine Transaktionssteuerng für einen Generator. :shock: Für Mehrplatz wird aber eine vernünftige Transaktionssteuerung gebraucht. Ansonsten fliegt einem bei jeder Gelegenheit die DB um die Ohren. Was ist denn wenn zwei Benutzer den gleichen Datensatz zur selben Zeit ändern oder löschen wollen ? Mit der richtigen Einstellung der Transaktions-Parameter lässt sich das schnell richtig realisieren. Zur erstbesten Gelegenheit die Transaktionssteuerung völlig unnötig zu umgehen ist meiner Meinung nach aber total falsch. Kann zwar gehen, wird aber ziemlich sicher Ärger bringen. Würde mich auch nicht wundern, wenn das in neueren FB-Versionen sogar unterbunden wird.

Mir fällt gerade ein, bei der Diskussion mit den fortlaufenden Rechnungsnummern, das warst ja auch Du, Markus, der Generatoren dafür empfohlen hat. War ähnliche Diskussion wie jetzt hier. Es könnte Spocki vielleicht helfen, statt des empfohlenen Generators genügt zumindest mir folgendes (zwar für Rechnungsnr., dürfte sich aber 1:1 übernehmen lassen, string verwenden o.ä.) :

Code:

CREATE TABLE RECNR (
    NR  INTEGER);

CREATE OR ALTER PROCEDURE RECNR_SP
returns (
    recnr integer)
as
declare variable neuerecnr integer;
BEGIN
  NEUERECNR = -1;
  SELECT NR FROM RECNR INTO :NEUERECNR;
  IF (NEUERECNR < 0) THEN BEGIN
    INSERT INTO RECNR (NR) VALUES (1);
    RECNR = 1;
  END
  ELSE BEGIN
    UPDATE RECNR SET NR = NR + 1;
    RECNR = NEUERECNR + 1;
  END
  SUSPEND;
END^

Kommt ziemlich auf Generator/Trigger raus. In dem Fall hier hat die Tabelle RECNR einen einzigen Datensatz mit der letzten Nr. Mit der SP zähle ich die Nr. immer um 1 hoch und kann sie auch direkt per Delphi verwenden. Im Gegensatz zum Generator kann ich aber auch Rollback usw. machen.

[mkinzler]

Zitat:

Für Mehrplatz wird aber eine vernünftige Transaktionssteuerung gebraucht.

Sicher, aber was hat das mit Generatoren zu tun?

Zitat:

Was ist denn wenn zwei Benutzer den gleichen Datensatz zur selben Zeit ändern oder löschen wollen ?

Was hat das mit Generatoren zu tun?
Rechnungsnummern sind etwas anderes als IDs. Aber auch bei Rechnungsnummern sind Lücken kein Problem. Die GEN_ID()-Funktion wird durch eine Semaphore implementiert und stellt somit sicher, dass die selbe Nummer 2mal erstellt wird.
Was bei Deiner Methode nicht der Fall ist! Ohne expliziten Lock der Tabelle könnte es passieren, dass 2 gleichzeitige Vorgänge 2 identische Nummern generiert!

[MrSpock]

Eben wegen der Semaphore bei GEN_ID() möchte ich diese ja als Zähler benutzen. Der Zähler beginnt jeder Woche/Jahr wieder bei 1 und GEN_ID() verhindert die doppelte Vergabe. Ich könnte jetzt z.B. 53 Zähler für die Kalenderwoche einsetzen und diese nach einem Jahr zurücksetzen. Dann müsste ich aber auch noch eine Fehlermeldung ausgeben, wenn jemand versucht eine Erfassung mit einer Erfassungswoche versucht, die mehr als 52 Wochen her ist, weil ich den Zählerstand nicht mehr habe. Oder ich müsste das die Erfassungsnummer manuel ermitteln, indem ich die letzte Nummer der Wochen/Jahr Kombination auslese. Wenn dann durch Mehrfachzugriff genau in diesem Moment zweimal dieselbe ERFNR ermittelt wird, soll der Nutzer das Einfügen des Datensatzes einfach noch einml versuchen.

Wobei ich in 10 Jahren etwa 530 Zähler erzeugen würde, wenn ich meinem ursprünglichen Ansatz treu bleibe. Und nach 100 Jahren könnte man ja auch die DB Generatoren einmal aufräumen. :lol:

[Hansa]

Zitat:

Zitat von mkinzler (Beitrag 1314011)

Ohne expliziten Lock der Tabelle könnte es passieren, dass 2 gleichzeitige Vorgänge 2 identische Nummern generiert!

Da passiert eben nichts. Ich will ja nicht streiten oder Spocki Vorschriften machen, aber Transaktionskontrolle muss sein. Ich habe umfangreiche Tests durchgeführt. Sowohl für diese Recnr-SP als auch für normale Datensätze, also Updates über Delphi-Programm. 3 Rechner : der erste speichert so schnell er kann. Der zweite lässt sich eine Sek. Zeit, der dritte sogar 2 Sek. Das Ganze übers Wochenende. Bei dem Table-Update habe ich dafür gesorgt, dass die 3 Rechner nur auf drei Datensätze Updates gemacht haben. Nach menschlichem Ermessen und einem nicht wasserdichten System hätte da bei über 60 St. Laufzeit irgendwo ein Konflikt auftauchen müssen. Zumindest sind sie sich wohl schon irgendwo ins Gehege gekommen. Sonntag mittags hat mich dann die Neugier gepackt und was war ? Kein Fehler, nichts. Das mit dem Lock, das scheint überflüssig zu sein.

Hat mich dann auch nicht gewundert, dass Montag morgens immer noch nichts war. Firebird geht schon gut. Für Netzwerk ist nur wichtig : richtige Transaktionskontrolle.

[mkinzler]

Vielleicht nur Glück oder durch das verwendete MP bedingt. Ich würde das aber an Deiner Stelle ändern, denn ab FB3 könnte das dann schön in dei Hose gehen.

[Dejan Vu]

Also bei einer derartigen Konstellation würde ich wirklich empfehlen, eine eigene Funktion dafür zu schreiben. Anhand einer Hilfstabelle werden die Zähler verwaltet. Da das Ganze eh in einer Transaktion ausgeführt wird, sind Dopplungen ausgeschlossen.

Wenn Du einen Generator verwendest, machst Du dich imho implizit von der Implementierung des Generators abhängig bzw. von etwaigen Restriktionen. Das ist zwar hier nicht der Fall, aber unsauber. Finde ich.

[MrSpock]

Jetzt hab ich es "fast" so, wie ich es haben wollte. Meine SP sieht wie folgt aus:

Code:

CREATE OR ALTER procedure GET_ERFNR (
    JAHR varchar(2) not null,
    WOCHE varchar(2) not null)
returns (
    ERFNR integer)
as
declare variable ANZ integer;
declare variable GEN_NAME varchar(10);
begin
  /* Procedure Text */
  gen_name = 'ERFNR' || Jahr || Woche;

  SELECT COUNT(1) from rdb$generators where rdb$generator_name = :gen_name into :anz;

  if (anz <> 1) then
    execute statement 'create sequence ' || gen_name;

  execute statement 'SELECT GEN_ID(' || gen_name || ',  1) from rdb$database';
  suspend;
end

Ich prüfe als, ob z.B. der Generator ERFNR1503 schon existiert und wenn nicht, erstelle ich ihn. Funktioniert einwandfrei.
Jetzt brauche ich nur noch den nächsten Wert üner GEN_ID, dazu habe ich die zweite execute statement Anweisung. Ich muss aber den Wert von dem Generator als Wert der SP zurückgeben, aber das will nicht funktionieren. Ich kann weder den Wert über "SELECT GE_ID ... into :ERFNR" noch über ERFNR = execute statement 'SELECT ...' oder über execute statement 'ERFNR = SELECT ...' in ERFNR zurückgeben.

Hat jemand eine Idee, wie ich den Wert des zweiten EXECUTE Statements zurückgebe?

[bepe]

Das kannst du mit execute into machen:

http://www.firebirdsql.org/refdocs/l...-execstat.html

[MrSpock]

Genial!

Das hat mir noch gefehlt, jetzt funktioniert es:

Code:

CREATE OR ALTER procedure GET_ERFNR (
    JAHR varchar(2) not null,
    WOCHE varchar(2) not null)
returns (
    ERFNR integer)
as
declare variable ANZ integer;
declare variable GEN_NAME varchar(10);
begin
  /* Procedure Text */
  gen_name = 'ERFNR' || Jahr || Woche;

  SELECT COUNT(1) from rdb$generators where rdb$generator_name = :gen_name into :anz;

  if (anz <> 1) then
    execute statement 'create sequence ' || gen_name;

  execute statement 'SELECT GEN_ID(' || gen_name || ',  1) from rdb$database' into :erfnr;
  suspend;
end