How to represent spaces in Perl's DBI properly

Question

I have a record in an Informix table. The table columns look like this:

acct_no integer,
suffix char(1),
meter_num char(20),
date_read datetime year to second not null ,
counter smallint,
reading integer not null ,
typeofread char(1),
estimated char(1),
time_billed datetime year to second

Using Informix's dbaccess tool:

select * 
from ws_mtrread 
where acct_no = 113091000 
and suffix = " " 
order by date_read desc;

this result (newest shown) is returned and works whether or not I use one or two spaces for suffix.

acct_no      113091000
suffix
meter_num    34153205
date_read    2013-09-09 23:31:15
counter      0
reading      1240
typeofread   g
estimated
time_billed  2013-10-22 11:48:21

However, this Perl DBI query

my $sql_statement = 
"select * ".
"from ws_mtrread ".
"where acct_no = ? ".
"and suffix = ? ".
"order by date_read desc ; ";

does not work. I can fetch the row without specifying $suffix, so I know the row exists.

I believe this is an error on my part in representing the suffix. In this example suffix is equal to a string of two spaces.

How do I represent spaces correctly, so the query works? Here is the rest of the code I used to fetch the row.

my $test_acct_no = 113091000;
my $suffix = "  ";

my $pt_sel_hdl = $DBHdl->prepare($sql_statement);

$pt_sel_hdl->execute($test_acct_no, $DBHdl->quote($suffix));

my $ws_mtr_read_rec_ref = $pt_sel_hdl->fetchrow_hashref;

After the call, $ws_mtr_read_rec_ref is undefined.

Answer 1

Don't use DBI's quote method here:

$pt_sel_hdl->execute($test_acct_no, $DBHdl->quote($suffix));

When you use ? placeholders in your SQL, the database driver will correctly parameterize the query arguments that you pass to execute. You are probably creating a query that is searching for the literal string " " (including the quotes) when you want to search for (just two spaces.)

So this should be all you need:

$pt_sel_hdl->execute($test_acct_no, $suffix);