Hello

I'm migrating an app from DBISAM, where I currently use in-memory tables particularly
because I can add a boolean field that the user uses to select various rows, using a check
box connected to the boolean field. I currently use SQL to select a range of records INTO
a memory table, then assign the memory table to a TDBISAMTable component.

I note that the MEMORY argument is only referred to in the CREATE DATABASE text in the
manual, so that appears to be a significant shift from DBISAM.

Are there any examples of in memory table creation that I could see please?

Regards & TIA

Peter

Peter


The change is that now memory is just a location for a database not a special directive. Tables are created exactly as you would create a disk based table but using a memory database.

The first thing you will have to do is create an in-memory database. If you click on the Create database button in EDBManager you'll see that there's two radio buttons - one for on disk the other for in memory. If you create the database and look at Explorer | SQL History you see the actual sql used

CREATE DATABASE "xxxx"
IN MEMORY

You then create tables using this database. The only difference between in-memory and on-disk is that disk based tables persist and memory ones don't.

Finally to see an example - just create one in EDBManager and look at SQL History eg

CREATE TABLE "Demo"
(
"one" VARCHAR(25) COLLATE "ANSI",
"two" INTEGER,
"three" TIMESTAMP,
CONSTRAINT "PK" PRIMARY KEY ("one")
)
VERSION 1.00
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768

Roy Lambert

Thanks Roy

I have also found your memory table script in Extensions, which I am working through.

With regard to the in-memory table that I wish to create, is there a way to obtain the DDL
from the target table, rather than hard wiring the DDL in the CREATE TABLE construct? I
appreciate that we cant just use 'SELECT * INTO MEMORY MemTable FROM TargetTable;' as I
currently use, but is there an equivalent?

Regards & TIA

Peter

Peter

This is the slightly modified script that Tim gave me. You pass in the select statement you want to use (eg SELECT * FROM fred) and off it goes.



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]

Roy

Thanks again, that code worked a treat. I changed it to...

SCRIPT (IN SQLStatement VARCHAR, IN TableName 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;
EXECUTE IMMEDIATE 'ALTER TABLE "'+TableName+'" ADD COLUMN "SelRow" BOOLEAN';

END

...and the boolean field is added as well. Great stuff. I used the parameters "InMemTable" and "select * from Tutorial.Customer".

I used the EDB Mgr to run the script, but I couldn't see how to induce the Parameters tab to open without trying to execute the SQL first. It would be
reasonable to assume that the Mgr doesn't know of the presence of the Parameters until it is executed, but I am new so I should ask.

The code is sufficiently generic for me use it a multitude of times; I guess that I should wrap in a Stored Procedure. Is that the usual practice?

Regards

Peter

Peter

>I used the EDB Mgr to run the script, but I couldn't see how to induce the Parameters tab to open without trying to execute the SQL first. It would be
>reasonable to assume that the Mgr doesn't know of the presence of the Parameters until it is executed, but I am new so I should ask.

This one got me as well, but in code to start with, the script has to be prepared before you can add parameters via ParamByName

>The code is sufficiently generic for me use it a multitude of times; I guess that I should wrap in a Stored Procedure. Is that the usual practice?

I have it in a TEDBScript on a datamodule in my app.

Unless you want a boolean in every table you could do something like



SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN AddBool BOOLEAN)
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 AddBool THEN
EXECUTE IMMEDIATE 'ALTER TABLE "'+TableName+'" ADD COLUMN "SelRow" BOOLEAN';
END IF;

END


Roy Lambert [Team Elevate]

Roy

Top idea - none of the current tables need the option, but in future...

Thanks heaps

Peter

Peter,

<< I used the EDB Mgr to run the script, but I couldn't see how to induce
the Parameters tab to open without trying to execute the SQL first. It would
be reasonable to assume that the Mgr doesn't know of the presence of the
Parameters until it is executed, but I am new so I should ask. >>

As Roy indicated, just hit the Prepare button first, and then the Parameters
tab will show up.

<< The code is sufficiently generic for me use it a multitude of times; I
guess that I should wrap in a Stored Procedure. Is that the usual practice?
>>

Sure, you can do that or leave it as a script.  Both execute directly on the
ElevateDB Server, but the stored procedure is nice because it will travel
with the database.

--
Tim Young
Elevate Software
www.elevatesoft.com