Wie fortlaufende Nr. in DB-Taballe erzeugen? Generator nicht möglich?

[BlueStarHH]

Hallo,

ich habe eine Firebird-Tabelle mit Buchungen:

Code:

Konto    BuchungNr   Text            usw.
------  ---------  -------------
4600    1          Reise München
4600    2          Reise Berlin
4930    1          Büroklammern

Jedes Konto hat seine eigene Buchungsnummer von 1 bis n. Wie kann ich nun die nächste Buchungsnummer für ein bestimmtes Konto ermitteln? Die nächste BuchungNr für Konto 4600 wäre hier also die 3 und für Konto 4930 die 2.

Es soll in einer Client/Server Umgebung keine doppelten Nummern geben. Auf dem Client den Max-Wert der BuchungNr für ein bestimmtes Konto mit SQL holen scheidet wohl aus, da in der Zeit wo die Abfrage ausgeführt wird, ein anderer Client dazwischenfunken könnte. Ein Generator scheidet wohl auch aus, da ich nicht für jedes Konto einen eigenen Generator anlegen kann. Es sind hunderte von Konten. Ab und zu kommen neue dazu.

Was also tun?

[HPB]

Guten Tag,
Wie wäre es mit

Delphi-Quellcode:

Select count(*) as AnzBuchungen from Table
 where kontonr = :pKontoNr

Allerding hättest Du wieder doppelte Nummern wenn ein Datensatz für ein Konto gelöscht wird.
Dies kann aber übergangen werden, wenn Du in der Tabelle eine Spalte "geloescht = J/N' führst
Sollte eine Buchung gelöscht werden dann setzt Du den Wert auf "J".
Damit kannst Du ja die Daten so selektieren:

Delphi-Quellcode:

select * from table where geloescht = 'N'

Für die nächste Nummer wäre so ein Konstrukt evtl hilfreich:

Delphi-Quellcode:

select count(*) as Anzahl from table where geloescht = 'N' 
and KontoNr = :pKontoNr

Gruß HPB

[BlueStarHH]

Zitat:

Zitat von HPB (Beitrag 1279066)

Guten Tag,
Wie wäre es mit

Delphi-Quellcode:

Select count(*) as AnzBuchungen from Table
 where kontonr = :pKontoNr

Allerding hättest Du wieder doppelte Nummern wenn ein Datensatz für ein Konto gelöscht wird.
Dies kann aber übergangen werden, wenn Du in der Tabelle eine Spalte "geloescht = J/N' führst
Sollte eine Buchung gelöscht werden dann setzt Du den Wert auf "J".
Damit kannst Du ja die Daten so selektieren:

Delphi-Quellcode:

select * from table where geloescht = 'N'

Für die nächste Nummer wäre so ein Konstrukt evtl hilfreich:

Delphi-Quellcode:

select count(*) as Anzahl from table where geloescht = 'N' 
and KontoNr = :pKontoNr

Gruß HPB

Hi HPB,

danke, das geht aber nicht denn es gibt beim "count" das selbe Problem wie beim "max-Wert":

Zitat:

Auf dem Client den Max-Wert der BuchungNr für ein bestimmtes Konto mit SQL holen scheidet wohl aus, da in der Zeit wo die Abfrage ausgeführt wird, ein anderer Client dazwischenfunken könnte.

Ich versuche es nochmal deutlicher zu machen. Client A fragt per SQL den count/max-Wert ab. Bevor er den neuen Datensatz posten kann, haben die Clients B und C ebenfalls den count/max-Wert abgefrag. Alle 3 Clients haben dann den selben count/max-Wert und wollen den selben Wert posten. Wer dann zuerst posted hat gewonnen und bei den anderen beiden Clients gibt es eine Fehlermeldung der Art "Buchungsnummer nicht eindeutig"

[Medium]

SQL-Code:

SELECT MAX(BuchungsNr) FROM Tabelle WHERE Konto = :GewünschtesKonto GROUP BY Konto

gibt dir die derzeit höchste Buchunsnummer für ein bestimmts Konto. Lücken bei mitten drin gelöschten Datensätzen verbleiben dann. Wenn dies nicht gewünscht ist, dann bleibt nicht viel mehr als manuell alle Sätze sortiert durchzuiterieren bis eine nicht vergebene Nummer gefunden wurde.

Edit: Aha, jetzt erst das Timingproblem gesehen. Dann bleiben nur Serverseitige Ansätze. Vielleicht ließe sich da ein Trigger für bauen, da bin ich allerdings nicht fit genug drin hierfür. Sorry.

[Lemmy]

Hi,

das Problem kannst Du nur über eine Tabellensperre lösen, d.h. einer schreibt, alle anderen dürfen nur noch lesen.

Je nachdem was Du mit den fortlaufenden Nummer machen willst, könnte dir da aber schon ein Generator helfen - aber kommt halt darauf an ob die nur der Ordnung dienen oder sonst noch eine Funktion übernehmen...

Grüße

[himitsu]

Wenn man nicht ständig suchen will, auch gelöschte Datensätze keine doppelte ID erzeugen sollen, dann könntest du dir auch mehrere "Generatoren" erstellen und je nach Konto den passenden/zugehörigen Generator verwenden.
Bzw. das halt in einer eigenen kleinen Tabelle (Konto, LastNumber) verwalten, oder gibt es schon eine Tabelle mit den Konten?

Selbst wenn man nun alles löscht, blieben due Nummern eindeutig.

[DeddyH]

Zitat:

Zitat von himitsu (Beitrag 1279071)

Wenn man nicht ständig suchen will, auch gelöschte Datensätze keine doppelte ID erzeugen sollen, dann könntest du dir auch mehrere "Generatoren" erstellen und je nach Konto den passenden/zugehörigen Generator verwenden.

Wenn das jemand hinbekommt, würde mich die Umsetzung interessieren.

[Jasocul]

Da sehe ich jetzt nicht so das Problem.
Generatoren kann man auch zur Laufzeit erzeugen. Also beim Anlegen eines Kontos einen Generator mit einem passenden Namen anlegen und diesen dann bei den Buchungen entsprechend benutzen.
Generatoren in Firebird erzeugen

[DeddyH]

Wenn man das aber nicht dem Frontend überlassen will, wird es etwas komplizierter. Sofern mein Gedankengang richtig ist, bräuchte man 2 SPs: eine für das Erzeugen eines neuen Kontos (legt den Generator an) und eine für das Erzeugen einer neuen Buchung (holt sich den aktuellen Generatorwert und trägt ihn ein). Dann müsste man nur noch sicherstellen, dass das Einfügen von Datensätzen nicht mehr direkt, sondern nur noch über diese SPs möglich ist. So könnte es funktionieren, wenn ich mich nicht irre.

[mjustin]

Zitat:

Zitat von himitsu (Beitrag 1279071)

... dann könntest du dir auch mehrere "Generatoren" erstellen und je nach Konto den passenden/zugehörigen Generator verwenden.

Einzige Einschränkung:
immer wenn eine neue Nummer generiert, aber (weil ein Fehler auftritt) nicht beim INSERT verwendet wird, wird diese Nummer unbenutzt und es bleibt eine "Lücke". (Da Generatoren ihre Werte ausserhalb von Transaktionen erhalten, können diese nicht durch ein Rollback verhindert werden.)

[DeddyH]

Das ist aber völlig normal: einmal angefasst, ist die Nummer weg, egal ob verwendet oder nicht.

[Jumpy]

Wenn man die angelegte ID nicht direkt wieder braucht, wäre es über ein Insert-Statement doch einfach möglich, oder geht sowas (Subselect in insert) nicht?

SQL-Code:

insert into tabelle (Konto,Buchungsnummer,Text)
Select
  '4600' as Konto,
  (Select Max(BuchungNr)+1 From Tabelle Where Konto='4600'),
  'Irgendein Text'
From
  InOracleWürdeIchHier"DUAL"nehmen

Evtl. kann man sowas in eine SP packen, die dann die ID noch zurück liefert?

[mm1256]

Hallo,

sorry, den Gedanken mit den Generatoren kann man vergessen, wenn ich die FB-Einschränkungen hinsichtlich der maximal möglichen Generatoren lese. Das wird also nichts. Ein Standard-SKR-03 umfasst schon mal über 1000 Konten. Auch wenn die i.d.R. nicht alle bebucht werden, die Maximal-Grenze der Generatoren kann bei diesem Ansatz trotzdem schnell erreicht werden.

Ich bin kein FB-Spezialist, und darum weiß ich nicht wie man das mit FB löst. Die einzige praktikable Lösung die ich kenne und auch schon über Jahrzehnte in diesem Zusammenhang (z.B. eindeutige Belegnummern-Erstellung mit mehreren Nummernkreisen) einsetze ist eine Hilfstabelle mit den entsprechenden Nummern(-Kreisen) wo sich der Server vor dem "Post" automatisch die nächste Nummer holt und die zuletzt vergebene Nummer wieder zurückschreibt. Der Client darf bzw. sollte bei einer automatischen Nummernvergabe gar nicht involviert sein.

[hoika]

Hallo,

ich würde auch eine eigene Tabelle oder ein Feld in der Kontentabelle benutzen.
Das Select Max() ist soweit OK, solange ein Unique Index auf den sntsprechenden Felder ist.
Kommt es zu einer key-Exception (duplicate value), muss ich mir nochmal den neuen Wert holen, solange, bis es klappt.

FB kann etwa 32.000 (32758) Generatoren haben (http://www.firebirdsql.org/pdfmanual...ator-Guide.pdf), aber ich würde das nicht ausreizen,
vielleicht werden es ja mal doch mehr Konten und dann müßte der Code komplett umgebaut werden.


Heiko

[p80286]

Und warum nicht ein Generator für alle Konten?
Dann ist jede Buchung eindeutig
und bei einem

SQL-Code:

insert

wir das so gemacht

SQL-Code:

insert into my table (id,...) values((SELECT NEXT VALUE FOR MyGeneratior FROM RDB$DATABASE),....)

Gruß
K-H

[himitsu]

Zitat:

Zitat von mjustin (Beitrag 1279079)

Einzige Einschränkung:
immer wenn eine neue Nummer generiert, aber (weil ein Fehler auftritt) nicht beim INSERT verwendet wird, wird diese Nummer unbenutzt und es bleibt eine "Lücke". (Da Generatoren ihre Werte ausserhalb von Transaktionen erhalten, können diese nicht durch ein Rollback verhindert werden.)

Dann kopiert man das eben nicht manuell, sondern läst sich die Nummer in einem BeforeInsert-Trigger erzeugen und einfügen. :angel:

Da geht der Wert nur noch verloren, wenn es beim Insert knallt.

Es ist aber vom Programm unabhängig
und es gibt keine Probleme, wenn mehrere Connections parallel arbeiten und wenn es beim Insert etwas dauert. (deswegen sind Generatoren auch unabhängig von Transaktionen)

[Hansa]

Hier im gesamten Thema sind mindestens 2 grundsätzliche Fehler zu sehen und noch etliche Kleinere :

1. Generatoren zu benutzen, um Datenabank-Felder durchzunummerieren. Und dann noch ohne Lücken ? Ohne Transaktionssteuerung ? Das sind nackte Zahlen ohne weitere Bedeutung. Ob da jetzt 1 steht oder 8937598 ist völlig egal. Das dient nur der Identifikation eines Datensatzes und eben der lückenlosen Weitergabe an abhängige Tabellen. Gibts diese Nr. nicht, dann ist eben kein Datensatz mit dieser ID vorhanden. Ob der jetzt zwischenzeitlich gelöscht wurde oder sonstwas interessiert da vorerst nicht.

2. Generatoren-Werte einzusparen. :wall: Was soll das ? Ist die 1 jetzt billiger als die 8937598 ? :shock: Wem integer (ca. 4.000.000.000 Werte möglich) nicht reicht, der soll eben bigint (64bit-Integer) nehmen. Wenn ich nicht irre sind das ca. 10 hoch 20, also 1 mit 20 Nullen. Nehmen wir mal 10 hoch 9 Menschen, dann hat jeder (bleibe mal bei Buchungen) 10 hoch 11 Buchungen im Leben zur Verfügung. Sagen wir mal weiter, jeder wird 100 Jahre alt, dann kann er im Jahr 10 hoch 9 Buchungen mache. Das würde bei 31.000.000 Sek. / Jahr pro Sekunde dann immer noch 32 Buchungen / Sek. ergeben.

P.S.: ich kenne das angebliche Bill Gates Zitat mit den 64 kB auch ! Bzw. das : "Niemand hat die Absicht eine Mauer zu errichten" :mrgreen:

[Dejan Vu]

Zitat:

Zitat von mm1256 (Beitrag 1279084)

...Die einzige praktikable Lösung die ich kenne... ist eine Hilfstabelle mit den entsprechenden Nummern(-Kreisen) wo sich der Server vor dem "Post" automatisch die nächste Nummer holt und die zuletzt vergebene Nummer wieder zurückschreibt.

Ist doch fein.

Code:

UPDATE NummernKreise 
   SET Nummer = Nummer + 1 
 WHERE NummernKreisPK = :MyNummernKreis 

RETURNING Nummer INTO :NeueNummer

Geht das so?

[Sir Rufo]

Zitat:

Zitat von Dejan Vu (Beitrag 1279138)

Zitat:

Zitat von mm1256 (Beitrag 1279084)

...Die einzige praktikable Lösung die ich kenne... ist eine Hilfstabelle mit den entsprechenden Nummern(-Kreisen) wo sich der Server vor dem "Post" automatisch die nächste Nummer holt und die zuletzt vergebene Nummer wieder zurückschreibt.

Ist doch fein.

Code:

UPDATE NummernKreise 
   SET Nummer = Nummer + 1 
 WHERE NummernKreisPK = :MyNummernKreis 

RETURNING Nummer INTO :NeueNummer

Geht das so?

Und wenn man diese Zeile entsprechend innerhalb der Transaktion lockt, dann reißt es bei einem Rollback auch keine Löcher in den Käse die Folge.

Aufpassen muss man allerdings, wenn man (beim Buchen nicht ungewöhnlich) innerhalb einer Transaktion jeweils für zwei Konten eine fortlaufende Zahl benötigt. Dann muss man dass in einem Rutsch anfordern (oder wenigstens den Lock anfordern), sonst kann es in dieser Situation zu einem Deadlock kommen.

[Dejan Vu]

Wenn man keine Löcher will, müsste der Transaction Level 'SERIALIZABLE' eingestellt sein. Das ist dann aber tödlich für die Performance. Irre ich mich da?

[jobo]

Ich denke auch, dass man hier aufpassen muss, nichts durcheinander zu werfen.
Das beginnt damit, wie die (fachliche?) Anforderung für die aufsteigende, lückenlose Konto abhängige Buchungsnummer begründet ist bzw. in der Praxis verwendet wird.
Das ist zu trennen von der technischen Anforderung nach einem eindeutigen Schlüssel für die Buchung, die
a) sicher ohne probleme über einen Generator (Sequence) abzuwickeln ist
b) so oder so transaktionssicher (auch ohne Table Lock!) funktioniert
c) prinzipbedingt Lücken hinterlassen kann
d) table lock kann in belasteten Umgebungen drastische Performanceprobleme mit sich bringen

Wenn die technischen Anforderungen also mit einem Generator unproblematisch zu erfüllen sind (was wahrscheinlich so ist), dann hat man vielleicht nur noch ein Darstellungs(!)problem für die Buchungsnummern.

Das Darstellungsproblem könnte man glaub ich auch unter Firebird als zusätzliches Feld über die Rankfunktion virtuell erzeugen, on the fly, also immer lückenlos, weil frisch generiert, auch wenn mal alle Buchungen in die Tonne kommen sollten.
Hab grad nicht ganz klar, ob das schon unter 2.5 so da ist, oder erst unter V 3.

[Dejan Vu]

Zitat:

Zitat von jobo (Beitrag 1279143)

Das Darstellungsproblem könnte man glaub ich auch unter Firebird als zusätzliches Feld über die Rankfunktion virtuell erzeugen, on the fly, also immer lückenlos, weil frisch generiert, auch wenn mal alle Buchungen in die Tonne kommen sollten.

So eine Buchungsnummer ist immer auch ein Schlüssel. Bei Rechnungsnummern hast Du ja das gleiche Problem. Die müssen ja auch lückenlos sein.
Das Performanceproblem kann man auch über bulk inserts umgehen, falls der use case in der Praxis auftaucht.

[mjustin]

Zitat:

Zitat von Dejan Vu (Beitrag 1279144)

So eine Buchungsnummer ist immer auch ein Schlüssel. Bei Rechnungsnummern hast Du ja das gleiche Problem. Die müssen ja auch lückenlos sein.

Wie ist "Lücke" definiert?

Zitat:

Die Rechnungsnummern dürfen also je nach Kunden oder Produkten unterschiedlichen Text enthalten. Sie brauchen nicht lückenlos aufeinander zu folgen.

Erlaubt sind zum Beispiel folgende Rechnungsnummern:

2008.25.0167
Interkauf-1267.10.08
10875-web-345-08

http://www.steuer-schutzbrief.de/ste...alig-sein.html

[Dejan Vu]

Lücke ist '1,3,4' statt '1,2,3'.
Rechnungsnummern müssen wirklich nicht fortlaufend sein, sollten es aber. Und es erspart viel Arbeit bei Buchprüfungen. Davon hatte ich schon zwei, und Prüfer lieben fortlaufende Rechnungsnummern. Klar kann man Rechnungsnummern, die man dem Finanzamt nicht melden will, über einen separaten Kreis erstellen. Aber nach die Prüfer (denen ich meine Idee mit dem separaten Kreis nicht mitgeteilt habe, um Verzögerungen zu vermeiden), meinten eben: 'Hättste mal, dann wären wir' (eher fertig).

Aber zurück zum Thema: Auch bei Buchungsvorgängen ist es einfach sinnvoll, die Buchungsnummern fortlaufend zu haben. Und zwar aus Nachweisgründen. Klar, in einer Datenbank geht selten etwas verloren, aber es ist einfach sicherer, hier eine fortlaufenden Nummerierung zu haben.

[dataspider]

Hi,

für die Buchungsnummern würde ich eine eigene Tabelle erzeugen.
Man könnte die Nummer zwar auch im Konto speichern, aber wenn man später die Vergabe z.B. abhängig vom Jahr machen will, baut man wieder um.

Also eine Tabelle Buchungsnummer mit den Feldern KONTO, JAHR, NUMMER.

Im Projekt vor dem Post eine PROCEDURE aufrufen, welche mit den Parametern KONTO, JAHR die Nummer zurück liefert.
Diese Procedure legt auch die Datensätze in BUCHUNGSNUMMER an bzw. zählt die Nummer hoch.

Im Code dann eine Methode GetBuchungsnummer in etwa so (Ist jetzt IBO und Barcode, aber kann man ja anpassen):

Delphi-Quellcode:

function TdmMain.NaechsteBarcodeNummer(ZaehlerId: Variant): string;
Var
  I: Integer;
begin
  Result := '';
  if not spBarcodeNummer.Prepared then
    spBarcodeNummer.Prepare;
  spBarcodeNummer.Params[0].Value := ZaehlerId;
  try
    for I := 1 to 10 do
    begin
      Sleep(500);
      try
        spBarcodeNummer.ExecProc;
        result := spBarcodeNummer.Fields[0].AsString;
        spBarcodeNummer.IB_Transaction.Commit;
        spBarcodeNummer.Unprepare;
        Break;
      except
        spBarcodeNummer.IB_Transaction.Rollback;
      end;
    end;
  finally
    if spBarcodeNummer.Prepared then
      spBarcodeNummer.Unprepare;
  end;
end;

Bei Lock Konflikt wird eine Exception ausgelöst und es wird insgesamt 10 mal ausgeführt.
Ich habe das getestet in einee MINI - Anwendung, die nur Nummer geholt hat und habe das mehrmals auf meinem PC gestartet.
Ich habe mehrere 1000 Nummern erzeugt und in einer Liste gespeichert.
Der eine Prozess hatte dann 100, 101, 103, der andere 99, 102, 104.

Ich werde sicher kritisiert wegen der Lösung mit eine stillen Exception.
Ich hatte es vorher mit einer Transaktion mit LOCK_WAIT getestet, da hatte aber IBO Probleme.

Wichtig, die PROCEDURE muss eine eigene Transaction bekommen und wenn es mit LOCK_WAIT funktioniert, braucht man die Schleife nicht.

Frank