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.

Solution 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.

Solution 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