Skip to Content.
Sympa Menu

freetds - Re: [freetds] FreeTds, Dblib, SQl server2000 and the strange case of drop queries

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Simone Magnaschi <simone.magnaschi AT rockol.it>
  • To: freetds AT lists.ibiblio.org
  • Subject: Re: [freetds] FreeTds, Dblib, SQl server2000 and the strange case of drop queries
  • Date: Wed, 12 Nov 2014 13:41:14 +0100

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





Archive powered by MHonArc 2.6.24.

Top of Page