Sybase Perl DBI

bheeshmaraja has asked for the wisdom of the Perl Monks concerning the following question:

I have a small subroutine which fetch user details from Sybase DB. Please find my below subroutine.. Is there any way to return the query results from the subroutine straight away without using @uids array and $i variables.

sub sa_details {
   $dbh=Sybase::DBlib->new("user", "passwd", "host");
   $dbh->dbcmd("SELECT userid FROM user");
   $dbh->dbsqlexec or die "Can't execute SQL statement:\n";
   while($dbh->dbresults!=NO_MORE_RESULTS){
        $i=0;
       while(@row=$dbh->dbnextrow){
           @uids[$i] = $row[0];
           $i++;
       }
       return @uids;
    }
    $dbh->dbclose
}

@array = sa_details();
[download]

Please help me to make this code efficiently(not lousy). Thanks... Bheeshmaraja

Comment on Sybase Perl DBI Download Code

Replies are listed 'Best First'.
Re: Sybase Perl DBI by Anonymous Monk on Oct 18, 2012 at 09:06 UTC
You should write like this because of Lexical scoping like a fox, Variable Scoping in Perl: the basics `sub sa_details { my $dbh = shift; my @uids; $dbh->dbcmd("SELECT userid FROM user"); $dbh->dbsqlexec or die "Can't execute SQL statement:\n"; while( $dbh->dbresults != NO_MORE_RESULTS ){ while( my @row = $dbh->dbnextrow ){ push @uids, $row[0]; } } return @uids; } my @array = sa_details( Sybase::DBlib->new( "user", "passwd", "host", ) );` [download] Or you could write `sub sa_details { my $dbh = shift; $dbh->nsql( "SELECT userid FROM user", "ARRAY" ); } my @arry = sa_details( Sybase::DBlib->new( "user", "passwd", "host", ) ); $DB_ERROR and die $DB_ERROR; ## $Sybase::DBlib::DB_ERROR and die $Sybase::DBlib::DB_ERROR;` [download]	[reply] [d/l] [select]
Re: Sybase Perl DBI by Anonymous Monk on Oct 18, 2012 at 20:15 UTC
Is there a reason you are using Sybase::DBlib instead of the more usual DBI? DBI has a handy selectcol_arrayref() function which folds a single-column resultset into an array reference. `sub sa_details { my $dbh = ... return $dbh->selectcol_arrayref( qq{SELECT userid FROM user} ); } my $arrayref = sa_details();` [download] (Usually you use DBI with the prepare-execute-fetch combination, though. The select{row,all,col} functions are there just for convenience.)	[reply] [d/l]