Hi (again) Folks,

I am hitting a problem with MEMORY Tables in some situations.

Take the following SQL:

--

SELECT MAX(ID) as ID, StockID

INTO MEMORY\MaxIDs

FROM Valuations

GROUP BY StockID

;

SELECT ID, StockID, Price

FROM Valuations

WHERE ID IN
(

SELECT ID

FROM MEMORY\MaxIDs

)

ORDER BY StockID

--

Sometimes I get a "cannot access table MaxIDs" error message and the query refuses to run.
This happens when something previously has messed up ...

I understand that it happens when a query is left hanging by a failed process, such as a
PC crashing.

I am considering adding the following line to the start of all my queries of this type:

--

DROP TABLE IF EXISTS MEMORY\MaxIDs

--

But I am not sure whether this solves the problem!

- What is the best way to deal with "hanging queries" which may have left stray
memory-tables around?
- Roughly what are the circumstances in which this happens?

adam

>Sometimes I get a "cannot access table MaxIDs" error message and the query refuses to run.
>This happens when something previously has messed up ...
>
>I understand that it happens when a query is left hanging by a failed process, such as a
>PC crashing.

I presume you mean a crash that doesn't require a reboot, and even then I'm doubtful. Memory tables are isolated to (I think) the Windows process (which is why you can share them in threads) so anything that requires an app restart should get its own process and hence its own memory tables. It much more likely that its something in your code.

>I am considering adding the following line to the start of all my queries of this type:
>
>DROP TABLE IF EXISTS MEMORY\MaxIDs
>
>But I am not sure whether this solves the problem!

It depends. If your code is intended to overwrite the table (say using SELECT INTO) simply trying to drop it probably won't work either.

>- What is the best way to deal with "hanging queries" which may have left stray
>memory-tables around?

The way I handle it is not to get into the situation to start with Essentially I use a unique name for all memory tables, generally made up of a prefix (like MaxIDs) and with IntToStr(GetTickCount) as a suffix.

>- Roughly what are the circumstances in which this happens?

The only thing I can think of (I'm sure Tim will have more) is an incorrectly handled try..except block which leaves things in an ambiguous state.

Roy Lambert [Team Elevate]

Adam,

<< Sometimes I get a "cannot access table MaxIDs" error message and the
query refuses to run. This happens when something previously has messed up
....  >>

An INTO statement will always overwrite any existing in-memory table, so if
the first query works okay, then the second query is guaranteed to work.  If
the first query can't create the target table, then the second query simply
won't run, *unless* you have an OnQueryError event handler defined and are
telling DBISAM to ignore any errors in the first query execution
(Action=aaContinue).

--
Tim Young
Elevate Software
www.elevatesoft.com