Migrating some code from DBISAM 4 to ElevateDB

I have in my apps a lot of code like below:

with Query do begin
   Sql.Clear
   Sql.Add('select * into memory\m1 from table1 where condition1');
   Sql.Add(';');
   Sql.Add('select * into memory\m2 from table2 where condition2');
   Sql.Add(';');
   Sql.Add('select anything from anothertable a');
   Sql.Add('inner join memory\m1 on (a.field=m1.field)');
   Sql.Add('inner join memory\m2 on (a.field=m2.field)');
   Open;
end;

Since ElevateDB does not have scripts like DBISAM I think the conversion
should be:

MemoryDB.Execute('create table m1 as select * from table1 where
condition1');
MemoryDB.Execute('create table m2 as select * from table2 where
condition2');
cSql := 'select anything from anothertable a  ';
cSql := cSql + 'inner join memory.m1 on (a.field=m1.field)  ';
cSql := cSql + 'inner join memory.m2 on (a.field=m2.field)';
DiskDB.Execute(cSql);

Any comments ? Is there an easy way to port the above code from DBISAM to
ElevateDB ?

Eduardo

Jose


ElevateDB does have scripts, its just that they're nothing like the DBISAM ones You could build what you have below into a single script, but there is a gotcha which has got you (I think)

>MemoryDB.Execute('create table m1 as select * from table1 where
>condition1');
>MemoryDB.Execute('create table m2 as select * from table2 where
>condition2');

If table1 and table2 are not in the memory database you'll have to preface them with their database. The other point to be wary about is that unlike the old SELECT INTO functionality CREATE TABLE AS does not REPEAT NOT remove any prior instances of the table so make sure you've dropped m1 and m2 first if they exist.

>cSql := 'select anything from anothertable a ';
>cSql := cSql + 'inner join memory.m1 on (a.field=m1.field) ';
>cSql := cSql + 'inner join memory.m2 on (a.field=m2.field)';
>DiskDB.Execute(cSql);

This one looks fine.

> Is there an easy way to port the above code from DBISAM to
>ElevateDB ?

If there was it would have saved me a lot of work

Roy Lambert [Team Elevate]

Eduardo,

<< I have in my apps a lot of code like below:

Since ElevateDB does not have scripts like DBISAM I think the conversion
should be: >>

The proper script is something like this:

SCRIPT
BEGIN
  DECLARE ResultCursor CURSOR WITH RETURN FOR ResultStmt;
  USE MemoryDB;
  EXECUTE IMMEDIATE 'CREATE TABLE M1 AS select * from Test.Customer where
State=''CA'' WITH DATA';
  EXECUTE IMMEDIATE 'CREATE TABLE M2 AS select * from Test.Customer where
State=''CA'' WITH DATA';
  USE Test;
  PREPARE ResultStmt FROM 'select * from orders a
inner join MemoryDB.m1 on (a.custno=m1.custno)
inner join MemoryDB.m2 on (a.custno=m2.custno)';
  OPEN ResultCursor;
END

However, there's a bug in 1.09 B1 whereby the last SELECT statement will
cause an error when trying to populate the SELECT columns for the * select
column list.  This will be fixed in 1.09 B2, which I should have ready by
late this evening or tomorrow afternoon.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim/Roy

I really apreciate your comments.

Only one doubt more (at least for now).

May I use "declare" just before "prepare" ? Or I always have to use at
beginning of the script ?

Eduardo

Jose


You can try putting DECLARE's elsewhere but anywhere but the top and ElevateDB has a tantrum

At least up to 1.08

Roy Lambert [Team Elevate]

Eduardo,

<< May I use "declare" just before "prepare" ? Or I always have to use at
beginning of the script ? >>

Currently you have to put all DECLARE statements at the beginning of the
script.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim/Roy

Thanks for answer

Eduardo