[freetds] empty results on multiple step query

Adrian Lattke adrian at techonline.com
Sat Jul 3 11:45:27 EDT 2004


Thank you James for your prompt response. It seems that you have hit the nail on the head. 
 
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?
 
In my Perl code, I use the following function to return a result-set back to my code by using fetchall_arrayref():
 
##############################################################
sub run_sql
{
    my ($datasource, $results, $sql) = @_;
    my ($query, $dbh);
    my ($return) = undef;
 
    ## We don't have a handle yet, so let's get one
    $dbh = DBI->connect("$datasource->{datasource}",
                        "$datasource->{user}", 
                        "$datasource->{password}", 
                        {PrintError => 1,RaiseError => 0})
        || return(0, "Cannot connect to datasource $datasource->{datasource}, 
                     $datasource->{user}, $datasource->{password}: $DBI::errstr");
    $dbh->{syb_show_sql} = 1;
 
    ## Prepare and execute query
    ($query = $dbh->prepare("set rowcount 0
                             set TEXTSIZE 128000
                             $sql"))
        || return(0, "Cannot prepare query: $DBI::errstr");
    ($query->execute())
        || return(0, "Cannot execute sql: $DBI::errstr");
 
    ## Are results expected?
    if ($results)
    {
        $return = $query->fetchall_arrayref();
        if ($DBI::err)
        {
            return(0, "Cannot get results: $DBI::errstr");
        };
    };
##    $query->finish;
##    $dbh->disconnect();
    return (1, "", $return);
};
##############################################################
 
This function is used by my code in the following manner:
 
##############################################################
my ($ok, $msg, $results, $sql);
 
$sql = "select FIELD1 from TABLE1";

(($ok, $msg, $results) = &run_sql("",1,$sql))[0]
        || die $msg;
 
foreach (@{$results})
{
    print $_->[0], "\n";
}
##############################################################
 
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. 
 
Thank you for your help,
-Adrian Lattke
 

________________________________

From: freetds-bounces at lists.ibiblio.org on behalf of James K. Lowden
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> 




More information about the FreeTDS mailing list