Re: Help: Using ADO,C++ with Stored Procedure Resultset

From: mark tomlinson <marktoml_at_hotmail.com>
Date: Tue, 22 Feb 2000 21:23:01 +0000
Message-ID: <38B2FE34.81348F59@hotmail.com>

Example of Calling a stored procedure and returning a resultset from a Ref Cursor using the Oracle 8.0.5.6 Driver

          and ADO Version 2.1

Private Sub STProc_Click()
' This example code demonstrates calling packaged procedures
' From ADO and also returning result sets from them
'
' This code was tested using the following software:
' Visual Basic Version 6 Service Pack 3 with
' Microsoft Activex Data Objects Version 2.1
' Microsoft ODBC Driver Manager Version 3.510.3711.0
' Oracle ODBC DRiver for Oracle Version 8.0.5.6
' (NOTE: that this does not work with Version 8.0.5.0 to 8.0.5.4
' Error Message:
' Run-Time Error -2147467259(80004005)':
' ORA-6550 - PLS-00306 wrong number or types of argument in call
' to 'GETEMPS' )
'
' Oracle Database Version 8.0.5.1

' The Command to create the packaged procedure (under the SCOTT schema)
is
'
'create or replace package adotst as
'type empcur is ref cursor;

'procedure getemps(vdeptno in number,vcount out number,ecur out empcur);
'end adotst;
'/

'create or replace package body adotst as
'procedure getemps(vdeptno in number,vcount out number,ecur out empcur)
is
'begin
' select count(*) into vcount from emp where
' deptno = vdeptno;

' open ecur for select ename from emp
' where deptno = vdeptno;
'end getemps;
'end adotst;
'/

   Dim cnn1 As ADODB.Connection
   Dim cmdExeproc As ADODB.Command
   Dim prmDeptno As ADODB.Parameter
   Dim prmECount As ADODB.Parameter
   Dim rstDepts As ADODB.Recordset
   Dim intDeptno As Integer
   Dim strEname As String
   Dim strCnn As String
' Open connection.

   Set cnn1 = New ADODB.Connection
' Modify the following line to reflect a DSN within your environment

   strCnn = "DSN=W805; UID=scott; PWD=tiger;"    cnn1.Open strCnn
   cnn1.CursorLocation = adUseClient
' Open command object with one parameter.

   Set cmdExeproc = New ADODB.Command
' Note that this has been tested using
' cmdExeproc.CommandText = "scott.adotst.GetEmps"
' which also works

   cmdExeproc.CommandText = "adotst.GetEmps"    cmdExeproc.CommandType = adCmdStoredProc
' Get parameter value and append parameter.

   intDeptno = Trim(InputBox("Enter Department:"))    Set prmDeptno = cmdExeproc.CreateParameter("vdeptno", _

      adInteger, adParamInput)
   Set prmECount = cmdExeproc.CreateParameter("vcount", _

      adInteger, adParamOutput)
   cmdExeproc.Parameters.Append prmDeptno    prmDeptno.Value = intDeptno
   cmdExeproc.Parameters.Append prmECount
' Create recordset by executing the command.
' NOTE: if no resultset is being returned execute the stroed procedure
' using cmdExeproc.Execute on it's own

   Set cmdExeproc.ActiveConnection = cnn1    Set rstEmps = cmdExeproc.Execute
' Build stirng to be displayed with information returned

   strEname = "The " & prmECount.Value & _      " Employees in Department " & intDeptno & " are :"    Do While Not rstEmps.EOF

      strEname = strEname & " " & rstEmps!ename & ","
      rstEmps.MoveNext

   Loop
'

   MsgBox (strEname)
' Close resultsets and log off

   rstEmps.Close
   cnn1.Close
End Sub Received on Tue Feb 22 2000 - 15:23:01 CST