What is the best way to update a client data base under the following scenario.

a) The client was a given a database application 2 months ago and he has been entering data into two or three client tables.

b) I have the same database on my computer and I have been updating the structure of the database and have updated data in the control tables (non-client area).

c) I can have the client stop using the database for a day or two while I upgrade it with my new structure and import his new data.

What is the best way to update his database?

1) Do I grab his latest backup file and restore it onto my computer?
If so, how do I do it with the current session in EDBMgr so I can access his data? Both databases have the same name. This is what has always bothered me with EDB. It can't see other databases unless it is in the same session.

2) Do I update his database with my new structure and import my data into his database?

3) Or do I import his data into my database which has the new structure.

4) Do I reverse engineer (with data) his 2 or 3 client tables and use it to create dummy tables in my database so I can access his data with my database and write SQL to update the 2 or 3 tables in my database from the dummy tables?

TIA

Barry
v2.13 B2

Barry


I'm sure everybody will have a different approach, and a lot depends on the nature of the changes you're making to the database. Simple ones eg adding or deleting a column can easily be dealt with wimply by running a query / script. If its more complex then providing everything can be done in sql its also simple. Otherwise you need a more complex solution.

If its a simple one create the sql, and once you're sure it works get the client to close down, log onto his database remotely through EDBManager and run your sql.

What I have in my programs these days is a method of running an external script. I generate that back at home base, test then encode (just UUEncode so a moron editing it is unlikely to screw it up) with a bit of validation stuff. My program decodes it and runs it. Job done as they say.

Reading you post again I'm guessing that most of the changes are simple with the exception of the control data so what I'd probably do is reverse engineer the control table with data then create a script to

1) drop the existing control table
2) create the new one and stuff the data in
3) modify the tables.

If the client is to far away to use remote access to run the script embed it in a small program and either email or post it to him.

I do not recommend giving clients access to EDBManager which is why my programs have a utility to run external scripts built in.

HTH


Roy Lambert

Barry wrote:

> What is the best way to update a client data base under the following
> scenario.
>
> a) The client was a given a database application 2 months ago and he
> has been entering data into two or three client tables.
>
> b) I have the same database on my computer and I have been updating
> the structure of the database and have updated data in the control
> tables (non-client area).
>
> c) I can have the client stop using the database for a day or two
> while I upgrade it with my new structure and import his new data.
>
> What is the best way to update his database?


Well, first of I wouldn't start from here. 8-)

Like Roy, I have the software capable of updating the database. One of
the first things my application does is check the database so that
nothing else has a chance to get in and stop an update happening. I can
then add additional fields (or remove old) according to what I've been
doing. This runs every time so that restoring from backups don't break
stuff. It doesn't take account of wholesale changes - that would need a
whole chunk of code.

The key is, once an application is deployed, ALL my changes are done
via this method, and thus I know that it will work for customer
installations too. I never add fields or indexes using EDB. If you
don't do this all the time, then obviously you have to sit down and
work out what is what.

For you, I'd get a copy of their database, then run your software using
it. Queries will fail, and then you'll add the code to update it to fix
it, and then iterate until the database was converted.

All IMO and Experience - your situation may have constraints I don't
normally consider.

--

Matthew Jones


procedure VerifyDatabases;
var
   szDatabaseLocation: string;
   dbChecker: TEDBDatabase;
begin
   szDatabaseLocation := g_xStartupConfig.DatabasePath;
   try
      Report('Database check start ' + DateTimeToStr(Now));

      dbChecker := TEDBDatabase.Create(nil);
      try
         dbChecker.SessionName := g_xThreadSession.SessionName;
         dbChecker.DatabaseName := g_xThreadSession.SessionName + 'DB';
         dbChecker.Database := g_xStartupConfig.DatabaseName; //
szDatabaseLocation;
         dbChecker.Open;

         CheckDatabase(dbChecker, 'ActionRequest');

         CheckFieldPresent(dbChecker, 'CustomerOrder', 'coOrderSuspended',
ftBoolean, 0);
         CheckFieldPresent(dbChecker, 'CustomerOrder', 'coOrderCoupon',
ftMemo, 0);

//         CheckFieldNotPresent(tableChecker, 'CustomerLog.dat',
'cgReplicateIndex');

         CheckIndexPresent(dbChecker, 'EmailRequest', 'idxHoldForAdmin',
'erHoldForAdmin');
         CheckIndexPresent(dbChecker, 'EmailRequest', 'idxRequestTime',
'erRequestTime');

         begin
            Report('Database check complete ' + DateTimeToStr(Now));
         end;
      finally
         FreeAndNil(dbChecker);
      end;
   except
      on errInfo: Exception do
         Report('Exception occurred in database check: ' + errInfo.Message);
   end;
end;

procedure CheckFieldPresent(dbChecker: TEDBDatabase; szDbName: string;
szFieldName: string; ftType: TFieldType; nFieldLength: Integer);
var
   szType : String;
   szSQL : String;
begin
   if not ColumnExists(szDbName, szFieldName, dbChecker) then
   begin
      case ftType of
      ftString: szType := 'VARCHAR(' + IntToStr(nFieldLength) + ')';
      ftSmallint: szType := 'SMALLINT';
      ftInteger: szType := 'INTEGER';
      ftWord: szType := 'INTEGER';
      ftBoolean: szType := 'BOOLEAN DEFAULT FALSE';
      ftFloat: szType := 'FLOAT';
      ftCurrency: szType := 'DECIMAL';
      ftBCD: szType := 'DECIMAL';
      ftDate: szType := 'DATE';
      ftTime: szType := 'TIME';
      ftDateTime: szType := 'DATETIME';
      ftBlob: szType := 'BLOB';
      ftMemo: szType := 'CLOB';
      ftLargeint: szType := 'BIGINT';
      else
         assert(false);
(*      ftBytes: ;
      ftVarBytes: ;
      ftAutoInc: ;
      ftMemo: ;
      ftGraphic: ;
      ftFmtMemo: ;
      ftParadoxOle: ;
      ftDBaseOle: ;
      ftTypedBinary: ;
      ftCursor: ;
      ftFixedChar: ;
      ftWideString: ;
      ftLargeint: ;
      ftADT: ;
      ftArray: ;
      ftReference: ;
      ftDataSet: ;
      ftOraBlob: ;
      ftOraClob: ;
      ftVariant: ;
      ftInterface: ;
      ftIDispatch: ;
      ftGuid: ;
      ftTimeStamp: ;
      ftFMTBcd: ;
      ftFixedWideChar: ;
      ftWideMemo: ;
      ftOraTimeStamp: ;
      ftOraInterval: ;
      ftLongWord: ;
      ftShortint: ;
      ftByte: ;
      ftExtended: ;
      ftConnection: ;
      ftParams: ;
      ftStream: ;
      ftTimeStampOffset: ;
      ftObject: ;
      ftSingle: ;            *)
      end;

//      szSQL := 'EXECUTE IMMEDIATE ''ALTER TABLE "' + szDbName + '" ADD
COLUMN "' + szFieldName + '" ' + szType + '''';
      szSQL := 'ALTER TABLE "' + szDbName + '" ADD COLUMN "' + szFieldName
+ '" ' + szType + '';
      LogReport(leInformation, g_szThreadPrefix + 'SQL ' + szSQL);
      dbChecker.Execute(szSQL);
   end;
end;

procedure CheckIndexPresent(dbChecker: TEDBDatabase; szDbName: string;
szIndexName: string; szFieldName: string);
var
   szSQL : String;
begin
   szSQL := 'SELECT CreateSQL FROM Information.Indexes WHERE CreateSQL
LIKE ''%' + szIndexName + '%''';
   LogReport(leInformation, g_szThreadPrefix + 'CheckIndexPresent SQL ' +
szSQL);
   if  not(dbChecker.Execute(szSQL) > 0) then
   begin
      szSQL := 'CREATE INDEX "' + szIndexName + '" ON "' + szDbName + '"
("' + szFieldName + '")';
      LogReport(leInformation, g_szThreadPrefix + 'CheckIndexPresent SQL '
+ szSQL);
      dbChecker.Execute(szSQL);
   end;
end;



procedure CheckFieldNotPresent(dbChecker: TEDBDatabase; szDbName:
string; szFieldName: string);
var
   szSQL : String;
begin
   if not ColumnExists(szDbName, szFieldName, dbChecker) then
   begin
assert(false); //not yet tested
      szSQL := 'ALTER TABLE "' + szDbName + '" DROP COLUMN "' + szFieldName
+ '"';
      LogReport(leInformation, g_szThreadPrefix + 'SQL ' + szSQL);
      dbChecker.Execute(szSQL);
   end;
end;

Function ColumnExists(TableName, ColumnName: string; ADatabase:
TEDBDatabase): boolean;
var
   szSQL : String;
begin
   szSQL := 'SELECT OrdinalPos FROM Information.TableColumns WHERE Name =
' + QuotedStr(ColumnName) + ' AND TableName = ' + QuotedStr(TableName);
   Result := ADatabase.Execute(szSQL) > 0;
end;

Barry


One point I forgot to make. When a customer says they won't put anything new into the database then unless you personally deleted the data from their computer, dug a moat round it and filled it with water and attack sharks don't believe them.

Roy Lambert

Roy Lambert wrote:

> attack sharks

Don't forget the lasers on the sharks.

--

Matthew Jones

Matthew

>> attack sharks
>
>Don't forget the lasers on the sharks.

Now you are just being silly!

Roy

Matthew Jones wrote:

> procedure CheckIndexPresent(dbChecker: TEDBDatabase; szDbName: string;
> szIndexName: string; szFieldName: string);
> var
>    szSQL : String;
> begin
>    szSQL := 'SELECT CreateSQL FROM Information.Indexes WHERE CreateSQL
> LIKE ''%' + szIndexName + '%''';
>    LogReport(leInformation, g_szThreadPrefix + 'CheckIndexPresent SQL '
> + szSQL);
>    if  not(dbChecker.Execute(szSQL) > 0) then
>    begin
>       szSQL := 'CREATE INDEX "' + szIndexName + '" ON "' + szDbName + '"
> ("' + szFieldName + '")';
>       LogReport(leInformation, g_szThreadPrefix + 'CheckIndexPresent SQL '
> + szSQL);
>       dbChecker.Execute(szSQL);
>    end;
> end;


I found this was failing on an installation the other day. Works fine
on one installation, not on the other. Somehow the one that didn't work
had the index as a constraint.

Changing the first line to:

szSQL := 'SELECT Name FROM Information.Indexes WHERE (TableName = ''' +
szDbName + ''') AND (Name LIKE ''%' + szIndexName + '%'')';

now works. The index in constraint version has a name, but no SQL to
create it.


--

Matthew Jones

Matthew


Is this a typo or a construct I don't understand?

(TableName = ''' +
szDbName + ''')


None of the constraints on a table have an entry in the CreateSQL column because they are created as part of the table definition. You'll also find you can't DROP them either.


Roy Lambert

Roy Lambert wrote:

> Is this a typo or a construct I don't understand?
>
> (TableName = ''' +
> szDbName + ''')

That's a pair of single quotes, which is output as one single quote,
then another single quote to close the string before including the
table name.

I'm happy that there is no CreateSQL for indexes created this way, but
before I was getting nothing back and thus it went to create the index
but was already there so failed and stopped creating the other indexes.
This way works for indexes created both ways.

--

Matthew Jones