[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.)

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.



More information about the interchange-users mailing list