[Melbourne-pm] Strange things about '0'

[Andrew Dent]

G'day

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'.

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.

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)
    }

Cheers

Andrew Dent