Solved

Sybase does not allow more than one active statement when retrieving results without a cursor

Posted on 2006-07-20
6
1,494 Views
Last Modified: 2008-01-09
Does anyone know what this error message means and how to resolve it ?
0
Comment
Question by:colechr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 10

Expert Comment

by:bret
ID: 17146706
What program is raising the error?  What version of what Sybase database product are you using?
0
 
LVL 19

Accepted Solution

by:
grant300 earned 500 total points
ID: 17157355
I believe you are trying to write a client application program that issues an SQL statement while you are still returning results from the prior statement.  This can be something you are doing on purpose or by accident.  Let's look at the accidental case first.

Before you can issue a command, the command/connection handle has to be in the correct state.  If you have issued a command that returns multiple rows and/or multiple result sets, you must either exhaust the return of all rows in all result sets, or clear the command (essentially tell it to abandon any remaining results and prepare to be used again.)  It may not be obvious that even for a simple select statement, you will likely get two result sets.  The first has the rows you want in it.  The second is a MSG result set that has the @@ROWCOUNT value in it.

The correct way to code a client application is to have an inner and outer loop.  The outer loop handles each result set (regardless of type) until there are none left and the inner handles the rows from each result set.  BTW, I can think of at least four different kinds of result sets including a regular, message, error, and compute.  There is some example code published with the client libraries that should help.

If you are trying to return partial results and do some kind of processing on them as they come in that requires database activity, you have a couple of choices.  CT_LIB support Asynchronous connections that will allow you to have more than one active command at a time.  This is kind of a pain in the neck because you have to handle callbacks and stuff.  Possible but not pretty.  The other method is to open up a second connection to the database.  This is a common technique for writing client/server applications against Sybase.  It works well because a Sybase connection is so fast to make and is such low overhead that you don't need to worry about loading up the server.  Typically, one connection is used to do all the reads while the other is reserved for writes (insert/update/delete).

Hope this helps,
Bill
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 17162134
Basically, it means you have not finished fetching all the rows
(either in the current result set, or in following result sets)
before you have executed the next command
0
 
LVL 3

Expert Comment

by:dave_gr
ID: 17179382
You're trying to do 2 queries over one connection.  Finish (and close) the first before starting the next one.  If you need both queries open at the same time use two distinct connections to the database.
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Compliance and data security require steps be taken to prevent unauthorized users from copying data.  Here's one method to prevent data theft via USB drives (and writable optical media).
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question