Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
Advice on Temporary/Memory tables needed. |
Mon, Jul 29 2013 12:37 PM | Permanent Link |
Mario Enríquez Open Consult | 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 |
Mon, Jul 29 2013 1:20 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Jul 29 2013 1:48 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |