[ic] DBI does not quote column names -> reserved words will break query

Gert van der Spoel gert at 3edge.com
Tue Jan 5 09:12:35 UTC 2010


If a column name turns out to be (or become) a reserved word then queries in
Interchange will break (ie set_slice etc) .. This is because column names
are not quoted.

(pj: the DBI method quote_identifier() is specifically for that purpose.)

Example: 
Mysql 4 ->  'condition' would be a column name without a problem 
Mysql 5 ->  'condition' is a reserved word and needs to be quoted

Boom ...


And while I'm at it ... From Mysql 4 to Mysql 5 there is a change in the
decimal datatype.

Mysql 4 :  decimal(3,2)  means  3 digits before and 2 after the .  (so
999.99 is the max value)
Mysql 5 :  decimal(3,2)  means  3 digits in total, 2 after the .   (so 9.99
is the max value)

In case you stick 25.33 in this field it will automatically go to 9.99  ...
I can imagine this will cause some problems heh.
In standard interchange there are a couple of decimal fields but they are
12,2 - some price fields I believe.

Unless you have a currency which grows to be in the range of Gazilions for a
standard product, or unless you sell continents or even planets you should
still be safe when upgrading to Mysql 5, at least for this difference.

CU,

Gert






More information about the interchange-users mailing list