Page 1 of 1
Long queries fails on PHP/MSSQL/FreeTDS
Posted: Tue Feb 12, 2008 9:14 am
by alexey_r
Sometime I need to execute long queries (it is takes for about 8-10 seconds), but it is fails with error "Query failed in sql_test.php on line ...". This error appearance when I use FreeTDS extension of php (php_dblib.dll).
However, when I use standart extension (php_mssql.dll) it is no problem.
Someone can help me?
Editing php.ini and increasing mssql.timeout or mssql.connect_timeout not solve this problem.
Editing freetds.conf and increasing timeout or connect timeout parameters not solve this problem.
Running on PHP 5.2.3, MS SQL Server 2000, php_dblib.dll version 5.2.4.4
When I enable dump on FreeTDS it is return this (just piece of dump):
net.c:743:Sending packet
0000 01 01 00 5e 00 00 01 00-65 00 78 00 65 00 63 00 |...^.... e.x.e.c.|
0010 20 00 61 00 6c 00 5f 00-73 00 65 00 6c 00 65 00 | .a.l._. s.e.l.e.|
0020 63 00 74 00 69 00 6e 00-76 00 6f 00 69 00 63 00 |c.t.i.n. v.o.i.c.|
0030 65 00 70 00 6f 00 73 00-69 00 74 00 69 00 6f 00 |e.p.o.s. i.t.i.o.|
0040 6e 00 73 00 20 00 38 00-36 00 38 00 2c 00 34 00 |n.s. .8. 6.8.,.4.|
0050 36 00 2c 00 20 00 31 00-2c 00 20 00 30 00 |6.,. .1. ,. .0.|
dblib.c:4297:dbsqlok(013A9F40)
net.c:363:error: select(2) returned 0x2726, "Unknown error"
util.c:328:tdserror(013A76E8, 013AA6B8, 20004, 10022)
dblib.c:7583:dbperror(013A9F40, 20004, 0)
dblib.c:7646:"Read from the server failed", client returns 2 (INT_CANCEL)
Re: Long queries fails on PHP/MSSQL/FreeTDS
Posted: Fri Mar 07, 2008 3:43 pm
by bnhcomputing
I have the EXACT same problem.
Currently researching possible solutions. If anybody has any ideas, I know of at least two (2) additional developers with the same problem.
Re: Long queries fails on PHP/MSSQL/FreeTDS
Posted: Tue Apr 15, 2008 1:13 pm
by kukulis
Same happened to me.
May be there is some configuration variable in freetds.conf, where we can set bigger time limit or smth.
Re: Long queries fails on PHP/MSSQL/FreeTDS
Posted: Wed May 14, 2008 10:21 am
by Dutchben
Did any of you find a solution to this problem?
Re: Long queries fails on PHP/MSSQL/FreeTDS
Posted: Wed May 14, 2008 8:04 pm
by bnhcomputing
Yes, we did find a cure.
It involved getting the SOURCE for freetds and PHP.
We ended up needing to recompile/rebuild EVERYTHING.
Re: Long queries fails on PHP/MSSQL/FreeTDS
Posted: Thu May 15, 2008 11:26 am
by Dutchben
That was not really the reply i was looking for

. Recompiling everything and modifying the source on a windows machine goes beyond my capabilities.
It appears to me that this behavior is a bug in the windows implementation of FreeTDS (php_dblib.dll) that occurs on complex querys with large result sets. Its not related to any timeout parameter. Using php_dblib.dll
SELECT * FROM table 1 // Works even for very large tables and long querys
SELECT * FROM table 1 INNERJOIN table 2 INNERJOIN table 3 ..// Doesn't work for very large result sets.
SELECT TOP 1000 * FROM table 1 INNERJOIN table 2 INNERJOIN table 3 ..// Does work- which is strange i think.
php_mssql.dll doesn't have the above problem but gives you others in return. I wasn't able to fix php_dblib.dll or php_mssql.dll but did find a working alternative. Microsoft has provided a free (as in beer) driver for PHP.
http://www.microsoft.com/sql/technologi ... fault.mspx
The functions provided are not documented on php.net but they do provide a decent help file. Well hope this helps someone.

Re: Long queries fails on PHP/MSSQL/FreeTDS
Posted: Thu May 15, 2008 2:21 pm
by bnhcomputing
They don't call it the "bleeding edge" for nothing.
Developers who have been working with the growing number of Community Technology Preview (CTP) builds of various Microsoft products are finding out the hard way what being an 'early adopter' means.
Testers (and Microsoft employees themselves) have run into difficulties when attempting to keep track of which CTP is which. In addition, CTPs of different products don't seem to mix very well with each other and/or with full-fledged beta versions of Microsoft wares. Sometimes, CTPs don't work correctly with shipping Micorsoft products products.
Link to entire article:
http://www.microsoft-watch.com/content/ ... rikes.html
We are a PRODUCTION shop and needed something RELIABLE that wasn't going to die tomorrow because of some patch/release/etc. causing a conflict.
CTP's are not 100% reliable OR 100% supported by MS which is why we chose the recompile/rebuild route.
Re: Long queries fails on PHP/MSSQL/FreeTDS
Posted: Thu May 15, 2008 2:36 pm
by Dutchben
Thats true and even Microsoft doesn't recommend it for use in a production evironment. But hey.. it works for me - and if it works it works (for now that is)

Re: Long queries fails on PHP/MSSQL/FreeTDS
Posted: Sat May 17, 2008 11:44 am
by alexey_r
Thanks to all persons, who answer on this topic!
Yes. MSSQL Driver for PHP works.
But the main problem I can't solve is - I need to get
UNICODE data from MSSQL.
I try to do something like that:
Code: Select all
$conn = sqlsrv_connect('192.168.0.1', array('Database'=>'Flowers', 'UID' => 'sa', 'PWD' => ''));
$sql = 'select Name from Farm where Name like \'%de la%\'';
$stmt = sqlsrv_query( $conn, $sql);
while ( sqlsrv_fetch( $stmt))
{
$res = sqlsrv_get_field($stmt, 1, SQLSRV_PHPTYPE_STRING(SQLSRV_SQLTYPE_NVARCHAR));
$res = mb_convert_encoding($res, 'UTF-8', 'UTF-16');
//$res = iconv('ASCII', 'UTF-8', $res);
//echo mb_detect_encoding($res).' - '.$res;
echo iconv_get_encoding($res).' - '.$res;
echo '<br>';
}
But this is not works (I get strange characters such this is not UTF-8, but UTF-16 (2 bytes)).
I continue find solutions, mayby we need to open new topic special for MSSQL Driver for PHP
Re: Long queries fails on PHP/MSSQL/FreeTDS
Posted: Mon May 19, 2008 8:44 am
by Dutchben
I do not have musch experience with the problem you have but i think for SQLSRV_PHPTYPE_STRING you can only use 2 constants as parameters.
From the help file:
SQLSRV Constant Description
SQLSRV_ENC_BINARY
Data is returned as a raw byte stream from the server without performing encoding or translation.
SQLSRV_ENC_CHAR
Data is returned in 8-bit characters as specified in the code page of the Windows locale set on the system. Any multi-byte characters or characters that do not map into this code page are substituted with a single byte question mark (?) character.
So
$res = sqlsrv_get_field($stmt, 1, SQLSRV_PHPTYPE_STRING(SQLSRV_SQLTYPE_NVARCHAR));
Should be
$res = sqlsrv_get_field($stmt, 1, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR));
Hope it helps
Re: Long queries fails on PHP/MSSQL/FreeTDS
Posted: Mon Oct 13, 2008 9:41 pm
by alany
Any chance of getting a php_dblib.dll (or php_mssql.dll) compiled against php 5.2.x and a recent FreeTDS?
The one from kromann.info fixed my remote connection problem, but I've now run into the problem discussed above and would like to avoid building it myself if I can (I'd prefer to do, but right at this moment I don't have the time).