I read the threads about temporary table.

I have a SP which uses editable result sets. I have to keep more than 2
result sets open  in a form but the sp can create single name of CREATE
TEMPRARY....

For example,
If XXX(yyy...) creates temporary table zzz, I don' know how i can call
XXX(zzz) without closing/deleting old zzz.

Can the parameter  like CREATE TEMPORARY TABLE :TableName ... be used?

Or how can i create temoporary with unique table name in SP?

Tomas,

> Can the parameter  like CREATE TEMPORARY TABLE :TableName ... be used?
> Or how can i create temoporary with unique table name in SP?

You can pass a tablename as param. Use something like this:

'CREATE PROCEDURE "Test" (IN "MyTablename" VARCHAR(40) COLLATE ANSI)
 BEGIN
    DECLARE ResultCursor CURSOR for stmt;
    DECLARE FTableName VarChar;
    SET FTableName = MyTablename;
    prepare stmt from ''Create Temporary Table '' + FTablename + ''
   (
       "Field1" VARCHAR(10) COLLATE "ANSI_CI",
       "Field2" VARCHAR(30) COLLATE "ANSI"
   )'';
  Execute stmt;
END
';

Uli

It does not work.

This code does not work.

PROCEDURE "Test" (IN "tablename" VARCHAR(32) COLLATE ANSI)
BEGIN
DECLARE ftbl VARCHAR(32);
SET ftbl = tablename;
EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE '+ ftbl+ ' AS SELECT * FROM
USERLIST WITH DATA';
END

But this code works well.

PROCEDURE "Test" (IN "tablename" VARCHAR(32) COLLATE ANSI)
BEGIN
DECLARE ftbl VARCHAR(32);
SET ftbl = tablename;
EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE xxx  AS SELECT * FROM USERLIST
WITH DATA';
END

"ulibecker" <test@test.com> wrote in message
news:B49C2551-9623-48FA-AF42-0A2DA8760331@news.elevatesoft.com...
> Tomas,
>
>> Can the parameter  like CREATE TEMPORARY TABLE :TableName ... be used?
>> Or how can i create temoporary with unique table name in SP?
>
> You can pass a tablename as param. Use something like this:
>
> 'CREATE PROCEDURE "Test" (IN "MyTablename" VARCHAR(40) COLLATE ANSI)
>  BEGIN
>     DECLARE ResultCursor CURSOR for stmt;
>     DECLARE FTableName VarChar;
>     SET FTableName = MyTablename;
>     prepare stmt from ''Create Temporary Table '' + FTablename + ''
>    (
>        "Field1" VARCHAR(10) COLLATE "ANSI_CI",
>        "Field2" VARCHAR(30) COLLATE "ANSI"
>    )'';
>   Execute stmt;
> END
> ';
>
> Uli
>

Tomas


I've just posted the code I use to create memory tables into the extensions newsgroup. It was originally supplied by Tim and it works very well.

Roy Lambert

> This code does not work.
>
> PROCEDURE "Test" (IN "tablename" VARCHAR(32) COLLATE ANSI)
> BEGIN
> DECLARE ftbl VARCHAR(32);
> SET ftbl = tablename;
> EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE '+ ftbl+ ' AS SELECT * FROM
> USERLIST WITH DATA';
> END

I ran the procedure in 2.0 Build 1 and it works fine.

Which error do you get?
Maybe you have to use double quotes depending on the tablename you are using.

CREATE TEMPORARY TABLE "'+ ftbl+ '" AS...

Uli

I'm using 2.00 (Build 1) EDB Manager.

No luck.

"ulibecker" <test@test.com> wrote in message
news:496A3BF8-BDCA-469A-8C5B-1C567013CA0A@news.elevatesoft.com...
>
>> This code does not work.
>>
>> PROCEDURE "Test" (IN "tablename" VARCHAR(32) COLLATE ANSI)
>> BEGIN
>> DECLARE ftbl VARCHAR(32);
>> SET ftbl = tablename;
>> EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE '+ ftbl+ ' AS SELECT * FROM
>> USERLIST WITH DATA';
>> END
>
> I ran the procedure in 2.0 Build 1 and it works fine.
>
> Which error do you get?
> Maybe you have to use double quotes depending on the tablename you are
> using.
>
> CREATE TEMPORARY TABLE "'+ ftbl+ '" AS...
>
> Uli
>

Sorry, if you refuse to tell me at least the error you get, I can't help
you.

Uli

#700 An error was found in the statement at line 6 and column 19 (Missing
SELECT, INSERT.......)

Thanks for the help

"Uli Becker" <test@test.com> wrote in message
news:C7488CA1-86F1-4148-B6FD-8A0A1C39F0AA@news.elevatesoft.com...
> Sorry, if you refuse to tell me at least the error you get, I can't help
> you.
>
> Uli

My current full test procedure is as follows:

PROCEDURE "Test" (IN "tablename" VARCHAR(32) COLLATE ANSI)
BEGIN
DECLARE stmt STATEMENT;
DECLARE ftbl VARCHAR(32);
SET ftbl = tablename;
PREPARE stmt FROM 'CREATE TEMPORARY TABLE "'+ ftbl + '" AS SELECT * FROM
test WITH DATA';
EXECUTE stmt;
END

"Tomas" <group1000@gmail.com> wrote in message
news:F743EB65-CA31-43EB-B240-E132885C9C67@news.elevatesoft.com...
> #700 An error was found in the statement at line 6 and column 19 (Missing
> SELECT, INSERT.......)
>
> Thanks for the help
>
> "Uli Becker" <test@test.com> wrote in message
> news:C7488CA1-86F1-4148-B6FD-8A0A1C39F0AA@news.elevatesoft.com...
>> Sorry, if you refuse to tell me at least the error you get, I can't help
>> you.
>>
>> Uli
>
>

Tomas


Look at the example I've posted in the extensions ng. All you need to do is supply the SQL as input, or replace that with a VARCHAR in the bidy of the SP.

I know that ElevateDB will not overwrite a temporary table so you'll need to get rid of it first if there's already a table of that name.

I'd also just use VARCHAR rather than VARCHAR(32) - ElevateDB will then allow any length from 1 to as long as a string can get.

I also tried running your script in EDBManager (V2.01) and, after changing test to one of my tables, it works.

Roy Lambert