I'd just like to share my experience in the hope that it will save someone
some pain.  I started with no experience in EDB and commenced changing a
large DBISAM3 application.  (I think that most of what I say below will also
apply to DBISAM4).

These are the notes I wrote for myself.
=============================================


Converting DBISAM editable canned DataSets to ElevateDB



In many places in my app I have used DBISAM queries to create canned
datasets and used the fact that theses dataset are editable.

Typical use would be to display a grid of landlords and be able to double
click in a line to select the landlord for further processing e.g. in a
MailMerge. The double click on th grid is captured and used to set a
"Selected" field in the dataset.

Converting to ElevateDB, these datasets became ReadOnly unless they were
Sensitive and I didn't need or want Sensitive.

These are the steps to get the same effect in ElevateDB with the least
amount of tinkering with the code.

1. Drop a memo component (memSQL) onto the form and set the Visible property
to False.

2 Copy the query SQL into memSQL.

3. Make any necessary changes to the SQL in memSQL to make it conform to
ElevateDB standards.

4. Prefix all table references with {Database}. e.g. SELECT * FROM
{Database}.MyTable

5. In the FormActivate event, (assuming that this isn't in the main form)

memSQL.Text := StringReplace(memSQL.Text, '{Database}',
DataModule.MyDatabase.Database, [rfReplaceAll]);

6. In the DataModule create TEDBDatabase pointing to a memory database.

7. Also in the DM there a script to create a temporary table from an SQL -
often posted here in the newsgroup - hooked to the memory DB.

8. Use GExperts or something similar to change the Query Component to a
Table Component.(MyTable) This preserves all

the persistent fields and links to data aware components.

9. Point MyTable to the memory DB.

10.In FormActivate

MyTable.TableName := sTempFileName; where sTempFileName is your choice.

11. To open the table:-

DM.scrCreateTempTable.Prepare;

DM.scrCreateTempTable.ParamByName('TableName').AsString := sTempFileName;

DM.scrCreateTempTable.ParamByName('SQLStatement').AsString := memSQL.Text;

DM.scrCreateTempTable.ParamByName('IdxSet').AsString := SequenceSelect; //
key1, key2, ...

DM.scrCreateTempTable.ExecScript;

MyTable.Open;

12. In FormClose, drop the table.

?

======================= scrCreateTempTable
========================================================

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


--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Jeff,

<< These are the steps to get the same effect in ElevateDB with the least
amount of tinkering with the code. >>

I would strongly suggest using temporary tables (CREATE TEMPORARY TABLE)
instead of actual tables.  Temporary tables do not cause a catalog update,
whereas actual tables do.  This can become a bottleneck in a very busy
system due to the additional time that EDB takes to ensure that the catalog
is flushed to disk, backed-up, etc.

Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:2DCEEA85-BAC0-40A9-87E1-5623C264DE58@news.elevatesoft.com...
>
> I would strongly suggest using temporary tables (CREATE TEMPORARY TABLE)
> instead of actual tables.  Temporary tables do not cause a catalog update,
> whereas actual tables do.  This can become a bottleneck in a very busy
> system due to the additional time that EDB takes to ensure that the
> catalog is flushed to disk, backed-up, etc.
>

Got it!  Thanks again Tim

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Tim

>I would strongly suggest using temporary tables (CREATE TEMPORARY TABLE)
>instead of actual tables. Temporary tables do not cause a catalog update,
>whereas actual tables do. This can become a bottleneck in a very busy
>system due to the additional time that EDB takes to ensure that the catalog
>is flushed to disk, backed-up, etc.

I'm baffled with this. If there's no catalog update how can you query temprarytables?

Roy

Jeff,

My way of dealing with the problem you describe is to use TClientDataset. It means 2 extra (very light weight) components must be created for each query, but I have bundled them into a single object, with the EDB Query hidden as a private field of my TClientDataset.

I have a SQL string property to the class, which when set, sets the SQL of the EDB Query field, and opens it.

TClientDataset's data is always read-write, regardless of the state of the underlaying data. Of course you have to manually code UPDATE's ... but the object has some useful methods to help with this.

You can then use the TClientDataset with any data-control, edit, grid etc.

Roy,

<< I'm baffled with this. If there's no catalog update how can you query
temprarytables? >>

The update is in-memory, specific to the current session, as opposed to a
"write the catalog to disk" type of update that is visible to all sessions.

Tim Young
Elevate Software
www.elevatesoft.com