Invalid cursor state, SQL state 24000 in SQLExecDirect

Question

I need to call two stored procedures in sequence via ODBC in PHP:

#run stored procedure 1
$query = "Shipped_Not_Shipped_Rep ".$_GET['rep_id'];
$result = odbc_exec($dbh, $query);
odbc_result_all($result);

#run stored procedure 2
$query = "Shipped_Not_Shipped_Account ".$_GET['account_id'];
$result = odbc_exec($dbh, $query);
odbc_result_all($result);

I'm getting this error in PHP after the second stored procedure call:

Warning: odbc_exec() [function.odbc-exec]: SQL error: [unixODBC][FreeTDS][SQL Server]Invalid cursor state, SQL state 24000 in SQLExecDirect

If I re-arrange the order I call the stored procedures, it is always the second that errors. Is there a way to, idk, reset the cursor position between calls? A little out of my element here.

Answer 1

Something to try for people getting invalid cursor state with SQL server:

SET NOCOUNT ON;

At the top of your stored procedure or SQL script. Found here: https://social.msdn.microsoft.com/Forums/en-US/f872382a-b226-4186-83c7-0d0fcadcd3eb/invalid-cursor-state?forum=sqldataaccess I had this problem just running some very average SQL in SQL Server 2017

Answer 2

Open two handles to the database. ODBC probably maintains the cursor in the handle.

Answer 3

I ran into the same problem, but odbc_free_result($result) between the 2 queries did the job for me.

Documentation:

bool odbc_free_result ( resource $result_id )

Free resources associated with a result.

odbc_free_result() only needs to be called if you are worried about using too much memory while your script is running. All result memory will automatically be freed when the script is finished.

Note: If auto-commit is disabled (see odbc_autocommit()) and you call odbc_free_result() before committing, all pending transactions are rolled back.

Answer 4

I found the exact problem as well. Apparently this is common with the free ODBC drivers. This has been my morning headache from trying to migrate a project from MySQL to ODBC SQL Server. I finally found what got rid of this for me.

This error shows up because an active cursor still exists from the previous result set. I was able to get rid of this error without using the disconnect/reconnect method by ensuring I read through the entire first record set (even if only using a partial piece of it) before issuing a new one. Note: I'm using PHP.

Gives me an error:

$sql="SELECT COUNT(whatever) as whatever FROM whatever";<br />
$countResult = odbc_exec($db, $sql);<br />
$countMenuHeader = odbc_fetch_array($countResult);<br />
extract ($countMenuHeader);<br />
$countRecords = $NumMenuHeader;<br />

$sql="SELECT whatever as whatever FROM whatever";<br />
$result = odbc_exec($db, $sql);<br />
$MenuHeader = odbc_fetch_array($result);<br />

Cleared the error:

 $sql="SELECT COUNT(whatever) as whatever FROM whatever";<br />
 $countResult = odbc_exec($db, $sql);<br />

 while($countMenuHeader = odbc_fetch_array($countResult))<br />
 {<br />&nbsp;&nbsp;
 extract ($countMenuHeader);<br />&nbsp;&nbsp;
 $countRecords = $NumMenuHeader;<br />}

 $sql="SELECT whatever as whatever FROM whatever";<br />
 $result = odbc_exec($db, $sql);<br />
 $MenuHeader = odbc_fetch_array($result);<br />

In short, make sure you completely read or fetch the data set before moving to the next statement.

Answer 5

Try accessing the results using different cursors, $result1 and $result2.

Answer 6

Just to clarify that calling finish() means you are merely finished with the current query and its results and you can now safely call execute() again. No need to call prepare() again first.

I got caught by this one on AIX using the ODBC driver to a DB2 SQL database. I think it was AIX that had an old ODBC driver perhaps because things were fine on Linux.

Anyway, I did an SQL query that returns one row, over and over in a for loop as follows and got the 24000 error.

my $sth = $dbh->prepare( $query ) or die "dying";

foreach my $i (@blah)
{
    $sth->execute($fred, $i) or die "dying";

    my $hash_ref = $sth->fetchrow_hashref("NAME_uc"); # only a single row exists

    ...

    $sth->finish(); # MUST do this
} 

My conclusion is that I had to call $sth->finish() so that I could safely call $sth->execute() again, otherwise I could get "Invalid cursor state. SQLSTATE=24000" error message.

This is because we must make sure we completely read or fetch all the data set before moving to the next statement.$sth->finish() indicates to DBI that you are finished with the statement handle. It can then be reused for a call to execute()

I also found that I could instead put the fetch in a while loop, even though only one row is ever returned but the query. It seems trying to fetch the next non-existent row also makes the sth resuable for an execute.

my $sth = $dbh->prepare( $query ) or die "dying";

for
{ 
    $sth->execute($fred, $i) or die "dying";

    while (my $hash_ref = $sth->fetchrow_hashref("NAME_uc"))
    {
         ...
    }    
}

Both solutions worked for me. :)

Answer 7

A work around for opening nested queries using unixODBC and freeTDS in PHP

if you can modify to your odbc.ini create another configuration section using the same connection info but another section name:

[DATASOURCE1]
Description = "Data Connection 1"
Driver = FreeTDS
Server = <your server>
Port = 1433
Database = <your db>

[DATASOURCE1A]
Description = "Data Connection 2"
Driver = FreeTDS 
Server = <your server>
Port = 1433
Database = <your db>

Then in your code create two handles, one for each data source description:

$dbhandle = odbc_connect('DATASOURCE1', 'user', 'password');
$dbhandle1 = odbc_connect('DATASOURCE1A', 'user', 'password');

Then you can use the handles in nested queries:

$dbresult = odbc_exec($dbhandle, "SELECT <some sql>");
while($row = odbc_fetch_array($dbresult)) {

 $dbresult1 = odbc_exec($dbhandle1, "<some different sql>");

 while($row1 = odbc_fetch_array($dbresult1)) {
  
  #do stuff with nested query data like $row['name'] and $row1['time']
 }
}

You could nest deeper but you have to create a odbc.ini entry for each level. It ain't pretty but it works for me until multiple cursors are available.