Cannot Insert into Oracle

From: Nelson Yik (nelson.yik_at_bell.ca)
Date: 02/12/05

  • Next message: Jared Still: "Re: problems building DBD::Oracle 1.16 - help - please" 
    Date: Fri, 11 Feb 2005 23:45:47 -0500
To: dbi-users@perl.org

    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

     

  • Next message: Jared Still: "Re: problems building DBD::Oracle 1.16 - help - please"

    Relevant Pages

    • Re: Statspack Help Please
      ... High logical reads probably points to badly untuned SQL ... >Module: JDBC Thin Client ... >Notice that the Parse & Executions are mostly the same. ... Sybrand Bakker, Senior Oracle DBA ...
      (comp.databases.oracle.server)
    • Re: Statspack Help Please
      ... > Shared Pool Size: 112M Log Buffer: 512K ... > Notice that the Parse & Executions are mostly the same. ... Cost Based Oracle: Fundamentals ...
      (comp.databases.oracle.server)
    • Unnecessary calls to DBMS_DESCRIBE Oracle Stored procedure
      ... I have utilized Microsoft Activex Data Object 2.6 library to call some Oracle ... It actually happens when we are calling the stored procedures that are going ... The problem is supposed to be fixed in the Oracle 9.2 provider... ... The parse stat count is being incremented twice, ...
      (microsoft.public.data.ado)
    • Safe way to escape form variables to insert in sql (to oracle)
      ... I'm connecting to a oracle db via ODBC (can't use native oracle functions) and i need to parse the input from a form to insert in a sql query. ... I would like to know if using placeholders is enough or should i do something else to have safe queries without strange chars. ...
      (alt.php)