Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General Discussion » View Thread |
Messages 1 to 8 of 8 total |
Altering tables |
Wed, May 14 2014 8:49 AM | Permanent Link |
Matthew Jones | One of the other things I do a lot in DBISAM is ensuring both creating a complete
database at run time and ensuring that the tables have the correct fields on startup. This allows me to: 1. create complete new configurations and databases on the fly (one application uses a database that is all zipped up into "projects" so you can have any number open), and 2. to issue an update to software and have it create any new fields and indexes I decided are needed (and remove others). Am I right in thinking that I can do all this in ElevateDB? I currently use the TTable.FieldDefs, and presume that I'd have to switch to SQL only, but I think that my existing function that looks like: CheckFieldPresent(tableChecker, 'ClientQueue.dat', 'cqBackupDone', ftBoolean, 0); will still work just fine once modified to SQL. If these are possible, I'll be able to migrate. /Matthew Jones/ |
Wed, May 14 2014 10:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Matthew
>One of the other things I do a lot in DBISAM is ensuring both creating a complete >database at run time and ensuring that the tables have the correct fields on >startup. This allows me to: >1. create complete new configurations and databases on the fly (one application >uses a database that is all zipped up into "projects" so you can have any number >open), and >2. to issue an update to software and have it create any new fields and indexes I >decided are needed (and remove others). > >Am I right in thinking that I can do all this in ElevateDB? I currently use the >TTable.FieldDefs, and presume that I'd have to switch to SQL only, but I think that >my existing function that looks like: > CheckFieldPresent(tableChecker, 'ClientQueue.dat', 'cqBackupDone', ftBoolean, 0); > >will still work just fine once modified to SQL. If these are possible, I'll be >able to migrate. As my other post you need to start thinking about the catalog. Quick bit of homework for you - go into EDBManager and if you haven't a session/database/table already there then create them (its all in the manual). Log into the database and enter the following SQL SELECT * FROM Information.Tables You get a list of the tables that are there with a load of information about them next SELECT * FROM Information.TableColumns this shows you the columns that are there These two tables can be used to allow you to build SQL to create or alter your tables. Remember the catalog holds all the information and its "just" a collection of tables itself and can be queried (you HAVE to use SQL) just like any others. I'm not going to guess at the internals of CheckFieldPresent but here's my function for finding if a table exists function IsTableInDatabase(const tName: string; tDatabase: TEDBDatabase): boolean; var Checker: TEDBQuery; begin Checker := TEDBQuery.Create(nil); try Checker.SessionName := tDatabase.SessionName; Checker.DatabaseName := tDatabase.DatabaseName; Checker.SQL.Text := 'SELECT * FROM Information.Tables WHERE Name = ' + QuotedStr(tName); Checker.ExecSQL; Result := Checker.RecordCount > 0; finally Checker.Close; Checker.Free; end; end; Have a look in the extensions newsgroup I think some more of the same have been posted. Roy Lambert |
Wed, May 14 2014 11:25 AM | Permanent Link |
Fernando Dias Team Elevate | Roy, Mathew
I have a collection of routines that I use in all my projects, like this: Function TableExists(TableName: string; ADatabase: TEDBDatabase): boolean; begin Result := ADatabase.Execute( 'SELECT Version FROM Information.Tables WHERE Name = ' + QuotedStr(TableName) ) > 0; end; The same for Indexes, constraints, procedures, functions, etc. -- Fernando Dias [Team Elevate] |
Thu, May 15 2014 8:25 AM | Permanent Link |
Matthew Jones | <smiles_sweetly> Are they available anywhere? </smiles_sweetly>
Such a thing would make getting started with EDB really good for many. /Matthew Jones/ |
Thu, May 15 2014 9:40 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Matthew
There are a couple of utilities in the extensions newsgroup, and I've just posted my utilities up there as well (not many unfortunately) but that doesn't excuse Fernando or anyone else from adding to the pile. Roy Lambert |
Fri, May 16 2014 4:28 AM | Permanent Link |
Matthew Jones | I had a look at that topic, and have to dedicate time to learning everything's
purpose. /Matthew Jones/ |
Sat, May 17 2014 3:31 AM | Permanent Link |
Peter Evans | On 15/05/2014 10:25 PM, (Matthew Jones) wrote:
> <smiles_sweetly> Are they available anywhere? </smiles_sweetly> I posted a utility unit. Search on my name to find the appended code. Another poster improved on one or more of my routines. Regards, Peter Evans |
Sat, May 17 2014 6:39 PM | Permanent Link |
Fernando Dias Team Elevate | Matthew,
<< <smiles_sweetly> Are they available anywhere? </smiles_sweetly> >> No need, it's trivial - Here are they. However, please note that they are only safe if you are using the database in exclusive mode. If not, constructs like if not TableExists('Tbl1', EDBBD1) then begin ... create table ... end for example, might fail as someone else could have created the table meanwhile. Function DatabaseExists(DatabaseName: string; AEDBSession: TEDBSession): boolean; begin Result:=(AEDBSession.Execute('SELECT Name FROM Databases WHERE Name = '+QuotedStr(DatabaseName))>0); end; Function TableIsEmpty(TableName: string; ADatabase: TEDBDatabase): boolean; begin Result:=ADatabase.Execute('SELECT * FROM "' + TableName + '" RANGE 1 TO 1') = 0; end; Function TableExists(TableName: string; ADatabase: TEDBDatabase): boolean; begin Result:=ADatabase.Execute('SELECT Version FROM Information.Tables WHERE Name = '+QuotedStr(TableName))>0; end; Function TempTableExists(TableName: string; ADatabase: TEDBDatabase): boolean; begin Result:=ADatabase.Execute('SELECT Version FROM Information.TemporaryTables WHERE Name = '+QuotedStr(TableName))>0; end; Function ColumnExists(TableName, ColumnName: string; ADatabase: TEDBDatabase): boolean; begin Result := ADatabase.Execute('SELECT OrdinalPos FROM Information.TableColumns WHERE Name = ' + QuotedStr(ColumnName) + ' AND TableName = ' + QuotedStr(TableName))>0; end; Function ViewExists(ViewName: string; ADatabase: TEDBDatabase): boolean; begin Result:=ADatabase.Execute('SELECT Updateable FROM Information.Views WHERE Name = '+QuotedStr(ViewName))>0; end; Function ProcedureExists(ProcedureName: string; ADatabase: TEDBDatabase): boolean; begin Result:=ADatabase.Execute('SELECT NumParams FROM Information.Procedures WHERE Name = '+QuotedStr(ProcedureName))>0; end; Function FunctionExists(FunctionName: string; ADatabase: TEDBDatabase): boolean; begin Result:=EDBBD.Execute('SELECT NumParams FROM Information.Functions WHERE Name = '+QuotedStr(FunctionName))>0; end; Function JobExists(JobName: string; ASession: TEDBSession): boolean; begin Result:=ASession.Execute('SELECT Name FROM Jobs WHERE Name = '+QuotedStr(JobName))>0; end; Function IndexExists(IndexName, TableName: string; ADatabase: TEDBDatabase): boolean; begin Result:=ADatabase.Execute('SELECT Type FROM Information.Indexes WHERE' + ' TableName = '+QuotedStr(TableName)+' AND ' + ' Name = '+QuotedStr(IndexName) )>0; end; Function ConstraintExists(ConstraintName, TableName: string; ADatabase: TEDBDatabase): boolean; begin Result:=ADatabase.Execute('SELECT Type FROM Information.Constraints WHERE' + ' TableName = '+QuotedStr(TableName)+' AND ' + ' Name = '+QuotedStr(ConstraintName) )>0; end; Function TriggerExists(TriggerName, TableName: string; ADatabase: TEDBDatabase): boolean; begin Result:=ADatabase.Execute('SELECT OrdinalPos FROM Information.Triggers WHERE' + ' TableName = '+QuotedStr(TableName)+' AND ' + ' Name = '+QuotedStr(TriggerName) )>0; end; -- Fernando Dias [Team Elevate] |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |