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/

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

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]

<smiles_sweetly> Are they available anywhere? </smiles_sweetly>

Such a thing would make getting started with EDB really good for many.

/Matthew Jones/

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

I had a look at that topic, and have to dedicate time to learning everything's
purpose.

/Matthew Jones/

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

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]