[freetds] empty results on multiple step query

Michael Peppler mpeppler at peppler.org
Sat Jul 3 11:59:05 EDT 2004


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:
> > 
> > http://lists.ibiblio.org/pipermail/freetds/2004q2/016465.html <http://lists.ibiblio.org/pipermail/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





More information about the FreeTDS mailing list