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

Firebird 2.5 maximum length for each type of identifier

Expand Messages
  • Roberto Carlos
    The following table describes the MySQL 5.5 maximum length for each type of identifier. It was taken from
    Message 1 of 15 , Nov 10, 2010
    View Source
    • 0 Attachment
      The following table describes the MySQL 5.5 maximum length for each type of identifier. It was taken from http://dev.mysql.com/doc/refman/5.5/en/identifiers.html

      Identifier Maximum Length (characters)
      Database 64
      Table 64
      Column 64
      Index 64
      Constraint 64
      Stored Procedure or Function 64
      Trigger 64
      View 64
      Event 64
      Tablespace 64
      Log File Group 64
      Alias 256 (see exception following table)
      Compound Statement Label 16

      Which would be these limits for Firebird 2.5?

      Thanks,

      Roberto
    • Roberto Carlos
      This are the limits for PostgreSQL: http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html)
      Message 2 of 15 , Nov 11, 2010
      View Source
      • 0 Attachment
        This are the limits for PostgreSQL:
        http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html)
        http://pgdocptbr.sourceforge.net/pg80/infoschema-sql-sizing.html#AEN34757

        And here for Microsoft SQL Server:
        http://msdn.microsoft.com/pt-br/library/ms143432.aspx

        Where can I find this kind of information for Firebird 2.5?

        Thanks,

        Roberto



        Em 10/11/2010 23:37, Roberto Carlos < rc.1990@... > escreveu:




        The following table describes the MySQL 5.5 maximum length for each type of identifier. It was taken from http://dev.mysql.com/doc/refman/5.5/en/identifiers.html

        Identifier Maximum Length (characters)
        Database 64
        Table 64
        Column 64
        Index 64
        Constraint 64
        Stored Procedure or Function 64
        Trigger 64
        View 64
        Event 64
        Tablespace 64
        Log File Group 64
        Alias 256 (see exception following table)
        Compound Statement Label 16

        Which would be these limits for Firebird 2.5?

        Thanks,

        Roberto
      • tomkrej
        Hi, all max.lengths for firebird are 31 characters. Where You can find it? I don t know - try firebird FAQ (http://www.firebirdfaq.org/), or
        Message 3 of 15 , Nov 11, 2010
        View Source
        • 0 Attachment
          Hi, all max.lengths for firebird are 31 characters.

          Where You can find it? I don't know - try firebird FAQ (http://www.firebirdfaq.org/), or http://www.destructor.de/firebird/index.htm
          or some printed documentation

          Not all of Your objects are available in FB

          Tom

          --- In firebird-support@yahoogroups.com, Roberto Carlos <rc.1990@...> wrote:
          >
          > The following table describes the MySQL 5.5 maximum length for each type of identifier. It was taken from http://dev.mysql.com/doc/refman/5.5/en/identifiers.html
          >
          > Identifier Maximum Length (characters)
          > Database 64
          > Table 64
          > Column 64
          > Index 64
          > Constraint 64
          > Stored Procedure or Function 64
          > Trigger 64
          > View 64
          > Event 64
          > Tablespace 64
          > Log File Group 64
          > Alias 256 (see exception following table)
          > Compound Statement Label 16
          >
          > Which would be these limits for Firebird 2.5?
          >
          > Thanks,
          >
          > Roberto
          >
        • Kjell Rilbe
          ... If I m not mistaken, they are max 32 *byte*. If you use UTF-8 and some chars are multi-byte, the max number of characters goes down in proportion to the
          Message 4 of 15 , Nov 12, 2010
          View Source
          • 0 Attachment
            tomkrej skriver:
            > Hi, all max.lengths for firebird are 31 characters.

            If I'm not mistaken, they are max 32 *byte*. If you use UTF-8 and some
            chars are multi-byte, the max number of characters goes down in
            proportion to the number of extra bytes used by multi-byte chars.

            Not sure if identifiers are always stored in UTF-8 or if the DB default
            charset or something else determines this. Anyone know?

            Regards,
            Kjell
            --
            ------------------------------
            Kjell Rilbe
            DataDIA AB
            E-post: kjell.rilbe@...
            Telefon: 08-761 06 55
            Mobil: 0733-44 24 64


            [Non-text portions of this message have been removed]
          • Helen Borrie
            ... You are mistaken, unfortunately. 31 *characters* is correct. Furthermore, I believe it was demonstrated at some point that the identifiers of triggers
            Message 5 of 15 , Nov 12, 2010
            View Source
            • 0 Attachment
              >tomkrej skriver:
              >> Hi, all max.lengths for firebird are 31 characters.

              At 09:56 PM 12/11/2010, Kjell Rilbe wrote:

              >If I'm not mistaken, they are max 32 *byte*.

              You are mistaken, unfortunately. 31 *characters* is correct. Furthermore, I believe it was demonstrated at some point that the identifiers of triggers (and possibly also stored procedures) will overflow if defined as 28 characters or more.

              >If you use UTF-8 and some
              >chars are multi-byte, the max number of characters goes down in
              >proportion to the number of extra bytes used by multi-byte chars.

              Identifiers (including user names) are stored in UNICODE_FSS, three bytes per character, no variability.

              >Not sure if identifiers are always stored in UTF-8

              Never.

              >or if the DB default charset or something else determines this. Anyone know?

              The DB default charset doesn't influence the charset of identifiers.

              ./heLen
            • Kjell Rilbe
              ... OK, but then please explain this: -- 31 single-char characters. create table X234567890123456789012345678901 ( X234567890123456789012345678901 int ) --
              Message 6 of 15 , Nov 12, 2010
              View Source
              • 0 Attachment
                Helen Borrie skriver:
                > >tomkrej skriver:
                > >> Hi, all max.lengths for firebird are 31 characters.
                >
                > At 09:56 PM 12/11/2010, Kjell Rilbe wrote:
                >
                > >If I'm not mistaken, they are max 32 *byte*.
                >
                > You are mistaken, unfortunately. 31 *characters* is correct.
                > Furthermore, I believe it was demonstrated at some point that the
                > identifiers of triggers (and possibly also stored procedures) will
                > overflow if defined as 28 characters or more.
                >
                > >If you use UTF-8 and some
                > >chars are multi-byte, the max number of characters goes down in
                > >proportion to the number of extra bytes used by multi-byte chars.
                >
                > Identifiers (including user names) are stored in UNICODE_FSS, three
                > bytes per character, no variability.

                OK, but then please explain this:

                -- 31 single-char characters.
                create table "X234567890123456789012345678901" (
                "X234567890123456789012345678901" int
                )
                -- No errors here.

                -- 31 characters, one of which requires 2 byte in UTF-8.
                create table "�234567890123456789012345678901" (
                "�234567890123456789012345678901" int
                )

                Error: *** IBPP::SQLException ***
                Context: Statement::Execute( create table
                "��234567890123456789012345678901" (
                "��234567890123456789012345678901" int
                )
                )
                Message: isc_dsql_execute2 failed

                SQL Message : -607
                This operation is not defined for system tables.

                Engine Code : 335544351
                Engine Message :
                unsuccessful metadata update
                Name longer than database column size

                This was executed over a UTF-8 connection, so maybe it's FlameRobin or
                IBPP that transliterates from UTF-8 to iso-8859-1 or whatever, resulting
                in 32 characters, that are later transliterated to UNICODE-FSS resulting
                in 32 * 3 byte.

                Seems convoluted. Does fbclient expect identifiers to be passed already
                encoded in UNICODE-FSS or what char encoding does it expect? Is it the
                connection charset that determines this? If so, then fbclient seems to
                fail in transliterating the above strings directly to UNICODE-FSS.

                Regards,
                Kjell
                --
                ------------------------------
                Kjell Rilbe
                DataDIA AB
                E-post: kjell.rilbe@...
                Telefon: 08-761 06 55
                Mobil: 0733-44 24 64


                [Non-text portions of this message have been removed]
              • Mark Rotteveel
                ... The InterBase 6 manual statest that constraints are limited to 27 characters. So it is not really unexpected. -- GMX DSL Doppel-Flat ab 19,99 €/mtl.!
                Message 7 of 15 , Nov 12, 2010
                View Source
                • 0 Attachment
                  > You are mistaken, unfortunately. 31 *characters* is correct.
                  > Furthermore, I believe it was demonstrated at some point that the identifiers of
                  > triggers (and possibly also stored procedures) will overflow if defined as 28
                  > characters or more.

                  The InterBase 6 manual statest that constraints are limited to 27 characters. So it is not really unexpected.


                  --
                  GMX DSL Doppel-Flat ab 19,99 €/mtl.! Jetzt auch mit
                  gratis Notebook-Flat! http://portal.gmx.net/de/go/dsl
                • Kjell Rilbe
                  ... In fact, it s more likely that FR, ibpp or fbclient doesn t transliterate the received UTF-8 string at all, but expects it encoded in such a way that it
                  Message 8 of 15 , Nov 12, 2010
                  View Source
                  • 0 Attachment
                    Kjell Rilbe skriver:
                    > -- 31 characters, one of which requires 2 byte in UTF-8.
                    > create table "�234567890123456789012345678901" (
                    > "�234567890123456789012345678901" int
                    > )
                    >
                    > Error: *** IBPP::SQLException ***
                    > Context: Statement::Execute( create table
                    > "��234567890123456789012345678901" (
                    > "��234567890123456789012345678901" int
                    > )
                    > )
                    > Message: isc_dsql_execute2 failed
                    >
                    > SQL Message : -607
                    > This operation is not defined for system tables.
                    >
                    > Engine Code : 335544351
                    > Engine Message :
                    > unsuccessful metadata update
                    > Name longer than database column size
                    >
                    > This was executed over a UTF-8 connection, so maybe it's FlameRobin or
                    > IBPP that transliterates from UTF-8 to iso-8859-1 or whatever, resulting
                    > in 32 characters, that are later transliterated to UNICODE-FSS resulting
                    > in 32 * 3 byte.
                    >
                    > Seems convoluted. Does fbclient expect identifiers to be passed already
                    > encoded in UNICODE-FSS or what char encoding does it expect? Is it the
                    > connection charset that determines this? If so, then fbclient seems to
                    > fail in transliterating the above strings directly to UNICODE-FSS.

                    In fact, it's more likely that FR, ibpp or fbclient doesn't
                    transliterate the received UTF-8 string at all, but expects it encoded
                    in such a way that it appears to be 32 characters, as displayed in the
                    error message above. So who's getting it wrong?

                    Kjell
                    --
                    ------------------------------
                    Kjell Rilbe
                    DataDIA AB
                    E-post: kjell.rilbe@...
                    Telefon: 08-761 06 55
                    Mobil: 0733-44 24 64


                    [Non-text portions of this message have been removed]
                  • Sean
                    Does anyone know why are they are still restricted to only 31 characters? Why can t they be increased to 64 characters? This is a major headache especially
                    Message 9 of 15 , Nov 12, 2010
                    View Source
                    • 0 Attachment
                      Does anyone know why are they are still restricted to only 31 characters? Why can't they be increased to 64 characters?

                      This is a major headache especially when porting databases from other databases e.g.. MS SQL

                      Sean.

                      --- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
                      >
                      >
                      > >tomkrej skriver:
                      > >> Hi, all max.lengths for firebird are 31 characters.
                      >
                      > At 09:56 PM 12/11/2010, Kjell Rilbe wrote:
                      >
                      > >If I'm not mistaken, they are max 32 *byte*.
                      >
                      > You are mistaken, unfortunately. 31 *characters* is correct. Furthermore, I believe it was demonstrated at some point that the identifiers of triggers (and possibly also stored procedures) will overflow if defined as 28 characters or more.
                      >
                      > >If you use UTF-8 and some
                      > >chars are multi-byte, the max number of characters goes down in
                      > >proportion to the number of extra bytes used by multi-byte chars.
                      >
                      > Identifiers (including user names) are stored in UNICODE_FSS, three bytes per character, no variability.
                      >
                      > >Not sure if identifiers are always stored in UTF-8
                      >
                      > Never.
                      >
                      > >or if the DB default charset or something else determines this. Anyone know?
                      >
                      > The DB default charset doesn't influence the charset of identifiers.
                      >
                      > ./heLen
                      >
                    • Lester Caine
                      ... Simply 25 years worth of backwards compatibility with tools and the like. If it was going to be easy to change then it would have happened, but it is not
                      Message 10 of 15 , Nov 12, 2010
                      View Source
                      • 0 Attachment
                        Sean wrote:
                        > Does anyone know why are they are still restricted to only 31 characters? Why can't they be increased to 64 characters?
                        Simply 25 years worth of backwards compatibility with tools and the like.
                        If it was going to be easy to change then it would have happened, but it is not
                        simply a matter of changing some limit, it will require some major coordination
                        to allow it to happen ... when it does become practical.

                        --
                        Lester Caine - G8HFL
                        -----------------------------
                        Contact - http://lsces.co.uk/wiki/?page=contact
                        L.S.Caine Electronic Services - http://lsces.co.uk
                        EnquirySolve - http://enquirysolve.com/
                        Model Engineers Digital Workshop - http://medw.co.uk//
                        Firebird - http://www.firebirdsql.org/index.php
                      • Kjell Rilbe
                        Maybe it would be possible to use a compatibility setting, along the lines of SQL dialect 1 and 3. That must have induced a lot of changes in a lot of tools,
                        Message 11 of 15 , Nov 18, 2010
                        View Source
                        • 0 Attachment
                          Maybe it would be possible to use a compatibility setting, along the
                          lines of SQL dialect 1 and 3. That must have induced a lot of changes in
                          a lot of tools, but "they made it". Same goes for identifier length, and
                          would probably induce a lot less changes in the tools.

                          I'm not sayin there should be a dialect 4 with longer identifiers, but
                          perhaps a new DB-level setting?

                          Kjell

                          Lester Caine skriver:
                          >
                          >
                          > Sean wrote:
                          > > Does anyone know why are they are still restricted to only 31
                          > characters? Why can't they be increased to 64 characters?
                          > Simply 25 years worth of backwards compatibility with tools and the like.
                          > If it was going to be easy to change then it would have happened, but it
                          > is not
                          > simply a matter of changing some limit, it will require some major
                          > coordination
                          > to allow it to happen ... when it does become practical.
                          >
                          > --
                          > Lester Caine - G8HFL
                          > -----------------------------
                          > Contact - http://lsces.co.uk/wiki/?page=contact
                          > L.S.Caine Electronic Services - http://lsces.co.uk
                          > EnquirySolve - http://enquirysolve.com/
                          > Model Engineers Digital Workshop - http://medw.co.uk//
                          > Firebird - http://www.firebirdsql.org/index.php
                          >
                          >

                          --
                          ------------------------------
                          Kjell Rilbe
                          DataDIA AB
                          E-post: kjell.rilbe@...
                          Telefon: 08-761 06 55
                          Mobil: 0733-44 24 64


                          [Non-text portions of this message have been removed]
                        • Ann W. Harrison
                          ... No, no, no. Multiple SQL dialects was a disaster that was countenanced only because there was an incompatible change to a datatype that the developers
                          Message 12 of 15 , Nov 19, 2010
                          View Source
                          • 0 Attachment
                            On 11/19/2010 1:22 AM, Kjell Rilbe wrote:
                            > Maybe it would be possible to use a compatibility setting, along the
                            > lines of SQL dialect 1 and 3. That must have induced a lot of changes in
                            > a lot of tools, but "they made it". Same goes for identifier length, and
                            > would probably induce a lot less changes in the tools.
                            >
                            > I'm not sayin there should be a dialect 4 with longer identifiers, but
                            > perhaps a new DB-level setting?
                            >

                            No, no, no. Multiple SQL dialects was a disaster that was countenanced
                            only because there was an incompatible change to a datatype that the
                            developers couldn't find a way around. It was (and still is) awful.

                            The problem with extending the length of identifiers is just that it's
                            a lot of work. Existing tools can still work unchanged on databases
                            that still use short names - the API separates the field definition from
                            buffer definitions - so the transition does not have to happen
                            simultaneously.

                            No more modes!

                            Best regards,


                            Ann
                          • Kjell Rilbe
                            ... OK, great! So is is it internally in FB it s a lot of work to bump up the max identifier length? Or were you referring to tools? Kjell -- ... Kjell Rilbe
                            Message 13 of 15 , Nov 19, 2010
                            View Source
                            • 0 Attachment
                              Ann W. Harrison skriver:
                              > On 11/19/2010 1:22 AM, Kjell Rilbe wrote:
                              > The problem with extending the length of identifiers is just that it's
                              > a lot of work. Existing tools can still work unchanged on databases
                              > that still use short names - the API separates the field definition from
                              > buffer definitions - so the transition does not have to happen
                              > simultaneously.

                              OK, great! So is is it internally in FB it's a lot of work to bump up
                              the max identifier length? Or were you referring to tools?

                              Kjell
                              --
                              ------------------------------
                              Kjell Rilbe
                              DataDIA AB
                              E-post: kjell.rilbe@...
                              Telefon: 08-761 06 55
                              Mobil: 0733-44 24 64


                              [Non-text portions of this message have been removed]
                            • Ann W. Harrison
                              ... Work all over the place, messing up what others are trying to do and not gratifying. Hey guys, we lived with 8.3 names for decades. Why can t you kids
                              Message 14 of 15 , Nov 19, 2010
                              View Source
                              • 0 Attachment
                                On 11/19/2010 10:49 AM, Kjell Rilbe wrote:
                                > Ann W. Harrison skriver:
                                >> On 11/19/2010 1:22 AM, Kjell Rilbe wrote:
                                >> The problem with extending the length of identifiers is just that it's
                                >> a lot of work. Existing tools can still work unchanged on databases
                                >> that still use short names - the API separates the field definition from
                                >> buffer definitions - so the transition does not have to happen
                                >> simultaneously.
                                >
                                > OK, great! So is is it internally in FB it's a lot of work to bump up
                                > the max identifier length? Or were you referring to tools?

                                Work all over the place, messing up what others are trying to do and
                                not gratifying. Hey guys, we lived with 8.3 names for decades. Why
                                can't you kids show some discipline?

                                Cheers,

                                Ann
                              • Kjell Rilbe
                                ... It s a bit confusing with identifiers like EmailcharencodingEmailJ8M and such, which ECO has to generate to be able to squeeze the real name into the
                                Message 15 of 15 , Nov 19, 2010
                                View Source
                                • 0 Attachment
                                  Ann W. Harrison skriver:
                                  >
                                  > On 11/19/2010 10:49 AM, Kjell Rilbe wrote:
                                  > > Ann W. Harrison skriver:
                                  > >> On 11/19/2010 1:22 AM, Kjell Rilbe wrote:
                                  > >> The problem with extending the length of identifiers is just that it's
                                  > >> a lot of work. Existing tools can still work unchanged on databases
                                  > >> that still use short names - the API separates the field definition from
                                  > >> buffer definitions - so the transition does not have to happen
                                  > >> simultaneously.
                                  > >
                                  > > OK, great! So is is it internally in FB it's a lot of work to bump up
                                  > > the max identifier length? Or were you referring to tools?
                                  >
                                  > Work all over the place, messing up what others are trying to do and
                                  > not gratifying. Hey guys, we lived with 8.3 names for decades. Why
                                  > can't you kids show some discipline?

                                  It's a bit confusing with identifiers like "EmailcharencodingEmailJ8M"
                                  and such, which ECO has to generate to be able to squeeze the real name
                                  into the limited number of characters without causing duplicate names.

                                  But I sympathize with you guys who do the devel work. I know it's not
                                  fun to rework such a thing. Very tiresome and error prone. :-(

                                  Kjell
                                  --
                                  --------------------------------------
                                  Kjell Rilbe
                                  DataDIA AB
                                  E-post: kjell@...
                                  Telefon: 08-761 06 55
                                  Mobil: 0733-44 24 64
                                Your message has been successfully submitted and would be delivered to recipients shortly.