CODEWITHSUNDEEP
phpsql-serverodbcfreetdsunixodbc
dynamphorous
dynamphorous

Reputation: 769

ODBC_CONNECT cursor type conflict with SQLGetData in PHP with freeTDS and unixODBC

I have a PHP project running on my Gentoo Linux that uses FreeTDS & UnixODBC to connect to a MSSQL database on a Windows server. I have had this project working with this exact code for years now, but recently I had to update PHP when Gentoo no longer had a version 5.3 ebuild in portage and other system updates needed to happen.

The current versions of the various software being used is:
PHP is version 5.6.17
FreeTDS is version 0.91
UnixODBC is version 2.3.2-r1

But now some of the very same queries that used to work perfectly return this error.

PHP Warning: odbc_fetch_object(): SQL error: [unixODBC][Driver Manager]SQLGetData is not allowed on a forward only (non-buffered) cursor, SQL state SL008 in SQLGetData in /home/XXXXX/XXXX.php on line Y

Not all queries return this error, only some, but the same queries consistently return the same error.

A simple PHP program which will return this error is as follows:

$con = odbc_connect(DBNAME,UNAME,PW,SQL_CUR_USE_ODBC)
$query = "SELECT * FROM SomeTable"
$Output = odbc_exec($con,$query);
$return_array = array();
while($row = odbc_fetch_object($Output)){
#
        foreach($row as &$value){
                $value = mb_convert_encoding($value, "UTF-8", "Windows-1252");
        }
        unset($value);
        $return_array[] = $row;
}
echo json_encode($return_array,JSON_UNESCAPED_UNICODE);
odbc_close($con);
?>

Now this is definitely related to the 4th parameter supplied to odbc_connect when using SQL_CUR_USE_ODBC, the error is as I said above. When this is changed to SQL_CUR_USE_IF_NEEDED it returns the error:

Warning: odbc_fetch_object(): No tuples available at this result index in /home/XXXXX/XXXX.php on line Y []

With a identical result for SQL_CUR_USE_DRIVER, or if it is left blank.

Again, two days ago this was all functional code across all queries. So something changed from PHP 5.3 to any version of php > 5.3. Every version of PHP has been tried from 5.4 to 7.0 (there is a PHP 7 ebuild in portage) and all produce the same errors.

Any help or direction in this matter would be greatly appreciated.

Upvotes: 2

Views: 1796

Answers (3)

AH Rahman
AH Rahman

Reputation: 1

I faced this exact problem. Adding 'SET ANSI_WARNINGS OFF' before my query solved the issue. thanks.

Freetds version : 0.91.112 PHP Version 5.3.29

Upvotes: 0

Milo LaMar
Milo LaMar

Reputation: 2256

The first major hint I discovered was that it only happened when we did a SELECT with a text column along with other columns. Using SELECT on a text column by itself did not throw the error. And selecting every other column except the text column did not throw the error. We had just moved from SQL Server 2000 to 2008 so I was more than willing to try out VARCHAR(MAX) in favor of the text column we had to use in 2000.

ALTER TABLE tbl_name ALTER COLUMN col_name VARCHAR(MAX) [NOT] NULL fixed all these error messages for us. It transfers VARCHAR(MAX) just fine even when there were several KB of text returned. The old Text implementation left the column size at 2GB so I'm hypothesizing FreeTSD 0.91 has some size restriction.

Upvotes: 2

dynamphorous
dynamphorous

Reputation: 769

After extensive testing, the solution to this problem was to notice that these are just Warnings, not errors. They should have been thrown, but then also have the SQL execute.

However some glitch in one of these ODBC or TDS libraries was causing warnings to prevent the execution of the SQL. It is not a GREAT solution, but including SET ANSI_WARNINGS OFF at the start of the query shut off the warnings, and now the code executes exactly as it did before.

$query = 'SET ANSI_WARNINGS OFF
          SELECT * FROM SomeTable';

Is what my query string looks like now. Not ideal, but it works.

Upvotes: 0

Related Questions