how to remove the special characters which is getting appended while reading sql file from perl

Question

I'm trying to execute queries from sql file using perl, but when I tried, I see some special char is getting appended in between or at the end of the query. I'm not sure how to resolve this.

my $dbh = DBI->connect("DBI:Oracle:$dbconnectstring","$user","$password");
open FH, "< Queries.sql"; 
$sth = $dbh->prepare(<FH>)     or die ("Can't prepare  $sqlStatement");
$sth->execute($name,$id)      or die ("Can't execute $sqlStatement");

SQL statement in the sql file :

delete from Employee where name = ? and id = ? ;

Below is the error I'm getting :

DBD::Oracle::st execute failed: ORA-00911: invalid character (DBD ERROR: error possibly near <> indicator at char 86 in 'delete from Employee where name = :p1 and id = :p2<>;') [for Statement "delete from Employee where name = ? and id = ?;" with ParamValues: :p1='3453', :p2='4545'] at ExecuteDelete.pl line 32, chunk 1.

Can't execute at ExecuteDelete.pl line 32, chunk 1

Kindly help me here please. I'm not sure if I'm doing anything wrong here.

I have tried all the other ways.. What ever i give.. some how " <*> " - this special charecter is getting appended .. Even i tried like below Please help me

my $sqlStatement = qq/delete from $tableName where name = ? AND id = ? AND place = $place/;

print "Statement is " . $sqlStatement . " \n"; my $sth = $dbh->prepare($sqlStatement);

DBD::Oracle::st execute failed: ORA-00904: invalid identifier (DBD ERROR: error possibly near <*> indicator at char 101 in 'delete from EMPLOYEE where ORDER_NUMBER = :p1 AND ORDER_VERSION = :p2 AND place = <*>EMPLOYEE') [for Statement "delete from EMPLOYEE where name = ? AND id = ? AND place = EMPLOYEE" with ParamValues: :p1='34534', :p2='09']

Yes i have tried.. But some other character is getting appended now :( DBD::Oracle::st execute failed: called with 2 bind variables when 0 are needed [for Statement "delete from Employee where name = '?' and id = '?';"] at ExecuteDelete.pl line 32, <FH> chunk 1. — muthu kumar
I dont think it is considering ? with single quotes. I think thats why i'm getting arguements not necessary — muthu kumar

Miguel Prz Miguel Prz · Accepted Answer · 2013-07-09T19:31:41

you are using an old way to open files. This is a modern approach:

use strict;
use warnings;
#...
open my $fh, '<', 'Queries.sql' or die $!; 
my $sql = <$fh>;
chomp $sql;
my $sth = $dbh->prepare($sql);
$sth->execute($name,$id);
#... 
close $fh;

also remove the ";" in your SQL

I recommend the use of this CPAN module Data::Phrasebook::SQL, it may be helpful for you in the context you are using SQLs.

how to remove the special characters which is getting appended while reading sql file from perl

1 Answers