Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

MySQL and Perl - Shorthand

by mt2k (Hermit)
on Sep 21, 2002 at 03:11 UTC ( #199692=perlquestion: print w/ replies, xml ) Need Help??
mt2k has asked for the wisdom of the Perl Monks concerning the following question:

Okay, I have gotten MySQL and Perl working together for a CGI script. Everything is working fine, but I can't help but stare at the lengths I have to go through when I want to get a single value from the database. Below are two samples of code that work, but I am looking to see if a shorter way exists:

# Example 1: fetchrow_arrayref my $sth = $db->prepare("SELECT the_name FROM the_table WHERE id=3"); $sth->execute(); print $sth->fetchrow_arrayref()->[0]; $sth->finish(); # Example 2: fetchrow_hashref $sth = $db->prepare("SELECT the_name FROM the_table WHERE id=3"); $sth->execute(); print $sth->fetchrow_hashref()->{'the_name'}; $sth->finish();

I skipped the while{} loop for the fetchrow_hashref() because this is an instance where I know for a fact that there will only be one value returned. My real question is whether or not the value I am retrieving can be put in a variable during the call to prepare() (perhaps replacing it with do()). Something like this perhaps:

my $sth = $db->do("SELECT the_name FROM the_table WHERE id=3"); print $sth;

Thanks ahead of time (assuming you help me) :)

Comment on MySQL and Perl - Shorthand
Select or Download Code
Re: MySQL and Perl - Shorthand
by schweini (Friar) on Sep 21, 2002 at 03:25 UTC
    AFAIK, there's no really shorter way to do this directly with the DBI.
    but using "do" directly on a dbh wont help you. the docs state that it wont return a sth.
    try "selectrow_array" and its cousins instead.
    ("Database Handle Methods" in the dbi-docs)

    i basically put the stuff you wrote into a sub in case i simply have to look up a single value.
Re: MySQL and Perl - Shorthand
by Zaxo (Archbishop) on Sep 21, 2002 at 03:26 UTC

    What you want is:

    my ($the_name) = $dbh->selectrow_array( "SELECT the_name FROM the_table WHERE id=3");
    but if you ever want to know another id, you're better off with:
    $sth = $db->prepare("SELECT the_name FROM the_table WHERE id=?"); $sth->execute(3); my ($the_name) = $sth->fetchrow_array;

    After Compline,
    Zaxo

      This
      $sth = $db->prepare("SELECT the_name FROM the_table WHERE id=?"); $sth->execute(3); my ($the_name) = $sth->fetchrow_array;
      Can be converted into this
      my ($the_name) = $dbh->selectrow_array(<<SQL, undef, 3); SELECT the_name FROM the_table WHERE id = ? SQL
      Although it always feels a little weird using selectrow_array for a single value. But I guess that's what abstraction layers are for :)
      HTH

      _________
      broquaint

Re: MySQL and Perl - Shorthand
by dws (Chancellor) on Sep 21, 2002 at 04:14 UTC
    Everything is working fine, but I can't help but stare at the lengths I have to go through when I want to get a single value from the database.

    If four statements strikes you as being a great length, then hide the length by wraping it up in a subroutine.

    sub the_name { my $id = shift; my $sth = $db->prepare("select the_name from table where id = ?" +); $sth->execute($id); my ($the_name) = $sth->fetchrow_array(); $sth->finish(); return $the_name; }
    then, you get the simplicity of   print the_name(3);
Re: MySQL and Perl - Shorthand
by George_Sherston (Vicar) on Sep 21, 2002 at 12:42 UTC
    dws is quite right. I agree, one gets bored typing in all that stuff. I have a few subroutines in a local module that I use with pretty much anything I write:
    package GS::DBIUtilities; require Exporter; @ISA = ("Exporter"); use DBI; my $module_dbh; sub GetDBH { #-------------------------------------------------------------- # Connect to the amt db and return a db #-------------------------------------------------------------- my $db = shift; $module_dbh = DBI->connect("DBI:mysql:database=$db", "xxxxx", "xxx +xx"); return $module_dbh; } sub DropDBH { #--------------------------------------------------------------- # Disconnect from DB #--------------------------------------------------------------- $module_dbh->disconnect if $module_dbh; } sub InsertMultipleValues { #--------------------------------------------------------------- # Inserts contents of a hashref into the db table specified #--------------------------------------------------------------- my $dbh = shift; my $table = shift; my $Inserts = shift; my @cols = keys %$Inserts; my @vals = @$Inserts{@cols}; my $cols = join ',', @cols; my $places = '?,' x @vals; chop $places; my $sth = $dbh->prepare("INSERT INTO $table ($cols) VALUES ($place +s)") or die $dbh->errstr; $sth->execute(@vals) or die "$dbh->errstr : $table"; } sub ReplaceMultipleValues { #--------------------------------------------------------------- # Replaces contents of a hashref into the db table specified #--------------------------------------------------------------- my $dbh = shift; my $table = shift; my $Replaces = shift; my @cols = keys %$Replaces; my @vals = @$Replaces{@cols}; my $cols = join ',', @cols; my $places = '?,' x @vals; chop $places; my $sth = $dbh->prepare("REPLACE INTO $table ($cols) VALUES ($plac +es)") or die $dbh->errstr; $sth->execute(@vals) or die $dbh->errstr; } sub FetchSingleItem { #--------------------------------------------------------------- # Fetch a single item from a database #--------------------------------------------------------------- my $dbh = shift; my $FetchCol = shift; my $table = shift; my $SearchCol = shift; my $SearchVal = shift; my $sth = $dbh->prepare("SELECT $FetchCol FROM $table WHERE $Searc +hCol = ? LIMIT 1") or die $dbh->errstr; $sth->execute($SearchVal); my @ref = $sth->fetchrow_array; return $ref[0]; } sub InsertAndGetID { #--------------------------------------------------------------- # inserts an entry into a db and gets the auto_increment ID #--------------------------------------------------------------- my $dbh = shift; my $table = shift; my $Inserts = shift; my $IDCol = shift; $Inserts->{$IDCol} = 'NULL'; $dbh->do("LOCK TABLES $table WRITE") or die $dbh->errstr; InsertMultipleValues($dbh,$table,$Inserts); $sth = $dbh->prepare("SELECT LAST_INSERT_ID() FROM $table") or die + $dbh->errstr; $sth->execute or die $dbh->errstr; my @ary = $sth->fetchrow_array or die $dbh->errstr; $dbh->do("UNLOCK TABLES") or die $dbh->errstr; $sth->finish; return $ary[0]; } sub FetchStar { #--------------------------------------------------------------- # Retrieves the whole of each row that matches the submitted # criteria. Returns a hashref if there is only one row, # otherwise a ref to an array of hashes. #--------------------------------------------------------------- my $dbh = shift; my $table = shift; my $SearchCol = shift; my $SearchVal = shift; my $sth = $dbh->prepare("SELECT * FROM $table WHERE $SearchCol = ? +") or die $dbh->errstr; $sth->execute($SearchVal); my @returns; while (my $ref = $sth->fetchrow_hashref) { push @returns, $ref; } if (@returns <= 1) { return $returns[0]; } else { return \@returns; } } @EXPORT = qw/ GetDBH DropDBH InsertMultipleValues ReplaceMultipleValues FetchStar FetchSingleItem InsertAndGetID /; 1;#


    George Sherston
Re: MySQL and Perl - Shorthand
by Chady (Priest) on Sep 21, 2002 at 16:46 UTC

    I use only one sub to run simple SQL:

    sub runSQL { my $sql = shift; if ($sql =~ /^select/i) { my $sth = $dbh->prepare($sql); if (!$sth) { die "Error:" . $dbh->errstr . " :: $sql\n"; } if (!$sth->execute) { die "Error:" . $sth->errstr . " :: $sql\n"; } my @ret; while (my $ref = $sth->fetchrow_hashref()) { push @ret, $ref; } $sth->finish(); return \@ret; } elsif ($sql =~ /^(insert|update|delete)/i) { if (!($dbh->do($sql))) { die "Error:" . $dbh->errstr . " :::: $sql\n"; } } }
    and I get a reference to an array of hashes that holds values of SELECTS..
    He who asks will be a fool for five minutes, but he who doesn't ask will remain a fool for life.

    Chady | http://chady.net/

      Pass @_ to execute() and you can use placeholders. Handy. (I'd also use a prefixed unless, but that's just me.)

Re: MySQL and Perl - Shorthand
by graff (Chancellor) on Sep 23, 2002 at 00:57 UTC
    I also use a method like the one that George Shearston suggested above, though mine is designed as a package module that is a bit more general-purpose -- you can find it here.

    My motivation was the same as yours. The DBI calls are many and varied and sometimes bulky, and the DBI manual is awfully long reading, so I just wanted my own "wrapper" module that would have a handful of always-useful, always compact methods, whose names and args are easy to remember. I seldom/never need more than what the wrapper provides, and hardly ever need to look back at the DBI man page anymore...

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://199692]
Approved by Zaxo
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (6)
As of 2014-09-18 11:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (113 votes), past polls