Insert help...

• Previous message: google_at_jbnoel.com: "Re: DBD-Oracle and Oracle Instant Client recipe"
• Next in thread: Ron Reidy: "RE: Insert help..."
• Maybe reply: Ron Reidy: "RE: Insert help..."
• Maybe reply: Scott V \ Nipp: "RE: Insert help..."
• Reply: Mark Addison: "Re: Insert help..."
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Tue, 15 Mar 2005 13:24:49 -0600
To: <dbi-users@perl.org>

        I am working on a Perl MySQL problem. Basically, the script
parses a text file and then checks a database table to see if it needs
to be updated. If the entry is NOT in the table, it inserts the entry.
If the entry does exist, it compares the data. Assuming these data is
identical nothing happens, if however, the data is different then the
entry is archived off to a history table and the new data is inserted.
Here is basically what I have...

while (my $file = <CSV>) {
  my $line = $csv->parse($file);
  my @data = $csv->fields($file);
  my $test = $dbh->prepare(qq{SELECT * FROM AllMid_Data WHERE
CPU_Hostname = ?});
  $test->execute ($data[14]);
  my $rows = $test->rows;
  if ($rows == 0) {
    print "Entry not found. Inserting into database. \n";
    my $sth = $dbh->prepare("INSERT INTO AllMid_Data VALUES(?".(",?" x
21).")") # This is the INSERT if the data is new.
      or print "Error with INSERT _prepare_ $DBI::errstr\n";
    $sth->bind_param(1, undef);
    $sth->bind_param($_+2, $data[$_]) foreach 0..20;
    $sth->execute() or print "Data insert failed.";
  } else {
    # print "Found entry and checking if updating is needed. \n";
    my @old = $test->fetchrow_array ();
    foreach $n (0..20) {
      chomp($file_val = $data[$n]);
      $file_val =~ s/\s*$//;
      chomp($db_val = $old[$n+1]);
      # print "Comparing $file_val to $db_val. \n"; Testing line
      if ($file_val eq $db_val) {
        $update = 1;
      } else {
        $update = 0;
        print "Comparing $file_val to $db_val. \n";
        last;
      }
    }
    if ($update == 0) {
      print "$data[14] requires updating in database. Updating entry
now.\n";
      # Insert existing data into AllMid_Hist.
      shift(@old);
      $dbh->do(qq{
              INSERT INTO AllMid_Hist VALUES (?".(",?" x 22)."))}, #
This is where I run into issues.
              undef,@old,NOW()); # I am not sure how to
structure this syntax and not having luck finding the answer.
     }
  }
}

        Thanks in advance for any help.

Scott Nipp
Phone: (214) 858-1289
E-mail: sn4265@sbc.com
Web: http:\\ldsa.sbcld.sbc.com

• Previous message: google_at_jbnoel.com: "Re: DBD-Oracle and Oracle Instant Client recipe"
• Next in thread: Ron Reidy: "RE: Insert help..."
• Maybe reply: Ron Reidy: "RE: Insert help..."
• Maybe reply: Scott V \ Nipp: "RE: Insert help..."
• Reply: Mark Addison: "Re: Insert help..."
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]