MS ADO / Sybase SQL Anywhere ASA 7 Stored Procedure Return Value Problem 
Author Message
 MS ADO / Sybase SQL Anywhere ASA 7 Stored Procedure Return Value Problem

I've got an application that we've upgraded from MS DAO & ASA 5 to MS ADO &
ASA 7. All this has worked quite smoothly, but I've now discovered that our
stored procedures are not
returning the correct return values. This was not previously a problem. We
are using ADO 2.5 SP1 and the latest ASA EBF 1133.

The specific problem seems to be that stored procedures that do not specify
a return value, return garbage.

I've checked the stored procedures using ISQL and code along the lines of:



This all works fine and I get a return value of 0. This was also the code we
used with DAO and I've tested it using ADO. No problems.

Using ADO with the following code, returns garbage as the return value:

Dim oConnection  As ADODB.Connection
Dim oCommand    As New ADODB.Command
Dim oRetVal          As ADODB.Parameter
Dim oRecordSet   As ADODB.RecordSet
Dim lSPRetValue  As Long

Set oConnection = New ADODB.Connection

oConnection.CursorLocation = adUseClient
oConnection.Open "Data Source=" & <DataSource>, <UserId>, <Password>

With oCommand

  '~ set up command object
  .CommandType = adCmdStoredProc
  .CommandText = <my_proc>
  Set .ActiveConnection = oConnection

  '~ Add a parameter for the stored procedure's return value
  Set oRetVal = .CreateParameter(Name:="RetVal", _
                                 Type:=adInteger, _
                                 Direction:=adParamReturnValue)
  .Parameters.Append oRetVal

  .Parameters("RetVal").Value = 0
End With

'~ open recordset and pass in Command object
Set oRecordSet = New ADODB.RecordSet

oRecordSet.CursorLocation = adUseClient
oRecordSet.Open oCommand

lSPRetValue = CLng(oCommand.Parameters("RetVal").Value)

I'm a bit loathed to revert to the code we used with DAO, as it is embeded
in a common DLL that is used for access to all varieties of databases. Any
help would be appreciated. Thank you.

Phil Conkie
Senior Consultant
Integris



Tue, 22 Apr 2003 23:48:09 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Passing parameter to Sybase ASA Stored procedure via ADO

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

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

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

5. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

6. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

7. Problem evaluating return value from SQL Server stored procedure

8. Sybase Anywhere 7.0 stored procedure call fail

9. SQL Stored Procedure Return Value

10. Getting a return value from a SQL Server stored procedure through VB

11. Return value from stored procedure in SQL 6.5 to VB4 to a 16 bits client

12. Return a value from stored procedure in SQL 6.5 to VB4 to a 16 bits client

 

 
Powered by phpBB® Forum Software