Hi Helen...I'd like to thank you for your reply to my question. There is a lot of useful information in there and I really do appreciate you taking the time to share your knowledge with us.

I do have to make one point...I know it's been said before...but...I think it's important. You've mentioned several times things that we shouldn't do...like use TIBOTable or AddIndex(), etc ... because these are here for compatibility purposes only. We are in a "compatibility" phase with our application. We have been for a couple of years and probably have a few years to go.

Essentially...we have a table wrapper class

TLWPortableTable={$IFDEF IBO}class(TIBOTable){$ELSE}class(TTable){$IFEND}

We have clients that are spread out over large areas...some of which are not easily accessible...and our reality is that we have to compile both a BDE and IBO version of our app...and...we will for sometime to come. So...unfortunately...it's not just as easy as knowing what the correct way of implementing an application would be if we were starting from scratch today. At this point in time...we rely on IBO's advertised benefits of compatibility with code written for BDE. I suspect we're not the only IBObjects customers that are in this predicament. We are certainly looking forward to the day that all BDE compatibility issues can be forgotten! :)

Once again...I appreciate your responses and the knowledge that you share ... I just wanted to share the reality that I am faced with and hope you also understand its not always as easy to get out of the "compatibility" phase as one might think.

Regards -- Jeff Gaiche

-----Original Message-----
From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com]On
Behalf Of Helen Borrie
Sent: Thursday, May 07, 2009 9:17 AM
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] Receiving an Object In Use error when adding an index
to a table


At 08:10 AM 7/05/2009, you wrote:

>Hi Everyone. I'm hoping someone can help me understand why I'm
>getting a "lock conflict on no wait transaction.....object XXX is in
>use" error when I'm trying to add an index to a table.

Don't use AddIndex() for real databases! It was designed for desktop databases that don't have transaction control over metadata.

While we're about it, don't use TIBOTable, either. It's there for compatibility only, a place for people to "park" their TTable objects in a conversion from Paradox, Access or whatever and the BDE. For a transactional database, you must learn SQL.

> I have a
>short contrived example that demonstrates this issue. Basically...I
>have a TIBODatabase object that is shared by two TIBOTable objects
>which reference the same table in my database. I insert a record
>using the first table object and free the object. Then...in a
>separate transaction, I attempt to create an index on the same table
>using my second TIBOTable object. When I try to commit the
>transaction I get the error "lock conflict on no wait transaction
>unsuccessful metadata update object XXXXX is in use".

The "lock conflict on no wait transaction" happens because the first transaction has posted changes that were not committed when the second transaction started. "Object in Use" errors appear when you try to alter the metadata of an object that is in use. ;-) In this case, adding an index alters the metadata of the owning table.

>If I use an
>TIB_Connection object instead of a TIBODatabase object this error
>does not occur.

Look at the default transaction settings in each case: Isolation, Wait/NoWait, and also RecVersion if you are using ReadCommitted isolation. Autocommit is also important: you get it by default with the TDataset model (TIBODatabase) and with it a dreadful trick called CommitRetaining, a dreadful legacy from Borland.

>As I understand it...we should never mix DDL and DML
>in the same transaction to avoid issues of this sort. I believe I am
>following that convention here...but...still getting errors.

Among other horrors, CommitRetaining starts a new transaction immediately on the Commit (the AutoCommit in this case) and your entire table remains an object in use. By default, the default transaction created internally by TIB_Connection is not Committed with CommitRetaining. Also, StartTransaction starts the transaction *explicitly* and overrides Autocommit, even if it is set.

Although it's not the issue here, in real databases, there is a thing called SQL privileges. You write user apps for ordinary users, i.e., those which don't have privileges to change the metadata of objects. You elect a specialised user to be the Owner of the database and you create both the database and all the objects in it as that owner-user. All other users are ordinary users and they can't change the owner's metadata.

>I have also tried calling the table1 "Unprepare" and "FreeServerResources"
>methods before freeing it but they have not helped in this case.

Once you have posted changes to the database, they remain pending until you either commit their transaction or roll it back. All you've done is abandoned uncommitted work. That remains true for about 4 hours (depending on how your network is configured), after which the database engine will try to rollback any abandoned transactions.

"Unprepare" belongs to a statement (in your example, the first statement is SELECT * FROM YOURTABLE which has a linked INSERT INTO YOURTABLE (<COLUMNLIST>) VALUES (<VALUELIST>) while the second is CREATE INDEX WHATEVER ON YOURTABLE(SOMETHING). All statements have to be prepared; unpreparing is not commonly something you need to do (although you can and there are situations where you might want to). But Unprepare has no effect on the transaction: the work has already been posted and remains pending until the transaction is completed.

>Can anyone shine some light on what I'm doing wrong ?

If you're going to work with Firebird or InterBase it's really important to understand transactions and how to manage them; it's absolutely essential to learn SQL and the importance of parameters; and you need to throw out any illusions you have that IB/Fb are in any way like Paradox, Access, dBase, MySQL, etc. Hit the Tech Info sheets at the IBO website; and also avail yourself of the examples in your IBO installation. There's also a concept book available, the Getting Started guide, a.k.a. GSG. It's quite old; but the concepts haven't changed for 22 years. ;-)

Helen



------------------------------------

___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more ! Yahoo! Groups Links