How can I insert into PostgreSQL using Perl DBI and placeholders

Questions

I am trying to insert a row into a pSQL table, specifying both keys and values as placeholders:

my @keys = keys %db_entr;                                                            
my @vals = values %db_entr;

my @items = (@keys, @values);

my $dbh = DBI->connect("DBI:Pg:dbname=testdb;host=localhost", "username",  password , { RaiseError  => 1});                                                                   
my $sth = $dbh->prepare("INSERT INTO grid ( ?, ?, ? ) values ( ?, ?, ? )");
my $rows = $sth->execute(@items);                                                    
print "$rows effected
";

However, whatever I do, this gives me an error:

DBD::Pg::st execute failed: ERROR:  syntax error at or near "$1"
LINE 1: INSERT INTO grid ( $1, $2, ...
                           ^ at ./file.pl line 262, <STDIN> line 11.

Does anyone have an idea of what I might be doing wrong?

Answers

You can t use placeholders for column names in the prepare call. The best you can do is to either interpolate the variable names into the SQL string or use sprintf to do the equivalent.

This is an example, but you may need to do something different. Note that it modifies the @items array, and you need to call prepare again each time the contents of @items may have changed.

my $sth = $dbh->prepare(
    sprintf "INSERT INTO grid ( %s, %s, %s ) values ( ?, ?, ? )",
    splice @items, 0, 3
);
my $rows = $sth->execute(@items);
print "$rows affected
";

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/14212567/how-can-i-insert-into-postgresql-using-perl-dbi-and-placeholders

Related