TSQLStoredProcedure

[SimonWilsher]

Hi

We're current evaluating the VISOCO dbExpress driver for Sybase ASA with an established Sybase ASA 9.0.2 database.

The existing solution uses the standard BDE components (TDatabase, TQuery, TStoredProc etc.) to access the database successfully.

However, we're currently experiencing difficulties retrieving a result from a stored procedure in the database using the dbExpress driver.

The stored procedure looks something like this:

ALTER FUNCTION "DBA"."TotalReceipts"(IN CurTransID INTEGER)
RETURNS MONEY
BEGIN
DECLARE TotRec MONEY;
SELECT SUM(Pmt_Total) INTO TotRec FROM "DBA".Accts_Pmts
WHERE Accts_Pmts.Trans_ID=CurTransID;
IF TotRec IS NULL THEN
SET TotRec=0
END IF;
RETURN TotRec;
end

The Delphi 7 code using the dbExpress driver to access the stored procedure looks like this:

with spTotalReceipts do
begin
SQLConnection := dbSource;
ParamCheck := True;
StoredProcName := 'DBA.TotalReceipts';
ParamByName('CurTransID').AsInteger := CurTransID;
ExecProc;
PmtsRec := ParamByName('TotalReceipts').AsCurrency;
end;

However, the final line returns an error: Parameter "TotalReceipts" not found. How can we access the result of the stored procedure?

Our existing application using TStoredProcedure works just fine, and looks like this:

with spTotalReceipts do
begin
ParamByName('CurTransID').AsInteger := CurTransID;
Prepare;
ExecProc;
GetResults; {forces results from a Sybase server}
PmtsRec := ParamByName('TotalReceipts').AsCurrency;
UnPrepare;
end;

Can you advise where we're going wrong on this one!?

Thanks

Simon

[VISOCO Support]

Hi there,

Unfortunately, there is an architectural drawback in the current version of the driver. It cannot obtain a result of the stored procedure. We hope, this will be fixed in the next major version.

Try to use out parameters instead.