VB - Oracle procedures (select)

Hi,

Platforms : VB6, RDO
Database  : Oracle 8:0:5.1
Drivers   : Oracle8 ODBC Driver 8.0.3.0.2

This could be a common problem but I could not find a solution in the previous messages posted.

I have created a package and a procedure on Oracle database that belong to me (arun) ss:

Create or Replace package GetBrokerFirmPkg as

        cursor c1 is select BrokerFirmId, BrokerFirmName from BrokerFirm;

        type broker_cur is ref cursor return c1%ROWTYPE;

        Procedure GetBrokerFirmStp (bid in number, BrokerCur in out broker_cur);
END GetBrokerFirmPkg;

CREATE or REPLACE package body GetBrokerFirmPkg as

     Procedure GetBrokerFirmStp (bid in number , BrokerCur in out broker_cur) is
begin
open BrokerCur for select BrokerFirmId, BrokerFirmName from BrokerFirm where BrokerFirmId = bid;
end GetBrokerFirmstp;
end GetBrokerFirmPkg;

I want to retrieve the information from VB using RDO. The code I wrote was:

On Error GoTo Sqlerror

    Dim cn As New rdoConnection
    Dim qd As rdoQuery
    Dim rs As rdoResultset
    Dim cl As rdoColumn

    cn.Connect = "uid=arun;pwd=arun;DSN=TEAU;"
    cn.CursorDriver = rdUseOdbc
    cn.EstablishConnection rdDriverNoPrompt

    sql = "{Call GetBrokerFirmPkg.GetBrokerFirmStp (?,?)}"     Set qd = cn.CreateQuery("Problem_sql", sql)     qd.rdoParameters(0).type = rdTypeINTEGER     qd.(0).Direction = rdParamInput

Not posting later part since I get error at either of the last 2 lines as:
37000: ODBC Driver for Oracle: Syntax error or access violation

Would anybody help me on this please and specify what is missing?

Hence presently I use embedded sqls in the VB code to avoid the above problem as:
Set rdoConn = rdoEngine.rdoEnvironments(0).OpenConnection("", rdDriverNoPrompt, False, connectstr)

sql = "select EmployeeName, EmployeeId from Employee" Set rdoResult = rdoConn.OpenResultset(sql, rdOpenDynamic, rdConcurRowVer)

Do Until rdoResult.EOF

   ...
LOOP Thanks in advance
Arun Kamat

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jul 29 1999 - 00:00:00 CEST