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

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

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

Brilliant Tim & a lightning fast response ... thanks 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

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

"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