Re: FreeTds, Dblib, SQl server2000 and the strange case of drop queries

Hi Frediano, thanks for the extensive reply!
> With default configuration TDS protocol (defined by Microsoft, not by
> us) cannot do two queries at the same time. To do what your pseudo
> code do we have to either

Uh.. I didn't know that. I'm wondering why the same code was running 
fine on the old server.

> However as James always remember you are probably doing the wrong
> thing. Looking at your code I would say why not using a query like
> Your code actually does 6 queries to server while mine single queries
> does only one. Also consider the query caching, locking, network round
> trips and so on usually there are better way to do it.

I should have pointed out the fact that the code was there just to 
recreate the problem . The real code is actually different and involves 
different and nested function calls that from a PDO and database point 
of view translate in one or more query issued inside a fetch loop of a 
main query. The sub queries may vary based on the circumstances, so a 
single query is not always an option for me. In this particular case btw 
I could just use a fetchall function and save everything in a array and 
then loop through the array and do my thing on each element (that's what 
I did to solve the problem in a hurry). This works fine since - I 
imagine - there aren't overlapping queries.

Code refactoring on the whole codebase is an option and, if I cannot get 
the problem solved, a necessity. My curiosity and head-scratching come 
from the fact that the old linux server (same client code, older os / 
freetds version / php version) worked without issues.

I'll try to get the machine back up to see if I'm missing something on a 
conf point of view.

>> Same code on a Windows PHP with pdo_sqlserver driver works just fine.
>>
> Which driver are you using?
>

On the windows workstation I'm using the php_pdo_sqlsrv_55_ts.dll 
against a SQL SERVER 2008.
I also use a PHP 5.2 version on the same Windows machine with the 
php_pdo_sqlsrv_53_ts_vc6.dll extension and still no issues.

> Usually on way to understand what's going on is to enable TDSDUMP on
> FreeTDS. See http://www.freetds.org/userguide/logging.htm.
Unfortunately the production box has a lot of traffic and I'm not able 
to isolate the log for my particular test script. I'm going to build a 
mirror server to being able to try with the loggin on



Il 12/11/2014 13:04, Frediano Ziglio ha scritto:
> 2014-11-12 11:40 GMT+00:00 Simone Magnaschi <[email protected]>:
>> Good morning to all.
>>
> Hi Simone,
>
>> I'm new to the mailing list, I subscribed since we're having issues on a new
>> server deployment using FreeTDS. We were using FreeTDS before on a old
>> server and everything was fine.
>>
>>
>> So we've got a new server with
>>
>>   * Debian Wheezy 32BIT
>>   * PHP 5.5.18
>>   * FreeTDS 0.91
>>
>> This PHP app needs to talk to an old SQL server 2000 server. We used the old
>> code from our previous server (PHP 5.2 and older FreeTDS - can't get the
>> version unfortunately). We connect to SQL server 2000 through PDO using
>> dblib driver.
>>
>> We're experiencing weird behaviour with the fetch function. Basically if we
>> issue a query during a fetch loop on the same pdo connection object, the
>> main query gets reset and next fetch call will return false even if there
>> are still records to be fetched.
>>
>> |// PSEUDO CODE
>> // Here the main query
>> $q = $sql7->query("SELECT TOP 5 * FROM News ORDER BY Data Desc");
>> while ($row = $q->fetch(PDO::FETCH_ASSOC)) {
>>      // Looping through the results
>>      echo "<h1>Main query</h1>";
>>      print_r($row);
>>
>>      // Issue a query on the same pdo connection
>>      $subq = $sql7->query("SELECT TOP 1 * FROM News WHERE IDNews = " .
>> $row['IDNews'] . " ");
>>      while ($subResult = $subq->fetch(PDO::FETCH_ASSOC)) {
>>          echo "<h1>Inner query</h1>";
>>          print_r($subResult);
>>      }
>>
>>      // Here the main query $q->fetch(PDO::FETCH_ASSOC) will answer false on
>> the next iteration
>>      // if we remove the subq, the main query loops just fine
>>      echo "<hr>";
>> }
>> |
> With default configuration TDS protocol (defined by Microsoft, not by
> us) cannot do two queries at the same time. To do what your pseudo
> code do we have to either
> - case result of first query;
> - use cursors on first query to you free connection;
> - use MARS (I don't think actually you can do it and not with dblib for sure!).
>
> However as James always remember you are probably doing the wrong
> thing. Looking at your code I would say why not using a query like
>
> SELECT TOP 5 * FROM News ORDER BY Data DESC
>
> as IDNews should probably be unique? Or something like
>
> SELECT * FROM News WHERE IDNews IN (SELECT TOP 5 IDNews FROM News
> ORDER BY Data DESC)
>
> Filtering double IDs with client. Or using a store procedure that does
> all for you?
>
> Your code actually does 6 queries to server while mine single queries
> does only one. Also consider the query caching, locking, network round
> trips and so on usually there are better way to do it.
>
>> Same code on a Windows PHP with pdo_sqlserver driver works just fine.
>>
> Which driver are you using?
>
>> It doesn't matter the type of fetch that we pass as argument of fetch
>> function.
>>
>> PHP doesn't throw any warning or error.
>>
>> I really don't know what's going on here, and I don't know if it's an issue
>> of PHP / FreeTDS / DBlib.
>>
>> Any help would be great!
>>
>>
>> Thanks in advance
>> Simone Magnaschi
>>
> Usually on way to understand what's going on is to enable TDSDUMP on
> FreeTDS. See http://www.freetds.org/userguide/logging.htm.
>
> Regards,
>    Frediano
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
>

-- 
Simone Magnaschi
Rockol s.r.l. - CTO
Tel. +39 02.45490556