CODEWITHSUNDEEP
perldbi
Chazg76
Chazg76

Reputation: 649

perl dbi prepare with variable table column name

I have used the following code many times before when inserting values into database tables using perl

my $SRV='xxx';
my $DB='dbname';        
my $db = DBI->connect("dbi:Oracle:$SRV/$DB", "user", "pword" )  or die "impossible de se connecter à $SRV / $DB";

my $insert_T1 = "INSERT INTO tablename (ColA, ColB) VALUES ( ?, ?) " ;
my $insert_T1_sth = $db->prepare($insert_T1) ;

Later in the code I can then call the following to do the insertion

$insert_T1_sth->execute('val1','val2');
$insert_T1_sth->execute('val3','val4');

So basically when I use the prepare function above I can replace the entries I want to insert by question marks and then put the values of these question marks in the execute statements later on.

So to my question: Can I use question marks in place of column names in the prepare statement? I'm thinking no because when I try the following I get a runtime error on the line where the execute statement(s) are.

my $SRV='xxx';
my $DB='dbname';        
my $db = DBI->connect("dbi:Oracle:$SRV/$DB", "user", "pword" )  or die "impossible de se connecter à $SRV / $DB";
$db->{AutoCommit} = 0 ;

my $insert_T1 = "INSERT INTO tablename (ColA, ?) VALUES ( ?, ?) " ;
my $insert_T1_sth = $db->prepare($insert_T1) ;

Then later, as before, use

$insert_T1_sth->execute('colname1','val1','val2');
$insert_T1_sth->execute('colname2','val3','val4');

Upvotes: 1

Views: 700

Answers (1)

Dada
Dada

Reputation: 6661

You can't use dynamic column names with prepare like you are trying to do.

Your column names shouldn't be known to the user, and therefore don't really need to be part of the parameters, since they are not sensitive (and don't need to be protected against SQL injection). Preparing is still useful for performances though.

What I'd suggest is to do a prepare for each of you column name, and store those in a hash:

my @col_names = qw(colname1 colname2);
my %inserts;
for my $col (@col_names) {
    $inserts{$col} = $db->prepare("INSERT INTO tablename (ColA, $col) VALUES (?, ?)");
}
...
$inserts{colname1}->execute('val1', 'val2');

Upvotes: 4

Related Questions