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

From: Phil Bolduc <pbolduc_at_my-deja.com>
Date: Fri, 11 Feb 2000 14:56:55 GMT
Message-ID: <8817vk$a1m$1@nnrp1.deja.com>

I have been having problems using a stored procedure as the source for my resultset in Oracle 8.1.5i on NT using MSVC++ 6.0 and ADO (MDAC 2.5 SDK). I have read http://osi.oracle.com/~tkyte/ResultSets/index.html which helped me get on the correct track. I tried to convert the VB example, however I think it is RDO instead of ADO. Below is the section of code I had trouble with

     sSQL = "{call RefTest.GetEmpData(?,?)}"

     Set qd = cn.CreateQuery("", sSQL)

     qd.rdoParameters(0).Type = rdTypeVARCHAR
     qd(0).Direction = rdParamInputOutput
     qd(0).Value = Text1.Text
     qd.rdoParameters(1).Type = rdTypeVARCHAR

     'Dynamic or Keyset is meaningless here
     Set rs = qd.OpenResultset(rdOpenStatic)

What I tried to do was:

     _CommandPtr pCmd = NULL;
     _RecordsetPtr pRs = NULL;

     SAFE_CALL( pCmd.CreateInstance( __uuidof(Command) ));
     pCmd->PutActiveConedction( pConnection );
     pCmd->PutCommandText( L”{ call sp_ListAirport }” );
     pCmd->PutPrepared(true);
     pRs = pCmd->Execute(NULL, NULL, adCmdText);
     while ( ! pRs->adoEOF )
     {
          pRs->MoveNext();
     }
     pRs->Close();

It always fails on the pCmd->Execute() statement. Following syntaxes errors

“{ call sp_ListAirport }” 0x80040E14 - The command contained one or more errors
“{ ? = call sp_ListAirport }” 0x80004005 - Unspecified error

Here is the source for Oracle objects:

CREATE TABLE AIRPORT2 (
  TLA VARCHAR2 (3),
  DESCRIPTION VARCHAR2 (80) ) ; CREATE OR REPLACE PACKAGE types IS

   TYPE cursorType IS REF CURSOR;
END types;
/

CREATE OR REPLACE FUNCTION sp_ListAirport2 Return types.cursorType AS

           l_cursor types.cursorType;
BEGIN

	 open l_cursor FOR SELECT TLA, DESCRIPTION FROM AIRPORT2;
	 return l_cursor;

END sp_ListAirport2;
/

I am logging in as the same user that owns all of the above objects. Any suggestions would be apreciated.

Phil Bolduc
North Vancouver, BC Canada

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Feb 11 2000 - 08:56:55 CST