Another locking isse

I'm still not comfortable with NexusDB locking mechanism:

- I've set the seoCloseInactiveFolders and seoCloseInactiveTables options on
the server.

- I've created and opened a memory table in a SQL script (shown later).

- Now I'm setting the SQL with the same script, except I've added a DROP
TABLE statement:

drop table "<Test>";
create Table "<Test>"(
ID integer,
StrField char(20),
primary key(ID)
);
insert into "<Test>" values(1, 'Row 1');
insert into "<Test>" values(2, 'Row 2');
insert into "<Test>" values(3, 'Row 3');

select *
from "<Test>"

Execution error: "Table is open. [$272F/10031]"

What is keeping the table open ? The query closes when the SQL is set, and
no other data access objects are accessing the table. It doesn't even help
to close the session.

Ole Willy Tuv

Ole Willy Tuv wrote:
> drop table "<Test>";

> Execution error: "Table is open. [$272F/10031]"

Is this the only table in the database or do you have regular tables
too? Is there a transaction running on the database?

--
David Marcus

David,

<< Is this the only table in the database or do you have regular tables too?
Is there a transaction running on the database? >>

I have regular tables in the database too, but none of them are open. I've
not started an explicit transaction, if that what you mean ?

What I'm doing is:

1) Assigning the script (without the drop table statement) and executing the
query, which creates the table with data and opens it.

2) Adding the drop table statement to the script, assign the revised script
and execute the query, which fails with the exception - probably on the drop
table statement.

Setting the SQL the second time closes the query as it should (I even
additionally closed the session object) and no other tables are open.

Ole

Ole Willy Tuv wrote:
> I have regular tables in the database too, but none of them are open. I've
> not started an explicit transaction, if that what you mean ?

Yes, that's what I meant.

> What I'm doing is:
>
> 1) Assigning the script (without the drop table statement) and executing the
> query, which creates the table with data and opens it.
>
> 2) Adding the drop table statement to the script, assign the revised script
> and execute the query, which fails with the exception - probably on the drop
> table statement.
>
> Setting the SQL the second time closes the query as it should (I even
> additionally closed the session object) and no other tables are open.

I don't know why this doesn't work. Perhaps it is a bug.

--
David Marcus

I see it too; issue #434.

--


Eivind Bakkestuen [NDD]
Please, no email unless requested.
Search Borland and third-party newsgroups here: www.tamaracka.com


"Ole Willy Tuv" <owtuv@online.no> wrote in message news:3f7850fa@wic040d....
> I'm still not comfortable with NexusDB locking mechanism:
>
> - I've set the seoCloseInactiveFolders and seoCloseInactiveTables options
on
> the server.
>
> - I've created and opened a memory table in a SQL script (shown later).
>
> - Now I'm setting the SQL with the same script, except I've added a DROP
> TABLE statement:
>
> drop table "<Test>";
> create Table "<Test>"(
> ID integer,
> StrField char(20),
> primary key(ID)
> );
> insert into "<Test>" values(1, 'Row 1');
> insert into "<Test>" values(2, 'Row 2');
> insert into "<Test>" values(3, 'Row 3');
>
> select *
> from "<Test>"
>
> Execution error: "Table is open. [$272F/10031]"
>
> What is keeping the table open ? The query closes when the SQL is set, and
> no other data access objects are accessing the table. It doesn't even help
> to close the session.
>
> Ole Willy Tuv
>
>

Per,

<< The SQL engine generally keeps the source tables open between Execs so
that it doesn't have to bind them each time the script is executed, that's
why the DROP fails. The engine does have explicit support for dropping a
table which was created earlier in the same SQL script, however, so the
following will work: >>

I see, and I also understand that your goal is to get NexusDB execute
fastest possible. However, this "feature" comes at the cost of less
flexibility. After all, SQL DDL often includes drop statements prior to
create statements. Could this be user controlled by the
seoCloseInactiveTables option or similar ?

Ole

Ole,

The SQL engine generally keeps the source tables open between Execs so that
it doesn't have to bind them each time the script is executed, that's why
the DROP fails. The engine does have explicit support for dropping a table
which was created earlier in the same SQL script, however, so the following
will work:

create Table "<Test>"(
ID integer,
StrField char(20),
primary key(ID)
);
insert into "<Test>" values(1, 'Row 1');
insert into "<Test>" values(2, 'Row 2');
insert into "<Test>" values(3, 'Row 3');

select *
from "<Test>";

drop table "<Test>";

Of course, this assumes that a live result set is *not* requested.

IHTH

- Per

"Ole Willy Tuv" <owtuv@online.no> wrote in message news:3f7850fa@wic040d....
> I'm still not comfortable with NexusDB locking mechanism:
>
> - I've set the seoCloseInactiveFolders and seoCloseInactiveTables options
on
> the server.
>
> - I've created and opened a memory table in a SQL script (shown later).
>
> - Now I'm setting the SQL with the same script, except I've added a DROP
> TABLE statement:
>
> drop table "<Test>";
> create Table "<Test>"(
> ID integer,
> StrField char(20),
> primary key(ID)
> );
> insert into "<Test>" values(1, 'Row 1');
> insert into "<Test>" values(2, 'Row 2');
> insert into "<Test>" values(3, 'Row 3');
>
> select *
> from "<Test>"
>
> Execution error: "Table is open. [$272F/10031]"
>
> What is keeping the table open ? The query closes when the SQL is set, and
> no other data access objects are accessing the table. It doesn't even help
> to close the session.
>
> Ole Willy Tuv
>
>

Per,

I forgot to mention that my query is closed between the two script
executions. One wouldn't expect the base tables being kept open in this
situation when the seoCloseInactiveFolders and seoCloseInactiveTables
options are set.

Ole

"Ole Willy Tuv" <owtuv@online.no> wrote in message news:3f7957ef@wic040d....
> I see, and I also understand that your goal is to get NexusDB execute
> fastest possible. However, this "feature" comes at the cost of less
> flexibility. After all, SQL DDL often includes drop statements prior to
> create statements. Could this be user controlled by the
> seoCloseInactiveTables option or similar ?

We're discussing it. Note, however, that it's only an issue if something in
the SQL script pulls data out of an table. It doesn't affect pure DDL. IOW,
something like CREATE TABLE doesn't lock (open, even) the table.

- Per

"Ole Willy Tuv" <owtuv@online.no> wrote in message news:3f79597a@wic040d....
> I forgot to mention that my query is closed between the two script
> executions. One wouldn't expect the base tables being kept open in this
> situation when the seoCloseInactiveFolders and seoCloseInactiveTables
> options are set.

Huh? No, that does sound a bit odd. I'll see what's going on.

- Per