Hi Everybody,

I would like to perform the following task inside a procedure..

1.Execute a query (heavy) and fill a temporary table (or in memory) with the results.
2.Perform some additional processing on the temporary table
3.Return a cursor on the table create on step 1 to the client (Delphi application).

The procedure is almost finish, but have the following questions.

1.Do I need to drop the table created on step 1 after the cursor has been transverse. Does EDB do this automatically o should I explicit close it?

2.Since the procedure could be executed by more than one user, what would be the best approach to avoid conflicting with the table creation?

2.Which kind of temporary storage should I use for this kind of operation. Temporary or Memory?

I'm use to work with MS SQL and for this task we used temporary tables "#Table1", and I'm looking to achieve something similar with EDB.

Regards,
Mario

Mario,

<< 1.Do I need to drop the table created on step 1 after the cursor has been
transverse. Does EDB do this automatically o should I explicit close it? >>

It doesn't hurt to do so.  Temporary tables (CREATE TEMPORARY TABLE) will be
dropped automatically when the current session is closed, but there may be a
bit of time between creation and when the session is closed.

<< 2.Since the procedure could be executed by more than one user, what would
be the best approach to avoid conflicting with the table creation? >>

Using temporary tables will ensure that the tables are unique to each
session, even with the same table name.

<< 2.Which kind of temporary storage should I use for this kind of
operation. Temporary or Memory? >>

If you use temporary tables, then EDB will take care of this for you,
automatically.

<< I'm use to work with MS SQL and for this task we used temporary tables
"#Table1", and I'm looking to achieve something similar with EDB. >>

The equivalent in EDB is CREATE TEMPORARY TABLE instead of the plain CREATE
TABLE.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Mario


Ages ago Tim gave me a script to do this sort of thing.


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

I create the table name using Project.TableName := GetUnique(HHCommons.UserID + '_');

function GetUnique(const Preface: string = ''): string;
var
 startnow: int64;
 tmp: string;
begin
 startnow := GetTickCount;
 repeat
 until startnow <> GetTickCount;
 Result := Preface;
 if Result = '' then Result := 'X';
 tmp := IntToStr(startnow);
 Result := Result + Format('%x', [GetCurrentThreadID]);
 while tmp <> '' do begin
  Result := Result + Format('%x', [StrToIntDef(Copy(tmp, 1, 9), 0)]);
  Delete(tmp, 1, 9);
  if tmp <> '' then Result := Result + '_';
 end;
end;

I tend to use in-memory tables rather than temporary ones because I can pretty much guarantee that even after a crash they'll get cleaned up when I turn the PC off

Roy Lambert