Long queries fails on PHP/MSSQL/FreeTDS

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alexey_r
Forum Newbie
Posts: 4
Joined: Thu Oct 04, 2007 5:27 am
Location: Moskow, Russia

Long queries fails on PHP/MSSQL/FreeTDS

Post 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)
bnhcomputing
Forum Newbie
Posts: 3
Joined: Fri Mar 07, 2008 3:41 pm

Re: Long queries fails on PHP/MSSQL/FreeTDS

Post 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.
kukulis
Forum Newbie
Posts: 1
Joined: Tue Apr 15, 2008 1:11 pm

Re: Long queries fails on PHP/MSSQL/FreeTDS

Post 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.
Dutchben
Forum Newbie
Posts: 12
Joined: Wed May 14, 2008 10:19 am

Re: Long queries fails on PHP/MSSQL/FreeTDS

Post by Dutchben »

Did any of you find a solution to this problem?
bnhcomputing
Forum Newbie
Posts: 3
Joined: Fri Mar 07, 2008 3:41 pm

Re: Long queries fails on PHP/MSSQL/FreeTDS

Post 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.
Dutchben
Forum Newbie
Posts: 12
Joined: Wed May 14, 2008 10:19 am

Re: Long queries fails on PHP/MSSQL/FreeTDS

Post 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. :D
bnhcomputing
Forum Newbie
Posts: 3
Joined: Fri Mar 07, 2008 3:41 pm

Re: Long queries fails on PHP/MSSQL/FreeTDS

Post 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.
Dutchben
Forum Newbie
Posts: 12
Joined: Wed May 14, 2008 10:19 am

Re: Long queries fails on PHP/MSSQL/FreeTDS

Post 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) 8)
alexey_r
Forum Newbie
Posts: 4
Joined: Thu Oct 04, 2007 5:27 am
Location: Moskow, Russia

Re: Long queries fails on PHP/MSSQL/FreeTDS

Post 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
Dutchben
Forum Newbie
Posts: 12
Joined: Wed May 14, 2008 10:19 am

Re: Long queries fails on PHP/MSSQL/FreeTDS

Post 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
alany
Forum Newbie
Posts: 1
Joined: Mon Oct 13, 2008 9:37 pm

Re: Long queries fails on PHP/MSSQL/FreeTDS

Post 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).
Post Reply