Oracle Placeholder Problem in my Perl Script

slg_saravanan has asked for the wisdom of the Perl Monks concerning the following question:

Hi Friends..

I am facing problem while running a query (with oracle placeholder) in my Perl script. Following is the code :

#!/usr/bin/perl -w
use warnings;
use strict;
use CGI;
use DBI;
use CGI::Carp qw(fatalsToBrowser);

my $query=new CGI;
my $cgiobj = CGI -> new();
#my $xlsdata;
my $querydata;
my $querycreated;

#print "Content-type:text/html\n\n";
my $querytitle = $cgiobj -> param('querytitle');

$ENV{ORACLE_HOME}="/home/oracle/product/10.2.0/db_1";
our $conn = DBI->connect("dbi:Oracle:host=10.0.0.1;sid=dev;","queryedi
+tor","editor");
die($DBI::errstr) unless $conn;

print $querytitle;
print "Content-type: application/HTML\n";
print "Content-disposition: inline; filename=\"Report.html\"\n";
print "Pragma: no-cache\n";
print "Expires: 0\n\n";

my $query_detail1 = "select querydata from querydetail where querytitl
+e='$querytitle'";
my $query_records1 = $conn->prepare($query_detail1) or die("Error on p
+reparing the sql statement1 \n");
$query_records1->execute() or die("Error on getting the data from QUER
+YDETAIL \n");
while(my @query_resultset1= $query_records1->fetchrow_array)
{
my $mainquery= $query_resultset1[0];
#print $mainquery;
my $query_records2 = $conn->prepare($mainquery) or die("Error on prepa
+ring the sql statement2 \n");
$query_records2->execute() or die("Error on getting the data from QUER
+YDETAIL \n");

print "<h1><center>Report</center></h1><br><br>";

my $dat = $query_records2 -> fetchall_arrayref;
foreach my $i ( 0..$#{ $dat } ) {
foreach my $j ( 0..$#{ $dat->[$i] } ) {
print $dat->[$i][$j]."<br>";
}
}
}
[download]

I am making the final result in HTML report format. For example I am using the "select * from EMP where EMPNO = &eno " query here. So, $mainquery will carry this query and will process the rest.

What could be the problem ? If you need more detail..then i can supply iy to you..

Thanks in Advance..
Warm Regards,
Saravanan

Code tags added by Arunbear

Comment on Oracle Placeholder Problem in my Perl Script Download Code

Replies are listed 'Best First'.
Re: Oracle Placeholder Problem in my Perl Script by bart (Canon) on May 28, 2007 at 08:46 UTC
Your code is pretty much unreadable now, as it's one blob of text, but I can see this: `my $query_detail1 = "select querydata from querydetail where querytitl +e='$querytitle'"; my $query_records1 = $conn->prepare($query_detail1)` [download] Do not, ever, embed values from variables directly into a query. Use placeholders instead, that is, typically, a question mark for every value, without any quotes: `my $query_detail1 = "select querydata from querydetail where querytitl +e=?"; my $query_records1 = $conn->prepare($query_detail1)` [download] You later have to pass the proper value(s) when you call the `execute` method on the statement handle: `$query_records1->execute($querytitle)` [download] I don't know the query `select * from EMP where EMPNO = &eno` syntax. Is this some form of placeholder too? If so, it doesn't look familiar, and Google doesn't help me there. (Placeholders in Oracle typically look like "`:foo`"; and I think that that syntax will work from DBI too.)	[reply] [d/l] [select]
Re: Oracle Placeholder Problem in my Perl Script by mpeppler (Vicar) on May 28, 2007 at 19:12 UTC
It looks like you are trying to use the &foo format for parameters from sqlplus. This will NOT work in perl. As others have mentioned, use a ? as the placeholder, and pass the parameter in the $sth->execute() call. Michael	[reply]
Re: Oracle Placeholder Problem in my Perl Script by nobull (Friar) on May 28, 2007 at 18:14 UTC
What could be the problem? What are the symptoms? Does your code behave is some way other than you intended? If so what way? `my $query_records2 = $conn->prepare($mainquery) or die("Error on prepa +ring the sql statement2 \n"); $query_records2->execute() or die("Error on getting the data from QUER +YDETAIL \n");` [download] Consider including the actual error (`$DBI::errstr` or the `errstr()` method) in your error messages. If you code is failing it can make much easy to figure out why.	[reply] [d/l] [select]
Re: Oracle Placeholder Problem in my Perl Script by andreas1234567 (Vicar) on May 29, 2007 at 06:48 UTC
I'm echoing bart here: You should always use bind variables when you query your Oracle database. I find the articles on asktom.oracle.com very helpful in explaining why and how it should be used.	[reply]
Re^2: Oracle Placeholder Problem in my Perl Script by slg_saravanan (Initiate) on May 31, 2007 at 05:26 UTC
Sure..will try that. Thanks a lot Pals !! Warm Regards, Saravanan	[reply]


Syntactic Confectionery Delight
	PerlMonks