[prev in list] [next in list] [prev in thread] [next in thread]
List: freetds
Subject: Re: [freetds] FreeTds, Dblib, SQl server2000 and the strange case of drop queries
From: "Thomas, Christopher (LLU)" <cwthomas () llu ! edu>
Date: 2014-11-14 7:49:50
Message-ID: D5C071CD696CB34EAE2B70DD5567F4C00640397A () LLUDBS2 ! llu ! ad ! lluahsc ! org
[Download RAW message or body]
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@lists.ibiblio.org] On Behalf Of Simone \
Magnaschi
Sent: Wednesday, November 12, 2014 7:02 AM
To: freetds@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@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@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@lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS@lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic