Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Help with placeholders

    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";
    }

  2. #2
    Join Date
    Feb 2006
    Posts
    56
    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

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •