SQL Anywhere Stored Procedures 
Author Message
 SQL Anywhere Stored Procedures

Has anyone been able to pass parameters, execute, receive return values,
and receive resultsets to stored procedures in SQL Anywhere with VB 4.0
RDO data access method?  I have tried it with both Intersolv's ODBC 3.0
and SimbaExpress ODBC products and got errors accessing the
rdoParameters.

Thanks!



Tue, 29 Feb 2000 03:00:00 GMT
 SQL Anywhere Stored Procedures


Quote:
>Has anyone been able to pass parameters, execute, receive return values,
>and receive resultsets to stored procedures in SQL Anywhere with VB 4.0
>RDO data access method?  I have tried it with both Intersolv's ODBC 3.0
>and SimbaExpress ODBC products and got errors accessing the
>rdoParameters.

>Thanks!

Hi,

I'm using VB5 and SQL Anywhere 5.5.03, and I initially had similar
problems. I fought this for a few hours. Delphi didn't exhibit this
problem with it's stored procedure object, so I think the problem lies
in RDO, although I haven't had problems calling MS SQL Server stored
procedures...

For whatever reason, rdo seems to get confused with the parameter
names and types; it seems to pull the names and types from other
stored procedures in the database. The number of parameters is always
correct, though.

To get around the problem, I specifically set the datatype and
direction parameters when I created the rdoQuery object, and it's
behaved ever since. It's a bit of a kludge and potentially a
maintenance headache, but it works...

Here's an example of what I mean:

  Dim rq As New rdoQuery
  Dim rdoCon as rdoConnection

  ...

  ' Create the predefined query
  Set rq = rdoCon.CreateQuery("spNextPKey", _
                "{Call spNextPKey( ?, ?, ?)}")

  ' Force parameter directions and data types
  ' for this stored procedure
  rq(0).Type = rdTypeVARCHAR
  rq(0).Direction = rdParamInput
  rq(1).Type = rdTypeINTEGER
  rq(1).Direction = rdParamOutput
  rq(2).Type = rdTypeINTEGER
  rq(2).Direction = rdParamOutput

After this you can successfully assign and read the "value" parameters

e.g.
  rq(0) = "MyTable"
  rq(1) = "5"

  X = rq(2)

I think in VB4 you need to use an rdoPreparedStatment instead of
rdoQuery, I think rdoQuery came in RDO v2.0, but I can't be certain.

Hope this helps!

----------------------------------------------------
Greg Strauss
Visionary Solutions Corp.
Victoria, BC

(remove obvious part above for email address)



Sat, 04 Mar 2000 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. SQL Anywhere stored procedures and VB5

2. Converting SQL Anywhere stored procedures to ORACLE for use with Powerbuilder

3. Error handling in SQL Anywhere stored procedure

4. SQL Anywhere stored procedures and VB5

5. Help on SQL Anywhere - Stored Procedures

6. SQL Anywhere Stored Procedure

7. SQL Anywhere stored procedure resultset saw as a parameter?

8. Executing a stored procedure in a Sybase SQL anywhere database, VB5 Enterprise

9. MS ADO / Sybase SQL Anywhere ASA 7 Stored Procedure Return Value Problem

10. SQL Anywhere 5.0.02 and Stored Procedures struggle

11. SQL anywhere assigning null to stored procedure variables?

12. Problem with SQL Anywhere 5.5 stored procedure call from Delphi 5


 
Powered by phpBB® Forum Software