Re: Booleans

on 11/4/02 10:38 AM, JTSMailing(at)gmx(dot)net 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"