Prepared SQL statement in Perl

Question

I'm very new to Perl, I'm trying to overcome the initial learning curve.

I have the following SQL statement:

SELECT 
    node_id 
FROM 
    pagenodes
INNER JOIN 
    pageproducts 
ON 
    pagenodes.node_id = pageproducts.nodeid
INNER JOIN 
    products 
ON 
    pageproducts.prodid = products.prodid 
WHERE 
    products.prodid  = ?
LIMIT 1

As you can see this has LIMIT 1 set in the query, I'm trying to perform this query from a Perl script, and set the result to a variable.

So far I have the following:

my $sql =
    'SELECT 
        node_id 
    FROM 
        pagenodes
    INNER JOIN 
        pageproducts 
    ON 
        pagenodes.node_id = pageproducts.nodeid
    INNER JOIN 
        products 
    ON 
        pageproducts.prodid = products.prodid 
    WHERE 
        products.prodid  = ?
    LIMIT 1';
    
    my $sth = $dbh->prepare($sql);
    my $sth->execute();

My question is, how can I pass in $prod_id as the WHERE paramater, and how can I set the result to a variable. Thanks

Answer 1

You're pretty far already. It's great that you already have the placeholders (?). All you need to do is pass the parameter as an argument to execute. It will fill them into the placeholders automatically, in the order they appear in your query.

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dsn goes here', 'user', 'password') or die $DBI::errstr;

# stuff happens ...

my $prod_id = 1337; # or whatever

my $sql =
    'SELECT 
        node_id 
    FROM 
        pagenodes
    INNER JOIN 
        pageproducts 
    ON 
        pagenodes.node_id = pageproducts.nodeid
    INNER JOIN 
        products 
    ON 
        pageproducts.prodid = products.prodid 
    WHERE 
        products.prodid  = ?
    LIMIT 1';

my $sth = $dbh->prepare($sql);
$sth->execute($prod_id);     # no `my` here!

The LIMIT in this case doesn't matter. It will also work in a loop.

Note that you had a my in front of the execute line. That is wrong syntax. my is used to declare variables, but in that line you only call a method on the already existing statement handle object. There is a return value for the execute method, but it is usually not assigned to a variable. If the return value is true, the execute was successful. If it's false, something went wrong. You can also use or to check for errors.

$sth->execute or die $dbh->errstr;

Now to get your single value from the database, you can use fetchrow_array in list context.

( my $node_id ) = $sth->fetchrow_array;

This will give you the single node_id, once.

---

If you intend to only get one value and only call this once, you can also save some lines of code and use selectrow_array instead.

( my $node_id ) = $dbh->selectrow_array('SELECT node_id FROM foo WHERE prodid=? LIMIT 1', undef, $prod_id);

Again, the list context is important, because fetchrow_array and selectrow_array return lists, not array references. Without the () around the left hand side, it would assign the number of results (because of scalar context).

If, however, you want to do all this in a loop for a bunch of values, it looks a bit different. In that case, do your prepare outside of the loop and just execute and fetchrow_array (or any of the other fetchrow_* methods) inside of the loop. That will make it considerably faster.

my $sth = $dbh->prepare($sql);
foreach my $prod_id ( @prod_ids ) {
  $sth->execute($prod_id);
  ( my $node_id ) = $sth->fetchrow_array;
  print "$node_id\n";
}