Re: Cannot Insert into Oracle
From: Jared Still (jkstill_at_cybcon.com)
Date: 02/12/05
- Next message: Amonotod: "Re: finding and inserting"
- Previous message: Robert: "Re: finding and inserting"
- In reply to: Nelson Yik: "Cannot Insert into Oracle"
- Next in thread: Ron Reidy: "RE: Cannot Insert into Oracle"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
To: nelson.yik@bell.ca Date: Fri, 11 Feb 2005 21:54:38 -0800
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 </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
>
>
>
- Next message: Amonotod: "Re: finding and inserting"
- Previous message: Robert: "Re: finding and inserting"
- In reply to: Nelson Yik: "Cannot Insert into Oracle"
- Next in thread: Ron Reidy: "RE: Cannot Insert into Oracle"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
