I am slowly looking at moving from DBISAM to EDB and am using the trial version to redesign a very old app before demonstrating it to the user  A big part of the original app is its use of memory tables in preparing data for use by Fastreport   I include the use of the following procedure to create these tables and as can be seen the properties of the fields so generated are automatically derived by referencing various TFields that already exist in the permanent database   It looks to me as though I would need to do a lot of string manipulation in order to prepare the SQL in  EDB just to replace this simple procedure   Maybe someone has done something similar or has some pointers on how to make this process as painless as possible

Many thanks

Mike

procedure CreateMemTable(nameoftable :string; const Defns: array of Tfield);

var
i: integer;
TableToCreate: TDBISAMTable;
AFieldDef: TFieldDef;

begin
 TableToCreate:=TDBISAMTable.Create(Application);
 try
   with TableToCreate do
     begin
       DatabaseName:='Memory';
       TableName:= nameoftable;
       Active := False;
       Exclusive:=True;

       for i := Low(Defns) to High(Defns) do
         begin
           AFieldDef := FieldDefs.AddFieldDef;
           AFieldDef.Name := Defns[i].FieldName;
           AFieldDef.datatype := Defns[i].datatype;
           AFieldDef.size := Defns[i].size;
           AFieldDef.Required := False;
         end;

       if not Exists then
         CreateTable;
     end
 finally
   TableToCreate.Free;
 end;
end;

Michael,

ElevateDb is more SQL oriented. You can accomplish the same using SQL, as in:

--Connect to your memory database
use MyMemoryDB;   

--Create the MyMemTable based on columns in MyDiskTable in your other database.
create table MyMemTable as (Select * from MyDiskDB.MyDiskTable) WITH NO DATA;

--If you also want to transfer the data, use "WITH DATA" instead of "WITH NO DATA".
--Using SQL you can limit which columns to transfer over, and even use table joins or views.
--You will still need to build indexes on MyMemTable if needed, using "Create Index ..." statement.
--All of this code could be put into stored procedures in the memory database so it creates the tables when necessary. This puts the intelligence into the database so it is more easily modified compared to recompiling your program.

Barry

Barry wrote:

Michael,

ElevateDb is more SQL oriented. You can accomplish the same using SQL, as in:

--Connect to your memory database
use MyMemoryDB;   

--Create the MyMemTable based on columns in MyDiskTable in your other database.
create table MyMemTable as (Select * from MyDiskDB.MyDiskTable) WITH NO DATA;

--If you also want to transfer the data, use "WITH DATA" instead of "WITH NO DATA".
--Using SQL you can limit which columns to transfer over, and even use table joins or views.
--You will still need to build indexes on MyMemTable if needed, using "Create Index ..." statement.
--All of this code could be put into stored procedures in the memory database so it creates the tables when necessary. This puts the intelligence into the database so it is more easily modified compared to recompiling your program.

Barry

Thanks for reply  My SQL is somewhat limited but I can see that this in fact once I have got the syntax issues  sorted then this will be a better solution  This unfortunately is the time consuming part

Michael,

just a hint:

when you select a table in EDBManager, you'll see the complete
sql-statement to create this table in the right lower tab.

Also:

1. Create a new Script.
2. Drag a table from the treeview into the script and you'll get the
code to create the table. Same thing works with indexes, procedures and
functions.

Uli

Uli Becker wrote:

Michael,

just a hint:

when you select a table in EDBManager, you'll see the complete
sql-statement to create this table in the right lower tab.

Also:

1. Create a new Script.
2. Drag a table from the treeview into the script and you'll get the
code to create the table. Same thing works with indexes, procedures and
functions.

Uli

Well that's something new I have learnt today.  One thing that I cannot seem to find good info on is when and why I would use scripts versus statements   Maybe you can clear that up or point me to a good explanation  eg  I see the term EXECUTE IMMEDIATE used Scripts which leads me to wonder there are other ways there may be to EXECUTE them as well

Thanks

Mike


Thanks

Michael,

<<
One thing that I cannot seem to find good info on is when and why I
would use scripts versus statements   Maybe you can clear that up or
point me to a good explanation  eg  I see the term EXECUTE IMMEDIATE
used Scripts which leads me to wonder there are other ways there may be
to EXECUTE them as well
>>

You'll need a script (or a stored procedure) if you want to execute more
than one statement or if you need parameters, cursors etc.

With a script (or stored procedure) you can do quite complex things,
e.g. pass params, lookup values based on these params and update/insert
records depending on the lookup values. As well you can use transactions
to make sure that the integrity of the database is not violated etc....

Execute Immediate (from the manual):

<<
Use this statement to execute the specified DDL, DML, or administrative
SQL statement. If the SQL statement is parameterized, then you can use
the USING clause to specify the values to use for the parameters. The
values are in left-to-right order, corresponding to how the parameters
were declared in the SQL statement.
>>

There are a number of samples for dynamic SQL in the manual, if you need
sample code for your special needs, feel free to ask here.

Uli

Michael,

<< I am slowly looking at moving from DBISAM to EDB and am using the trial
version to redesign a very old app before demonstrating it to the user  A
big part of the original app is its use of memory tables in preparing data
for use by Fastreport   I include the use of the following procedure to
create these tables and as can be seen the properties of the fields so
generated are automatically derived by referencing various TFields that
already exist in the permanent database   It looks to me as though I would
need to do a lot of string manipulation in order to prepare the SQL in  EDB
just to replace this simple procedure   Maybe someone has done something
similar or has some pointers on how to make this process as painless as
possible  >>

I've made this into a FAQ:

http://www.elevatesoft.com/supportfaq?action=view&category=edb&question=create_table_from_fielddefs

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

Michael,

<< I am slowly looking at moving from DBISAM to EDB and am using the trial
version to redesign a very old app before demonstrating it to the user  A
big part of the original app is its use of memory tables in preparing data
for use by Fastreport   I include the use of the following procedure to
create these tables and as can be seen the properties of the fields so
generated are automatically derived by referencing various TFields that
already exist in the permanent database   It looks to me as though I would
need to do a lot of string manipulation in order to prepare the SQL in  EDB
just to replace this simple procedure   Maybe someone has done something
similar or has some pointers on how to make this process as painless as
possible  >>

I've made this into a FAQ:

http://www.elevatesoft.com/supportfaq?action=view&category=edb&question=create_table_from_fielddefs

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com


Thanks that is very useful

Mike