Fehlerbehandlung bei Stored-Procedure

[Sascha Möller]

Hallo!

Ich habe auf dem SQL-Server eine gespeicherte Prozedur, die ich mit der TAdoStoredProc-Komponente ausführe. Nun kann es vorkommen, dass die
Prozedur nicht vollständig ausgeführt wird, da z.B. ein Fehler aufgetreten ist. In der Stored-Procedure selber habe ich für den Fehlerfall auch eine
Fehlerbehandlung eingebaut, die im Query-Analyser einwandfrei funktioniert. Sobald ich die Procedure mit ExecProc ausführe, bekomme ich im Fehlerfall
eine Delphi Exception und meine eigene Fehlerroutine in der Stored-Procedure wird nicht mehr ausgeführt.
Wie bekomme ich es hin, dass keine Delphi-Exception auftritt, sondern meine Fehlerbehandlung in der Stored-Procedure den Fehler behandelt??

Danke für Eure Hilfe !
Sascha

[Andreas Käding]

Hallo Sascha,

also der einfachste Weg wäre Deine ExecProc in einen try except Block einzupacken und im Except Block Deine Fehlerbehandlung zu definieren.

m.f.G. Andrea

[Sascha Möller]

Ja, das würde gehen, will ich aber nicht. Ich möchte gerne die Fehlerbehandlung direkt in meiner Stored-Procedure machen. Delphi soll von dem Fehler eigentlich garnichts mitbekommen. Die Fehlerbehandlung auf dem Server liefert auch noch bestimmte Werte als Output-Parameter zurück.

Gruss
Sasch

[Andreas Kosch]

Hallo,

in meinem Buch <i>ADO und Delphi</i> stelle ich im Abschnitt <i>10.6.3 Fehlerbehandlung beim Aufruf einer Stored Procedure</i> auf den Seiten 478 bis 484 gleich 7 verschiedene Konfigurationsalternativen vor. Bei 2 Konfiguration bleibt eine in der SP ausgelöste Exception in Delphi völlig unentdeckt, bei 3 Konfiguration löst Delphi keine automatische Exception aus, aber der Fehler kann auf Wunsch über die Errors-Kollektion abgefragt werden.

Lange Rede - kurzer Sinn: Wenn die <b>Execute</b>-Methode des Command-Objekts die Konstante <b>adOptionUnspecified</b> übergibt, muss ADO mit einer Ergebnismenge als Recordset rechnen, so dass der Fehler nicht automatisch weitergeleitet wird. Delphi kann somit nicht automatisch eine Exception auslösen.

Das Testprogramm greift über die nativen ADO-Objekte Connection und Command auf die Stored Procedure zu. Alle Aussagen gelten für den OLE DB Provider SQLOLEDB, der ODBC-Treiber verhält sich an dieser Stelle unterschiedlich.

Datenbank + SP: Wenn für den Parameter iMode der Wert 1 übergeben wird, provoziert die SP ein Veto des MS SQL Server, da über eine INSERT-Anweisung versucht wird, einen neuen Datensatz mit einem bereits vorhandenen Primärschlüsselwert anzulegen.
<pre>
USE tempdb
GO
CREATE TABLE Konto (
KontoNr INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
Betrag MONEY NOT NULL,
Datum DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP)
GO
INSERT INTO Konto (Betrag) VALUES (500.00)
INSERT INTO Konto (Betrag) VALUES (600.00)
INSERT INTO Konto (Betrag) VALUES (700.00)
INSERT INTO Konto (Betrag) VALUES (800.00)
INSERT INTO Konto (Betrag) VALUES (900.00)
INSERT INTO Konto (Betrag) VALUES (999.99)
GO

CREATE PROCEDURE TransferMoney
@iMode INT, @iFromKontoNr INT, @iToKontoNr INT, @mValue MONEY, @iStatus INT OUTPUT
AS
DECLARE @iError INT
DECLARE @iRowCount INT
SET @iStatus = 0
-- Schritt 1: Konto des Absenders belasten
UPDATE Konto
SET Betrag = Betrag - @mValue
WHERE KontoNr = @iFromKontoNr
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT
-- Schritt 2: Prüfen, ob Schritt 2 erfolgreich war
IF @iRowCount = 0
-- Status 1 = Konto des Absenders wurde nicht aktualisiert
SELECT @iStatus = 1
-- Zweiten Teilschritt nur im Erfolgsfall starten
IF (@iERROR = 0) AND (@iRowCount = 1)
BEGIN
IF @iMode = 0
BEGIN
-- Schritt 3: Konto des Empfängers gutschreiben
UPDATE Konto
SET Betrag = Betrag + @mValue
WHERE KontoNr = @iToKontoNr
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT
IF @iRowCount = 0
-- Status 2 = Konto des Empfängers wurde nicht aktualisiert
SELECT @iStatus = 2
END
IF @iMode = 1
BEGIN
-- Schritt 3: Konto des Empfängers gutschreiben
UPDATE Konto
SET Betrag = Betrag + @mValue
WHERE KontoNr = @iToKontoNr
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT
IF @iRowCount = 0
-- Status 2 = Konto des Empfängers wurde nicht aktualisiert
SELECT @iStatus = 2
-- Fehler provozieren
INSERT INTO Konto (KontoNr,Betrag) VALUES (1,1.00)
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT
IF @iRowCount = 0
-- Status 3 = Provoziertes Veto
SELECT @iStatus = 3
END
END
-- Fehlernummer zurückliefern
RETURN(@iError)
GO
</pre>
Testprogramm:
<pre>
uses ADOInt;

procedure TForm1.Button1Click(Sender: TObject);
resourcestring
cCS = 'Provider=SQLOLEDB.1;' +
'Persist Security Info=False;User ID=sa;' +
'Initial Catalog=tempdb;Data Source=Rechnername';
var
aCon : _Connection;
aCommand : _Command;
vRows : OleVariant;
iCnt : Integer;
iMode : Integer;
iError : Integer;
sError : String;

procedure AddParamObj(const sName: String; iType,iParam,iSize: Integer);
var
aParam : _Parameter;
begin
with aCommand do
begin
aParam := CreateParameter(sName, iType, iParam,iSize, EmptyParam);
Parameters.Append(aParam);
end;
end;

begin
aCon := CoConnection.Create;
case RadioGroupCursorLocation.ItemIndex of
0 : aCon.CursorLocation := adUseClient;
1 : aCon.CursorLocation := adUseServer;
end;
aCon.Open(cCS, '', '', adConnectUnspecified);
try
aCommand := CoCommand.Create;
try
with aCommand do
begin
CommandType := adCmdStoredProc;
CommandText := 'TransferMoney';
AddParamObj('RETURN_VALUE', adInteger, adParamReturnValue, 4);
// Input-Parameter
AddParamObj('@iMode', adInteger, adParamInput, 4);
AddParamObj('@iFromKontoNr', adInteger, adParamInput, 4);
AddParamObj('@iToKontoNr', adInteger, adParamInput, 4);
AddParamObj('@mValue', adCurrency, adParamInput, 8);
// Output-Parameter
AddParamObj('@iStatus', adInteger, adParamOutput, 4);
// Parameter-Werte zuweisen
if CheckBoxError.Checked then
iMode := 1
else
iMode := 0;
Parameters[1].Value := iMode;
Parameters[2].Value := 1;
Parameters[3].Value := 2;
Parameters[4].Value := 25.50;
// Ausführen
Set_ActiveConnection(aCon);
case RadioGroupOptions.ItemIndex of
0 : Execute(vRows, EmptyParam, adExecuteNoRecords);
1 : Execute(vRows, EmptyParam, adOptionUnspecified);
2 : Execute(vRows, EmptyParam, 0);
end;
// Daten anzeigen
Memo1.Lines.Add('Modus :' + IntToStr(iMode));
Memo1.Lines.Add('Rückgabewert: ' +
IntToStr(Parameters.Item[0].Value));
Memo1.Lines.Add('Statuswert: ' +
IntToStr(Parameters.Item[5].Value));
end;
finally
aCommand := nil;
end;
finally
with aCon do
for iError := 0 to Errors.Count - 1 do
sError := sError + Errors[iError].Description + #10#13;
Memo1.Lines.Add('Inhalt der Errors-Kollektion:');
Memo1.Lines.Add(sError);
aCon.Close;
aCon := nil;
end;
StatusBar1.SimpleText := 'Command-Objekt ist fertig.';
end;
</pre&gt

[Sascha Möller]

Hallo Andreas

Danke für Deinen Tipp. Funktioniert soweit ganz gut.
Ich habe mal testhalber einer Tabelle für Besitzer dbo alle Rechte ausser Select entzogen und versucht mit Insert Datensätze anzufügen.
Mit Insert testtable (col1) Values (1) funktioniert das einwandrei, d.h. keine Delphi-Exception, obwohl ein Fehler aufrtitt.
Sobald nun aber der SQL-Befehl einer varchar-Variablen zugewiesen wird und diese dann ausgeführt,klappt es nicht mehr:
set @execstring = 'Insert testtable (col1) Values (1)'
exec(@execstring)

Delphi meldet bei Execute EOleException: Insert verweigert ...

Gruss
Sasch

[Andreas Kosch]

Hallo,

in diesem Fall (es wird in der SP nur eine Anweisung aufgerufen) muss man Delphi etwas kräftiger beschummeln. Das könnte so aussehen:<br>
a) SET NOCOUNT ON <br>
b) Dummy-SELECT liefert leere Ergebnismenge <br>
c) Erst danach die INSERT-Anweisung abschicken <br>
d) Command-Objekt mit adOptionUnspecified aufrufen
<pre>
CREATE PROCEDURE Sascha
@sSQL VARCHAR(100),
@iStatus INTEGER OUTPUT
AS
DECLARE @iError INT
DECLARE @iRowCount INT

SET NOCOUNT ON
-- Delphi beschummeln
SELECT * FROM Konto WHERE 1 = 2

EXEC(@sSQL)
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT
IF @iRowCount = 0
SELECT @iStatus = 1

SET NOCOUNT OFF

RETURN @iError
GO
</pre>
Aufruf:
<pre>
uses ADOInt;

procedure TForm1.Button1Click(Sender: TObject);
resourcestring
cCS = 'Provider=SQLOLEDB.1;' +
'Integrated Security=SSPI;Persist Security Info=False;' +
'Initial Catalog=tempdb;Data Source=(local)';
var
aCon : _Connection;
aCommand : _Command;
vRows : OleVariant;
iError : Integer;
sError : String;

procedure AddParamObj(const sName: String; iType,iParam,iSize: Integer);
var
aParam : _Parameter;
begin
with aCommand do
begin
aParam := CreateParameter(sName, iType, iParam,iSize, EmptyParam);
Parameters.Append(aParam);
end;
end;

begin
aCon := CoConnection.Create;
aCon.CursorLocation := adUseClient;
aCon.Open(cCS, '', '', adConnectUnspecified);
try
aCommand := CoCommand.Create;
try
with aCommand do
begin
CommandType := adCmdStoredProc;
CommandText := 'Sascha';
AddParamObj('RETURN_VALUE', adInteger, adParamReturnValue, 4);
// Input-Parameter
AddParamObj('@sSQL', adVarChar, adParamInput, 100);
// Output-Parameter
AddParamObj('@iStatus', adInteger, adParamOutput, 4);
// Parameter-Werte zuweisen
Parameters[1].Value := 'INSERT INTO Konto (KontoNr,Betrag) VALUES (1,1)';
// Ausführen
Set_ActiveConnection(aCon);
Execute(vRows, EmptyParam, adOptionUnspecified);
// Daten anzeigen
Memo1.Lines.Add('Rückgabewert: ' +
IntToStr(Parameters.Item[0].Value));
Memo1.Lines.Add('Statuswert: ' +
IntToStr(Parameters.Item[2].Value));
end;
finally
aCommand := nil;
end;
finally

with aCon do
for iError := 0 to Errors.Count - 1 do
sError := sError + Errors[iError].Description + #10#13;
Memo1.Lines.Add('Inhalt der Errors-Kollektion:');
Memo1.Lines.Add(sError);
aCon.Close;
aCon := nil;
end;
StatusBar1.SimpleText := 'Command-Objekt ist fertig.';
end;
</pre&gt

[Sascha Möller]

Ja, funktioniert gut.
Das mit dem Set nocount on hatte ich auch schon. Aber eine Abfrage auszuführen, die keinen Wert liefert bin ich nicht gekommen.

Danke
Sasch

[Andreas Kosch]

Hallo,

&gt;Aber eine Abfrage auszuführen, die keinen Wert liefert bin ich nicht gekommen.

der Trick ist ganz einfach: Getreu dem Motto "Es kann nur Einen geben" wird ein Recordset entweder für den automatisch Transport der Fehlermeldung genutzt <b>oder</b> für den Transport einer Ergebnismenge. Die Dummy-SELECT-Abfrage sorgt dafür, dass eine leere Ergebnismenge entsteht, die zwar keine Daten transportiert, aber trotzdem aus den Spalteninformationen der abgefragten Tabelle besteht. Da somit "kein Platz" mehr für die Problembeschreibung ist, kann Delphi keine automatische Exception mehr auslösen :-)

&#10