Loading ...
Sorry, an error occurred while loading the content.

Equivalent to ENUM?

Expand Messages
  • Robin Davis
    Hello folks, Is there an equivalent to ENUM(-1,0) which I can use for boolean fields. I am trying to ween my office off Microsoft Access as theor database, but
    Message 1 of 6 , Feb 26, 2006
    • 0 Attachment
      Hello folks,

      Is there an equivalent to ENUM(-1,0) which I can use for boolean fields.
      I am trying to ween my office off Microsoft Access as theor database,
      but they simply won't stop using Access as the front-end. We have made
      extensive use of checkboxes in the past to speed up the work but I'm
      afraid they'll choke on using Firebird unless I can provide a totally
      transparent chage over for them. They are not interested in the
      background advantages of the change over, they just want EXACTLY the
      same interface they're used to. I've solved the case insensitive search
      issue, but this checkbox lark could be a deal breaker.

      Alternatively, how do I get access to use 1 and 0 instead of -1 and 0 in
      checkboxes? ;-)

      Sorry if this is poor question, not totally my fault. ;-)

      Rob Davis
    • Robin Davis
      Sorry for that badly formed message, I was very tired when I wrote that! To explain more fully, they want to use Access as their front end, but I want to use
      Message 2 of 6 , Feb 27, 2006
      • 0 Attachment
        Sorry for that badly formed message, I was very tired when I wrote that!

        To explain more fully, they want to use Access as their front end, but I
        want to use OpenOffice. Access sees boolean as 0 and -1, and Openoffice
        sees boolean as 0 and 1, so I just wondered if there was a way of
        forcing either program to match the other, as ENUM does in MySql.

        Rob Davis



        Robin Davis wrote:
        > Hello folks,
        >
        > Is there an equivalent to ENUM(-1,0) which I can use for boolean fields.
        > I am trying to ween my office off Microsoft Access as theor database,
        > but they simply won't stop using Access as the front-end. We have made
        > extensive use of checkboxes in the past to speed up the work but I'm
        > afraid they'll choke on using Firebird unless I can provide a totally
        > transparent chage over for them. They are not interested in the
        > background advantages of the change over, they just want EXACTLY the
        > same interface they're used to. I've solved the case insensitive search
        > issue, but this checkbox lark could be a deal breaker.
        >
        > Alternatively, how do I get access to use 1 and 0 instead of -1 and 0 in
        > checkboxes? ;-)
        >
        > Sorry if this is poor question, not totally my fault. ;-)
        >
        > Rob Davis
        >
        >
        >
        >
        > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        >
        > Visit http://firebird.sourceforge.net and click the Resources item
        > on the main (top) menu. Try Knowledgebase and FAQ links !
        >
        > Also search the knowledgebases at http://www.ibphoenix.com
        >
        > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        >
        > Yahoo! Groups Links
        >
        >
        >
        >
        >
        >
        >
      • vogonjeltzprostetnic
        Are you trying to emulate a boolean data type in Firebird? If so, a domain will be your ticket (until such time as Firebird implements booleans, that is):
        Message 3 of 6 , Feb 27, 2006
        • 0 Attachment
          Are you trying to emulate a boolean data type in Firebird? If so, a
          domain will be your ticket (until such time as Firebird implements
          booleans, that is):

          CREATE DOMAIN D_BOOLEAN AS SMALLINT
          CHECK (VALUE IS NULL OR VALUE IN (-1, 0));

          This places the desired restriction on any columns defined using the
          domain. Make sure, of course, that you don't actually name the domain
          BOOLEAN, since this is a Firebird reserved word.

          -Marc Benedict
        • Adam
          ... fields. ... 0 in ... Firebird does not have a boolean datatype at all. It has something called a domain which can (and should) be used for defining a
          Message 4 of 6 , Feb 27, 2006
          • 0 Attachment
            > Hello folks,
            >
            > Is there an equivalent to ENUM(-1,0) which I can use for boolean
            fields.
            > I am trying to ween my office off Microsoft Access as theor database,
            > but they simply won't stop using Access as the front-end. We have made
            > extensive use of checkboxes in the past to speed up the work but I'm
            > afraid they'll choke on using Firebird unless I can provide a totally
            > transparent chage over for them. They are not interested in the
            > background advantages of the change over, they just want EXACTLY the
            > same interface they're used to. I've solved the case insensitive search
            > issue, but this checkbox lark could be a deal breaker.
            >
            > Alternatively, how do I get access to use 1 and 0 instead of -1 and
            0 in
            > checkboxes? ;-)

            Firebird does not have a boolean datatype at all. It has something
            called a 'domain' which can (and should) be used for defining a custom
            type which only accepts two values, we also have one that allows nulls
            for when that is appropriate. We use a field that only accepts 'T' or
            'F' based on char(1), others use a smallint 0,1 etc. In other words,
            if access feeds it -1 or 0, then Firebird will store -1 or 0. It
            doesn't care what they stand for. So it is not really a problem for
            Firebird. Perhaps you could clarify what you think the problem will be?

            You could create a UDF function to convert one to the other, or use
            triggers to do the translation before insert/update.

            Adam
          • The Wogster
            ... Probably the best solution would be a trigger, then code the inside like this: CREATE TRIGGER FIX_BOOL_FIELD FOR MY_TABLE BEFORE UPDATE BEGIN IF (
            Message 5 of 6 , Feb 27, 2006
            • 0 Attachment
              Robin Davis wrote:
              > Sorry for that badly formed message, I was very tired when I wrote that!
              >
              > To explain more fully, they want to use Access as their front end, but I
              > want to use OpenOffice. Access sees boolean as 0 and -1, and Openoffice
              > sees boolean as 0 and 1, so I just wondered if there was a way of
              > forcing either program to match the other, as ENUM does in MySql.

              Probably the best solution would be a trigger, then code the inside like
              this:


              CREATE TRIGGER FIX_BOOL_FIELD FOR MY_TABLE BEFORE UPDATE
              BEGIN
              IF ( NEW.BOOLFIELD <> 0 ) THEN NEW.BOOLFIELD = -1;
              END;


              Where BOOLFIELD is the "boolean" field you want.

              This would really allow the Front end to set the boolean to anything,
              since true is actually not false. Realistically Access or OpenOffice
              should assume that 0 is false, and anything else is true, for a checkbox.

              One thing you do want to do, is make sure that Access is using native
              SQL, Access traditionally does things within the client, that should be
              done in the database. For example say your Customer Table has 1,000,000
              records and your parts table has 1,000,000,000 records, your orders
              table contains both the customer number and part number and you want to
              read a specific orders customer and parts detail. A three table join
              passed to Access will read the ENTIRE customer table, parts table and
              order_detail table, and execute it's own SQL on the result. Performance
              wise this really, really sucks.

              You also want to build all the business rules into the database, and
              gradually move the users away from General purpose tools to a specific
              application that does what they need to do.

              W
            • Robin Davis
              Hi, Thank you to all for their advice, I have now solved the boolean problem. Now, if we could just get OpenOffice and Firebird to talk to each other about the
              Message 6 of 6 , Mar 1, 2006
              • 0 Attachment
                Hi,

                Thank you to all for their advice, I have now solved the boolean
                problem. Now, if we could just get OpenOffice and Firebird to talk to
                each other about the auto-increment problem, we'd have it sorted! ;-)

                Thanks again,

                Rob Davis
              Your message has been successfully submitted and would be delivered to recipients shortly.