in reply to DBI: when 1 != '1'

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.

Replies are listed 'Best First'.
Re: Re: DBI: when 1 != '1'
by rdfield (Priest) on Sep 27, 2002 at 12:51 UTC
    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

[reply]
[d/l]

      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.

[reply]
[d/l]
[select]
Re: Re: DBI: when 1 != '1'
by bart (Canon) on Sep 27, 2002 at 23:55 UTC
    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.

[reply]
[d/l]
[select]

In Section Meditations