FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

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