Hi,

I want to create a table in a memory-database if it doesn't exist.
Use MemoryDB is obviously not allowed in procedures. So: how can I do that?

BEGIN
 DECLARE Result CURSOR with Return for Stmt;
 Prepare Stmt from 'select name from MemoryDb.Information.Tables
   where name = ''PrintRechnungenCopies''';
 Open Result;
 if RowsAffected(Stmt) = 1 then
    Execute Immediate 'delete from MemoryDB.PrintRechnungenCopies';
 ELSE
   USE MemoryDB;   <-------------------------------------------------------------
   Execute Immediate 'Create Table PrintRechnungenCopies as
     select * from Informa.PrintRechnungen';
 end if;
END

Thanks. Uli.

Uli


I learnt this one a while back Essentially a CREATE TABLE statement can ONLY be run from a script that has the appropriate database set.

Set the databasename at the component level to the memory database then reference all other db's you use within the script apart from the memory db.

So your    USE MemoryDB;  is there - sort of - but can't be set from within a procedure.

Rather badly explained but I hope it helps.

Roy Lambert [Team Elevate]

Uli


I've just read another post from Tim and USE database is there, so I think we need Tim to clarify.

Roy Lambert [Team Elevate]

Roy,

> I learnt this one a while back Essentially a CREATE TABLE statement can ONLY be run from a script that has the appropriate database set.
> So your    USE MemoryDB;  is there - sort of - but can't be set from within a procedure.

I know all that. The point is that it must be possible to create an
In-Memory-Table within a procedure of a non-memory database.
Tim will tell us how it works, I hope.

Regards Uli

Uli

I had asked the same question.
See the thread DBISAM -> ElevateDB posted in NG elevatedb.general (04/04/08)

Eduardo

Jose,

> I had asked the same question.
> See the thread DBISAM -> ElevateDB posted in NG elevatedb.general (04/04/08)

I read that, but the difference is, that you are using a script and want
to use a procedure.

Regards Uli

Uli,

<< I know all that. The point is that it must be possible to create an
In-Memory-Table within a procedure of a non-memory database.Tim will tell us
how it works, I hope. >>

No, you cannot do so.  For stored procedures, you should use a temporary
table instead of an in-memory table if you want a temporary table.  Any
objects contained with a specific database and catalog can only access
objects within that same database catalog.

Scripts and jobs are not catalog-level objects, so they can use the USE
statement to access multiple databases.  The upcoming session-level
procedures and functions that Roy asked for will also be able to use the USE
statement.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> No, you cannot do so.  For stored procedures, you should use a temporary
> table instead of an in-memory table if you want a temporary table.  Any
> objects contained with a specific database and catalog can only access
> objects within that same database catalog.

Thanks for the clarification.

Regards Uli