如何使用C#在Firebird中执行事务(或多个SQL查询) [英] How to execute transactions (or multiple sql queries) in firebird using c#

问题描述

我尝试了几种方法，包括在SO上.

I have tried several ways, including on SO.

以下MYSQL代码在Firebird中不起作用:

The following MYSQL code does not work in Firebird:

CREATE TABLE publications (
  INT NOT NULL AUTO_INCREMENT , 
  PRIMARY KEY (`id`),
  filename varchar(500) not null unique,
  title varchar(500) DEFAULT NULL,
  authors varchar(1000) DEFAULT NULL,
  uploader int DEFAULT NULL,
  keywords varchar(500) DEFAULT NULL,
  rawtext text,
  lastmodified timestamp default CURRENT_TIMESTAMP
);

因此要在Firebird中实现此目的，我正在使用:

So to achieve this in Firebird, I am using:

 CREATE TABLE publications (
   id int NOT NULL PRIMARY KEY,
   filename varchar(500) NOT NULL UNIQUE,
   title varchar(500) DEFAULT NULL,
   authors varchar(1000) DEFAULT NULL,
   uploader int DEFAULT NULL,
   keywords varchar(500) DEFAULT NULL,
   rawtext text,
   file_data BLOB SUB_TYPE 0,
   insertdate timestamp DEFAULT NULL
 );

 CREATE GENERATOR gen_t1_id;
 SET GENERATOR gen_t1_id TO 0;

set term !! ;

 CREATE TRIGGER journalInsertionTrigger FOR publications 
 ACTIVE BEFORE INSERT POSITION 0
 AS
 BEGIN
  if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_T1_ID, 1);
 END!!

set term ; !!

以上所述，我得到了错误:

And with the above, I get the error:

Batch execution aborted The returned message was: Dynamic SQL Error SQL error code = -104 Token unknown - line 13, char 2 CREATE"

当我取消注释//FbTransaction fbt = Connection.BeginTransaction();和//fbt.Commit();

Execute要求Command对象在以下情况下具有Transaction对象: 分配给命令的Connection对象位于待处理的本地中 交易.该命令的Transaction属性尚未 初始化.

Execute requires the Command object to have a Transaction object when the Connection object assigned to the command is in a pending local transaction. The Transaction property of the Command has not been initialized.

我正在使用以下C#代码:

I am using the following C# code:

//FbTransaction fbt = Connection.BeginTransaction(); // 
FbBatchExecution fbe = new FbBatchExecution( Connection );

fbe.SqlStatements = new System.Collections.Specialized.StringCollection();//.Add( queryString ); // Your string here                    
fbe.SqlStatements.Add( queryString ); // Your string here

fbe.Execute();
//fbt.Commit();

注意::在sql代码的开头设置set term ; !!会给出错误:The type of the SQL statement could not be determinated

NB: Setting set term ; !! at the beginning of the sql code gives the error: The type of the SQL statement could not be determinated

我该怎么做?

推荐答案

Firebird只能执行单个SQL语句，并且大多数Firebird驱动程序都遵循相同的规则.您不能像这样一次执行脚本.

Firebird can only execute individual SQL statements, and most drivers for Firebird follow that same rule. You cannot execute a script at once like this.

Firebird.net提供程序包含一个实用程序类，用于将脚本拆分成单独的语句.

The Firebird.net provider contains a utility class to split scripts into individual statements.

您需要执行以下操作:

using (var connection = new FbConnection(@"User=sysdba;Password=masterkey;Database=D:\data\db\testdatabase.fdb;DataSource=localhost"))
{
    connection.Open();
    FbScript script = new FbScript(dbScript);
    script.Parse();
    FbBatchExecution fbe = new FbBatchExecution(connection);
    fbe.AppendSqlStatements(script);
    fbe.Execute();
}

请注意，要使当前脚本正常工作，还需要替换:

Note that for your current script to work you also need to replace:

rawtext text,

使用

rawtext BLOB SUB_TYPE TEXT CHARACTER SET UTF8

从技术上讲，您可以省略字符集子句，但是除非为数据库定义了默认字符集，否则应指定字符集，否则将为NONE，这可能会在以后导致问题.

Technically you could leave off the character set clause, but unless you defined a default character set for your database, you should specify the character set otherwise it will be NONE which might lead to problems later.

使用FbBatchExecution时，您无法自己启动事务，因为该事务是在Execute方法内部处理的.请注意，如果您还想在脚本中插入(或修改)数据，则应使用Execute(true)，以便立即提交每个DDL语句. Firebird不允许DML在同一事务中使用事务中的DDL更改.

You cannot start a transaction yourself when you use FbBatchExecution, because the transaction is handled internally in the Execute method. Note that if you also want to insert (or otherwise modify) data in the script, then you should use Execute(true), so that each DDL statement is committed immediately. Firebird doesn't allow DDL changes in a transaction to be used by DML in the same transaction.

SET TERM的问题是由于SET TERM不属于Firebird语法的事实引起的.它是ISQL和FlameRobin等工具使用的语法的一部分，例如FbScript.

The problem with SET TERM is caused by the fact that SET TERM is not part of the Firebird syntax. It is part of the syntax used by tools like ISQL and FlameRobin, and for example FbScript.

如果您要单独执行这些语句并控制交易，则可以执行以下操作:

If you want to execute these statements individually and have control over the transaction, you'd do something like:

using (var connection = new FbConnection(@"User=sysdba;Password=masterkey;Database=D:\data\db\testdatabase.fdb;DataSource=localhost"))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    using (var command = new FbCommand())
    {
        command.Connection = connection;
        command.Transaction = transaction;

        command.CommandText = @"CREATE TABLE publications (
           id int NOT NULL PRIMARY KEY,
           filename varchar(500) NOT NULL UNIQUE,
           title varchar(500) DEFAULT NULL,
           authors varchar(1000) DEFAULT NULL,
           uploader int DEFAULT NULL,
           keywords varchar(500) DEFAULT NULL,
           rawtext BLOB SUB_TYPE TEXT CHARACTER SET UTF8,
           file_data BLOB SUB_TYPE 0,
           insertdate timestamp DEFAULT NULL
         )";
        command.ExecuteNonQuery();

        command.CommandText = "CREATE GENERATOR gen_t1_id";
        command.ExecuteNonQuery();

        command.CommandText = @"CREATE TRIGGER journalInsertionTrigger FOR publications 
         ACTIVE BEFORE INSERT POSITION 0
         AS
         BEGIN
          if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_T1_ID, 1);
         END";
        command.ExecuteNonQuery();

        transaction.Commit();
    }
}

这篇关于如何使用C#在Firebird中执行事务(或多个SQL查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！