Solved

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

Posted on 2006-07-20
6
1,469 Views
Last Modified: 2008-01-09
Does anyone know what this error message means and how to resolve it ?
0
Comment
Question by:colechr
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sybase sql anywhere unload database 3 829
How would you store an Access 2003 memo data type field in Sybase. 1 560
SyBase SQL Query Syntax 11 470
Why did SAP buy Sybase? 3 168
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 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