Executing stored procedures on Sybase server through MS RDO

Executing stored procedures on Sybase server through MS RDO

Post by Alan Leu » Wed, 19 Nov 1997 04:00:00



Hi,

        How do you call a stored procedure in Sybase 10 through MS RDO?  
        On the Sybase server, you can type

EXEC OPR_ACN_QUERY '0181-63931'
GO

        and get some results.

I have tried two ways of getting a rdoResultset, with varying degrees of
failure.  

(1) Calling the stored procedure as the source argument of my
rdoConnections(0).OpenResultset call

    Dim results As rdoResultset

    On Error GoTo DataRetrievalErrorHandler

    Set results = rdoEnvironments(0).rdoConnections(0).OpenResultset( _
        "EXEC OPR_ACN_QUERY '0181-63931'", _
        rdOpenForwardOnly, rdConcurReadOnly)

The results.RowCount is "-1" meaning that the number of rows returned is
"not available".      

(2) As a Prepared Statement

    Dim prep_stmt As rdoPreparedStatement
    Dim query As String

    query = "{ call OPR_ACN_QUERY ( ? ) }"

    Set prep_stmt =
rdoEnvironments(0).rdoConnections(0).CreatePreparedStatement("ACN_Query",
query)

    prep_stmt.rdoParameters(0) = "' 0181-6391' "
    Set results = prep_stmt.OpenResultset(rdOpenForwardOnly,
rdConcurReadOnly)

        The ODBC driver reports that there is a syntax error in this case.

I' d appreciate any help.  Thanks.

--




 
 
 

Executing stored procedures on Sybase server through MS RDO

Post by Simon Jone » Thu, 20 Nov 1997 04:00:00


In your first method
The fact that the rowcount is unavailable does not surprise me. The
rowcount may not be known until you transfer all the rows to the client PC.
With a forward only cursor this won't happen until you have read all the
rows into your application. If you change to a static cursor then the ODBC
driver will buffer rows for you and it _may_ know the rowcount earlier.

NOTE You should use the rdExecDirect option when opening a resultset
directly from the connection object. If you don't do this RDO will create a
stored procedure with which to execute your SQL statement. This will slow
down execution.

In your second method

Quote:>     prep_stmt.rdoParameters(0) = "' 0181-6391' "

Should be
     prep_stmt.rdoParameters(0) = "0181-6391"
You don't need to give any delimiters. RDO knows which parameters should be
strings, and which should be dates, integers, etc
--
Regards,
Simon Jones
PC Pro Magazine

 
 
 

1. MS SQL Server BLOBS, RDO and Stored Procedures


<error report and request for help deleted>

Do you have the bugfix version of RDO?  Check the Microsoft web site for
it.  It requires the VB 4.0a upgrade; check the MS site for that too.

--
o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=
Bob McDonald
Correct reply address by replacing "STOPSPAMMING" with "com".
o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=o=0=

2. Grant BINDADD

3. Perl Library to execute stored procedure calls to MS-SQL Server

4. Can oracle write blobs into Informix ?

5. Executing Stored procedure with multiple select statement using RDO

6. Query assistance

7. Stored Procedure with parameters and Rdo Execute execdirect

8. Instant Success-Increased Sales leads guaranteed...

9. RDO and executing stored procedures

10. Executing a Stored Procedure in a Stored Procedure and selecting on the result

11. executing system stored procedures within a stored procedure

12. Executing stored procedure from another stored procedure.