| Subject: | Error in determining if value is a number (floating point or hexadecimal) |
Jeff,
I encountered a problem with the SQL Statement module.
Currently I'm writing a program which stores some kind of serial numbers in a CVS (flat file) database.
These serials are actually ESS LUN-IDs, which are hexadecimal 'numbers'. For example: '02827674' is such a number.
When I run an SELECT statement on my database table, I noticed that every time the serial number contains the letter 'E',
I received ALL serials with the letter 'E'!
The following example program code (simplified)...
#!/usr/bin/perl
use strict;
use warnings;
use SQL::Statement 1.14; # (default installed: 1.09)
my $stmt;
my $cache={};
my $parser = SQL::Parser->new();
for my $sql(split /\n/,
" CREATE TABLE mytable (id INT PRIMARY KEY,serial VARCHAR(30))
INSERT INTO mytable VALUES(1,'02027674')
INSERT INTO mytable VALUES(2,'02527674')
INSERT INTO mytable VALUES(3,'02927674')
INSERT INTO mytable VALUES(4,'02A27674')
INSERT INTO mytable VALUES(5,'02E27674')
INSERT INTO mytable VALUES(6,'40127674')
INSERT INTO mytable VALUES(7,'40E27674')
INSERT INTO mytable VALUES(8,'40F27674')
SELECT * FROM mytable WHERE serial = '02E27674'"
){
$stmt = SQL::Statement->new($sql,$parser);
$stmt->execute($cache);
next unless $stmt->command eq 'SELECT';
while (my $row=$stmt->fetch) {
print "@$row\n";
}
}
__END__
...produces the following output:
5 02E27674
7 40E27674
Which ofcourse is not correct!
Line 7 has a serial value of '40E27674' and not '02E27674'
After some investigation, I noticed the function is_number. I've added some debug lines to see what's going wrong.
It seems that the code to determine if the value is a number fails on the floating point notation. After I changed this
check, my output was correct!
OS : AIX 5200-04
Perl : v5.8.0 built for aix-thread-multi
Module : SQL::Statement
Version : 1.14
Line : 94+
Code snippet:
$numexp = exists $self->{"text_numbers"}
###new
# ? qr/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/
? qr/^([+-]?|\s+)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/
###endnew
: qr/^\s*[+-]?\s*\.?\s*\d/;
I changed the the above line in the following (note the | plus sign instead of a question mark!):
v
? qr/^([+-]?|\s+)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]+\d+))?$/
Which did the trick.
Hope you can evaluate this bug and correct it in a new release?
Kind Regards,
Evert Hasselaar
Next One IT Solutions
Bereklauw 28
4007 VM TIEL, Holland
tel 0344 63 53 93
fax 0344 63 10 37
mail info@nextOne.nl
web www.nextOne.nl