Unfortunately EDB won't allow DDL statements inside of a transaction.

I'd like to be able to drop a list of tables and views ONLY if I can drop all of them.  (I'm using Delphi)

Example:
Tables: TableA_1, TableA_2, TableA_3
Views: vTableA_1, vTableA_2, v_TableA_3

I'd like to be able to drop all of these tables and views. But if someone has one of them open for reading/writing, I'd like to prevent any of them from being dropped.

The only solution I can see is to instantiate TEDBTable's and open the tables & views with Exclusive=True. If any of them fails to open, then I know one or more of them are in use.

Of course this means I will also have to close these TEDBTable's prior to executing the SQL to drop these same tables and views. This leaves open a small window where someone (Murphy?) can then get read/write access to the tables I'm dropping. So it is not 100% bullet proof.

Is there a better solution?

TIA
Barry

Barry

There are clues in the manual - it mentions a database read lock when doing a backup, but its obviously not something available to us.

The only thing I can think of is what I'm done in a couple of apps. If you have logon control to the app 1) don't run the restructure if anyone else is logged on and 2) prevent them from logging on until you're done.

Roy Lambert [Team Elevate]

Barry,

<< I'd like to be able to drop all of these tables and views. But if someone
has one of them open for reading/writing, I'd like to prevent any of them
from being dropped.

The only solution I can see is to instantiate TEDBTable's and open the
tables & views with Exclusive=True. If any of them fails to open, then I
know one or more of them are in use. >>

Just to confirm: do you need a check on whether anyone has the database
open, or whether anyone has a specific set of tables open ?  IOW, does your
DDL "transaction" have, or do you want it to have, exclusive access to the
database ?

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Just to confirm: do you need a check on whether anyone has the database
>open, or whether anyone has a specific set of tables open ?  IOW, does your
>DDL "transaction" have, or do you want it to have, exclusive access to the
>database ?

I don't need a database lock. I just need to know if a specific set of tables is open by a user.

If any of the tables/views are open by the user, then I just create another set of tables/views with a different version number. Like "Tablea_2", "Tableb_2", "Tablec_2", "v_Tablea_2" etc.

If no one has any of the tables open, then I can drop them and reuse the existing table names:
"Tablea_1", "Tableb_1", "Tablec_1", "v_Tablea_1" etc.

I need to do it this way because I have a process that runs every 24hrs that imports data. If someone is running a report or viewing the data in a grid (user only has read access to the tables), then I don't want to disturb the user so I create a different set of tables with a different version number and post this new version number in a reference table. The next time the report or grids are displayed, it looks up the version number in the reference table so it knows which set of tables to use.

Barry

Barry,

<< I don't need a database lock. I just need to know if a specific set of
tables is open by a user.

If any of the tables/views are open by the user, then I just create another
set of tables/views with a different version number. Like "Tablea_2",
"Tableb_2", "Tablec_2", "v_Tablea_2" etc. >>

In that case, you can use this table:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=ServerSessionLocks_Table

Just filter or query on ObjectType='Table', and that will show you who, if
anyone, has what tables open.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

That will work, thanks

Barry

Tim,

Ironically if the database is running a few long queries on my database tables, I may get:

 "ElevateDB Error #506 Cannot lock the session manager (ID: 3)"

when executing something simple like this:

 "select * from configuration.serversessionlocks"

Why is the serversessionlocks table locked for so long (15+ seconds) when another application is building tables and importing data? It doesn't matter if the Select statement above is Sensitive or InSensitive; it will still time out.

TIA

Barry

Barry,

<< Ironically if the database is running a few long queries on my database
tables, I may get:

 "ElevateDB Error #506 Cannot lock the session manager (ID: 3)"

when executing something simple like this:

 "select * from configuration.serversessionlocks"

Why is the serversessionlocks table locked for so long (15+ seconds) when
another application is building tables and importing data? It doesn't matter
if the Select statement above is Sensitive or InSensitive; it will still
time out. >>

EDB needs to acquire a session lock in order to get a snapshot of the
memory/locks for a given session.  If the session is executing a long
process, then the session performing the query will have to wait until its
done.

Tim Young
Elevate Software
www.elevatesoft.com

<<EDB needs to acquire a session lock in order to get a snapshot of the
memory/locks for a given session.  If the session is executing a long
process, then the session performing the query will have to wait until its
done.>>

It was 2 different sessions. The long query was running in my application, and I was trying to execute:

"select * from configuration.serversessionlocks"

from EDBMgr at the same time.

Barry

Barry,

<< It was 2 different sessions. The long query was running in my
application, and I was trying to execute:

"select * from configuration.serversessionlocks"

from EDBMgr at the same time. >>

Yes, it's the long-running query in your application that is blocking the
other query from being able to get the snapshot of the locks for your
application's session.

Tim Young
Elevate Software
www.elevatesoft.com