Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
MEMORY Tables |
Tue, Dec 11 2007 6:57 AM | Permanent Link |
Adam | Dear All,
I am running a query in 2 sections, I show the rough plan below, but note that the actual SQL involves multiple joins & multiple MEMORY tables: Section 1: SELECT Fields, INTO MEMORY\Store FROM Table1 Section 2: SELECT Fields FROM MEMORY\Store -- It works just fine, but once run I can't run it again, I get an "access denied to table MEMORY\Store" error message. If I call DROP Table IF EXISTS MEMORY\Store With in the Delphi code at the end of the procedure that uses the query I also get the same "access denied" error message (note that the query which created the MEMORY\Store table is closed by the time this code is called). ... How do I manage my memory tables & close them after use? -- Adam |
Tue, Dec 11 2007 7:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
It sounds as though the in memory table is in use somewhere, or at least open. Are any components bound to it, is it used in a subsequent query which might have it open? Roy Lambert |
Tue, Dec 11 2007 11:05 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< It works just fine, but once run I can't run it again, I get an "access denied to table MEMORY\Store" error message. >> Make sure after the first run that you UnPrepare the query that created the in-memory table using the INTO clause. That is the only way to get it to release its exclusive lock on the in-memory table. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Dec 11 2007 12:20 PM | Permanent Link |
Adam | Brilliant Tim & a lightning fast response ... thanks Adam
|
Fri, Dec 14 2007 11:47 AM | Permanent Link |
Adam | Dear Tim,
The following code will not run, as the MEMORY tables created do not get freed / unlocked. I am following your recommendation of UnPreparing ... to no effect. No doubt I am doing something stupid. -- I have a TDBISAM descendent class with this procedure (I have pulled out a few lines with Except clauses etc, but all the critical stuff is here): procedure TabQ.RefreshData(aSQLStr: String; aLiveResult: Boolean=false); begin if (State IN [dsEdit, dsInsert]) then begin post; flushbuffers; end; close; Fields.Clear; SQL.clear; RequestLive:= ALiveResult; ReplaceParams(aSQLStr); SQL.Add(aSQLStr); Open; end; In an application I have the following lines: var SalesTotalSQLStr, SQLStr : string; begin ExecQ.RefreshData(SalesTotalSQLStr); calViewerQ.RefreshData(SQLStr); ShowTimedMessage('Loading Calendar'); LoadCalendar(calViewerQ); ExecQ.Close; ExecQ.Unprepare; end; SalesTotalSQLStr is: SELECT SI.DateWanted, ROUND(SUM(TotalNet)) as SumSales INTO MEMORY\CalSumSales FROM SalesInvoice SI WHERE SI.DateWanted BETWEEN '2007-11-01' and '2007-12-14' GROUP BY DateWanted ; SELECT SP.DateWanted, ROUND(SUM(TotalNet)) as SumPurchases INTO MEMORY\CalSumPurchases FROM StockPurchase SP WHERE SP.DateWanted BETWEEN '2007-11-01' and '2007-12-14' GROUP BY DateWanted ; -- So ExecQ grabs data & puts it into 2 MEM tables. The main SQLStr varies ... but uses the MEM tables. The code runs once ... then says it cannot unlock MEM tables when run a second time. Adam |
Fri, Dec 14 2007 12:18 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< The code runs once ... then says it cannot unlock MEM tables when run a second time. >> What is the error message that you're seeing ? Remember, the INTO clause completely overwrites any existing table with the same name, and opens the table exclusively. Therefore, the table will remain opened exclusive until the TDBISAMQuery component that executes the script is unprepared. You're un-preparing the ExecQ query, which isn't actually what is creating the in-memory tables - the script is. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Dec 14 2007 12:24 PM | Permanent Link |
"Robert" | "Adam" <adam@nospamplease.fullwellmill.co.uk> wrote in message news:2E422338-69F4-44B6-8796-71EB727BD05A@news.elevatesoft.com... > > The code runs once ... then says it cannot unlock MEM tables when run a > second time. I always put a DROP TABLE IF EXISTS before creating a new memory table. Works OK for me. Robert |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |