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

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

"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.

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

"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.  

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.

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.
>
>
>

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

"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

"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.