Problem with Large Query Returns from MS-SQL

------=_Part_76269_33179861.1180109409529
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hello,

I am writing an application that is using DBI to access a MS SQL database
hosted on a remote server. I think I am having trouble with larger result
sets.

For example:

If I do

$stmt = "SELECT * FROM tableA";

Where where tableA is around 10 rows, then my test script returns all of the
rows in the table and prints them out to the user on the browser.

However, if I do:

$stmt = "SELECT * FROM tableB";

Where tableB is around 6000 rows, the script does not produce any results
and continues with the rest of the script with an empty result set. Even if
I put conditions on the query for the large table that would return only 2
or 3 rows, the result set is not returned.

Does DBI have some kind of default timeout setting that breaks off after
waiting for a query result? If so, can I alter it somewhere?

I should also mention that all of the queries I am trying in the script work
fine when I directly call them on the database.

Thank you,

Jordan

------=_Part_76269_33179861.1180109409529--

Re: Problem with Large Query Returns from MS-SQL

Hey!

You haven't mentioned which DBD you're using.

I suspect it's DBD::ODBC.

If so, you might want to check out this page:
http://search.cpan.org/~jurl/DBD-ODBC-1.13/ODBC.pm

for 'odbc_query_timeout' parameter.

Try playing with it, _however_, it should be unlimited by default.
Have you measured how long does your 'long' query take?

Fi.

On 5/25/07, Jordan Mueller wrote:
> Hello,
>
> I am writing an application that is using DBI to access a MS SQL database
> hosted on a remote server. I think I am having trouble with larger result
> sets.
>
> For example:
>
> If I do
>
> $stmt = "SELECT * FROM tableA";
>
> Where where tableA is around 10 rows, then my test script returns all of the
> rows in the table and prints them out to the user on the browser.
>
> However, if I do:
>
> $stmt = "SELECT * FROM tableB";
>
> Where tableB is around 6000 rows, the script does not produce any results
> and continues with the rest of the script with an empty result set. Even if
> I put conditions on the query for the large table that would return only 2
> or 3 rows, the result set is not returned.
>
> Does DBI have some kind of default timeout setting that breaks off after
> waiting for a query result? If so, can I alter it somewhere?
>
> I should also mention that all of the queries I am trying in the script work
> fine when I directly call them on the database.
>
> Thank you,
>
> Jordan
>

Re: Problem with Large Query Returns from MS-SQL

Also try enaling tracing to see what's realy going on.

Tim.

On Fri, May 25, 2007 at 08:42:41PM +0400, Fi Dot wrote:
> Hey!
>
> You haven't mentioned which DBD you're using.
>
> I suspect it's DBD::ODBC.
>
> If so, you might want to check out this page:
> http://search.cpan.org/~jurl/DBD-ODBC-1.13/ODBC.pm
>
> for 'odbc_query_timeout' parameter.
>
> Try playing with it, _however_, it should be unlimited by default.
> Have you measured how long does your 'long' query take?
>
> Fi.
>
> On 5/25/07, Jordan Mueller wrote:
> >Hello,
> >
> >I am writing an application that is using DBI to access a MS SQL database
> >hosted on a remote server. I think I am having trouble with larger result
> >sets.
> >
> >For example:
> >
> >If I do
> >
> >$stmt = "SELECT * FROM tableA";
> >
> >Where where tableA is around 10 rows, then my test script returns all of
> >the
> >rows in the table and prints them out to the user on the browser.
> >
> >However, if I do:
> >
> >$stmt = "SELECT * FROM tableB";
> >
> >Where tableB is around 6000 rows, the script does not produce any results
> >and continues with the rest of the script with an empty result set. Even
> >if
> >I put conditions on the query for the large table that would return only 2
> >or 3 rows, the result set is not returned.
> >
> >Does DBI have some kind of default timeout setting that breaks off after
> >waiting for a query result? If so, can I alter it somewhere?
> >
> >I should also mention that all of the queries I am trying in the script
> >work
> >fine when I directly call them on the database.
> >
> >Thank you,
> >
> >Jordan
> >