VB RDO / Oracle - stored procedures, problem

Sorry if this was posted twice, I'm not sure that it was correctly sent the first time...

**

Hello everybody. I'm hoping that an RDO wiz might be able to help me out with an answer. I've researched the problem a fair bit, but I haven't found a satisfactory answer yet.

I'm trying to execute an Oracle stored procedure in a VB app using RDO (VB6.0, RDO 2.0, Oracle 8.0.4, Oracle ODBC driver 8.00.04.00). Here's the VB code I'm using:

  Dim test As rdoConnection
  Dim testQuery As rdoQuery
  Dim strSQL As String

  Set test = rdoEnvironments(0).OpenConnection("DSN=QWX_Oracle_8_0_4", 1, 0, "DSN=QWX_Oracle_8_0_4;UID=dbo;PWD=testpwd;")   strSQL = "{call dbo.sp_insert_funct (?,?)}"   Set testQuery = test.CreateQuery("InsertTest", strSQL)

  testQuery(0).Type = rdTypeVARCHAR
  testQuery(0).Direction = rdParamInput
  testQuery(0).Value = "TestInsertion on Oracle"
  testQuery(1).Type = rdTypeINTEGER
  testQuery(1).Direction = rdParamInput
  testQuery(1).Value = 1

Pretty straightforward, I think.

The RDOConnection object is connecting correctly to the Oracle database. The rdoQuery object also gets created. However, when I try to refer to an rdoParameter object, I get the runtime error 40041: "Object Collection: Couldn't find item indicated by text".

I can plainly see (in the watch window) that no rdoParameter objects are created/present in the rdoParameters collection, even after the rdoQuery object has been successfully created.

I've done some tests using SQL Server, and I've noticed that when the rdoQuery object refers to a non-existent stored procedure, no rdoParameter objects are in the rdoParameters object, no matter the number of '?' placeholders in the SQL query. On the other hand, if the rdoQuery object is successfully created, the correct number of rdoParameter objects are already in the collection, ready to be refered to and set. (Which I find odd, because that means that RDO must be connecting to the DB to confirm that the stored procedure in question exists and its' number of params. Otherwise, how would it know?).

Please note, the code above works correctly with a DSN to an SQL Server database which contains an equivalent stored procedure.

I do not believe this is an issue with the Oracle ODBC driver, as the same query works properly on my machine from SQL query tools (I successfully executed the query on Oracle (using the same DSN) with MSQuery, a tool which comes with SQL Server 6.5).

A few other notes:
1)user "dbo" I'm using to log in owns the stored procedure. [Quoted] 2)I've also tried using "{begin call dbo.sp_insert_funct (?,?); end;}" [Quoted] as query string, as suggested by someone in a few older posts refering [Quoted] to a similar problem, with the same results.

This problem has me stumped. The only possibility I can see is a bug with RDO, but I haven't found any notes about it on MS' website. Can anyone help me out? Suggest workarounds? Alternative methods? I've seen a number of posts concerning very similar problems, but I haven't found a solution anywhere.

Thanks in advance.
R/mi.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Feb 21 2000 - 21:11:35 CET