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

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]
      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]
      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]
        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]

Back to Seekers of Perl Wisdom