FreeTDS Development Group

["Thomas, Christopher (LLU)" <cwthomas AT llu.edu>]

I would prepare a statement and then call fetchAll on the statement. This way 
you don't have an open recordset that might go away. Since you are only 
getting the top 5, there shouldn't be too much overhead by fetching all.

Chris


-----Original Message-----
From: FreeTDS [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Simone 
Magnaschi
Sent: Wednesday, November 12, 2014 7:02 AM
To: freetds AT lists.ibiblio.org
Subject: Re: [freetds] FreeTds, Dblib, SQl server2000 and the strange case of 
drop queries

Hi to all,
with the help of Stack Overflow, this came up:
https://bugs.php.net/bug.php?id=65945

So apparently it was the previous version of PHP (5.3 and before) that were 
not "compliant" with the actual MSSQL (TDS), DBLIB and FreeTDS behaviour. 
They buffered the entire recordset during a fetch call rather than fetch a 
row at a time.
By doing this our code was completely valid and working (except for the fact 
that fetch was a sort of fetchAll behind the curtains). As a side note, now I 
understand completely why using fetch with large recordset were not a problem 
using the pdo_sqlserver driver on my windows machine, while triggered the out 
of memory on the Linux + PHP 5.2 box.

Other drivers may behave differently (pdo mysql  and pdo sqlserver don't use 
the one connection - one statement enforcement).

So I think that we'll need to refactor all the code where necessary.

Simone


Il 12/11/2014 13:41, Simone Magnaschi ha scritto:
> 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
>> <simone.magnaschi AT rockol.it>:
>>> 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
>> FreeTDS AT lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>>
>

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

_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds