Re: Stored Procedures / Oracle / ODBC
Date: 1996/05/21
Message-ID: <31A0C1D5.4A12_at_competent.com>#1/1
Vincent Wouters wrote:
>
> Can someone tell me how to work with Stored Procedures
> in a VB4.0/32 environment, working wit Data Access Object.
>
> I'm using a statement like:
> BEGIN
> proc_name ('inputstring',output);
> END;
>
> On the server there is a Oracle procedure wich will
> return a list off names as output.
> But Oracle has problems with the 'output' and says that
> it has to be declared. I thought the result (output) wil
> send in the RecordSet, like a normal Select procedure.
>
> Please mail any suggestions:
> v.wouters_at_fd.agro.nl
If ORACLE stored procedures are anything like SQL Server, then the stored procedures can have both input and output arguments, but ODBC (as far as I have been able to determine) can't handle the output arguments. What you need to do within the stored procedure is SELECT the data that is to be returned from the procedure and then use the OpenRecordset method to view the results.
For instance, I have an SQL procedure which has one input argument and returns the sum value of a field in the target table:
PROCEDURE sp_foo
/* input argument */
_at_account_id INTEGER
AS
BEGIN
/* Declare a local variable to hold the summary information */
DECLARE
_at_total_due INTEGER
/* Add up all of the amount due's and store the total in the local variable _at_total_due */SELECT _at_total_due = SUM(amount_due) FROM bills WHERE account_id = _at_account_id
/* Now select the result so that the caller can see it. The "total_due" assigns a temp column name. */
SELECT "total_due" = _at_total_due
END Now, back in VB, I execute:
Function TotalAmountDue(accountId as Long) as Long
Dim SQL as String
Dim snpTotalDue as Recordset
' Build the call to the stored procedure
SQL = "sp_foo " & accountId
' Execute the stored procedure, returning the results into the snapshot
' ensure that passthrough is set.
Set snpTotalDue = accountDB.OpenRecordSet(SQL, dbOpenDynaset, dbSQLPassThrough)
If snpTotalDue.RecordCount <> 0 Then
snpTotalDue.MoveFirst ' If the return value is null, totalDue = 0 TotalAmountDue = iif(IsNull(snpTotalDue!total_due), 0, snpTotalDue!total_due Else TotalAmountDue = 0
End If
End Sub
I hope this helps.
-- Karl Costenbader, President Competent Consulting Sacramento, CA EMail: karl_at_competent.com Home Page: http://www.competent.comReceived on Tue May 21 1996 - 00:00:00 CEST