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

cannot alter column collate. does it exists in FB 2.0?

Expand Messages
  • emb_blaster
    hi all, I´m trying this SQL (FB 2.0.4 Win32): alter table NEW_TABLE alter column col_temp type varchar(14) character set WIN1252 collate PXW_INTL850 This is
    Message 1 of 6 , Jan 27, 2009
    View Source
    • 0 Attachment
      hi all,
      I´m trying this SQL (FB 2.0.4 Win32):

      alter table NEW_TABLE
      alter column col_temp
      type varchar(14)
      character set WIN1252
      collate PXW_INTL850

      This is the error:

      Invalid token.
      Dynamic SQL Error.
      SQL error code = -104.
      Token unknown - line 5, column 1.
      collate.

      searching I don´t found that this is not possible. May be I
      misunderstd the docs from interbase60:

      ALTER TABLE table <operation> [, <operation> …];

      <operation> = {ADD <col_def>
      | ADD <tconstraint>

      | ALTER [COLUMN] column_name <alt_col_clause>
      | DROP col
      | DROP CONSTRAINT constraint}

      <alt_col_clause> = {TO new_col_name
      | TYPE new_col_datatype
      | POSITION new_col_position}

      <col_def> = col {<datatype> | COMPUTED [BY] (<expr>) | domain}

      [DEFAULT {literal | NULL | USER}]

      [NOT NULL]

      [<col_constraint>]

      [COLLATE collation]
    • Paul Vinkenoog
      ... Yes, but you are forgiven because it s easy to be misled here. This is what the IB6 LangRef says about altering colums: = {TO new_col_name
      Message 2 of 6 , Jan 27, 2009
      View Source
      • 0 Attachment
        emb_blaster wrote:

        > I´m trying this SQL (FB 2.0.4 Win32):
        >
        > alter table NEW_TABLE
        > alter column col_temp
        > type varchar(14)
        > character set WIN1252
        > collate PXW_INTL850
        >
        > This is the error:
        >
        > Invalid token.
        > Dynamic SQL Error.
        > SQL error code = -104.
        > Token unknown - line 5, column 1.
        > collate.
        >
        > searching I don´t found that this is not possible. May be I
        > misunderstd the docs from interbase60:

        Yes, but you are forgiven because it's easy to be misled here.

        This is what the IB6 LangRef says about altering colums:

        <alt_col_clause> =
        {TO new_col_name
        | TYPE new_col_datatype
        | POSITION new_col_position}

        Now, new_col_datatype is not broken down any further, but from what follows under <col_def>:

        <col_def> =
        col {<datatype> | COMPUTED [BY] (<expr>) | domain}
        [DEFAULT {literal | NULL | USER}]
        [NOT NULL]
        [<col_constraint>]
        [COLLATE collation]

        ...you can gather that COLLATE is not part of the datatype specification.

        And from <datatype>:

        <datatype> =
        ...
        | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)]
        [<array_dim>] [CHARACTER SET charname]
        ...

        ...it follows that CHARACTER SET *is* part of the datatype specification.

        So the documentation is correct here: IB6 doesn't support a COLLATE clause with ALTER COLUMN -- and neither does Firebird.

        But it *is* a shortcoming IMO - maybe you want to add it as a feature request on tracker.firebirdsql.org, if somebody else hasn't already done that.


        Regards,
        Paul Vinkenoog
      • Paul Vinkenoog
        Hi, ... A nice workaround would seem to define a domain with the desired type, charset and collation, and then alter the column s type like this: alter table T
        Message 3 of 6 , Jan 27, 2009
        View Source
        • 0 Attachment
          Hi,

          > So the documentation is correct here: IB6 doesn't support a COLLATE clause with ALTER COLUMN -- and neither does Firebird.

          A nice workaround would seem to define a domain with the desired type, charset and collation, and then alter the column's type like this:

          alter table T alter MyColumn type MyCollatedDomain

          ...but alas, this will give MyColumn the type and charset of MyCollatedDomain, but *not* its collation.

          If you add a column to T with type MyCollatedDomain, it *will* get the collation.


          Just thought I'd let you know, to cheer you up ;-)


          Paul Vinkenoog
        • emb_blaster
          Many thanks Paul, as I feared... I was thinking in creating temporary tables copy the data from original ones, drop original ones. Then I can recreate with the
          Message 4 of 6 , Jan 28, 2009
          View Source
          • 0 Attachment
            Many thanks Paul,

            as I feared... I was thinking in creating temporary tables copy the
            data from original ones, drop original ones. Then I can recreate with
            the correct colation and insert the data from temporary table. I don´t
            see another way... there´s any?

            For example:
            CREATE TABLE temp_tb
            (
            columns....
            MyColumn VARCHAR(xx) CHARACTER SET WIN1252 COLLATE PXW_INTL850,
            columns....
            );

            INSERT
            INTO temp_tb
            SELECT * FROM original_table;

            DROP TABLE original_table;

            CREATE TABLE original_table
            (
            columns....
            MyColumn VARCHAR(xx) CHARACTER SET WIN1252 COLLATE PXW_INTL850,
            columns....
            );

            INSERT
            INTO original_table
            SELECT * FROM temp_tb;



            --- In firebird-support@yahoogroups.com, Paul Vinkenoog <paul@...> wrote:
            >
            > Hi,
            >
            > > So the documentation is correct here: IB6 doesn't support a
            COLLATE clause with ALTER COLUMN -- and neither does Firebird.
            >
            > A nice workaround would seem to define a domain with the desired
            type, charset and collation, and then alter the column's type like this:
            >
            > alter table T alter MyColumn type MyCollatedDomain
            >
            > ...but alas, this will give MyColumn the type and charset of
            MyCollatedDomain, but *not* its collation.
            >
            > If you add a column to T with type MyCollatedDomain, it *will* get
            the collation.
            >
            >
            > Just thought I'd let you know, to cheer you up ;-)
            >
            >
            > Paul Vinkenoog
            >
          • Paul Vinkenoog
            Hi emb_blaster, ... Just creating a new column ought to be enough. Create it with the desired collation, then: update table MyTable set NewColumn = OldColumn
            Message 5 of 6 , Jan 28, 2009
            View Source
            • 0 Attachment
              Hi emb_blaster,

              > as I feared... I was thinking in creating temporary tables copy the
              > data from original ones, drop original ones. Then I can recreate with
              > the correct colation and insert the data from temporary table. I don´t
              > see another way... there´s any?

              Just creating a new column ought to be enough. Create it with the desired collation, then:

              update table MyTable set NewColumn = OldColumn

              After that:

              alter table MyTable drop OldColumn

              Then rename the new column:

              alter table MyTable alter NewColumn to OldColumn

              And optionally reposition it to where the original OldColumn used to be:

              alter table MyTable alter OldColumn position ...


              Greetings,
              Paul Vinkenoog
            • emb_blaster
              Haha! Many thanks again! Best regards
              Message 6 of 6 , Jan 28, 2009
              View Source
              • 0 Attachment
                Haha!

                Many thanks again!

                Best regards


                --- In firebird-support@yahoogroups.com, Paul Vinkenoog <paul@...> wrote:
                >
                > Hi emb_blaster,
                >
                >
                > Just creating a new column ought to be enough. Create it with the
                desired collation, then:
                >
                > update table MyTable set NewColumn = OldColumn
                >
                > After that:
                >
                > alter table MyTable drop OldColumn
                >
                > Then rename the new column:
                >
                > alter table MyTable alter NewColumn to OldColumn
                >
                > And optionally reposition it to where the original OldColumn used to be:
                >
                > alter table MyTable alter OldColumn position ...
                >
                >
                > Greetings,
                > Paul Vinkenoog
                >
              Your message has been successfully submitted and would be delivered to recipients shortly.