sybperl-l Archive

From: Ashu Joglekar <ajogleka at ltcm dot com>
Subject: RE: How do I get a value with a select constant ?
Date: Aug 30 1999 8:01PM

Hi,

Your SybCmd routine calls dbresults only once although in this case you need
to call it twice, once for the embedded select * from sysobjects and once
for the select 1 or select 0 bit.

It is a bit odd that Sybase expects the client to call dbresults once for an
embedded select that doesn't return a value to the client, but it does! 

The following code prints out "ret is 1" with two calls to dbresults and
"ret is " with only one call:

#!/bin/perl5

use LTCM::Sybutils;

$dbh = new LTCM::Sybutils "LTCM"; # just a local routine for connecting to
servers without specifying a uid/password

$dbh->dbcmd("if exists(select * from sysobjects where name = 'sysobjects')
select 1 else select 0");
$dbh->dbsqlexec();
$dbh->dbresults();
$dbh->dbresults();
($ret) = $dbh->dbnextrow();
print "ret is $ret\n";
exit;

The moral of the story is "always call dbresults in a loop, then call DBROWS
to make sure you have rows in the result and then call dbnextrow"... or so
says the DBLIB manual :-)

Cheers,
Ashu

> -----Original Message-----
> From: Robert Bresner [mailto:rbresner@olf.com]
> Sent: Monday, August 30, 1999 3:14 PM
> To: SybPerl Discussion List
> Subject: How do I get a value with a select constant ?
> 
> I want to see if a table exists in a database, 
> and if it does, I want to select data from that table. 
> So, I connect to the database and check sysobjects 
> for the table, and if it exists I select 1, if 
> it doesn't exist, I select 0. Then I check the 
> result of that bit of code. But, when I do a 
> dbnextrow(), I'm getting an undefined. 
> 
> Here's my Perl:
> 
>    $dbh = SybConnect($Password, $Server, $User );
>    $dbh->dbuse($DB) or return 0;
>    my $Cmd = "if exists( select * from sysobjects where name = 
>       'version_number' and type = 'U' ) select 1 else select 0";
>    my $Result = SybCmd( $dbh, $Cmd );
>    my $RetVal = $dbh->dbnextrow();   # Is this the right thing? 
>    if( not defined $RetVal )  {
>       print "NO RETVAL ($Result)\n";
>       return 0;
>    }
>    else  {
>       print "RETVAL: $RetVal\n";
>       return $RetVal;
>    }
> 
>    sub SybCmd
>    {
>       my ($dbh, $Cmd) = @_;
>       chomp $Cmd;
>       print STDOUT "SybCmd: $Cmd\n" if $verbose;
>       $dbh->dbcmd("$Cmd\n");
>       $dbh->dbsqlexec;
>       return $dbh->dbresults;
>    }
> 
> SybCmd() is returning 1 (one). But dbnextrow() is 
> getting me nothing. So, I'm wondering what I'm doing
> wrong. It looks so right to me, ya know?
> 
> (The reason I'm doing a select 0/1 in the if exists
> is because Sybase is parsing the whole command string
> before it runs it, and if the table doesn't exist, I 
> get an error before the block of code runs. Make sense?)
> 
> Thanks in advance for another perspective ...
> 
> ----------------------------------------
> Robert Bresner          rbresner@olf.com
> Open Link Financial    516-227-6600 x216
> http://www.olf.com/    fax: 516-227-1799
> ----------------------------------------
> Opinions expressed are explicitly my own
> "No more talking!  Cerebus has a SWORD!"
> 
> 
>