RE: Insert help...

• Previous message: Scott V \ Nipp: "Insert help..."
• Maybe in reply to: Scott V \ Nipp: "Insert help..."
• Next in thread: Scott V \ Nipp: "RE: Insert help..."
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Tue, 15 Mar 2005 12:33:59 -0700
To: "NIPP, SCOTT V \(SBCSI\)" <sn4265@sbc.com>, <dbi-users@perl.org>

Doesn't MySQL have the concept of a unique or primary key constraint? If it does, why not ...

1. INSERT
2. If insert fails due to PK violation, perform archiving and update the row

This saves the cost of an extra insert (which is what the PK check sort of performs ;)

I also would like to suggest that you put all of your prepare() statements outside of your while { }. This will save the time to parse the statement for each line from the file.

-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.
303.386.1480

-----Original Message-----
From: NIPP, SCOTT V (SBCSI) [mailto:sn4265@sbc.com]
Sent: Tuesday, March 15, 2005 12:25 PM
To: dbi-users@perl.org
Subject: Insert help...

        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

This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.

• Previous message: Scott V \ Nipp: "Insert help..."
• Maybe in reply to: Scott V \ Nipp: "Insert help..."
• Next in thread: Scott V \ Nipp: "RE: Insert help..."
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]