RE: Cannot Insert into Oracle
From: Ron Reidy (Ron.Reidy_at_arraybiopharma.com)
Date: 02/12/05
- Next message: Mladen Gogala: "Re: DBI error checking not working"
- Previous message: Jared Still: "Re: problems building DBD::Oracle 1.16 - help - please"
- Maybe in reply to: Nelson Yik: "Cannot Insert into Oracle"
- Next in thread: Igor Korolev: "RE: Cannot Insert into Oracle"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Sat, 12 Feb 2005 03:48:28 -0700 To: <nelson.yik@bell.ca>, <dbi-users@perl.org>
Nelson,
It looks like a logic issue to me. See below (look for lines starting with [rr]) ...
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-----Original Message-----
From: nelson.yik@bell.ca [mailto:nelson.yik@bell.ca]
Sent: Fri 2/11/2005 9:45 PM
To: dbi-users@perl.org
Cc:
Subject: Cannot Insert into Oracle
Hello everyone,
I'm trying to insert into an oracle db. There's nothing wrong with the
connection it seems, I can retrieve records from the db just fine.
However, I can't seem to commit.
Here's my code:
Note: the line with the regex in it is just to parse the text file so
that I can have the correct values in each variable. The purpose of the
perl script is to parse a text file, then store each line as a record in
an oracle database, as well as create an HTML page to list the data.
#!/usr/bin/perl
use DBI;
my $db = DBI->connect('dbi:Oracle:NelOraDB', 'system', 'bell',
{RaiseError => 1, AutoCommit => 0}) || die "Database connection not
made: $DBI::errstr";
use Fcntl;
open (INFILE, "/home/nyik/snmp_poller_data.txt") or die"Couldn't open
snmp_poller_data.txt";
open (FD, "+>/usr/local/apache2/htdocs/snmp.html") or die"Couldn't open
snmp.html";
# read in each line from the input and print to output
print FD "<table border=1>";
print FD "<tr><td><b>MIB</b></td><td><b>Var</b></td><td><b>Var
Type</b></td><td><b>Var Value</b></td></tr>";
while(<INFILE>)
{
$line = $_ ;
# print FD "<tr><td>$line</td></tr>";
if ($line =~
/((\w*|-|\d*)*)MIB::(\w*(\.(\d*))*)\s*=\s*((\w*|-|\d*)*):*\s*(.*)/)
{
$mib = $1."MIB";
$var = $3;
$vartype = $6;
$varvalue = $8;
} else {
print FD "<tr><td colspan=4>$line</td></tr>";
$mib = "<b>WRONG!!!</b>";
$var = "<b>WRONG!!!</b>";
$vartype = "<b>WRONG!!!</b>";
$varvalue = "<b>WRONG!!!</b>";
}
$sql = qq{ INSERT INTO SNMP_DATA_TABLE VALUES ( ?, ?, ?, ? ) };
$sth = $db->prepare( $sql );
[rr] You should prepare your statement outside the file read loop.
eval {
$sth->bind_param( 1, $mib, SQL_VARCHAR2);
$sth->bind_param( 2, $var, SQL_VARCHAR2);
$sth->bind_param( 3, $vartype, SQL_VARCHAR2);
$sth->bind_param( 4, $varvalue, SQL_VARCHAR2);
$sth->execute();
$dbh->commit();
};
[rr] Shouldn't this eval {} be part of the if block where you have found data? It will get executed for each record read from the file, including the case where you print "WRONG!!!" several times.
#if ( $@ ) {
# warn "Database error: $DBI::errstr\n";
# $db->rollback();
#}
print FD "<tr>";
print FD "<td><font face=arial size=1>$mib </font></td>";
print FD "<td><font face=arial size=1>$var </font></td>";
print FD "<td><font face=arial size=1>$vartype </font></td>";
print FD "<td><font face=arial
size=1>$varvalue </font></td>";
print FD "</tr>";
}
close(INFILE);
close(FD);
$sql = qq{ SELECT * FROM SNMP_DATA_TABLE };
$sth = $db->prepare( $sql );
$sth->execute();
$sth->bind_columns(undef,\$m, \$v, \$type, \$value);
while( $sth->fetch() ) {
print "$m, $v, $type, $value\n";
}
$sth->finish();
$db->disconnect();
Thanks so much!
Nelson Yik
Business Systems Analyst Co-op
New Technology Integration
Bell Canada
76 Adelaide St. W., Floor 15
Toronto, ON
* nelson.yik@bell.ca <mailto:+nelson.yik@bell.ca>
* (416) 353-9159
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: Mladen Gogala: "Re: DBI error checking not working"
- Previous message: Jared Still: "Re: problems building DBD::Oracle 1.16 - help - please"
- Maybe in reply to: Nelson Yik: "Cannot Insert into Oracle"
- Next in thread: Igor Korolev: "RE: Cannot Insert into Oracle"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
