Problem with quotes (DBI) 2

[gr0undtek]

Hi,

I have the following problem:
I open a file and store the data to an array.
Then I use
foreach my $tempLine (@indata)
{
my $sth = $dbh->prepare("Select iid from users where id='$tempLine'")
$dbh->execute();
}
*Note: the real code is tested the only error is the quotes.
The problem is that no matter if I use $dbh->quote($tempLine)
or $temp = q($tempLine) or any other way to put the parameter in the query with single quotes ut never works.

The query is ok and it works if I input the parameter normaly, with no variable.
As well I used print to see the output of the quotation and I ALWAYS get this:
'12356
''12315
''21354
etc...

I am using ActivePerl in WindowsXP and Notepad with UTF-8 setting as an editor.
Any help appreciated as this is really getting on my nerves!!!!!!!!!
Thanks
stelios

 

[raklet]

Try it like this:

{
my $sth = $dbh->prepare("Select iid from users where id=?")
$dbh->execute($templine);
}

Using placeholders forces DBI to automatically take care of quoting special charactes. Set the placeholder, and then pass the variable during execution.

Raklet

 

[mbrooks]

Or you can do..

foreach my $tempLine (@indata) {
	my $sth = $dbh->prepare(qq{
		Select id from users where id = '$tempLine'
	});
	$dbh->execute();
}

or even like

foreach my $tempLine (@indata) {
	my $sth = $dbh->prepare(
		"Select id from users where id =" . $sth->quote($tempLine)
	);
	$dbh->execute();
}

M. Brooks

http://mbrooks.info

 

[gr0undtek]

I have tried all of this but still I get the same problem
Maybe is something specific to the Oracle DB?

 

[brigmar]

Ok, your problem, as *I* see it is twofold:

The first part is passing parameters in a prepared statement. Raklet's solution is the answer to that part.

The second part is that you are reading and passing the newline characters in from the file. Hence:

As well I used print to see the output of the quotation and I ALWAYS get this:
'12356
''12315
''21354
etc...

You need to either 'chomp(@indata);' before your foreach() loop, or 'chomp($tempLine);' inside the foreach() loop.

 

[gr0undtek]

I tried Raklet's solution and it did not work. In adition the file I'm parsing from has no quotes in it. I will try your solution as well though...

 

[gr0undtek]

Thanks a lot brigmar. This worked!!!! Can you explain why did I had to use chomp. Is Perl puting quotes in the variabels by default?

Thanks again!

 

[brigmar]

Each line that you read from your file ends in a CR/LF or CR character (dependent on your system).

chomp removes those characters from the end strings.

http://perldoc.perl.org/functions/chomp.html

perl is not putting quotes in the variables. Your print statement that was echoing those characters was something like:

print "'$variable'";

i.e., the programmer put the single quotes in.

print "'$variable'\n";

would have made the problem more obvious, as it would have outputted:

'aaaaa
'
'bbbbb
'
etc....

 

[gr0undtek]

thanks