Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
Is it possible to free a Memory Table in a SQL Script? |
Fri, Apr 10 2009 5:40 AM | Permanent Link |
adam | 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? |
Fri, Apr 10 2009 10:14 AM | Permanent Link |
Roy Lambert NLH Associates 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 ... > >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] |
Mon, Apr 13 2009 6:31 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |