Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 8 of 8 total |
Migrators: SQL before the database is defined |
Tue, Aug 13 2013 12:18 AM | Permanent Link |
Jeff Cook Aspect Systems Ltd | I am writing a program to migrate a DBISAM3 databases to ElevateDB and hit a
problem ... a "hitting my head against a brick wall" sort problem. I want to do it in code rather than a bunch of scripts because I have dozens of databases to convert over a short period of time. It is also more than a straight migration, with various database changes and field name changes etc. I also want to hand the conversion task over to a non technical person who will just run one exe and that's it. I have been working through the manual and bumped into a problem straight away. Here is the first bit of code:- the CREATE MODULE ========================= qry.SQL.Text := 'SELECT * FROM Migrators WHERE ModuleName = ''edbmigratedbisam3'''; qry.ExecSQL; if qry.RecordCount = 0 then begin s := 'CREATE MODULE "DBISAM3" ' + 'PATH ' + Engine.QuotedSQLStr (IncludeTrailingPathDelimiter(ExtractFilePath(Application.ExeName)) + 'edbmigratedbisam3.dll') + ' DESCRIPTION ''DBISAM 3 Migrator'''; Session.Execute(s); ========================== The CREATE MODULE worked, without the first 3 lines of code, the first time I ran it, but failed subsequently because the module was already created. I added the first 3 lines to test for the presence of the module and they work OK in EDBMgr without any Databases defined. It fails with "ElevateDB Error #401 The database DB does not exist." OK - that's obvious and I know I could wrap my CREATE in "try except end" and swallow the error as I'm sure Roy will suggest but apart from being biased against that approach, I can see there are other things I want to do in these "System" tables possibly before creating the database. Like testing the presence of a database and confirming with the user that the database can be dropped and replaced. Conclusion - I must be doing something silly or overlooking the obvious. EDBMgr can do it, so I should be able to do it too! Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz |
Tue, Aug 13 2013 4:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jeff
Well eating the execption is one way to do it it wasn't the way I went <vbg> It was a long time ago and because I was making a LOT of changes to the structures and content I did all of the real conversion work using DBISAM only converting to ElevateDB at the end and then doing a bit of tidying up. It made sense since back then I was reasonably good with DBISAM and knew very little about ElevateDB. I also had to run things multiple times until I got it right so what I did was have a front screen which asked for old and new locations, copied the data from old to new, ran the changes and deleted the new DBISAM tables. One reason for doing that was that the application was designed to allow multiple databases (especially a training database and live one). If you want a copy of the code I used I'll be happy to email it to you. Roy Lambert [Team Elevate] |
Tue, Aug 13 2013 6:59 AM | Permanent Link |
Peter Evans | On 13/08/2013 6:25 PM, Roy Lambert wrote:
> Jeff > > > It was a long time ago and because I was making a LOT of changes to the structures and content I did all of the real conversion work using DBISAM only converting to ElevateDB at the end and then doing a bit of tidying up. It made sense since back then I was reasonably good with DBISAM and knew very little about ElevateDB. > Obviously I don't know your specific situation but Roy's approach is probably the one I would take. Look out for another post very soon from me about migrating from DBISAM 3. Regards, Peter Evans |
Tue, Aug 13 2013 7:02 AM | Permanent Link |
Peter Evans | On 13/08/2013 6:25 PM, Roy Lambert wrote:
> Jeff > > > It was a long time ago and because I was making a LOT of changes to the structures and content I did all of the real conversion work using DBISAM only converting to ElevateDB at the end and then doing a bit of tidying up. It made sense since back then I was reasonably good with DBISAM and knew very little about ElevateDB. > Obviously I don't know your specific situation but Roy's approach is probably the one I would take. Look out for another post very soon from me about migrating from DBISAM 3. Regards, Peter Evans |
Tue, Aug 13 2013 8:04 AM | Permanent Link |
Peter Evans | On 13/08/2013 2:18 PM, Jeff Cook wrote:
> > I want to do it in code rather than a bunch of scripts because I have dozens > of databases to convert over a short period of time. It is also more than a > straight migration, with various database changes and field name changes I realise that at the moment your code changes are at the source DBISAM 3 end and not the target ElevateDB end. However, here are some Procedures that I have found useful in my migration. Note that I am using ElevateDB Unicode and not all the routines have been tested properly. If you use them and are able to improve them then please reply at some time. The quotes are from Elevate Software documents. Thanks to Roy and others for inspiration. Regards, Peter Evans interface function CreateDB(const Session1 : TEDBSession; const DBName : String; const DBPath : String; const DBDescription : String) : Boolean; {Creates a database} function CreateDBMemory(const Session1 : TEDBSession; const DBName : String; const DBDescription : String) : Boolean; {Creates a database in memory. Thus there is no need for a path} function DBDrop(const Session1 : TEDBSession; const DBName : String) : Boolean; {This routine deletes the database. "Dropping a database will drop all tables and the entire catalog for the database specified. This means that all data and metadata for the database will be permanently deleted"} function DBExists(const Session1 : TEDBSession; const DBName : String) : Boolean; {Returns True if the database exists.} function NumTablesInDB(const Session1 : TEDBSession; const DBName : String) : Integer; {Returns the number of tables in a database. Example of use is when you have a memory database. You have just deleted a memory table. If there are no tables remaining you may want to delete the memory database} procedure TableDrop(const ThisDatabase : TEDBDatabase; const ThisTable : String); {This routine deletes the table from the database} function TableExists(const ThisDatabase : TEDBDatabase; const ThisTable : String) : Boolean; {Returns True if the table exists in the database.} implementation function CreateDB(const Session1 : TEDBSession; const DBName : String; const DBPath : String; const DBDescription : String) : Boolean; var Str : String; begin TRY Str := 'CREATE DATABASE "' + DBName + '"' + ' PATH ' + '''' + DBPath + '''' + ' DESCRIPTION ' + '''' + DBDescription + ''''; Session1.Execute(Str); Result := True EXCEPT Result := False; END; end; function CreateDBMemory(const Session1 : TEDBSession; const DBName : String; const DBDescription : String) : Boolean; var Str : String; begin TRY Str := 'CREATE DATABASE "' + DBName + '"' + ' IN MEMORY ' + ' DESCRIPTION ' + '''' + DBDescription + ''''; Session1.Execute(Str); Result := True EXCEPT Result := False; END; end; function DBDrop(const Session1 : TEDBSession; const DBName : String) : Boolean; var Str : String; begin Result := False; Str := 'DROP DATABASE "' + DBName + '"'; if Session1.Execute(Str) = 1 then Result := True else Result := False; end; function DBExists(const Session1 : TEDBSession; const DBName : String) : Boolean; begin if Session1.Execute( 'SELECT * FROM Databases ' + 'WHERE Name=' + Engine.QuotedSQLStr(DBName) ) = 1 then Result := True else Result := False; end; function NumTablesInDB(const Session1 : TEDBSession; const DBName : String) : Integer; {"The Execute method returns the number of rows affected or returned by a particular SQL statement, so you can use the return value of an indication of whether any rows exist for the SELECT statement on the Configuration database"} var Str : String; begin Str := 'SELECT * FROM Databases ' + 'WHERE Name = ' + Engine.QuotedSQLStr(DBName); Result := Session1.Execute(Str); end; procedure TableDrop(const ThisDatabase : TEDBDatabase; const ThisTable : String); begin TRY ThisDatabase.Execute( 'DROP TABLE "' + ThisTable + '"' ); EXCEPT END; end; function TableExists(const ThisDatabase : TEDBDatabase; const ThisTable : String) : Boolean; begin Result := False; IF ( ThisDatabase.Execute( 'SELECT * FROM Information.Tables ' + 'WHERE Name=' + Engine.QuotedSQLStr(ThisTable) )) = 1 THEN Result := True; end; |
Tue, Aug 13 2013 5:11 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | "Roy Lambert" <roy@lybster.me.uk> wrote in message
news:773CF82E-A461-4EAF-A2DD-77E19EE4C95A@news.elevatesoft.com... > > If you want a copy of the code I used I'll be happy to email it to you. > Thanks for the offer Roy - I might take you up on that if my current plan turns to custard. Meanwhile reading the help properly has solved the problem per my answer to Peter Evans' post. Cheers Jeff |
Tue, Aug 13 2013 5:23 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi Peter
Your code examples have lead me to reading the help again and realising the errors of my ways I read the code for your "function DBExists" and saw that Session.Execute returns an integer. Read the help and see that this is the number of rows. AND "Any SQL statement executed using this method is automatically executed from the context of the system-created Configuration database" So my code becomes simply:- ----------------------------------- if Session.Execute ('SELECT * FROM Migrators WHERE ModuleName = ''edbmigratedbisam3''') = 0 then begin s := 'CREATE MODULE "DBISAM3" ' + 'PATH ' + Engine.QuotedSQLStr (IncludeTrailingPathDelimiter(ExtractFilePath(Application.ExeName)) + 'edbmigratedbisam3.dll') + ' DESCRIPTION ''DBISAM 3 Migrator'''; Session.Execute(s); end; ------------------------------- Thanks for your help. Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz "Peter Evans" <pgevans@melbpc.org.au> wrote in message news:1F707649-D842-46A5-A332-3D13047CD3C6@news.elevatesoft.com... > On 13/08/2013 2:18 PM, Jeff Cook wrote: > >> >> I want to do it in code rather than a bunch of scripts because I have >> dozens >> of databases to convert over a short period of time. It is also more >> than a >> straight migration, with various database changes and field name changes > > I realise that at the moment your code changes are at the source DBISAM 3 > end and not the target ElevateDB end. > > However, here are some Procedures that I have found useful in my > migration. Note that I am using ElevateDB Unicode and not all the routines > have been tested properly. If you use them and are able to improve them > then please reply at some time. The quotes are from Elevate Software > documents. Thanks to Roy and others for inspiration. > > Regards, > Peter Evans > > > interface > > function CreateDB(const Session1 : TEDBSession; > const DBName : String; > const DBPath : String; > const DBDescription : String) : Boolean; > {Creates a database} > > function CreateDBMemory(const Session1 : TEDBSession; > const DBName : String; > const DBDescription : String) : Boolean; > {Creates a database in memory. > Thus there is no need for a path} > > function DBDrop(const Session1 : TEDBSession; > const DBName : String) : Boolean; > {This routine deletes the database. > "Dropping a database will drop all tables and the entire catalog for the > database specified. This means that all data and metadata for the > database > will be permanently deleted"} > > function DBExists(const Session1 : TEDBSession; > const DBName : String) : Boolean; > {Returns True if the database exists.} > > function NumTablesInDB(const Session1 : TEDBSession; > const DBName : String) : Integer; > {Returns the number of tables in a database. > Example of use is when you have a memory database. You have just deleted > a memory table. If there are no tables remaining you may want to delete > the > memory database} > > procedure TableDrop(const ThisDatabase : TEDBDatabase; > const ThisTable : String); > {This routine deletes the table from the database} > > function TableExists(const ThisDatabase : TEDBDatabase; > const ThisTable : String) : Boolean; > {Returns True if the table exists in the database.} > > implementation > > > > function CreateDB(const Session1 : TEDBSession; > const DBName : String; > const DBPath : String; > const DBDescription : String) : Boolean; > var > Str : String; > begin > TRY > Str := 'CREATE DATABASE "' + DBName + '"' + > ' PATH ' + '''' + DBPath + '''' + > ' DESCRIPTION ' + '''' + DBDescription + ''''; > Session1.Execute(Str); > Result := True > EXCEPT > Result := False; > END; > end; > > function CreateDBMemory(const Session1 : TEDBSession; > const DBName : String; > const DBDescription : String) : Boolean; > var > Str : String; > begin > TRY > Str := 'CREATE DATABASE "' + DBName + '"' + > ' IN MEMORY ' + > ' DESCRIPTION ' + '''' + DBDescription + ''''; > Session1.Execute(Str); > Result := True > EXCEPT > Result := False; > END; > end; > > function DBDrop(const Session1 : TEDBSession; > const DBName : String) : Boolean; > var > Str : String; > begin > Result := False; > Str := 'DROP DATABASE "' + DBName + '"'; > if Session1.Execute(Str) = 1 then > Result := True > else > Result := False; > end; > > function DBExists(const Session1 : TEDBSession; > const DBName : String) : Boolean; > begin > if Session1.Execute( > 'SELECT * FROM Databases ' + > 'WHERE Name=' + Engine.QuotedSQLStr(DBName) > ) = 1 then > Result := True > else > Result := False; > end; > > function NumTablesInDB(const Session1 : TEDBSession; > const DBName : String) : Integer; > {"The Execute method returns the number of rows affected or returned by a > particular SQL statement, so you can use the return value of an indication > of whether any rows exist for the SELECT statement on the Configuration > database"} > var > Str : String; > begin > Str := 'SELECT * FROM Databases ' + > 'WHERE Name = ' + Engine.QuotedSQLStr(DBName); > Result := Session1.Execute(Str); > end; > > procedure TableDrop(const ThisDatabase : TEDBDatabase; > const ThisTable : String); > begin > TRY > ThisDatabase.Execute( > 'DROP TABLE "' + ThisTable + '"' > ); > EXCEPT > END; > end; > > function TableExists(const ThisDatabase : TEDBDatabase; > const ThisTable : String) : Boolean; > begin > Result := False; > IF ( ThisDatabase.Execute( > 'SELECT * FROM Information.Tables ' + > 'WHERE Name=' + Engine.QuotedSQLStr(ThisTable) > )) = 1 THEN > Result := True; > end; |
Tue, Aug 27 2013 2:01 AM | Permanent Link |
Peter Evans | On 13/08/2013 10:04 PM, Peter Evans wrote:
> > function NumTablesInDB(const Session1 : TEDBSession; > const DBName : String) : Integer; That function did not work. Its replacement is :- function NumTablesInDB(const ThisDatabase : TEDBDatabase) : Integer; begin Result := ThisDatabase.Execute('SELECT * FROM Information.Tables'); end; Regards, Peter Evans |
This web page was last updated on Monday, May 6, 2024 at 03:23 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |