Firebird dot net provider doesn't fully execut

Question

I'm running a number of SQL command using the dot net provider for Firebird in c#. Specifically I'm changing the database schema, and doing data updates and the like.

As part of my processing I create a new table, run a query to copy data from an old table, and then drop the old table.

When I do this firebird generates and error:

unsuccessful metadata update object is in use

I've done some looking and it seems like the query to copy the data hasn't been "cleared" our or something yet. What I mean is when I check the monitoring tables in Firebird with my c# execution paused I see the query in the MON$STATEMENTS table, as inactive. This is after I've run a commit statement.

My questions:

Is there a way to pause, or wait, or force the query to fully complete before I try to run the next command?

When I run the same sequence of queries in ISQL it works perfectly. Is there something different ISQL does that I can force the dot net Firebird provider to do so it doesn't keep this query open or something?

So for reference the code looks something like this (obviously this is a very simplified):

    // create the table
    string commandString  = "CREATE TABLE ...";

    // run the command in a transaction and commit it
    mtransaction = Connection.BeginTransaction( IsolationLevel.Serializable );
    FbCommand command = new FbCommand(commandString, Connection, mtransaction);
    command.ExecuteNonQuery();
    transaction.Commit();
    transaction.Dispose();
    transaction = null;

    // copy the data to the new table from the old
    commandString = "INSERT INTO ...";

    mtransaction = Connection.BeginTransaction(IsolationLevel.Serializable);
    FbCommand command = new FbCommand(commandString, Connection, mtransaction);
    command.ExecuteNonQuery();
    transaction.Commit();
    transaction.Dispose();
    transaction = null;

    // drop the old table
    commandString = "DROP TABLE ...";

    mtransaction = Connection.BeginTransaction(IsolationLevel.Serializable);
    FbCommand command = new FbCommand(commandString, Connection, mtransaction);
    command.ExecuteNonQuery();

    // this command fails with the exception
    // if I pause execution in c# before running this command, and 
    // use isql to look at the db I see the new table, and the data fully populated
    // and I also see the inactive insert command in MON$STATEMENTS
    transaction.Commit();
    transaction.Dispose();
    transaction = null;

Answer 1

I encountered the same problem and verified Beau's (crude) hotfix. Yet, I found a simple solution: Dispose the command after the transaction has been committed! Then reconnection is not neccessary anymore.

mtransaction = Connection.BeginTransaction(IsolationLevel.Serializable);
FbCommand command = new FbCommand(commandString, Connection, mtransaction);
command.ExecuteNonQuery();
transaction.Commit();
command.Dispose(); // Thus!
transaction.Dispose();

Regards, AtoN

Answer 2

I believe I've ran into a similar thing. My guess is that the root cause seems to be a feature: MVCC. When I mess with schemas, or drop tables only then to recreate, Visual Studio is usually holding it open. I just restart the service, and everything is fine.

Answer 3

Okay, Horrible solution to problem:

I actually was able to get this to work by closing and disposing the connection, then re-connecting. This resulted in the "stuck" query being removed somehow, and then I can exectute the table drop command. So squence looks something like this:

// create the table
string commandString  = "CREATE TABLE ...";

// run the command in a transaction and commit it
mtransaction = Connection.BeginTransaction( IsolationLevel.Serializable );
FbCommand command = new FbCommand(commandString, Connection, mtransaction);
command.ExecuteNonQuery();
transaction.Commit();
transaction.Dispose();
transaction = null;

// copy the data to the new table from the old
commandString = "INSERT INTO ...";

mtransaction = Connection.BeginTransaction(IsolationLevel.Serializable);
FbCommand command = new FbCommand(commandString, Connection, mtransaction);
command.ExecuteNonQuery();
transaction.Commit();
transaction.Dispose();
transaction = null;


// ------------------  
// Drop the connection entirely and start a new one
// so the table can be dropped 

Connection.Close();
Connection.Dispose();

// build connection string 
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.DataSource ... etc...

// connect
Connection = new FbConnection(connectionString);
Connection.Open();

// Now have new connection that does not have weird
// lingering query, and table can now be dropped
// -----------------



// drop the old table
commandString = "DROP TABLE ...";

mtransaction = Connection.BeginTransaction(IsolationLevel.Serializable);
FbCommand command = new FbCommand(commandString, Connection, mtransaction);
command.ExecuteNonQuery();

// this no longer fails because the connection was complete closed
// and re-opened
transaction.Commit();
transaction.Dispose();
transaction = null;

NOTE: I am very not happy with this solution. It works, but I don't know why. It seems excessive and unnessicary for me to have to do this to drop a table. I would very much appricate any insight anyone may have to offer in this matter!!!