SQL and Placeholders

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

Wise Monks,

I am searching a DBD:CSV database based on a unique key like 1234567801234. The code I am using is below:

my $query="SELECT * FROM $table WHERE (ukey = ?)";
        my $sth=$dbh->prepare($query);
        $sth->execute($ukey);
[download]

where $ukey is from a hidden field in an html form.
I know everything up to this point works becasue if I change $query to select * from $table where ukey = '1234567801234' I get the record back that I expect. But just using the posted code, I get nothing.

I thought it might be a quoting issue, but according to the docs, the use of placeholders takes care of those issues. I have other scripts performing searches that work just fine, but those scripts search text fields. I am assuming my problem is due to the fact that this is a number instead of text, but I am not sure how to get around the problem.

Help, I am very confused

Thanks much,

digger

Comment on SQL and Placeholders Select or Download Code

Replies are listed 'Best First'.
Re: SQL and Placeholders by pfaut (Priest) on Mar 15, 2003 at 23:09 UTC
Since the value is all numeric, DBI/DBD might be attempting to treat it as a number instead of a string. This could be a problem since it's too large for an int unless you have a 64-bit perl. You might try explicitly binding as a string. `use DBI qw(:sql_types); my $query="SELECT * FROM $table WHERE (ukey = ?)"; my $sth=$dbh->prepare($query); $sth->bind_param(1, $ukey, SQL_VARCHAR); $sth->execute();` [download] BTW, since you're not checking the returns, I hope you have RaiseError set. `--- print map { my ($m)=1<<hex($_)&11?' ':''; $m.=substr('AHJPacehklnorstu',hex($_),1) } split //,'2fde0abe76c36c914586c';` [download]	[reply] [d/l] [select]
Re: Re: SQL and Placeholders by digger (Friar) on Mar 16, 2003 at 02:16 UTC
I am setting RaiseError to 1 when I create the connection. When I first created this code, I was checking after every action. Thank `$deity` for the documentation. I am going to save many keystrokes with RaiseError. Thanks, digger	[reply] [d/l]
Re: SQL and Placeholders by Abigail-II (Bishop) on Mar 16, 2003 at 00:05 UTC
Did you turn tracing on and saw how DBI deals with your method calls, and what's exactly send to the database? Abigail	[reply]
Re: Re: SQL and Placeholders by digger (Friar) on Mar 16, 2003 at 02:13 UTC
I did a trace as you suggested. I must admit my ignorance of this, but simple perusing of the DBI docs showed me how. I found that a typo in my template was causing additional info to be put into the hidden field. It wasn't showing up when I did a simple print of the value because the extra info was valid html. I must admit, I didn't peek at the html source until I saw the trace. When I saw what showed up in the trace, I was able to fix the problem and everything worked as expected. Thanks for all of your responses. Every time I ask a question, I end up learning many new and valuable things. That is one of the reasons I persevere in learning more about perl. I will be curling up with the DBI perldoc and probably buying a copy of Programming the Perl DBI from O'Reilly next week as I dig deeper into DB programming with perl. Again, thanks to all, digger	[reply]
Re: Re: Re: SQL and Placeholders by Desdinova (Friar) on Mar 16, 2003 at 10:51 UTC
I own and that book and can give it a really great recomendation. Lots of useful info laid out very nice	[reply]
Re: SQL and Placeholders by Tomte (Priest) on Mar 15, 2003 at 23:04 UTC
Try to use: `$sth->bind_param(1, undef, DBI::SQL_VARCHAR);` before you call `$sth->execute($ukey)` This should help, but this is out of my head, so read `perldoc DBI` if I'm mistaken on how SQL_TYPE is exactly accessed. regards, tomte	[reply]


Clear questions and runnable code get the best and fastest answer
	PerlMonks