One of the SELECT fails using unixOdbc - SQLSTATE[24000]: Invalid cursor state

Question

I am running Ubuntu 13.10 with FreeTDS and ODBC (package: php5-odbc) installed. I use tds version = 8.0, but also tried tds version = 7.2.

I am using PDO and this is my DSN:

$dsn = sprintf('odbc:Driver=FreeTDS;Server=%s;Port=1433;Database=%s', DB_SQL_SERVERNAME, DB_DB_NAME);

I connect to MSSQL instance and perform some INSERT/SELECT queries using transactions, however I can not figure out why this query fails:

SELECT id 
FROM tblColumns 
WHERE siteID = 10063 AND 
    typeID = 1000 AND 
    extendedTypeID = 18 AND 
    label = 'RwThiFc85A'

giving error:

SQLSTATE[24000]: Invalid cursor state: 0 [FreeTDS][SQL Server]Invalid cursor state (SQLExecute[0] at /build/buildd/php5-5.5.3+dfsg/ext/pdo_odbc/odbc_stmt.c:254)

I am running bunches of similiar queries before and they are performed well, e.g.:

SELECT id 
FROM tblColumns 
WHERE siteID = 10063 AND 
    typeID = 1000 AND 
    extendedTypeID = 3 AND 
    label = 'VwThiFc91B'

Do you have ideas why it happens?

I did not have such a issues with dblib and sqlsrv, however now I am on Unix and can not use sqlsrv, and due to issues with dblib UTF-8 encoding I am trying to use ODBC.

bjnr · Accepted Answer · 2013-12-15T13:09:22.503

8

It looks you have two different result sets open at the same time. You have to finish processing your first ResultSet and close it so you can re-use the Statement to create the second ResultSet.

edited Dec 15 '13 at 13:09

answered Dec 15 '13 at 12:55

bjnr

3,353
1
18
32

I am using just query() instead of prepare() and execute(). Does it make any difference? – Tom Raganowicz Dec 15 '13 at 12:58
Also I don't understand what you mean by saying: "two different request open at the same time". I run one of the queries and after it's ran there is next query, there is no way to run 2 at once or something like that. Right? – Tom Raganowicz Dec 15 '13 at 13:08
2

@NeverEndingQueue: I suspect the first rs is not closed, so rs1.Close() should fix it. – bjnr Dec 15 '13 at 13:12
1

I am not entirely sure why it worked, but it's fine now. Other drivers did not require that, also there are few queries executed inside of that loop, but it was failing only on that specific SELECT. Thanks for help. – Tom Raganowicz Dec 16 '13 at 10:40
@NeverEndingQueue Some drivers do FetchAll and Close on their own. Other does fetch line by line, and you have to Close() result set by yourself. Which behavior is better - hard to say, depends on the size of result dataset, but both of them exists. I run into the same situation, this thread helped me to solve my problem, Thanks ! – TPAKTOPA Mar 23 '15 at 08:58

score 1 · Answer 2 · answered Apr 16 '18 at 06:00

Here is the practical implementation of @mihai-bejenariu answer.

If you are using PHP and PDO, you can do like this:

$query = "<your sql query>";
$sth = $connection->prepare($query);
$sth->execute();
$result = $sth->fetchAll();
$sth->closeCursor();   //Write this after you have fetched the result

One of the SELECT fails using unixOdbc - SQLSTATE[24000]: Invalid cursor state

2 Answers2

Linked