Mehr Benutzer Primary Key Insert in Tabelle

[etom291272]

Hallo DP ler

Ich würde mich über eine Einschätzung von euch zu folgendem Problem und meinen Lösungsweg freuen.

Problem:
Ich entwickle zur Zeit ein Dokumentenmanagementsystem dass eng mit unserer ERP Lösung zusammenarbeitet. Die Haupttabelle für dieses Projekt ist eine Tabelle wo jedes Dokument einmalig gespeichert ist. Durch folgende Vorgänge können neue Sätze in diese Tabelle erstellt werden.
1. Aus dem ERP System beim Druck diverser Belegarten
2. Über einen Dokumentencsanner der von meinem Programm angesprochen wird.
3. Von bis zu 100 Usern im Netzwerk durch diverse Ablagemöglichkeiten im Programm.

Ich halte es für nicht ausgeschlossen dass in dieser Konstellation es vorkommen kann dass zwei oder mehr Inserts zusammenstossen (Ich verwende einen Primary Key in dieser Tabelle für das ID Feld).

folgenden Code habe ich mir für die Lösung dieses Problems überlegt

Delphi-Quellcode:

 while i < 50 do     //50 gleichzeitige Zugriffe simulieren 
  begin
    i:=i+1;
    MaxInsVersuche:=0; olev:=0;
    while (MaxInsVersuche < 5) and (olev = 0) do  // Verhinderung Endlosschleife falls
                                                  // Exception durch anderen Grund ausgelöst
    begin
      Inc(MaxInsVersuche,1);
      aNextDokId:=DataMain.NeueDokId; // Holt nächste höhere Nummer (DokID) aus Datenbank
      try
        sSql := Format('INSERT INTO dokumente (dkDokumentId, dkDokGruppe, dkErstelltAm, dkBezeichnung) VALUES (%s , %s, %s, %s)',
                      [IntToStr(aNextDokId),'99',QuotedStr(DateToStr(now)),QuotedStr('Bezeichnung')]);
        DataMain.AdoConMain.ConnectionObject.Execute(sSql,olev,0);
      except
        Sleep(1000) //Annahme Exception ausgelöst durch Verletzung Primary Key
                   // warten bis anderer User Insert abgeschlossen hat
      end;
    Memo1.Lines.Add(inttostr(aNextDokId)+'@'+IntToStr(MaxInsVersuche));
    end;
    if olev<>1 then Application.MessageBox('Insert nicht erfolgreich','Fehler',16)
  end;

  ShowMessage('Instert Ok');

Ich habe diesen Programmteil von 2 Pc's absolut gleichzeitig gestartet und es funktioniert imho zielführend.

Anmerkung: Ich kann keinen Autowert oder GUID für den eindeutigen Schlüssel verwenden.

[jensw_2000]

Keine schlechte Variante um "unnötige" OLE Exceptions abzufangen. Die 1000 ms zwischen den Versuchen finde ich jedoch etwas zu lang ... 500ms sollten IMHO locker reichen ...

Zitat:

Anmerkung: Ich kann keinen Autowert oder GUID für den eindeutigen Schlüssel verwenden.

Ein GUID als Primary Key funktioniert...

SQL-Code:

INSERT INTO TABLE1 (GUID,Vorname,Name)
            VALUES (GetGUID,'Hans','Wurst')

Delphi-Quellcode:

// benötigt Unit ACTIVEX
function GetGuid: variant;
var
  ID: TGUID;
begin
  Result := '';
  if CoCreateGuid(ID) = S_OK then
    Result := GUIDToString(ID);
end;

Bei Identity Werten (Autoinc) wirds komplizierter..

Schöne Grüße,
Jens

[etom291272]

@ Jens
Danke für deine Meinung

Der Grund für die Nichtverwendung eines Autowerts oder eine GUID ist das ich bei der Scannererfassung
eine fortlaufende Zahl ohne Lücken brauche weil ich in den Barcodes eigene Prüfziffern integrieren muss die auf den Etiketten der Dokumente sind.
:coder:

[Jelly]

Zitat:

Zitat von etom291272

Der Grund für die Nichtverwendung eines Autowerts oder eine GUID ist das ich bei der Scannererfassung
eine fortlaufende Zahl ohne Lücken brauche weil ich in den Barcodes eigene Prüfziffern integrieren muss die auf den Etiketten der Dokumente sind.

Du wirst nie eindeutig sicher sein können, daß zwischen 2 Inserts nicht noch ein Insert von einem anderem Rechner aus stattfindet. Fortlaufend nummerieren ist also so nicht möglich. Aber 2 Alternatitiven kann ich dir anbieten, beide allerdings mit Arbeit verbunden:

• Neue Tabelle "SCANS" und in Dokumente ein Link zum Hauptscan... Dann kannst du mit Identity Feldern arbeiten, und es macht dann nix, wenn zwischen deinen Dokumenten Inserts eine Lücke entsteht. Mit
  SQL-Code:

  select * from Dokumente where Scan=%d order by dkDokumentId

  kriegst du dann deine zugehörigen Dokumente in der richtigen Reihenfolge
• Statt einfach nur eine ID als Dokumentennummer zu nehmen, setz deine eindeutige Nummer entweder aus 2 Feldern zusammen, oder erweiter das bestehende Feld um eine Rechnerkennung. Also aus 1,2,3 wird z.B. PC011, PC012, PC013 etc... PC01 ist hier dann die Rechnerkennung. Diese Variante halte ich allerdings für ziemliches Gebastele.

Ich tendiere zu Lösung 1.

[shmia]

Zitat:

Zitat von etom291272

Der Grund für die Nichtverwendung eines Autowerts oder eine GUID ist das ich bei der Scannererfassung
eine fortlaufende Zahl ohne Lücken brauche weil ich in den Barcodes eigene Prüfziffern integrieren muss die auf den Etiketten der Dokumente sind.

Du kannst auf dem MSSQL Server auch einen Generator nachbilden:
Erzeuge eine Tabelle "Generators" mit:

SQL-Code:

CREATE TABLE [Generators] (
   [IdGenerator] [varchar] (20) NOT NULL ,
   [CurrentValue] [int] NOT NULL 
)
GO

ALTER TABLE [Generators]
   CONSTRAINT [PK_Generators] PRIMARY KEY
   (
   [IdGenerator]
   )
GO

Über folgende Stored Procedure bekommst du dann immer einen neuen Keywert:

SQL-Code:

Create procedure generator_id (  @IdGenerator varchar(20)) 
as 
begin 
      declare  @ID integer 

      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
      BEGIN TRANSACTION SP_GEN_ID 

      select @ID = CurrentValue from Generators where IdGenerator =@IdGenerator
      IF @@ERROR <> 0 GOTO LBL_ERROR 
      update Generators set CurrentValue =CurrentValue + 1 where IdGenerator=@IdGenerator
      IF @@ERROR <> 0 GOTO LBL_ERROR 

      COMMIT TRANSACTION SP_GEN_ID 
      return @ID 

LBL_ERROR: 
      ROLLBACK TRANSACTION SP_GEN_ID 
      return 0 
end

[etom291272]

ok Ich hätte vielleicht schreiben sollen ich will keine GUID oder Autowert verwenden

Die Lücken in den fortlaufenden Nummern sind nicht wirklich ein Problem ich will halt nur den Bereich in dem Sie gebildet werden beeinflussen und selbst kontrollieren können.
Das mit dem Generator direkt auf dem MSSQL Server ist ein interessanter Vorschlag. Ich will aber so wenig logik wie möglich in die Datenbank packen da ich Datenbank unabhängig bleiben will.

Primär gehts mir um eure Meinung bei dem Code innherhalb der beiden While schleifen eine weitere Idee
die ich hatte war kurz vor dem Insert eine funktion aufzurufen die mir prüft ob die Nummer die ich einfügen will bereits in der Datenbank vorhanden ist aber das könnte ja dann wieder zu früh sein ???

[Brainshock]

Hi,

kann man die Generator-Funktion aus Beitrag #5 auch so umbauen, dass man per SELECT Abfrage an den nächsten Wert kommt? Etwa: Select id from generator_id('meinetab');
Ich bin nicht so firm in den SQL Funktionssachen und muss deshalb fragen.

[Brainshock]

hat da nicht einer eine idee?

[Phoenix]

Benutze doch den 'Generator'. Was ist dagegen einzuwenden?
Das ist doch nix anderes als ein Nachbau von 'Sequenzen', die es z.B. auf Oracle schon von Haus aus gibt. Postgres und Firebird kennen solche Sequenzen auch, für MySQL müsste man sowas dann wieder mit diesen Tabellen nachbauen. Ist aber kein großer Akt.

[Brainshock]

Mein Problem ist, dass ich die Kompontensuite mODBC für den Datenbankzugriff benutze und deren Komponente für stored procedures mStoredProcedures gibt keine Werte zurück.
Mit einer Select-Abfrage könnte ich direkt per mQuery an die Funktion herangehen.

Gruß

[Elvis]

Du kannst mit Hilfe von Rdb$Database Pseudo-Selects ausführen.
Also Selects, die immer eine Zeile liefern:

SQL-Code:

SELECT Gen_ID(DeinGenerator, 1)
FROM   Rdb$Database

[mkinzler]

Zitat:

Zitat von Elvis

Du kannst mit Hilfe von Rdb$Database Pseudo-Selects ausführen.
Also Selects, die immer eine Zeile liefern:

SQL-Code:

SELECT Gen_ID(DeinGenerator, 1)
FROM   Rdb$Database

Sieht aber sehr nach Firebird aus, er verwendet aber MSSQL.

[alzaimar]

Wir verwenden auch Generatoren in MSSQL, also die von shmia propagierte Lösung. Wichtig ist nur der Transaktionslevel, der garantiert, das wirklich nie zwei Prozesse den gleichen Wert bekommen...

[Brainshock]

Wie gesagt, ich kann kein

SQL-Code:

exec generator_id 'meinetabelle'

benutzen, da hier das Ergebnis im Parameter zurückkommt und der mit mStoredProcedure nicht gefüllt wird.

Gruß

[mkinzler]

Dann mach doch einen select auf die SP

[alzaimar]

Versuch doch mal so ein script:

SQL-Code:

Declare @r int
exec MyProcedure @r output
select @r as Ergebnis

[Brainshock]

@alzaimar: Wie soll ich das Skript ausführen, über eine Query?

@mkinzler: Ein Select auf die SP haut nicht hin bzw. ich weiß nicht genau wie dieses aussehen muß. Hab folgendes probiert:

SQL-Code:

select * from generator_id('meinetabelle')

Ergebnis: Ungültiger Objektname 'generator_id'.

SQL-Code:

select * from generator_id 'meinetabelle'

Ergebnis: Falsche Syntax in der Nähe von 'meinetabelle'

Gruß

[alzaimar]

Yup über eine TQuery oder ein TDataset. Die BDE unterstützt leider keine Skripte, aber ADO...

Ich würde vielleicht auch einfach die StoredProcedure so umschreiben, das sie genau eine Tabelle liefert (eine Zeile, eine Spalte). Dann geht's auf jeden Fall mit einer TQuery, auch unter der BDE.

SQL-Code:

Create procedure generator_id (  @IdGenerator varchar(20))
as
begin
      declare  @ID integer

      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
      BEGIN TRANSACTION SP_GEN_ID

      select @ID = CurrentValue from Generators where IdGenerator =@IdGenerator
      IF @@ERROR <> 0 GOTO LBL_ERROR
      update Generators set CurrentValue =CurrentValue + 1 where IdGenerator=@IdGenerator
      IF @@ERROR <> 0 GOTO LBL_ERROR

      COMMIT TRANSACTION SP_GEN_ID
      select @ID as NewID -- Statt return @ID
      return (0) -- fehlte sowieso

LBL_ERROR:
      ROLLBACK TRANSACTION SP_GEN_ID
      select 0 as NewID -- Statt return 0
      return (1)
end

[Elvis]

@alzhaimar
Ups, sorry. Irgendwie dachte ich er benutzt Firebird... :gruebel:

btw: Kannst du keine Warnungen schreiben bevor du TSQL Sourcen zeigst? Ich habe gerade gegessen, da kommt das nicht so gut! :shock:

[Brainshock]

@alzaimar: Danke für das umschreiben, habs noch nicht so mit den Funktionen in T-SQL.

Die Funktion aus #18 hab ich erstmal übernommen und sie funktioniert wie gewünscht im SQL Management Studio.
Die Frage ist nur wie das Ganze über die Query funktionieren soll. Mein Test war der:

Delphi-Quellcode:

procedure TMainForm.test1Click(Sender: TObject);
var
  TmpQuery: TmQuery;
begin
  TmpQuery:= DatabaseModule.GetTemporaryQuery;
  try
    TmpQuery.SQL.Text('exec generator_id ''meinetabelle''');
    TmpQuery.Open;
    ShowMessage(TmpQuery.FieldByName('NewID').AsString);
  finally
    TmpQuery.Free;
  end;
end;

Das Ergebnis war der Fehler: 24000:[Microsoft][ODBC Cursor Library] Ungültiger Cursorstatus

Gruß

[Brainshock]

ping. Hat einer eine Idee wie das funktionieren kann?