Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

Sybase Perl DBI

by bheeshmaraja (Novice)
on Oct 18, 2012 at 08:50 UTC ( #999695=perlquestion: print w/replies, xml ) Need Help??
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();

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

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", ) );

    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;
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();

    (Usually you use DBI with the prepare-execute-fetch combination, though. The select{row,all,col} functions are there just for convenience.)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://999695]
Approved by Corion
[LanX]: poor attempt to poke fun ...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (4)
As of 2017-05-30 01:33 GMT
Find Nodes?
    Voting Booth?