Help with placeholders

ssmith001 · #1 (**permalink**) 05-31-07, 17:07

I am trying to write some code that will create insert statements on the fly so I can insert records into an Oracle table.

For each element of the @dlrloc_array I need to create the $insert_query shown below. I've played around with the placeholders but I cannot seem to figure out how to string it all together. For the sake of this example, I just want to print out what the SQL statement would be so I can verify it gets build correctly.

Code:

#!/usr/bin/perl


my @dlrloc_array = qw(A100 A200 A300 A400 A500 A600 A700 A800);
my $dlrgrp_name = 'RUSH';
my $dlrdiv = 'PB';
my $dlrcountry = 'USA';
my $runDOW = 'WEDNESDAY';
my $daysofsupply = 45;
my $ordertype = 'SEASONAL';
my $plannercode = 'M63';

my $table = 'pac.promo_rpt';
my @fields = qw( dlrcode dlrgroup division country planner
                 ordertype daysofsupply run_dow sof_or_excel abc_codes );

my $fieldlist = join(', ', @fields);
my $field_placeholders = join(', ', ('?') x @fields);
my $insert_query = "INSERT into $table ($fieldlist) values ($field_placeholders)";

for my $dlrcode ( @dlrloc_array ) {
#    $sth->execute( $a_val, $ordtype );
  print "$insert_query $dlrcode\n";
}

KevinADC · #2 (**permalink**) 05-31-07, 17:55

whats the problem? Your code outputs:

INSERT into pac.promo_rpt (dlrcode, dlrgroup, division, country, planner, ordertype, daysofsupply, run_dow, sof_or_excel, abc_codes) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) A100
INSERT into pac.promo_rpt (dlrcode, dlrgroup, division, country, planner, ordertype, daysofsupply, run_dow, sof_or_excel, abc_codes) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) A200
INSERT into pac.promo_rpt (dlrcode, dlrgroup, division, country, planner, ordertype, daysofsupply, run_dow, sof_or_excel, abc_codes) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) A300
INSERT into pac.promo_rpt (dlrcode, dlrgroup, division, country, planner, ordertype, daysofsupply, run_dow, sof_or_excel, abc_codes) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) A400
INSERT into pac.promo_rpt (dlrcode, dlrgroup, division, country, planner, ordertype, daysofsupply, run_dow, sof_or_excel, abc_codes) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) A500
INSERT into pac.promo_rpt (dlrcode, dlrgroup, division, country, planner, ordertype, daysofsupply, run_dow, sof_or_excel, abc_codes) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) A600
INSERT into pac.promo_rpt (dlrcode, dlrgroup, division, country, planner, ordertype, daysofsupply, run_dow, sof_or_excel, abc_codes) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) A700
INSERT into pac.promo_rpt (dlrcode, dlrgroup, division, country, planner, ordertype, daysofsupply, run_dow, sof_or_excel, abc_codes) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) A800

sco08y · #3 (**permalink**) 06-17-07, 15:43

All that's missing is to execute the query. From what I gather, the statement is not changing, just the dlrcode value being bound.

See <a href="http://search.cpan.org/~timb/DBI-1.56/DBI.pm#execute">perldoc DBI</a>.

Code:

for my $dlrcode ( @dlrloc_array ) {
  $sth->execute($dlrcode, $dlrgrp_name, $dlrdiv, $dlrcountry, 
    $plannercode, $ordertype, $daysofsupply, $runDOW, 
    $sof_or_excel, $abc_codes);
}

I made up the last two because you didn't have them in your code.

Now... you're not actually creating an INSERT statement on the fly here. Really dynamic SQL might look like this:

Code:

open my $csvfile, $ARGV[0];
my @fields = split /,/, <$csvfile>;
chomp $fields[-1];
$dbi->do("CREATE TABLE $ARGV[0] (".join(", ", 
  map("$_ VARCHAR(255)", @fields)).")");
while(<$csvfile>) {
  chomp;
  $dbi->do("INSERT INTO $ARGV[0] (".join(", ", @fields).") VALUES ("
    . join(", ", ("?") x @fields).")", undef, split /,/, $_);
}

Not that I'm actually suggesting this is a good way to get a CSV file into a database, but that's all you need.

Notice the bold undef? Many DBI methods take an anonymous hash (you'll see \%attr in the parameters list) to pass special attributes. You'll rarely use these unless your driver needs them (in which case it's in the docs) and if you don't need them you can pass undef. Don't forget it or the method will either throw an error or you'll be wondering why it's not passing the bound variables as it should be. Don't put one in where it's not needed (again, refer to the docs) or, again, your bound variables will be off.

Internet Services

Web Development

Digital Marketing

Consumer Tech