yes/no data type

Hello all

Is there a yes/no boolean datatype with mySQL? I can't seem to find if there
is, and I have used an int type set to 1 or 0 but that breaks some of my
apps that used to use access which does have a yes/no field.

Many thanks

Dave

Re: yes/no data type

"Dave" wrote in message
news:Bq0Ag.17139$WY2.5105@newsfe3-gui.ntli.net...
> Hello all
>
> Is there a yes/no boolean datatype with mySQL? I can't seem to find if
there
> is, and I have used an int type set to 1 or 0 but that breaks some of my
> apps that used to use access which does have a yes/no field.
>
> Many thanks
>
> Dave

That would be type "tinyint" for which the type "bool" is a synonym.
Access doesn't have a special boolean type either. It also uses an integer
numeric for "yes/no".

The yes/no true/false is all in the interpretation.
Thomas Bartkus

Re: yes/no data type

"Thomas Bartkus" wrote in message
news:UMOdnegW0a9-KU3ZnZ2dnUVZ_qednZ2d@telcove.net...
> "Dave" wrote in message
> news:Bq0Ag.17139$WY2.5105@newsfe3-gui.ntli.net...
>> Hello all
>>
>> Is there a yes/no boolean datatype with mySQL? I can't seem to find if
> there
>> is, and I have used an int type set to 1 or 0 but that breaks some of my
>> apps that used to use access which does have a yes/no field.
>>
>> Many thanks
>>
>> Dave
>
> That would be type "tinyint" for which the type "bool" is a synonym.
> Access doesn't have a special boolean type either. It also uses an
> integer
> numeric for "yes/no".
>
> The yes/no true/false is all in the interpretation.
> Thomas Bartkus
>
>

When i query an access database which has a yes/no field with vbscript in
asp, the return i get is True or False but when I do the same query with
mySQL the return is 1 or 0.

Is this just the interpretation of the odbc driver or am i misinterpreting
it?

Thanks

Dave

Re: yes/no data type

"Dave" wrote in message
news:O06Ag.56281$eQ.34919@newsfe7-win.ntli.net...
> "Thomas Bartkus" wrote in message
> news:UMOdnegW0a9-KU3ZnZ2dnUVZ_qednZ2d@telcove.net...
> > "Dave" wrote in message
> > news:Bq0Ag.17139$WY2.5105@newsfe3-gui.ntli.net...
> >> Hello all
> >>
> >> Is there a yes/no boolean datatype with mySQL? I can't seem to find if
> > there
> >> is, and I have used an int type set to 1 or 0 but that breaks some of
my
> >> apps that used to use access which does have a yes/no field.
> >>
> >> Many thanks
> >>
> >> Dave
> >
> > That would be type "tinyint" for which the type "bool" is a synonym.
> > Access doesn't have a special boolean type either. It also uses an
> > integer
> > numeric for "yes/no".
> >
> > The yes/no true/false is all in the interpretation.
> > Thomas Bartkus
> >
> >
>
> When i query an access database which has a yes/no field with vbscript in
> asp, the return i get is True or False but when I do the same query with
> mySQL the return is 1 or 0.
>
> Is this just the interpretation of the odbc driver or am i misinterpreting
> it?

I'm guessing it's an interpretation of the DAO or ADO database libraries
you are using with vbscript.

I presume that when you say "do the same query with mySQL", you are
referring to the MySQL command line client app. - yes/no ;-)

In MySQL, there is no real "boolean" flag attached to the field.
Try this:
SELECT True; # MySQL returns then integer 1
SELECT False; # MySQL returns the integer 0

The fact is, "True" and "False" are simple constants reflecting the integers
1 and 0 respectively.
More specifically, 0 is False and any number<> 0 is True.
Boolean algebra, Linux, MySQL running under Linux, and (I presume!) MySQL
running under Windows all follow this convention consistently.

As far as the MySQL command client is concerned, one needs to coerce it to
display the words "True" or "False"
SELECT IF(fld<>0, "True", "False")
or if you prefer:
SELECT IF(fld<>0, "Yes", "No")

I don't *think* there is any way to get that to happen automatically in the
MySQL command line client the way you can in Access. If you have linked
tables or pass through queries in Access that are speaking with MySQL
tables - these will never see a field labeled "boolean". You will have to
corece the type using the SELECT IF statements shown above.

But - That's a display/user interface issue.
The logic works exactly the way you would expect.

Thomas Bartkus

Re: yes/no data type

>Is there a yes/no boolean datatype with mySQL? I can't seem to find if there
>is, and I have used an int type set to 1 or 0 but that breaks some of my
>apps that used to use access which does have a yes/no field.

You can try an enum type:

enum('No', 'Yes')

Gordon L. Burditt

Re: yes/no data type

"Thomas Bartkus" wrote in message
news:ZeSdnapfWKkglUzZnZ2dnUVZ_vWdnZ2d@telcove.net...
> "Dave" wrote in message
> news:O06Ag.56281$eQ.34919@newsfe7-win.ntli.net...
>> "Thomas Bartkus" wrote in message
>> news:UMOdnegW0a9-KU3ZnZ2dnUVZ_qednZ2d@telcove.net...
>> > "Dave" wrote in message
>> > news:Bq0Ag.17139$WY2.5105@newsfe3-gui.ntli.net...
>> >> Hello all
>> >>
>> >> Is there a yes/no boolean datatype with mySQL? I can't seem to find if
>> > there
>> >> is, and I have used an int type set to 1 or 0 but that breaks some of
> my
>> >> apps that used to use access which does have a yes/no field.
>> >>
>> >> Many thanks
>> >>
>> >> Dave
>> >
>> > That would be type "tinyint" for which the type "bool" is a synonym.
>> > Access doesn't have a special boolean type either. It also uses an
>> > integer
>> > numeric for "yes/no".
>> >
>> > The yes/no true/false is all in the interpretation.
>> > Thomas Bartkus
>> >
>> >
>>
>> When i query an access database which has a yes/no field with vbscript in
>> asp, the return i get is True or False but when I do the same query with
>> mySQL the return is 1 or 0.
>>
>> Is this just the interpretation of the odbc driver or am i
>> misinterpreting
>> it?
>
> I'm guessing it's an interpretation of the DAO or ADO database libraries
> you are using with vbscript.
>
> I presume that when you say "do the same query with mySQL", you are
> referring to the MySQL command line client app. - yes/no ;-)
>
> In MySQL, there is no real "boolean" flag attached to the field.
> Try this:
> SELECT True; # MySQL returns then integer 1
> SELECT False; # MySQL returns the integer 0
>
> The fact is, "True" and "False" are simple constants reflecting the
> integers
> 1 and 0 respectively.
> More specifically, 0 is False and any number<> 0 is True.
> Boolean algebra, Linux, MySQL running under Linux, and (I presume!) MySQL
> running under Windows all follow this convention consistently.
>
> As far as the MySQL command client is concerned, one needs to coerce it to
> display the words "True" or "False"
> SELECT IF(fld<>0, "True", "False")
> or if you prefer:
> SELECT IF(fld<>0, "Yes", "No")
>
> I don't *think* there is any way to get that to happen automatically in
> the
> MySQL command line client the way you can in Access. If you have linked
> tables or pass through queries in Access that are speaking with MySQL
> tables - these will never see a field labeled "boolean". You will have to
> corece the type using the SELECT IF statements shown above.
>
> But - That's a display/user interface issue.
> The logic works exactly the way you would expect.
>
> Thomas Bartkus
>
Thanks for the info Thomas