on 11/4/02 10:38 AM, JTSMailing@xxxxxxx purportedly said: >> This would work only if the fields are boolean. Unfortunately, bit fields >> are a poor choice to store boolean values, and you don't get any real >> benefit from them. >> > > I havent found any better yet... (i will try shortint next) > >> You should also use proper bit string constant syntax, prefacing each value >> with "B": >> insert into table (a,b,c) values (B'$a',B'$b',B'$c') > > > I would like to use the same queries for mysql and postgres. I don't think > mysql will handle with that. Which is why bit fields are a bad idea, because they aren't very portable. Both Postgres and MySQL understand the BOOL type, although Postgres is more flexible at what kinds of constants are allowed. In MySQL, BOOL (as well as BIT) is just a synonym for TINYINT, but the net effect is that both DB's use the same storage space (1 byte). The problem is that your code relies on the failure of MySQL to understand column constraints. '' is not a valid constant for numeric types in either PG or MySQL, however MySQL will not reject the malformed SQL and instead use the closest numeric equivalent which is 0. If MySQL ever becomes truly SQL compliant your code will break, so you will get better mileage out of correcting this issue now, and making sure that your boolean values are '1' or '0' only, which is portable across PG and MySQL without issue. This would also allow your code to e more portable to other RDBMSes. Keary Suska Esoteritech, Inc. "Leveraging Open Source for a better Internet"
