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.

Macrae

Answer 1

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.

Boresome

I am using just query() instead of prepare() and execute(). Does it make any difference? – Macrae

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? – Macrae

@NeverEndingQueue: I suspect the first rs is not closed, so rs1.Close() should fix it. – Boresome

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. – Macrae

@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 ! – Schumacher

Answer 2

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

Bolognese