RE: Cannot Insert into Oracle

• Previous message: Mladen Gogala: "Re: DBI error checking not working"
• Maybe in reply to: Nelson Yik: "Cannot Insert into Oracle"
• Next in thread: Nelson Yik: "RE: Cannot Insert into Oracle"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Sat, 12 Feb 2005 16:19:44 -0600
To: "Jared Still" <jkstill@cybcon.com>, <nelson.yik@bell.ca>

Also "if $@" part needs to be uncommented. If it was, and the print included $@ variable, it would have been obvious that $dbh is undefined.
 
"use strict" and compilation with -w option would also help.

________________________________

From: Jared Still [mailto:jkstill@cybcon.com]
Sent: Fri 2/11/2005 11:54 PM
To: nelson.yik@bell.ca
Cc: DBI List
Subject: Re: Cannot Insert into Oracle

Hi Nelson,

Is there an error message or messages the result from this script?

For all we know, your failure to commit is just a personal problem. :)

Also, you are committing every row. Please stop doing that,
your database will love you for it.

Just issue one commit at the end of the loop, or if this file is
*really* big (millions of records) commit every 10k or 100k or so.

Jared

On Fri, 2005-02-11 at 20:45, nelson.yik@bell.ca wrote:
> 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 );
>
>
>
> 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();
>
> };
>
>
>
> #if ( $@ ) {
>
> # warn "Database error: $DBI::errstr\n";
>
> # $db->rollback();
>
> #}
>
>
>
> print FD "<tr>";
>
> print FD "<td><font face=arial size=1>$mib&nbsp</font></td>";
>
> print FD "<td><font face=arial size=1>$var&nbsp</font></td>";
>
> print FD "<td><font face=arial size=1>$vartype&nbsp</font></td>";
>
> print FD "<td><font face=arial
> size=1>$varvalue&nbsp</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
>
>
>

• Previous message: Mladen Gogala: "Re: DBI error checking not working"
• Maybe in reply to: Nelson Yik: "Cannot Insert into Oracle"
• Next in thread: Nelson Yik: "RE: Cannot Insert into Oracle"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]