CODEWITHSUNDEEP
sqlperlinsertdbi
Franco
Franco

Reputation: 965

PERL DBI - Inserting While Reading File Via Regular EXP

I Have a very god Knowledge of SQL (Oracle) but just lately I am trying to make it co-operate with PERL;

reason is queit obvious i am reading log files of thousand of rows and cleaning them with a REGXP;

While cleaning them I want to Insert into a Table (already existing in my Server) - but i am struggling with syntax and quotes;

example:

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:Oracle:DB1", "test", "test")
or die "Couldnt connect to database: $DBI::errstr";

my $file = 'C:\Users\log.txt';
open my $fh, "<", $file or die "Could not open $file: $!";

......
......
.....
....
......

##THIS PRINTS ABSOLUTELY FINE

 printf('$datetime_event=%s; $logonid=%s; $clientid=%s; $cliet_user_id=%s; $ip=%s; $duration=%s; $class_path=%s', @fields);
    print "\n";

## THIS IS THE INSERT THAT IS NOT WORKING

    my $sth = $dbh->prepare('INSERT INTO REQUEST (REQUEST_ID,DATETIME_EVENT, LOGONID, CLIENTID,USERID, DURATION,IP,METHOD)
    Values (REQUEST_ID_SEQ.NEXTVAL,TO_DATE(SUBSTR($datetime_event ,0,19), "YYYY-MM-DD HH24:MI:SS"),$logonid,$clientid,$cliet_user_id,$duration, $ip,$class_path');
    #or die "Couldn't execute statement: " . $sth->errstr;

    # Execute the SQL statement; don't print it yet
    $sth->execute or warn 'Execute failed: ' . $dbh->errstr;

    # This "loop" prints all the rows (actually just one, in this case)
    while (my @row = $sth->fetchrow_array) {
    print "@row\n";
}



}


# gracefully disconnect from the database
$dbh->disconnect();

errors:

DBD::Oracle::st execute failed: ORA-00911: invalid character (DBD ERROR: error possibly near <*> indicator at char 141 in 'INSERT INTO REQUEST (REQUEST_ID,DATETIME_EVENT, LOGONID, CLIENTID,USERID, DURATION,IP,METHOD)
    Values (REQUEST_ID_SEQ.NEXTVAL,TO_DATE(SUBSTR(<*>$datetime_event ,0,19), "YYYY-MM-DD HH24:MI:SS"),$logonid,$clientid,$cliet_user_id,$duration, $ip,$class_path') [for Statement "INSERT INTO REQUEST (REQUEST_ID,DATETIME_EVENT, LOGONID, CLIENTID,USERID, DURATION,IP,METHOD)
    Values (REQUEST_ID_SEQ.NEXTVAL,TO_DATE(SUBSTR($datetime_event ,0,19), "YYYY-MM-DD HH24:MI:SS"),$logonid,$clientid,$cliet_user_id,$duration, $ip,$class_path"] at D:/PERL/Log/CleanFileV1.pl line 42, <$fh> line 1.
Execute failed: ORA-00911: invalid character (DBD ERROR: error possibly near <*> indicator at char 141 in 'INSERT INTO REQUEST (REQUEST_ID,DATETIME_EVENT, LOGONID, CLIENTID,USERID, DURATION,IP,METHOD)
    Values (REQUEST_ID_SEQ.NEXTVAL,TO_DATE(SUBSTR(<*>$datetime_event ,0,19), "YYYY-MM-DD HH24:MI:SS"),$logonid,$clientid,$cliet_user_id,$duration, $ip,$class_path') at D:/PERL/Log/CleanFileV1.pl line 42, <$fh> line 1.

Updated

my ( $datetime_event, $logonid, $clientid, $cliet_user_id,$duration, $ip, $class_path ) = @fields;


    # Execute the SQL statement; don't print it yet
    my $sth = $dbh->prepare('INSERT INTO REQUEST (REQUEST_ID, DATETIME_EVENT, LOGONID, CLIENTID, USERID, DURATION, IP, METHOD)
    VALUES (REQUEST_ID_SEQ.NEXTVAL, TO_DATE(SUBSTR(?, 0, 19), "YYYY-MM-DD HH24:MI:SS"), ?, ?, ?, ?, ?, ?)')
    or die "Couldn't execute statement: " . $sth->errstr;

    $sth->execute( $datetime_event, $logonid, $clientid, $cliet_user_id,$duration, $ip, $class_path )
    or warn 'Execute failed: ' . $dbh->errstr;

}

# gracefully disconnect from the database
$dbh->disconnect();

NEW ERRORS:

DBD::Oracle::st execute failed: ORA-00984: column not allowed here (DBD ERROR: error possibly near <*> indicator at char 159 in 'INSERT INTO REQUEST (REQUEST_ID, DATETIME_EVENT, LOGONID, CLIENTID, USERID, DURATION, IP, METHOD)
    VALUES (REQUEST_ID_SEQ.NEXTVAL, TO_DATE(SUBSTR(:p1, 0, 19), <*>"YYYY-MM-DD HH24:MI:SS"), :p2, :p3, :p4, :p5, :p6, :p7)') [for Statement "INSERT INTO REQUEST (REQUEST_ID, DATETIME_EVENT, LOGONID, CLIENTID, USERID, DURATION, IP, METHOD)
    VALUES (REQUEST_ID_SEQ.NEXTVAL, TO_DATE(SUBSTR(?, 0, 19), "YYYY-MM-DD HH24:MI:SS"), ?, ?, ?, ?, ?, ?)" with ParamValues: :p1='2014-09-21 00:09:22,718', :p2='admin12', :p3='895', :p4='258', :p5='111.1.1.1', :p6='0.023', :p7='Calculate.LoanExmple'] at D:/PERL/Log/CleanFileV1.pl line 43, <$fh> line 6.
Execute failed: ORA-00984: column not allowed here (DBD ERROR: error possibly near <*> indicator at char 159 in 'INSERT INTO REQUEST (REQUEST_ID, DATETIME_EVENT, LOGONID, CLIENTID, USERID, DURATION, IP, METHOD)
    VALUES (REQUEST_ID_SEQ.NEXTVAL, TO_DATE(SUBSTR(:p1, 0, 19), <*>"YYYY-MM-DD HH24:MI:SS"), :p2, :p3, :p4, :p5, :p6, :p7)') at D:/PERL/Log/CleanFileV1.pl line 43, <$fh> line 6.

Upvotes: 0

Views: 162

Answers (1)

Jim Davis
Jim Davis

Reputation: 5290

Your SQL statement is surrounded by single quotes, so none of your $variables will be interpolated.

You'd do better to replace them with ?s and then pass the variables to execute to let the database driver do the work:

my $sth = $dbh->prepare(
    'INSERT INTO REQUEST (
        REQUEST_ID, DATETIME_EVENT, LOGONID, CLIENTID, USERID, DURATION, IP, METHOD
    ) VALUES (
        REQUEST_ID_SEQ.NEXTVAL, TO_DATE(SUBSTR(?, 0, 19), "YYYY-MM-DD HH24:MI:SS"), ?, ?, ?, ?, ?, ?
    )'
) or die "Couldn't execute statement: " . $sth->errstr;

# Execute the SQL statement; don't print it yet
$sth->execute(
    $datetime_event, $logonid, $clientid, $cliet_user_id, $duration, $ip, $class_path
) or warn 'Execute failed: ' . $dbh->errstr;

Upvotes: 1

Related Questions