Обсуждение: Booleans

Hi,
I've written an php script with many queries like

"insert into table (a,b,c) VALUES('$q','$w','$e')"

where $q,$w and $e are booleans. I've written it with mysql and it works fine
there, now I want to use it with postgres and have the following problem:
As type for a,b and c I used the bit type with length 1. Now if $q is true
there is now problem it evaluates to '1', but false evaluates to '' and
postgres then complains that the bit string length does not match. Its
clearly why it happens, but is there an easy way to fix this, without
rewriting all the sql queries with something like if($q===false) $q='0';.

Thank you
Jascha Schubert

On Mon, 4 Nov 2002, Jascha Schubert wrote:

> Hi,
> I've written an php script with many queries like
>
> "insert into table (a,b,c) VALUES('$q','$w','$e')"
>
> where $q,$w and $e are booleans. I've written it with mysql and it works fine
> there, now I want to use it with postgres and have the following problem:
> As type for a,b and c I used the bit type with length 1. Now if $q is true
> there is now problem it evaluates to '1', but false evaluates to '' and
> postgres then complains that the bit string length does not match. Its
> clearly why it happens, but is there an easy way to fix this, without
> rewriting all the sql queries with something like if($q===false) $q='0';.

I'd recommend taking off your mysql thinking cap for this one. :-)

To set your values, I'd suggest using the TRUE and FALSE method, like so:

insert into table (a,b,c) values (TRUE,FALSE,FALSE)

So that you use PHP to set each field to TRUE or FALSE (note there's no '
marks around the TRUE or FALSE).

When testing for true or false, do it the same basic way:

select * from table where a IS TRUE

or

select * from table where a IS TRUE and (b IS FALSE or c IS FALSE)

kind of thing.

That keeps you from having to worry about how TRUE and FALSE are being
stored in the database.

on 11/4/02 9:26 AM, scott.marlowe@ihs.com purportedly said:

> To set your values, I'd suggest using the TRUE and FALSE method, like so:
>
> insert into table (a,b,c) values (TRUE,FALSE,FALSE)
>
> So that you use PHP to set each field to TRUE or FALSE (note there's no '
> marks around the TRUE or FALSE).

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.

You will have to force PHP to convert your values into a number, such as
    $a = $a|0 or $a = number_format( $a )
or use the workaround mentioned in your original email.

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')


Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"

Am Monday 04 November 2002 17:26 schrieb scott.marlowe:
> On Mon, 4 Nov 2002, Jascha Schubert wrote:
> > Hi,
> > I've written an php script with many queries like
> >
> > "insert into table (a,b,c) VALUES('$q','$w','$e')"
> >
> > where $q,$w and $e are booleans. I've written it with mysql and it works
> > fine there, now I want to use it with postgres and have the following
> > problem: As type for a,b and c I used the bit type with length 1. Now if
> > $q is true there is now problem it evaluates to '1', but false evaluates
> > to '' and postgres then complains that the bit string length does not
> > match. Its clearly why it happens, but is there an easy way to fix this,
> > without rewriting all the sql queries with something like if($q===false)
> > $q='0';.
>
> I'd recommend taking off your mysql thinking cap for this one. :-)
>
> To set your values, I'd suggest using the TRUE and FALSE method, like so:
>
> insert into table (a,b,c) values (TRUE,FALSE,FALSE)
>
> So that you use PHP to set each field to TRUE or FALSE (note there's no '
> marks around the TRUE or FALSE).

The Problem is I use variables ($q) then i would have to to something like
this for every querie
if ($q)
    $q1="TRUE";
else
    $q1="FALSE";
...... (also for $w and $e)
"insert into table (a,b,c) VALUES($q1,$w1,$e1)"

that would be much writing and makes the code more complicated.

I will only do that if there is now possibility to manage this in the
database. It would just need something that lets '' become to '0' for this
fields.

Jascha

Am Monday 04 November 2002 18:27 schrieb Keary Suska:
> on 11/4/02 9:26 AM, scott.marlowe@ihs.com purportedly said:
> > To set your values, I'd suggest using the TRUE and FALSE method, like so:
> >
> > insert into table (a,b,c) values (TRUE,FALSE,FALSE)
> >
> > So that you use PHP to set each field to TRUE or FALSE (note there's no '
> > marks around the TRUE or FALSE).
>
> 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.

on 11/4/02 10:38 AM, JTSMailing@gmx.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"