FreeTDS Invalid Cursor State When Counting NULL Columns

3
votes

I am having trouble running a query that uses the COUNT() function on a field that contains NULL values.

I am using FreeTDS v0.91 with unixODBC, PHP 5.3.10, and SQL Server 2008. I have my connection to use the 7.2 protocol. I am attempting to run the following query:

SELECT COUNT(ProductCode) AS p FROM Membership

This query only throws an error when every ProductCode in Membership is null. When I run this on SQL Server directly, it evaluates how it should (to 0).

When I try to run this query from FreeTDS, I receive this error:

SQLSTATE[24000]: Invalid cursor state: 0 [FreeTDS][SQL Server]Invalid cursor state (SQLFetchScroll[0] at /builddir/build/BUILD/php-5.3.10/ext/pdo_odbc/odbc_stmt.c:537)

Does anyone know why I would receive this error? I remember encountering a similar error when I was attempting to loop through two result sets at once, but this is a single query.

This query works fine when there is a record with a non-null value in ProductCode. It seems to only fail when the result of COUNT() would have been zero.

Does anyone know what could be causing this issue or how I could resolve it?

1
phpsql-serverfreetdsunixodbc
Have you tried setting it to tds version 8.0? - Andomar
Try wrapping an isnull around the column: SELECT COUNT(ISNULL(ProductCode,'')) AS p FROM Membership - twoleggedhorse
@Andomar: When I use version 8.0, I receive "Can't open cursor lib 'libodbccr' : file not found" - Jeremy Livingston
Try this [link][1]. It worked for me in xampp 1.4(RHEL). [1]: stackoverflow.com/questions/16963282/… - Prabhakar Manthena

1 Answers

0
votes

Since the Product code is NULL it is not counted by SQL Server for it to do this you'll need to tell it:

 ISNULL(ProductCode, 0 )

or 

ISNULL(ProductCode, '' )