CODEWITHSUNDEEP
perlsqlite
kdubs
kdubs

Reputation: 1722

perl SQLite DBI - bind_value don't work with '?', but works when using hard coded value

I'm using Perl's DBI with SQLite.

The issue is when using selectrow_hashref by passing in the values with a bind_value. I have a field that's a Julian Date. When I put the value of the date in the call directly it works. If I try to use the value in a bind_value it doesn't work. I know I'm close, because I can use the bind_value when I use a different key to query on. You can see the code in the debugger below.

Any suggestions ?

This works:

  DB<20> $hash_ref = $dbh->selectrow_hashref("select * from run_data  where date = '$date'")

  DB<21> x $hash_ref
  0  HASH(0x3e8ad28)
   'date' => '2014-09-17 17:35:28'
   'kit' => '15.1.0.40'
   'run_id' => 1

This one doesn't, just added the ? and moved the date to the bind_value:

  DB<22> $hash_ref = $dbh->selectrow_hashref("select * from run_data  where date = '?'", undef, $date)

  DB<23> x $hash_ref
  0  undef
  DB<24>

This works, but it's using an integer key:

  DB<24> $hash_ref = $dbh->selectrow_hashref("select * from run_data  where run_id = ?", undef, 1)

  DB<25> x $hash_ref
  0  HASH(0x3e8b0d0)
   'date' => '2014-09-17 17:35:28'
   'kit' => '15.1.0.40'
   'run_id' => 1

Update

After sleeping it occurred to me that bind_value is an array. so I tried this :

$hash_ref = $dbh->selectrow_hashref("select run_id from run_data where date = ?", undef, ($date));

and that works. So no quotes and make sure I use an array.

I am curious now why $DBI::errstr didn't have a value, you would think the data was malformed. I'm going to try running with RaiseError and see what happens.

Upvotes: 1

Views: 130

Answers (2)

ikegami
ikegami

Reputation: 385657

'?' means "the string consisting of the character ?. Remove the quotes.

my $hash_ref = $dbh->selectrow_hashref(
   "select * from run_data  where date = ?", undef, $date);

Upvotes: 2

Ahmad Bilal
Ahmad Bilal

Reputation: 382

try something similar to this:

$sth = $dbh->prepare(qq(SELECT * FROM run_data WHERE run_id=(?) ));
$sth->execute($date) or die "Some message";

Upvotes: 0

Related Questions