Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 12 total |
SELECT INTO memory table question |
Sat, Jan 21 2006 1:00 PM | Permanent Link |
Dave M | When using SELECT INTO with a memory table and a prepared query, unless the query is
unprepared (creating a speed penalty) I receive DBISAM error 11013, Access Denied, when I try to access the table with Table.Open. Questions: 1) I assume this is by design? 2) If so, could this be changed in the future? I suppose this can be worked around by avoiding the SELECT INTO memory table and using SaveToStream/LoadFromStream with an already created memory table; however, this can be a little less convenient. Dave M |
Sat, Jan 21 2006 1:02 PM | Permanent Link |
"Ralf Mimoun" | Dave M wrote:
> When using SELECT INTO with a memory table and a prepared query, > unless the query is unprepared (creating a speed penalty) I receive > DBISAM error 11013, Access Denied, when I try to access the table > with Table.Open. The query that creates the memory table must be closed (I hope I am right here). So, close it. If that means that you have to unprepare it, then do it. Ralf |
Sat, Jan 21 2006 1:14 PM | Permanent Link |
Dave M | "Ralf Mimoun" <nospam@rad-on.de> wrote:
Dave M wrote: > When using SELECT INTO with a memory table and a prepared query, > unless the query is unprepared (creating a speed penalty) I receive > DBISAM error 11013, Access Denied, when I try to access the table > with Table.Open. The query that creates the memory table must be closed (I hope I am right here). So, close it. If that means that you have to unprepare it, then do it. Ralf ------------------------------------------------------------ The query is closed. Perhaps I should have mentioned that. My question is about the need to UnPrepare, which causes a performance penalty on next execution. |
Sat, Jan 21 2006 1:30 PM | Permanent Link |
"Ralf Mimoun" | Dave M wrote:
.... > The query is closed. Perhaps I should have mentioned that. My > question is about the need to UnPrepare, which causes a performance > penalty on next execution. Preparing is something like a half open. Performance penalties hurt, but sometimes there is no other solution. Oh, wait, try something like SELECT [something] from [some table] WHERE TRUE=FALSE at the end of the query. Some statement that needs practically no time. Will cut the connection to the created in-memory table. I don't know if it helps but I have something deep inside my stem brain that I did that a while ago. Ralf |
Sat, Jan 21 2006 2:35 PM | Permanent Link |
Dave M | "Ralf Mimoun" <nospam@rad-on.de> wrote:
...Performance penalties hurt, but sometimes there is no other solution. ---------------------------------------------------------- The work around I did is not using a memory table, instead using a stream to transfer data to a table. This allows the use of a prepared query, since there is no memory table. Maybe the memory table is being created with an exclusive attribute, and since the query is prepared, the exclusive table is being kept open, leading to the access error. *If* this is the case, changing this might involve a syntax extention for SELECT INTO. |
Sat, Jan 21 2006 2:43 PM | Permanent Link |
Dave M | Dave M said: -------------------------------------------------------------------------------------------------------------------------------- The work around I did is not using a memory table, instead using a stream to transfer data to a table. This allows the use of a prepared query, since there is no memory table. -------------------------------------------------------------------------------------------------------------------------------- What this messge should have said is that I pre-created the memory table and did not use a SELECT INTO memory table. The streaming was done from the Query to the pre-created memory table. |
Sat, Jan 21 2006 5:30 PM | Permanent Link |
Michael Baytalsky | Just guessing: could you pre-create the table with create table statement and then use INSERT INTO table SELECT .... sql statement? When you do select into it tries to actually re-create (or empty) the table, so the table must be accessed exclusively. INSERT should not require exclusive access - or so it seem. Regards, Michael Dave M wrote: > Dave M said: > -------------------------------------------------------------------------------------------------------------------------------- > The work around I did is not using a memory table, instead using a stream to transfer data > to a table. This allows the use of a prepared query, since there is no memory table. > -------------------------------------------------------------------------------------------------------------------------------- > What this messge should have said is that I pre-created the memory table and did not use a > SELECT INTO memory table. > The streaming was done from the Query to the pre-created memory table. > > > |
Sat, Jan 21 2006 7:11 PM | Permanent Link |
Dave M | Michael Baytalsky <mike@contextsoft.com> wrote:
Just guessing: could you pre-create the table with create table statement and then use INSERT INTO table SELECT .... -------------------------------------------------------------------- I wonder.. do memory tables *have* to remain as exclusive access once the SELECT INTO query has completed? Maybe SELECT INTO [SHARED]? Your suggestion works fine, and doesn't involve the streaming "hack" , but I also have needs to insert into tables from the results of a UNION. I just read the docs. Looks like this isn't allowed.... INSERT INTO Table(...) SELECT .... FROM .... UNION SELECT... FROM... So, besides the INSERT INTO memory table question/minor issue, there is this second issue. So, as a workaround, I am still doing a UNION, then streaming ... Dave |
Sat, Jan 21 2006 7:16 PM | Permanent Link |
"Robert" | "Dave M" <mr_mensch2@hotmail.com> wrote in message news:04BD9B05-5F0C-4DE7-9CC7-A455E757A1F0@news.elevatesoft.com... > I just read the docs. Looks like this isn't allowed.... > > INSERT INTO Table(...) > > SELECT .... > FROM .... > > UNION > > SELECT... > FROM... > Shure it is. I do it all the time. Robert |
Sat, Jan 21 2006 8:04 PM | Permanent Link |
Dave M | "Robert" <rkaplan@AdvantSoft.com> wrote:
Shure it is. I do it all the time. Robert -------------------------------------------------------------------------------------------------------------- I am in the process of converting an app to DBISAM. (First time user.) Didn't even try the SELECT with UNION. The code was already done for the streaming, so that was used. Also, I went by page 205 of the manual (dbisam4d2006.pdf): "The INSERT statement can use a single SELECT statement as the source for the new rows, but not multiple statements joined with UNION." Thanks, the SELECT & UNION does work. Dave M. |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |