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: "Adrian Lattke" <adrian AT techonline.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] empty results on multiple step query
  • Date: Sat, 3 Jul 2004 11:45:27 -0400

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:
>
> 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>





Archive powered by MHonArc 2.6.24.

Top of Page