Hi Guys,
Trying o create an in-memory table.  Have the following based on what I can follow from the Help files.
{sql}
create temporary table MyCustomers (like customers) as select * from Customers with data
{sql}
This will be the SQL in an EDBQuery that gets Opened to create the table then closed.
Currently trying to get it to work in EDBMgr.
It falls over at the 'as'.
What have I missed please?
Regards & TIA,
Ian

Ian


Try this script given to me years ago by Tim

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

You then do something like

 dm.SelectInto.Prepare;
 dm.SelectInto.ParamByName(F_SQLStatement).AsString := 'SELECT *,CAST(NULL AS CLOB) AS _UsrData FROM "' + dm.DB.Database + '"."Config" WHERE _UserSettable = TRUE';
 dm.SelectInto.ParamByName(F_TableName).AsString := 'UsrConfig';
 dm.SelectInto.ParamByName(F_IdxSet).AsString := '_ID';
 dm.SelectInto.ExecScript;
 dm.SelectInto.Close;

Make sure the script's DatabaseName is set to your in-memory database and that your qualify the tables in your sql


Roy Lambert

Gulp!  Thanks Roy.
If I read this right and your last sentence, then I have to create a Memory Database for my Memory Table to be associated with?
I can't just have a free standing in-memory table?
What I am endeavoring to o is read all the look-up tables from the main Database into memory when the App(s) initialise so there is no need to go back to the Database/Disk when lookups are required.
Regards,
Ian

Ian


If you're using oncalc fields that will work, and yes you do need to create the in-memory database.

Unlike DBISAM where there was just one in-memory database Tim has expanded things so that you can have as many as you want. It has good and bad points, but creating the database is trivial, and if you do it in EDBManager and then save as part of the catalog its always there. I tend to create an in-memory (strangely enough called Memory) at the same time as creating on-disk database(s).

Using navigational methods you just refer to the table but when writing SQL you'll need to preface the table name with the database name.

However, if you're going c/s then you may want to rethink your strategy. I think (could well be wrong) that in-memory tables are created in the servers memory so you get its memory populated rather than the local machines. I sort of remember a request to change this and it might be implemented so that it uses local rather than server memory but I can't remember. With the new caching Tim's introduced I'm not sure you'd get any benefit for lookup tables.

Tim will know.

Roy Lambert

Ian


Just remembered another critical point. C/S in-memory tables are now shared unless they are created as TEMPORARY then they're private again.

What this means is that if you're running f/s & c/s versions of your software you'll either need to create all in-memory tables as TEMPORARY or (and this would be my preferred option) check to see if the table already exists and only create if not.

Have a look in the extensions newsgroup for

Message-ID: <90CFDB87-183A-40C9-B206-D55355AE6CAD@news.elevatesoft.com>
Date: Thu, 15 May 2014 14:40:33 +0100
Subject: A few EDB Utilities

It has a utility to test if a tables exists

Roy Lambert

Hi Roy,
Thanks for that.  Much to think about and learn.
Downloaded your nlhEDB.pas, will have a look tomorrow.
Gotta have a look at EDBMgr to see how to make an in-memory DB.  

Cheers,
Ian

Ian


>Gotta have a look at EDBMgr to see how to make an in-memory DB.

Almost the same as an on-disk one - just check in memory rather than on disk <vbg>

or using SQL

CREATE DATABASE "Memory"
IN MEMORY
UNENCRYPTED CATALOG


Roy

Ian,

<< Gulp!  Thanks Roy.
If I read this right and your last sentence, then I have to create a Memory Database for my Memory Table to be associated with?

I can't just have a free standing in-memory table? >>

If you want a free-standing in-memory table, then you should consider just using a temporary table (like you were in your original example above).  They aren't stored in memory entirely, but are optimized to not write to disk unless absolutely necessary, so they are effectively the same thing and can be mixed in with regular tables.  Furthermore, they are automatically private to sessions and are automatically dropped by ElevateDB when a session terminates.

Tim Young
Elevate Software
www.elevatesoft.com

Tim


<<They aren't stored in memory entirely, but are optimized to not write to disk unless absolutely necessary,>>

Do you know anywhere I can get my brain dry cleaned? Reading that I thought "useful bit of intelligence" my second thought was "that implies normal tables do write to disk when not absolutely necessary"

Roy

Ian


Just to continue what Tim said - a small table will probably be held totally in memory anyway, but, and this is a very important but, unless they are large and static (ie rarely change) lookup tables then its probably not worth it.

I should have said this before but I'd forgotten and it just that Tim's post has reminded me. Even with a very chatty app you probably won't get sufficient performance benefit to justify it. Even before that latest release Tim's & Windows caching did a pretty good job.

Unless your lookup tables don't change much you have the fun and games of keeping things in sync. It may not matter in your app but in the one I worked on I wanted to make sure everyone entered the same code for someone's job and anyone could add a new code. I worked out how to keep a dozen different copies of the master table in sync but ultimately it was easier to just use normal on-disc tables.

Over a slow or unstable LAN or WAN it may be different.

Roy Lambert