There is an apparent problem with inserting integers into a table through
the DBI that does not manifest itself when the same command is executed
through the mysql command prompt. It appears that it has to do with the size
of the integer.
(Using RH7.0,DBI 1.14 & MySQL 3.23.35)
Consider:
Table Structure:
create table t2 ( seq int auto_increment primary key, num int, chr
char(12));
Snippet:
INSERT INTO t2 VALUES (NULL,3682932,"3682932")
Response:
DBD::mysql::db do failed: You have an error in your SQL syntax near '' at
line 1
at Stuff.pl line 224.
ERROR: could not |INSERT INTO t2 VALUES (NULL,3682932,"3682932")|
When the INSERT is executed from the command prompt it functions:
INSERT INTO t2 VALUES (NULL,3682932,"3682932");
Query OK, 1 row affected (0.00 sec)
Giving the following:
+-----+---------+--------------+
| seq | num | chr |
+-----+---------+--------------+
| 1 | 3682932 | 3682932 |
+-----+---------+--------------+
When the integer data is divided by 10, the insert through the DBI works:
INSERT INTO t2 VALUES (NULL,368293,"3682932")
Giving the following:
+-----+--------+--------------+
| seq | num | chr |
+-----+--------+--------------+
| 1 | 368293 | 3682932 |
+-----+--------+--------------+
Per the manual (Section 7.3 Column Types of the MySQL manual), an int should
accept values as follows:
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.
Clearly -2147483648 < 3682932 < 2147483647.
mysqlshow DB t2 results in:
+-------+----------+------+-----+---------+----------------+---------------------------------+
| Field | Type | Null | Key | Default | Extra | Privileges
|
+-------+----------+------+-----+---------+----------------+---------------------------------+
| seq | int(11) | | PRI | | auto_increment |
select,insert,update,references |
| num | int(11) | YES | | | |
select,insert,update,references |
| chr | char(12) | YES | | | |
select,insert,update,references |
+-------+----------+------+-----+---------+----------------+---------------------------------+
MySQL believes num to be an int.
Changing the type of num from int to bigint does not cure the problem (same
behaviour as above).
Even more strange is that the number the DBI does not accept does not map
cleanly to either a SMALLINT or a MEDINT.
This one for the experts! What's Happening? (Thanks!)
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
