Re: Stored Procedures / Oracle / ODBC

From: Karl Costenbader <karl_at_competent.com>
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.com
Received on Tue May 21 1996 - 00:00:00 CEST

Original text of this message