sub is_purple {
  ...
  return $color eq 'purple' ? '1' : '0';
}
[download]

-Blake

I'll pass on the ability of Perl to distinguish between 1 and '1'. To my knowledge there's no way to separate them, hence all the questions about using a regex to determine whether a variable contains a number or not.

Anyway... Oracle (about which I know) certainly has a problem when you pass it queries such as SELECT name FROM user WHERE id = '1', if id is a numeric value. Every other language will also have the same problem, since it's trying to compare a number with a string.

Oracle's rule used to be (but it's changed now, and I'm not entirely sure what to) that the left-hand value got converted to the type of the right-hand value before the comparison is made. So, if you coded id = '1.0' the test would always fail (as the id 1 would get converted to '1'), whereas '1.0' = id would suceed (as '1.0' would be converted to 1).

This caused some problem with writing general comparisons across different types: if a is numeric and b is a string, then a = b could fail (as above). However, b = a could actually cause a database error if b ever ended up holding a value which it was not possible to interpret as a numeric value.

--
Tommy
Too stupid to live.
Too stubborn to die.

In Oracle:


create table rdf_test(v1 number);
insert into rdf_test values(1);
select count(*) from rdf_test where v1 = 1;

COUNT(*)
========
       1

select count(*) from rdf_test where v1 = '1';

COUNT(*)
========
       1

select count(*) from rdf_test where v1 = '1.0';

COUNT(*)
========
       1
[download]




rdfield

Yes, it look's like the string parameter gets converted to a number. Told you it had changed. ;-)

Which means that

SQL> select count(*) from rdf_test where '1.0' = v1;

  COUNT(*)
----------
         1

SQL>  select count(*) from rdf_test where 'a' = v1;
 select count(*) from rdf_test where 'a' = v1
                                     *
ERROR at line 1:
ORA-01722: invalid number


SQL> select count(*) from rdf_test where v1 = 'a';
select count(*) from rdf_test where v1 = 'a'
                                         *
ERROR at line 1:
ORA-01722: invalid number
[download]

Obviously the fix is to convert the number into a string (since the exception can't be generated then). Thus,

  1* select count(*) from rdf_test where 'a' = TO_CHAR(v1)
SQL> /

  COUNT(*)
----------
         0
[download]

Great. It's working fine now. But...

SQL> select count(*) from rdf_test where '1.0' = TO_CHAR(v1);

  COUNT(*)
----------
         0
[download]

which leaves it still being a headache.

--
Tommy
Too stupid to live.
Too stubborn to die.

I'll pass on the ability of Perl to distinguish between 1 and '1'. To my knowledge there's no way to separate them

But there is: for bitwise operators like "&" and "|", strings behave differently than numbers. If one is a number, both will be treated as a number. For example

$\ = "\n";
for $a (1, "1") {
    print $a | " $a";
}
[download]

will produce a different result for 1 and for "1".

Coming back to DBI: It doesn't make this kind of distinction IIRC. Anything is a string.

Binding placeholders to a variable type can help. I don't know for sure for enumerated types in MyQSL, I can't test that (easily), but I do recall that DBI appears to make a guess on a field type based on the first value you feed it. If it looks like a number, DBI will treat the column as a number, which will produce an error if the field contains a non-numerical value for one of the next rows. I think even that if the first value is undef, DBI treats it as a string... And various MS databases do mind, even if MySQL doesn't.

This prevents that:

use DBI ':sql_types';

my $dbh = DBI->connect(...);

my $sth = $dbh->prepare("SELECT * FROM $table WHERE $field = ?");
$sth->bind_param(1, undef, SQL_VARCHAR);  # or SQL_CHAR? untested.
# For numeric types, experiment with SQL_NUMERIC or SQL_INTEGER

...
# Now go ahead, DBI will treat any data for the (first) field as a str
+ing...
[download]

p.s. Disclaimer: this is all based on my memory of experiences of a few years ago. Please don't hold it too much against me if it contains a few minor errors.

Interesting... very interesting. So what happens for values that are both numbers and strings? I just ran your is_number() trick against a string/number variable (actually a PVIV) and it correctly reported that there it's a number... it just also happens to be a string.

use Devel::Peek;
$a = 0;
scalar "$a";

Dump($a); # prove it's a PVIV with IOK and POK set
print is_number($a); # returns true

sub is_number { ( $_[0] & ~ $_[0] ) eq '0' }
[download]

The ~ operator has to decide whether to treat the scalar as a string or as a numeric. I would say it makes a lot of sense to prefer numeric treatment whenever that flag's set - as it's likely that a number that's also string is really a stringified number, and not that likely that it's a numified string.

So long as any XS or otherwise non-Perl code plays by the same rule, you're not going to run into trouble with that situation.

Makeshifts last the longest.

ah. that explains it.

at work we use postgres on the dev machines and DB2 on the production server. i encountered this a few times where postgres would have no problem and DB2 would give me a cryptic error until i changed a parameter from $string to "$string". i decided that it was best for my sanity if i just forgot about it and buried the ugliness with a little help from DBIx::AnyDBD.

anders pearson

Is there any way to tell DBI how to quote things? It's really quite awful at guessing sometimes.

In theory one can use $sth->bind_param to tell type which has variable to be inserted in place of placeholder so DBI has a chance to do proper quoting. In reality I'm not sure that it works for anything other than Oracle. At least I've never seen it being used with DBI drivers other than DBD::Oracle.

--
Ilya Martynov (http://martynov.org/)