[Melbourne-pm] Strange things about '0'
Mathew Robertson
mathew.robertson at netratings.com.au
Mon Oct 27 15:20:36 PDT 2008
> I have a problem with zero ('0'), and I'm hoping someone could please
> help. Sorry in advance for the long explanation.
>
> I've written a sub called sql_engine that I would like to put into a
> module. But before I do, I have two separate problems I'd like to
> solve. One is when a scalar has the value of 0 and the other is with
> HTML::Template. The HTML::Template problem I'll put in a separate thread.
>
> sql_engine takes a number of parameters to help control it. Its main
> job is to execute the query and return the results to the calling sub
> in the required data structure.
> sql_engine can be called like this
>
> my $data = sql_engine($entity, 'array_hash', 'asset_data', 1, $foocode);
>
> I've put the first part of the code at the end of the email.
> It calls prep_query to obtain the correct SQL code based on
> $query_name. This SQL code is stored in $query.
> Some queries will be returned with a ? inside them for the where
> clause. When these queries are required, $var_num and $query_var are
> also needed, other wise the code will break at the $report->execute()
> line.
> sql_engine is given the return type required using the $ret_type
> variable so I can select a scalar, array, hash, array of hashes etc as
> the data structure.
> The database type I am connecting to is Ingres, though I'm not sure if
> this makes a difference to this problem or not.
>
> An example of the query I see the problem with is.
>
> select code_descr from c_table where foocode = ?
>
> The table called c_table in an Ingres database with only two columns
> in it. Lets call them 'foocode' and 'description'. The current values
> of 'foocode' are A, D, S, 0, 1..8. These codes are used in other much
> larger tables and I sometimes need to filter on those larger tables by
> using a mixture of codes from the c_table and codes from other tables.
>
> Now, if $query_var is a 0 then the ? in the above sql there are no
> results returned from the query, but the Perl code doesn't break. The
> Ingres db system must get passed a false value.
> If I change $query_var to be '0' to try and get the right string into
> the query in the $report->execute statement it doesn't work. Again the
> Perl code doesn't break, but I still don't get any results.
> As the I see the Camel book mentions, Perl equates 0 and '0' both to
> be false. I think this is why there ar no results of both 0 and '0'.
Its not Perl's fault -> it (aka DBI) will be passing the literal value
to the database - the database should automatically quote numeric
values. What you can do is try a simple SQL statement directly to your
db, eg:
SELECT * FROM some_table WHERE some_column = 0;
If this doesn't fail with a bad-statement error, then your db install
(like most databases), automatically quotes numeric values.
>
> The workaround is to use the ---> if ($query_var... <--- block to
> modify the SQL code in $query and replace the ? with the string '0'.
> This is fine at the moment, but if in the future I have SQL code in
> $query that has more than one ? and I want only one of them to be '0'
> I'll be in trouble.
This approach is dubious... you should be using the bind_param() feature
of DBI, which internally uses the corresponding database's specific
implementation or the generic DBI implementation. You shouldn't be
doing your own placeholder transpositions (unless you are a uber geek).
>
> Is there another way to approach this problem so I can deal with the
> value of foocode when it is 0, but keeping the sql_engine sub flexible
> so it can make use of any number of ? in the SQL code, and not have to
> predict in advance which ? is for foocode.
>
> sub sql_engine {
> my $entity = shift @_; # Used to select the database and all
> required inside the queries
> my $ret_type = shift @_; # Determines if this sub should return a
> scaler, array, hash, array of hashes etc
> my $query_name = shift @_; # A place to hold the query that will be
> run
> my $var_num = shift @_;
> my $query_var = shift @_; # place to put variables for the SQL query
> my $dbh = DBI->connect( "DBI:Ingres:database_$entity", 'username' );
> my $report;
> my $query = prep_query($query_name,$entity);
> # The disp of 0 had to be passed in as '0' because a scaler with
> the value
> # of 0 will not be passed in properly to the query
> if ($query_var eq "'0'") {
> $var_num = 0;
> $query =~ s/\?/'0'/;
> }
>
> $report = $dbh->prepare($query);
> unless ($var_num > 0) {
> $report->execute();
> } else { $report->execute($query_var)
> }
Sounds like you want something like:
sub sql_engine {
my ($entity,$query_name) = (shift,shift);
my $dbh = ...connect(...$entity);
my $qry = prep_query($query_name,$entity);
my $sth = $dbh->prepare($qry);
// one of these:
my $max = scalar(@_);
for my $p_num (1..$max) {
$sth->bind_params($p_num,$_[$p_num-1]);
}
return $sth->execute;
// or
return $sth->execute(@_);
// or
use Contextual::Return;
$sth->execute(@_);
return (
SCALAR { scalar($sth->fetchall_arrayref()) }
LIST { $sth->fetchall_arrayref() }
HASHREF { $sth->fetchall_hashref() }
VOID { print scalar($sth->fetchall_arref)." rows found" }
);
}
I'd suggest not passing in the 'array_hash' argument (or any other
variation). Either:
- use the returned $sth by calling the appropriate method.
- try using a contextual return as shown here.
Hope this helps,
Mathew
PS: your post was a little hard to follow - if you post the actual code,
it might give some more insight as to what you are trying to do.
More information about the Melbourne-pm
mailing list