Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

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


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:


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");
($ret) = $dbh->dbnextrow();
print "ret is $ret\n";

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


> -----Original Message-----
> From: Robert Bresner []
> 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
> Open Link Financial    516-227-6600 x216
>    fax: 516-227-1799
> ----------------------------------------
> Opinions expressed are explicitly my own
> "No more talking!  Cerebus has a SWORD!"