Skip to Content.
Sympa Menu

freetds - RE: [freetds] empty results on multiple step query

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Michael Peppler <mpeppler AT peppler.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] empty results on multiple step query
  • Date: Sat, 03 Jul 2004 17:59:05 +0200

On Sat, 2004-07-03 at 17:45, Adrian Lattke wrote:
>
> Now, I have a different problem on my hands... 4043 will be returned
> if I am fetching a row at a time, but what if I want to return the
> entire result-set?

> ## Prepare and execute query
> ($query = $dbh->prepare("set rowcount 0
> set TEXTSIZE 128000
> $sql"))

This is at least three queries, more depending on what is in $sql.

This means that to fetch the results you must loop, like this:

do {
$return = $query->fetchall_arrayref();
... do something with $return....
} while($query->{syb_more_results});

assuming that fetchall_arrayref() actually does the right thing (I have
to admit that I've never used it, and haven't verified that it does what
it is supposed to do with DBD::Sybase.)

> I have modified my original function, like this:
>
> ##############################################################
> sub run_sql {
> ...
> ## Are results expected?
> # $return = $query->fetchall_arrayref();
> # if ($DBI::err)
> # {
> # return(0, "Cannot get results: $DBI::errstr");
> # };
> while ($return = $query->fetchall_arrayref())
> {
> if ($DBI::err)
> {
> return(0, "Cannot get results: $DBI::errstr");
> };
> if ($query->{syb_result_type} == 4046)
> {
> return (1, "", $return);
> }
> }
> };
> ...
> };
> ##############################################################
>
> Now it all works fine. I thought I should post this in case someone
> else comes accross the same problem.

While this works for you, now, you should be careful about this.

You should really use a loop and check the syb_more_results attribute to
make sure that you really pull back all of the results from your query.

Michael



> Sent: Sat 7/3/2004 10:19 AM
> To: FreeTDS Development Group
> Subject: Re: [freetds] empty results on multiple step query
>
>
>
> On Sat, 3 Jul 2004 <adrian AT techonline.com> wrote:
> > Hi Guys,
> >
> > David Barnwell posted a very similar problem to the one I am
> > experiencing on Fri Jun 25 15:07:35 EDT 2004:
> >
> > https://lists.ibiblio.org/sympa/arc/freetds/2004q2/016465.html
> > <https://lists.ibiblio.org/sympa/arc/freetds/2004q2/016465.html>
> ...
> > ----------------------------------------------
> > set rowcount 0
> > select distinct
> > c.content_id as content_id,
> > c.content_title as content_title
> > into #temptable
> > from MYDB.dbo.content c
> > where c.content_type_name = 'mycontenttype'
> > select t.content_id,
> > t.content_title,
> > c.content_summary,
> > c.broadcast_date,
> > c.broadcast_length,
> > c.content_author_company
> > from TOL.dbo.content c, #temptable
> > where c.content_id = t.content_id
> > order by c.broadcast_date desc
> > drop table #temptable
> > set rowcount 0
> > ----------------------------------------------
> >
> > If I run this query against my SQL Server 7.0 server, using the new
> > configuration (i.e. freetds-0.62.4), it works fine. In fact, if I run
> > the query by tsql and run it against my SQL Server 2000 server, it runs
> > fine. For some reason, it returns "0" when I try fetching the results in
> > PERL against SQL Server 2000.
>
> The answer may be that you need to check $sth->{syb_result_type}, because
> the new server is returning spurious result status codes. Cf. perldoc
> DBD::Sybase.
>
> Your logs do have different counts for these things, see below. The 4043
> is a (perhaps unexpected) CS_STATUS_RESULT.
>
> HTH.
>
> --jkl
>
> $ grep ct_results S*.log |grep \(type |cut -b-24,85-
>
> SQLServer2K(tds70).log:2 (type 4052)
> SQLServer2K(tds70).log:2 (type 4052)
> SQLServer2K(tds70).log:2 (type 4049)
> SQLServer2K(tds70).log:2 (type 4040)
> SQLServer2K(tds70).log:2 (type 4052)
> SQLServer2K(tds70).log:2 (type 4052)
> SQLServer2K(tds70).log:2 (type 4052)
> SQLServer2K(tds70).log:2 (type 4052)
> SQLServer2K(tds70).log:2 (type 4052)
> SQLServer2K(tds70).log:2 (type 4052)
> SQLServer2K(tds70).log:2 (type 4043)
> SQLServer2K(tds70).log:2 (type 4049)
> SQLServer2K(tds70).log:2 (type 4040)
> SQLServer2K(tds80).log:2 (type 4052)
> SQLServer2K(tds80).log:2 (type 4052)
> SQLServer2K(tds80).log:2 (type 4049)
> SQLServer2K(tds80).log:2 (type 4040)
> SQLServer2K(tds80).log:2 (type 4052)
> SQLServer2K(tds80).log:2 (type 4052)
> SQLServer2K(tds80).log:2 (type 4052)
> SQLServer2K(tds80).log:2 (type 4052)
> SQLServer2K(tds80).log:2 (type 4052)
> SQLServer2K(tds80).log:2 (type 4052)
> SQLServer2K(tds80).log:2 (type 4043)
> SQLServer2K(tds80).log:2 (type 4049)
> SQLServer2K(tds80).log:2 (type 4040)
> SQLServer7(tds70).log:21(type 4052)
> SQLServer7(tds70).log:21(type 4052)
> SQLServer7(tds70).log:21(type 4049)
> SQLServer7(tds70).log:21(type 4040)
> SQLServer7(tds70).log:21(type 4052)
> SQLServer7(tds70).log:21(type 4052)
> SQLServer7(tds70).log:21(type 4052)
> SQLServer7(tds70).log:21(type 4052)
> SQLServer7(tds70).log:21(type 4052)
> SQLServer7(tds70).log:21(type 4052)
> SQLServer7(tds70).log:21(type 4049)
> SQLServer7(tds70).log:21(type 4040)
> SQLServer7(tds70).log:21(type 4052)
> SQLServer7(tds70).log:21(type 4052)
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> <http://lists.ibiblio.org/mailman/listinfo/freetds>
>
>
>
> ______________________________________________________________________
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
--
Michael Peppler Data Migrations, Inc.
mpeppler AT peppler.org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short
or long term contract positions - http://www.peppler.org/resume.html






Archive powered by MHonArc 2.6.24.

Top of Page