Firebird点网提供程序无法完全执行查询? [英] Firebird dot net provider doesn't fully execute query?

问题描述

我正在使用c#中的Firebird的点网提供程序来运行许多SQL命令.具体来说，我正在更改数据库架构，并进行数据更新等.

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:

未成功使用元数据更新对象

我已经做了一些查找，并且似乎好像尚未清除"我们的数据或复制数据的查询.我的意思是，当我暂停c#执行时在Firebird中检查监视表时，在MON$STATEMENTS表中看到该查询为非活动状态.这是在我运行commit语句之后.

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.

我的问题:

在尝试运行下一个命令之前，是否可以暂停，等待或强制查询完全完成?

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

当我在ISQL中运行相同的查询序列时，它可以很好地工作.是否有其他ISQL可以强制点网Firebird提供程序执行此操作，以使其无法保持该查询打开状态?

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;

推荐答案

好的，可怕的问题解决方案:

Okay, Horrible solution to problem:

我实际上能够通过关闭并释放连接，然后重新连接来使其正常工作.这导致卡住"查询以某种方式被删除，然后我可以执行表drop命令.因此，顺次看起来像这样:

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

这篇关于Firebird点网提供程序无法完全执行查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！