[Melbourne-pm] Strange things about '0'

[Mathew Robertson]

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