Perl DBI execute with an array instead of list of scalars

Question

I build a query depending on certain conditions - such as type of operation, or the presence of a certain value - by adding fields to a INSERT statement. But then I have to branch for different DBI executes with different lists of parameters, like this:

if ($x) {$extraFields .= ' , X'; $extraValues= ',? '}
if ($y) {$extraFields .= ' , Y, Z'; $extraValues= ',?, ? '}

my $theBasicQuery = "INSERT INTO sometable (A, B, $extraFields) VALUES (?, ? $extraValues)";

$sth = $dbh->prepare($theBasicQuery) or error

# but I dont want to have to do this if for execute
if ($x) {$sth->execute(1,2,99);}
if ($y) {$sth->execute(1,2,88, 77);}

I would prefer to do something like this:

{$sth->execute($anArrayWithDifferentParams);}

Is this possible? Or is there another way to do something similar?

Why not store the field names in an array, and then join the array when you form your query string? — user554546
– user554546, Commented Feb 27, 2013 at 20:58

zb226 · Accepted Answer · 2016-06-21 11:51:16Z

7

To more directly answer the question, the way to execute a query with an array instead of a list of scalars is simply to pass it an array. $sth->execute(@params) will work just fine.

if ($x) {$extraFields .= ' , X'; $extraValues = ',? '; @params = (99); }
if ($y) {$extraFields .= ' , Y, Z'; $extraValues = ',?, ? '; @params = (88, 77); }

my $theBasicQuery = "INSERT INTO sometable (A, B, $extraFields) VALUES (?, ? $extraValues)";

$sth = $dbh->prepare($theBasicQuery) or error

$sth->execute(1,2, @params);

edited Jun 21, 2016 at 11:51

zb226

10.7k6 gold badges57 silver badges91 bronze badges

answered Feb 27, 2013 at 21:32

Dave Sherohman

46.4k14 gold badges67 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Stosberg · Accepted Answer · 2013-02-27 20:58:01Z

4

I strongly recommend using a SQL building tool to help you with this. My favorite is to SQL::Interp, used in combination with DBIx::Simple. Such a tool will manage your bind variables for you, and DBIx::Simple also handles preparing and re-using the statement handles for you automatically. A solution with DBIx::Simple/SQL::Interp would look like this:

$db->iquery("INSERT INTO sometable", {
    a => 1,
    b => 2,
    %extra
});

SQL::Abstract is also popular and have a solution with a similar syntax.

answered Feb 27, 2013 at 20:58

Mark Stosberg

13.5k6 gold badges48 silver badges49 bronze badges

2 Comments

orlybg Over a year ago

Thanks! they do look pretty cool. One thing I forgot to mention is that I'm not allowed / is not possible to install new CPAN modules. Will try it out still.

Mark Stosberg Over a year ago

Strange. You mean, the administrator won't allow new global installation of Perl modules, or that you aren't allowed to add them locally, either? What if you copy a file from CPAN, paste it into your tree, and name it My::SQL::Interp?

pilcrow · Accepted Answer · 2013-02-27 21:27:15Z

One approach:

my %insert = ( A => 1, B=> 2 );

if ($condition_x) {
  $insert{X} = 99;
}

if ($condition_y) {
  $insert{Y} = 88;
  $insert{Z} = 77;
}

# 1. sprintf isn't very perlish, but I find it clearer here
# 2. quote_identifier():  you won't ever need this until you need it very badly
#
my $query = sprintf('INSERT INTO tbl (%s) VALUES (%s)',
                       join ', ' => map { $dbh->quote_identifier($_) } keys %insert,
                       join ', ' => ('?') x keys %insert);

my $sth = $dbh->prepare($query);  # Perhaps prepare_cached, instead?
$sth->execute(values %insert);

Caveat: !DIY

If at all possible, don't do it yourself! Use a module, as suggested elsewhere. Others have already solved this problem, more cogently and reliably than the above.

daotoad · Accepted Answer · 2013-02-27 23:46:13Z

You might find my DBIx::PreQL library helpful for this kind of task. It provides a way to mark up an SQL query for processing based on available data.

DBIx::PreQL uses simple prefixes and named placeholders to label which lines of a query should be included.

For example, * means 'always include' and & means 'include if all data fields for the line are present.

Your query would be marked up as follows:

*  INSERT INTO sometable (
*     A
*    ,B
&    ,C !C!  
&    ,D  !D!
)
*VALUES (
*   ?A?
*  ,?B?
&  ,?C?
&  ,?D?
* )

And you'd pass the query and a data hash like { A => '123', B => 'foo', D => 'bar' } to the query processor, which would return the query:

INSERT INTO sometable (
     A
    ,B
    ,D
)
VALUES (
   ?
  ,?
  ,?
 )

And the parameter list (123, 'foo', 'bar').

Collectives™ on Stack Overflow

Perl DBI execute with an array instead of list of scalars

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related