Datenbank Tabellen aktualisierung

[manfred_h]

Hallo zusammen

seit einiger Zeit versuche ich in meiner Anwendung eine Funktion zu integrieren um die
Datenbank Tabellen zu aktualisieren. Auf dem Entwiklungssystem funktioniert das einwandfrei.
Auf einer Windows XP Testinstallation ( VMWare ) wird ein Script ( zur DB aktualisierung ) nicht ausgeführt. Das Problem ist bei der Stelle an der db_version_upgrade.sql aufgerufen wird.
( Die Message Boxen sind nur zu Testzwecken integriert )

hier der Delphi-Code:

Delphi-Quellcode:

procedure TDM_update.db_convert(Sender: TObject);
begin
  // Do Backup !!
  begin
    DM.DB_Gideons.Connected:= false;
    db_backupfile := db_dir +('\gideons_db_backup_bevor_db_upgrade.gbk');
    dm.db_backup(Sender);
  end;
  //
  // restore the DB
  begin
    DM.DB_Gideons.Connected:= false;
    db_backupfile := (db_dir+('\gideons_db_backup_bevor_db_upgrade.gbk'));
    DM.DB_Gideons.Connected:= true;
  end;
  //
  // Creating the procedures in the database
  DM.pFIBScript.Script.LoadFromFile (ScriptsDir+'metadata_charset_create.sql');
  DM.pFIBScript.ExecuteScript;
  //
  // Removing the upgrade procedures
  DM.pFIBScript.Script.LoadFromFile (ScriptsDir+'metadata_charset_drop.sql');
  DM.pFIBScript.ExecuteScript;
//  //
  // Convert the Database
  MessageBox(0, 'Datenbank Konertierung', 'Datenbank Konertierung', MB_ICONINFORMATION or MB_OK);
  DM.pFIBScript.Script.LoadFromFile (ScriptsDir+'db_version_upgrade.sql');
  DM.pFIBScript.ValidateScript;
  MessageBox(0, 'ValidateScript', 'ValidateScript', MB_ICONINFORMATION or MB_OK);
  DM.pFIBScript.ExecuteScript;
  MessageBox(0, 'Datenbank Konertierung OK ', 'Datenbank Konertierung OK ', MB_ICONINFORMATION or MB_OK);
  //
  with DM.db_vers do
  begin
    DM.db_vers.open;
    DM.db_vers.Edit;
    FieldByName('ID').AsInteger:= 1;
    FieldByName('DB_DATE').AsDateTime := now;
    FieldByName('MAJOR').AsInteger:= 2;
    FieldByName('MINOR').AsInteger:= 1;
    FieldByName('REL').AsInteger:=   0;
    FieldByName('BUILD').AsInteger:= 41;
    FieldByName('SCRIPT').AsString:=('NULL');
    FieldByName('RUNNED').AsString:=('1');
    DM.db_vers.Post;
  end;
end;

und dies ist der SQL-Code von db_version_upgrade.sql:
( In IB Expert lässt sich dieser auch einwandfrei ausführen )

SQL-Code:

/* Create Domain... */
CREATE DOMAIN "D_DATE" AS DATE;
CREATE DOMAIN "D_INTEGER" AS INTEGER;
CREATE DOMAIN "D_SMALLINT" AS SMALLINT;
CREATE DOMAIN "D_TIME" AS DATE;

/*COMMIT WORK;*/

DROP TABLE DB_VERSION;
COMMIT WORK;

/* Create Table... */
CREATE TABLE DB_VERSION (
    ID       D_INTEGER NOT NULL,
    DB_DATE  D_DATE NOT NULL,
    MAJOR    D_INTEGER NOT NULL,
    MINOR    D_INTEGER NOT NULL,
    REL      D_INTEGER NOT NULL,
    BUILD    D_INTEGER NOT NULL,
    SCRIPT   VARCHAR(32762) CHARACTER SET ASCII,
    RUNNED   D_SMALLINT
);
COMMIT WORK;


/* Create Table... */
CREATE TABLE "BOOKKEEPING_BANK"("ID" "ID" NOT NULL,
"C_NO" "T10",
"U_NO" "T10",
"ACNO" "D_INTEGER",
"BANK_ACCOUNT" "T50",
"L_UPDATE" "TIME_S");

COMMIT WORK;

CREATE TABLE "BOOKKEEPING_BOOKING"("ID" "ID" NOT NULL,
"C_NO" "T10",
"U_NO" "T10",
"BDATE" "D_DATE",
"VOUCHER_NO" "D_INTEGER",
"BTEXT" "T130",
"AMOUNT" "AMOUNT",
"DEBIT_ACNO" "D_INTEGER",
"CREDIT_ACNO" "D_INTEGER",
"DEBIT_ACTYPE" "D_INTEGER",
"CREDIT_ACTYPE" "D_INTEGER",
"L_UPDATE" "TIME_S");

COMMIT WORK;

CREATE TABLE "BOOKKEEPING_BOOKING_PRINT"("C_NO" "T10",
"U_NO" "T10",
"ACNO" "D_INTEGER",
"ACTEXT" "T130",
"BDATE" "D_DATE",
"VOUCHER_NO" "D_INTEGER",
"BTEXT" "T130",
"DEBIT_AMOUNT" "AMOUNT",
"CREDIT_AMOUNT" "AMOUNT",
"ACNO_CROSS" "D_INTEGER",
"DEBIT_ACNO" "D_INTEGER",
"CREDIT_ACNO" "D_INTEGER",
"DEBIT_ACTYPE" "D_INTEGER",
"CREDIT_ACTYPE" "D_INTEGER",
"L_UPDATE" "TIME_S");

COMMIT WORK;

CREATE TABLE "BOOKKEEPING_CHART_ACCOUNTS"("ID" "ID" NOT NULL,
"C_NO" "T10",
"U_NO" "T10",
"ACNO" "D_INTEGER",
"ACTYPE" "D_INTEGER",
"ACTEXT" "T130",
"RW_STATUS" "I_O",
"LANG" "T10",
"L_UPDATE" "TIME_S");

COMMIT WORK;

CREATE TABLE "CAMPS_D_TODO"("ID" "ID",
"C_NO" "T10" NOT NULL,
"U_NO" "T10" NOT NULL,
"TODO_TYPE" "T20",
"TODO_DATE" "D_DATE",
"L_UPDATE" "STATUS",
"STAT_UPDATE" "D_INTEGER",
"STAT_DEL" "D_INTEGER");

COMMIT WORK;

CREATE TABLE "CAMPS_D_VISIT"("ID" "ID",
"C_NO" "T10" NOT NULL,
"U_NO" "T10" NOT NULL,
"VISIT_POS" "T20",
"VISIT_TYPE" "T20",
"VISIT_DATE" "D_DATE",
"L_UPDATE" "STATUS",
"STAT_UPDATE" "D_INTEGER",
"STAT_DEL" "D_INTEGER");

COMMIT WORK;



/* Alter Table (Field)... */
ALTER TABLE "CAMPS_D_PUB" ADD "P_MEETING" "T130";
ALTER TABLE "CAMPS_D_PUB" ADD "P_MEETING_TIME" "D_TIME";
ALTER TABLE "CAMPS_D_PUB" ADD "P_MEETING_LOC" "T130";
ALTER TABLE "CAMPS_D_PUB" ADD "ELECTIONS_DATE" "D_DATE";
ALTER TABLE "CAMPS_D_PUB" ADD "ELECTION_SUPERVISOR" "T20";
ALTER TABLE "CAMPS_D_PUB" ADD "M_MEETING_TIME" "D_TIME";
ALTER TABLE "CAMPS_D_PUB" ADD "BANK_DONATION" "T130";
ALTER TABLE "CAMPS_D_PUB" ADD "BANK_ACC_DONATION" "T130";
ALTER TABLE "TESTIMONY" ADD "VERIFIED" "I_O";
COMMIT WORK;



/* Alter Field (Null / Not Null)... */
UPDATE "RDB$RELATION_FIELDS" SET "RDB$NULL_FLAG"=NULL WHERE "RDB$FIELD_NAME"='RUNNED' AND "RDB$RELATION_NAME"='DB_VERSION';

COMMIT WORK;



/* Create Foreign Key... */
ALTER TABLE "DB_VERSION" ADD CONSTRAINT PK_DB_VERSION PRIMARY KEY ("ID");
COMMIT WORK;

ALTER TABLE "BOOKKEEPING_BANK" ADD CONSTRAINT FK_BOOKKEEPING_BANK_1 FOREIGN KEY ("C_NO") REFERENCES "CAMPS"("C_NO") ON UPDATE CASCADE;
COMMIT WORK;

ALTER TABLE "BOOKKEEPING_BOOKING" ADD CONSTRAINT FK_BOOKKEEPING_BOOKING_B2 FOREIGN KEY ("C_NO") REFERENCES "CAMPS"("C_NO") ON UPDATE CASCADE;
COMMIT WORK;

ALTER TABLE "BOOKKEEPING_CHART_ACCOUNTS" ADD CONSTRAINT FK_BOOKKEEPING_CHART_ACCOUNTS_1 FOREIGN KEY ("C_NO") REFERENCES "CAMPS"("C_NO") ON UPDATE CASCADE;
COMMIT WORK;

ALTER TABLE "CAMPS_D_TODO" ADD CONSTRAINT CAMPS_D_TODO1 FOREIGN KEY ("C_NO") REFERENCES "CAMPS"("C_NO") ON UPDATE CASCADE;
COMMIT WORK;

ALTER TABLE "CAMPS_D_VISIT" ADD CONSTRAINT CAMPS_D_VISIT1 FOREIGN KEY ("C_NO") REFERENCES "CAMPS"("C_NO") ON UPDATE CASCADE;
COMMIT WORK;



/* Drop Fields on Table... */
ALTER TABLE "CAMPS_AC" DROP "CABINET_LAST_V";
ALTER TABLE "CAMPS_AC" DROP "CABINET_NEXT_V";
ALTER TABLE "CAMPS_AC" DROP "M_LAST_V";
ALTER TABLE "CAMPS_AC" DROP "M_NEXT_V";
ALTER TABLE "CAMPS_D_PUB" DROP "PRAYER_M";
ALTER TABLE "CAMPS_D_PUB" DROP "PRAYER_M_LOC";
ALTER TABLE "CAMPS_D_PUB" DROP "ELECTIONS_LAST";
ALTER TABLE "CAMPS_D_PUB" DROP "ELECTIONS_NEXT";
ALTER TABLE "CAMPS_FO" DROP "CABINET_LAST_V";
ALTER TABLE "CAMPS_FO" DROP "CABINET_NEXT_V";
ALTER TABLE "CAMPS_FO" DROP "M_LAST_V";
ALTER TABLE "CAMPS_FO" DROP "M_NEXT_V";
ALTER TABLE "CAMPS_FO" DROP "ELECTIONS_HQ";
ALTER TABLE "CAMPS_FO" DROP "NMP";
ALTER TABLE "CAMPS_FO" DROP "NMM";
ALTER TABLE "CAMPS_FO" DROP "PB";
ALTER TABLE "CAMPS_FO" DROP "MEMBER_FEE";
ALTER TABLE "CAMPS_FO" DROP "TARGET_SET";
ALTER TABLE "CAMPS_FO" DROP "ANNUAL_REPORT";
ALTER TABLE "CAMPS_ZD" DROP "CABINET_LAST_V";
ALTER TABLE "CAMPS_ZD" DROP "CABINET_NEXT_V";
ALTER TABLE "CAMPS_ZD" DROP "M_LAST_V";
ALTER TABLE "CAMPS_ZD" DROP "M_NEXT_V";
COMMIT WORK;

Besten Dank für Eure Tipps
Manfred