RE: Insert help...
From: Scott V \ Nipp (sn4265_at_sbc.com)
Date: 03/15/05
- Next message: PerlDiscuss - Perl Newsgroups and mailing lists: "connecting to informix via network"
- Previous message: Ron Reidy: "RE: Insert help..."
- Maybe in reply to: Scott V \ Nipp: "Insert help..."
- Next in thread: Mark Addison: "Re: Insert help..."
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Tue, 15 Mar 2005 16:25:43 -0600 To: "Reidy, Ron" <Ron.Reidy@arraybiopharma.com>, <dbi-users@perl.org>
OK... I have been able to solve this problem, and it was no big
deal. However, the problem I am currently facing is really hosing me
up... Basically, a "NULL" field is getting converted to a 0 in the
database. This causes a comparison of the same file and the database to
yield a lot of differences. Please help. I think this has got to be
related to the INSERT. The table column is setup as 'smallint' allowing
NULLS with the default value set to NULL. This column however is
getting a '0' if the input file is empty for that field.
Here is the pertinent code...
<... Snipped ...>
my $sth = $dbh->prepare("INSERT INTO AllMid_Data VALUES(?".(",?" x
21).")")
or print "Error with INSERT _prepare_ $DBI::errstr\n";
<... Snipped ...>
$sth->bind_param(1, undef);
$sth->bind_param($_+2, $data[$_]) foreach 0..20;
$sth->execute() or print "Data insert failed.";
<... Remainder snipped ...>
Please help as I am almost there. Thanks in advance.
Scott Nipp
Phone: (214) 858-1289
E-mail: sn4265@sbc.com
Web: http:\\ldsa.sbcld.sbc.com
-----Original Message-----
From: Reidy, Ron [mailto:Ron.Reidy@arraybiopharma.com]
Sent: Tuesday, March 15, 2005 1:34 PM
To: NIPP, SCOTT V (SBCSI); dbi-users@perl.org
Subject: RE: Insert help...
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.
- Next message: PerlDiscuss - Perl Newsgroups and mailing lists: "connecting to informix via network"
- Previous message: Ron Reidy: "RE: Insert help..."
- Maybe in reply to: Scott V \ Nipp: "Insert help..."
- Next in thread: Mark Addison: "Re: Insert help..."
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
