yes/no data type

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Dave
    #1

    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


    Tags: mysql boolean
    • Thomas Bartkus
      #2
      Re: yes/no data type

      "Dave" <david@nospam.c o.ukwrote in message
      news:Bq0Ag.1713 9$WY2.5105@news fe3-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


        Comment

        • Dave
          #3
          Re: yes/no data type

          "Thomas Bartkus" <thomasbartkus@ comcast.netwrot e in message
          news:UMOdnegW0a 9-KU3ZnZ2dnUVZ_qe dnZ2d@telcove.n et...
          "Dave" <david@nospam.c o.ukwrote in message
          news:Bq0Ag.1713 9$WY2.5105@news fe3-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


            Comment

            • Thomas Bartkus
              #4
              Re: yes/no data type

              "Dave" <david@nospam.c o.ukwrote in message
              news:O06Ag.5628 1$eQ.34919@news fe7-win.ntli.net...
              "Thomas Bartkus" <thomasbartkus@ comcast.netwrot e in message
              news:UMOdnegW0a 9-KU3ZnZ2dnUVZ_qe dnZ2d@telcove.n et...
              "Dave" <david@nospam.c o.ukwrote in message
              news:Bq0Ag.1713 9$WY2.5105@news fe3-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



                Comment

                • Gordon Burditt
                  #5
                  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

                    Comment

                    • Dave
                      #6
                      Re: yes/no data type


                      "Thomas Bartkus" <thomasbartkus@ comcast.netwrot e in message
                      news:ZeSdnapfWK kglUzZnZ2dnUVZ_ vWdnZ2d@telcove .net...
                      "Dave" <david@nospam.c o.ukwrote in message
                      news:O06Ag.5628 1$eQ.34919@news fe7-win.ntli.net...
                      >"Thomas Bartkus" <thomasbartkus@ comcast.netwrot e in message
                      >news:UMOdnegW0 a9-KU3ZnZ2dnUVZ_qe dnZ2d@telcove.n et...
                      "Dave" <david@nospam.c o.ukwrote in message
                      news:Bq0Ag.1713 9$WY2.5105@news fe3-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
                      >misinterpretin g
                      >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


                        Comment

                        Previous template Next
                        Working...