Board index » delphi » Alter table before an update

Alter table before an update

Hi!

ALTER TABLE SYS_CIE ADD SHOW_NEWS CHAR(1)
Update sys_cie set show_news = "T"

These two lines are in a text file. My application opens the file, reads one
line, put the line in a TIBSQL component, execute it and do a
Transaction.Commit (not a CommitRetaining), reads the next line, etc...

Here's the Sys_Cie structure before the alter table:

CREATE TABLE SYS_CIE (
    NOM_CIE DOM_NOM100,
    ADRE_CIE DOM_ADRESSE,
    VILL_CIE DOM_VILLE,
    PROV_CIE DOM_PROVINCE,
    PAYS_CIE DOM_PAYS,
    CODE_POST DOM_CODEPOSTAL,
    BOIT_POST DOM_BOITEPOSTALE,
    TEL_CIE DOM_NOTEL,
    FAX_CIE DOM_NOTEL,
    CELL_CIE DOM_NOTEL,
    LANG_CIE DOM_LOGIQUE,
    MESS_SOUM DOM_MEMO,
    NO_TPS DOM_TAXES,
    NO_TVQ DOM_TAXES,
    NO_SOUMM DOM_ID,
    LAST_UPDT DOM_DATE,
    LAST_NO_UPDT INTEGER,
    LAST_NO_LINE_UPDT INTEGER,
    USER_NAME DOM_USER_NAME,
    PASS_WORD DOM_PASS_WORD,
    SHOW_MESSAGE_1 CHAR (1));

After the execution of the Alter and the Update, the SHOW_NEWS field is
there, but it's the field NOM_CIE that has been updated. In another file, I
have another Alter table:

ALTER TABLE SYS_CIE ADD REP_UPDT CHAR(1)
Update sys_cie set rep_updt = "3"

The same occurs here. The field REP_UPDT is there, but it's the field
NOM_CIE that is updated.

I did some test. When the TIBDatabase is closed and reopened between the
alter table and the update, it works fine.

I don't really understand why. The transaction is closed by my
transaction.commit, so the following transactions should see the changes...

Please help with this problem.

Thanks

VB

 

Re:Alter table before an update


Hi Vincent,

Quote
> I did some test. When the TIBDatabase is closed and reopened between the
> alter table and the update, it works fine.

This behaviour is as designed.

The Docu (EmbedSQL) says  :

Dropping an existing column
To delete a column definition and its data from a table, use ALTER TABLE. A
column can
only be dropped by the owner of the table, the SYSDBA, or a user with root
privileges. If
a table is in use when a column is dropped, the drop is postponed until the
table is no
longer in use.

So there no way around close and reopen.

Be aware : After ~ 250 changes of your metadata you have do do a backup
and a restore of your database.

HTH Achim

Other Threads