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

    Unanswered: Newbie question - update in loop

    I am very new to Perl and I need some help. I am trying to write some code that will accept one or more location numbers from the command line, and then will update a row in an Oracle database. I have code that will connect to the DB, but I'm not sure how to loop through and do the update until I run out of parameters on the command line. Can someone give me a hand?

    Code:
    #!/usr/bin/perl -w
    
    use DBI;
    use strict;
    
    my $instance = 'mdip1';
    my $data_source = "dbi:Oracle:$instance";
    my $user = "abc";
    my $password ="abc";
    my $dbh = DBI->connect($data_source, $user, $password, {
    RaiseError => 1});
    
    my $sth = $dbh->prepare( q{ UPDATE stsc.sku
    		SET p_abc = 'D', 
        		p_abcunitstotqty = 0
    		WHERE loc = ? 
    		AND p_manualparameters = 0
    		AND p_abc in ('A','B','C') });
    						 
    $dbh->disconnect;

  2. #2
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    if you want to use the locs as parameters:
    Code:
    #!/usr/bin/perl -w
    
    use DBI;
    use strict;
    
    my $instance = 'mdip1';
    my $data_source = "dbi:Oracle:$instance";
    my $user = "abc";
    my $password ="abc";
    my $dbh = DBI->connect($data_source, $user, $password, {
    RaiseError => 1}) or die $DBI::errstr;
    
    my $sth = $dbh->prepare( q{ UPDATE stsc.sku
    		SET p_abc = 'D', 
        		p_abcunitstotqty = 0
    		WHERE loc = ? 
    		AND p_manualparameters = 0
    		AND p_abc in ('A','B','C') }) or die $dbh->errstr();
    
    for my $loc(@ARGV){
      $sth->execute($loc) or die $dbh->errstr();
    }
    						 
    $dbh->disconnect;
    Usage: skript.pl <loc1> <loc2> <loc3> ...


    And if you want to read from a file:
    Code:
    #!/usr/bin/perl -w
    
    use DBI;
    use strict;
    
    my $instance = 'mdip1';
    my $data_source = "dbi:Oracle:$instance";
    my $user = "abc";
    my $password ="abc";
    my $dbh = DBI->connect($data_source, $user, $password, {
    RaiseError => 1}) or die $DBI::errstr;
    
    my $sth = $dbh->prepare( q{ UPDATE stsc.sku
    		SET p_abc = 'D', 
        		p_abcunitstotqty = 0
    		WHERE loc = ? 
    		AND p_manualparameters = 0
    		AND p_abc in ('A','B','C') }) or die $dbh->errstr();
    
    open(my $fh,"<",$ARGV[0]) or die $!;
    while(my $line = <$fh>){
      chomp $line;
      $sth->execute($line) or die $dbh->errstr();
    }
    close $fh;
    						 
    $dbh->disconnect;
    usage: skript.pl <filename>
    board.perl-community.de - The German Perl-Community

Posting Permissions

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