Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 9 of 9 total |
Best way to update client database? |
Thu, Oct 23 2014 12:57 PM | Permanent Link |
Barry | 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 |
Fri, Oct 24 2014 4:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Oct 24 2014 6:35 AM | Permanent Link |
Matthew Jones | 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; |
Fri, Oct 24 2014 7:11 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Oct 24 2014 9:43 AM | Permanent Link |
Matthew Jones | Roy Lambert wrote:
> attack sharks Don't forget the lasers on the sharks. -- Matthew Jones |
Fri, Oct 24 2014 10:41 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Matthew
>> attack sharks > >Don't forget the lasers on the sharks. Now you are just being silly! Roy |
Mon, Jan 5 2015 10:59 AM | Permanent Link |
Matthew Jones | 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 |
Mon, Jan 5 2015 11:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Jan 5 2015 12:26 PM | Permanent Link |
Matthew Jones | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |