Stored Procedure: Rückgabewert

[fkerber]

Hi,

ich habe folgende SP:

SQL-Code:

CREATE OR ALTER PROCEDURE CREATECUSTOMER (
    name varchar(100),
    title varchar(25),
    forename varchar(50),
    surname varchar(50),
    street varchar(50),
    houseno varchar(10),
    country varchar(3),
    zipcode varchar(10),
    cityname varchar(50))
returns (
    contactid integer)
as
declare variable entityid integer;
declare variable cityid integer;
BEGIN
    SELECT cityid FROM getCityid(:zipcode,:cityname,:country) INTO :cityid;
    INSERT into customer(name) VALUES (:name) RETURNING entityid INTO :entityid;
    INSERT into contact (title, forename, surname, street, housenumber, cityid, entityid) VALUES (:title, :forename, :surname, :street, :houseno, :cityid, :entityid) RETURNING contactid INTO :contactid;
    UPDATE customer set maincontactid = :contactid WHERE entityid=:entityid;
END

Die hat auch einwandfrei funktioniert (damals noch ohne das returns und mit contactid als Variable).
Jetzt wollte ich die ContactID zurückgeben lassen, daher das returns.
Der Zugriff von Delphi sieht so aus:

Delphi-Quellcode:

var
  proc: TIBCStoredProc;
begin
  proc := TIBCStoredProc.Create(nil);
  proc.StoredProcName := 'CREATECUSTOMER';
  proc.PrepareSQL(True);
  proc.ParamByName('name').Value := custName;
  proc.ParamByName('title').Value := title;
  proc.ParamByName('forename').Value := forename;
  proc.ParamByName('surname').Value := surname;
  proc.ParamByName('street').Value := street;
  proc.ParamByName('houseNo').Value := houseNo;
  proc.ParamByName('country').Value := Uppercase(country);
  proc.ParamByName('zipcode').Value := zipcode;
  proc.ParamByName('cityname').Value := cityname;
  proc.Open;
  result := proc.FieldByName('contactid').AsInteger;
  proc.Close;
  proc.Free;
end;

Das wurde dann mit der Meldung quittiert, dass das Ganze nicht klappt, da meiner SP ein Suspend fehle.
Also habe ich vor dem letzten end noch ein suspend; spendiert.

Jetzt bekomme ich auch brav die neu generierte ID zurück (die Generatoren werden auch schön hochgezählt), aber, die neuen Einträge landen nicht mehr in der DB.

Was mache ich falsch?


LG, Frederic

[TBx]

Hast Du die SP zuvor auch so von Delphi aus aufgerufen oder nur in einem Tool wie IBExpert ausgetestet?

In Deinem Codebeispiel fehlt das Transactionhandling.

[tsteinmaurer]

Die zur Laufzeit erzeugte TIBCStoredProcedure Instanz ist keiner Connection bzw. Transaktion zugewiesen. Vielleicht läuft da im Hintergrund etwas Magisches ab, aber ich würde ihr explizit eine Connection und vielleicht auch eigene Transaktionskomponente zuweisen.

Mit der Verwendung von SUSPEND wird aus deiner Stored Procedure eine "Selectable" SP, obwohl du ja als solches nicht wirklich eine Ergebnismenge zurückgibst. Geht zwar, aber klarer wäre hier vielleicht eine "Executable" SP, wenn es bei dem Skalaren Wert bleibt. D.h. das SUSPEND wieder entfernen, als Executable SP darauf zugreifen (d.h. kein Open von TIBCStoredProcedure verwenden) und auf den zurückgegebenen Wert mit ParamByName zugreifen.

PS: Firebird 3 (noch weit weg), wird Stored Functions unterstützen.

Thomas

[mkinzler]

Oder du Verwendest sie in einer normalen Abfrage (TIBCQuery):

select contactid from CREATECUSTOMER( :name, :titel, :forename, ...);

[fkerber]

Hi,

danke euch.

@Thomas:
Ja, die SP lief auch aus dem Programm heraus - bis das Suspend kam
Trotzdem schadet es sicher nicht die Connection zuzuweisen, das ist wohl wahr...
Für solche "atomaren" Sachen ist eine extra Transaktion aber nicht notwendig, oder?


@Thomas:
Ja, das wollte ich auch ursprünglich so machen, aber ich habe es nicht hinbekommen.
Jetzt wollte ich nochmal meinen Versuch rekonstruieren, habe es aber nicht mehr geschafft - es läuft jetzt

Mit folgendem Code klappt es nun:

Delphi-Quellcode:

var
  proc: TIBCStoredProc;
begin
  proc := TIBCStoredProc.Create(nil);
  proc.Connection := ibc_MainDB;
  proc.StoredProcName := 'CREATECUSTOMER';
  proc.Prepare;
  proc.ParamByName('name').Value := custName;
  proc.ParamByName('title').Value := title;
  proc.ParamByName('forename').Value := forename;
  proc.ParamByName('surname').Value := surname;
  proc.ParamByName('street').Value := street;
  proc.ParamByName('houseNo').Value := houseNo;
  proc.ParamByName('country').Value := Uppercase(country);
  proc.ParamByName('zipcode').Value := zipcode;
  proc.ParamByName('cityname').Value := cityname;
  proc.ExecProc;
  result := proc.ParamByName('contactid').AsInteger;
  proc.Close;
  proc.Free;
end;

@Markus:
Hätte das gegenüber obigen einen Vorteil oder ist es nur "anders"?


LG, Frederic

[tsteinmaurer]

Ich würde ehrlich gesagt lieber mehr als weniger Transaktionskomponenten verwenden. In deinem Fall, durch die Zuweisung der Connection, wird die SP nun entweder durch die explizit zugewiesene oder interne Transaktion der Connection laufen. Kann ja sein, dass auch noch andere Komponenten dann in diesem Transaktionskontext laufen. Wenn dann mal ein Commit etc. gemacht wird, gilt das für alle Komponenten.

Ich würde für jede "single unit of work" eine eigene Transaktion verwenden. Mit der kann dann sauber in Bezug auf Commit/Rollback gearbeitet werden, ohne dabei andere Komponenten zu beeinflussen.

Ich weiss nicht welchen Background du hast, aber ein sauberes Transaktions-Handling in der Client-Anwendung ist das A und O bei der Entwicklung mit Firebird.

Thomas

[fkerber]

Hi,

ja, im Moment geht es über die interne TA der Connection.

Mein DB-Background ist eher theoretischer Natur (mehrere Vorlesungen zu dem Thema) + MySQL, aber dort war bislang nicht so viel Notwendigkeit für sowas.

Mein Gedankengang war, dass ich eine TA dort brauche, wo mehrere zusammenhängende Sachen sind, die ich dann eben en-bloc wieder zurücksetzen kann (wenn ich es mal muss).
Für den Fall hier jetzt (Anlegen eines Kunden) sah ich keine Notwendigkeit eines Rollbacks und da die Connection auf Auto-Commit steht, sollte der Kunde ja direkt in der DB festgeschrieben werden. Weitere Sachen, die ich mit dieser internen TA mache, sollten doch das Anlegen des Kunden dann nicht mehr betreffen, oder?

LG, Frederic

[mkinzler]

Zitat:

@Markus:
Hätte das gegenüber obigen einen Vorteil oder ist es nur "anders"?

In diesem Fall nur anders.

Wg Tranaktionen:
Bei FireBird ist aber zu beachten, dass keine Transaktionslog verwendet wird, wie bei den meisten anderen DBMS, sondern Datensatzversionierung.
Deshalb ist diese auch bei/für nicht ändernde Vorgänge wichtig.

[tsteinmaurer]

Hallo Frederic,

dein Verständnis zu Transaktionen sind prinzipiell richtig. Das Ganze in Verbindung mit Firebird wäre jetzt eine etwas längere Geschichte, aber prinzipiell ist ein ständiges COMMIT RETAINING (eine spezielle Form von COMMIT, wo der Transaktionskontext erhalten bleibt) über eine längere Dauer für den Firebird Server nicht gut, weil hiermit das Fortschreiten der "Oldest Active Transaction" blockiert wird. Ich erwähne das deswegen, weil jeglicher AutoCommit Modus ein COMMIT RETAINING verwendet. Man muss dann schon mal von Zeit zu Zeit ein COMMIT anstatt von COMMIT RETAINING ausführen, damit man hier nicht in ein Performanceproblem reinläuft. Dazu am besten die Transaktions-Counter (ermittelbar über gstat -h oder Admin/Dev-Tool) im Auge behalten.

Gerade für deinen Anwendungsfall, wo es um das Anlegen eines Benutzers geht, würde ich eine explizit gestartete Transaktion vorziehen. Wer weiss, wer sonst noch die interne/implizite Transaktion der Connection in Verwendung hat. Ist sauberer und frei von Seiteneffekten, weil man hier mit der eigenen Transaktion alles selbst im Griff hat.

Was ich gerne mit AutoCommit verwende sind Datenmengen, die im Kontext einer ReadOnly-Transaktion geöffnet wurden, sofern die Datensätze nicht bearbeitet werden müssen.

Thomas

[fkerber]

Hi,

das sit natürlich gut zu wissen.
Danke für die Erläuterung.

LG, Frederic