Episode 2


I forgot one thing if you supply the complete package - you'll need to supply the config file as well. You may also still need to set the database path if its not the same as you set up in ElevateDB.

If you can't or don't want to supply the files and you need to create them in your exe the process is roughly as follows:

To get started you'll need the TEDBEngine and TEDBSession components. As before set Set UseLocalSessionEngineSettings to True in the engine.

Set the session details programmatically and open it. This will create the config file. You can then use the session to create the database..

session.EXECUTE('CREATE DATABASE fred PATH ''x:\y\z''');


then you can set the database properties and use its EXECUTE method to create the tables. The tables can be created using a standard query using the session name set and pointing to the Configuration database.

You can use ElevateDB to see what the syntax is for creating a table but remember ElevateDB queries only executes one piece of sql unlike DBISAM where you could have several bits of sql separated by a semicolon. This means either use a script or have several queries for creating indices apart from the primary key.

Does the table exist now falls into two parts - does the table exist and does the data exist. If you're bothered check the latter first using FileExists because if the table exists and you try and open it the data files will be created.

To see if a table exists you now have to query the database catalog. Set up a query - point it at the database and run a query:

SELECT * FROM Information.Tables WHERE Name = 'whatever'

check to see if any rows are returned. If so the table exists, if not it doesn't. I have a couple of little utility routines

function DoesTableExist(chk: TnlhTable): boolean;
var
Checker: TEDBQuery;
begin
Checker := TEDBQuery.Create(nil);
try
 Checker.SessionName := chk.SessionName;
 Checker.DatabaseName := chk.DatabaseName;
 Checker.SQL.Text := 'SELECT * FROM Information.Tables WHERE Name = ' + QuotedStr(chk.TableName);
 Checker.ExecSQL;
 Result := Checker.RecordCount > 0;
finally
 Checker.Close;
 Checker.Free;
end;
end;

and


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;

and

function IsTableThere(const sName, dName, tName: string): boolean;
var
Checker: TEDBQuery;
begin
Checker := TEDBQuery.Create(nil);
try
 Checker.SessionName := sName;
 Checker.DatabaseName := dName;
 Checker.SQL.Text := 'SELECT * FROM Information.Tables WHERE Name = ' + QuotedStr(StripCtrlChars(tName));
 Checker.ExecSQL;
 Result := Checker.RecordCount > 0;
finally
 Checker.Close;
 Checker.Free;
end;
end;


If you're doing it in a script - here's the script Tim supplied to create an in-memory table slightly enhanced.


SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR)
BEGIN     
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;

PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING TableName;

IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"';
END IF;

CLOSE InfoCursor;

PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA';
EXECUTE ResultStmt;

IF IdxSet IS NOT NULL THEN
 PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')';
 EXECUTE  ResultStmt;
END IF;
END


Roy Lambert [Team Elevate]

Hi Roy,

Thank you very much for the help... I think I can get it going from here.

It should be no problem to include the configuration files with my app.  Checking for table existence looks fairly straight forward from your examples.  

I did manage to get to the table creation point (not checking, just trying it in code)... and I get a message telling me that the configuration is read only.....

I'll dig into your examples... I am sure that once I get it figured out, the rest should fall into place.

I'm looking forward to getting going with EDB 2!

Thanks again,

>>> Rick <<< (as he gets his mastercard out to pay his support contracts )

I forgot that one Roy,  I have several thousand bits of SQL in my
application that use UPPER to compare strings.

Chris Holland [Team Elevate]

On 06/01/2012 18:13, Roy Lambert wrote:
> Rick
>
>
> Just remembered another one - in DBISAM you used UPPER or LOWER to indicate case insensitivity in a query - ElevateDB you can set the collation in the table creation or use COLLATE in a query.
>
>
> Roy Lambert
>

Rick,

<< I did manage to get to the table creation point (not checking, just
trying it in code)... and I get a message telling me that the configuration
is read only..... >>

Any time that you see that error message, it means that your query component
is pointing to the system-defined Configuration database instead of the
actual database.  In general, you should consider using the
TEDBSession.Execute method for executing DDL statements that
create/alter/drop Configuration objects such as databases, users, jobs,
etc., and the TEDBDatabase.Execute method for executing DDL statements that
create/alter/drop database objects.  It's easier than having to set up a
TEDBQuery every time:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2&product=delphi&version=7&topic=Creating_Configuration_Objects

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2&product=delphi&version=7&topic=Creating_Database_Objects

Also, regarding shipping the configuration file: just remember that the
configuration file can contain paths for things like databases, external
modules, and stores, so you'll need to take into account modifying these
accordingly using the appropriate ALTER statement during
installation/startup.  In general, my recommendation would be to *not* ship
the configuration file, especially if you're only going to be creating a
single database and leaving the user security alone for the most part.  In
such a case, the code in the CDCollector demo application will suffice for
checking to see if the database exists, and if not, creating it.

Now, for the database catalog: it is perfectly fine to ship the database
catalog with the application since it never contains path information and is
completely self-contained.

I hope that helps,

--
Tim Young
Elevate Software
www.elevatesoft.com

Rick,

<< Please ignore most of what I said in my previous post above... I think I
have this figured out... but the login thing concerns me still... if I
install my executable on a new computer, will the login always be the
defaults if there is no database admin program available? >>

The user names/passwords that are available for login are dependent upon the
configuration file, which is where they are stored.  If the configuration
file was just created by EDB because it didn't exist, then it will contain
the default users/roles noted here:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=User_Security

under "Default Users and Roles".

In addition to my other response, if you don't plan on using user security
and only use a single database, then you might want to consider using a
virtual configuration file instead.  A virtual configuration file is stored
in memory instead of on-disk, and because of this, it needs to be populated
at application startup with the necessary databases, etc.  But, it does
allow you to ship an application that doesn't require a configuration path
setting or an on-disk configuration file, and will allow you to manage the
user security as you see fit in your application.

You can find out more information on that feature here:

http://www.elevatesoft.com/manual?action=viewprop&id=edb2&product=delphi&version=7&comp=TEDBEngine&prop=ConfigMemory

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Now, for the database catalog: it is perfectly fine to ship the database
>catalog with the application since it never contains path information and is
>completely self-contained.

For some reason my first reading of that was "completely self satisfied"

Roy Lambert

Chris

>I forgot that one Roy, I have several thousand bits of SQL in my
>application that use UPPER to compare strings.

I try and remember to make sure any CHAR or VARCHAR columns are set to ANSI_CI. Works a charm.

Roy Lambert