如何在c#中同时向firebird数据库中添加二进制数据和字符串数据

本文关键字：数据 添加 数据库 二进制 字符串 firebird | 更新日期: 2023-09-27 18:24:02

我目前正在使用此代码尝试同时保存二进制数据（pdf文件）和字符串数据：

string qy = 
String.Format( @"INSERT INTO publications (  uploader ,  filename ,  title ,  authors ,  keywords ,  file_data ,  insertdate  )                    
VALUES
( '{0}' , '{1}' , '{2}' , '{3}' , '{4}' , @file_data , '{5}' ) ",
 staffid, filemd5, title, author, keyword, DateTime.Now );

using( FbConnection fconn = connect_to_fbserver() ) 
{   
    FbCommand fbcom = new FbCommand( qy, fconn );   
    FbParameter parBlob = new FbParameter( "file_data", FbDbType.Binary );      
    parBlob.Direction = ParameterDirection.Output;  parBlob.Value = filearray;  
    fbcom.Parameters.Add( parBlob );  
    fbcom.Connection.Open();
    fbcom.ExecuteNonQuery();
}

上面的代码失败了，因为二进制数据是空的（尽管其他字符串数据是适当插入的）。

我不敢相信我花了一整天的时间来调整这个（多亏了火鸟ibphoenix页面记录不清）

做这件事的正确方法是什么？

如何在c#中同时向firebird数据库中添加二进制数据和字符串数据

我终于得到了以下解决方案（感谢alexm的评论）：

string insert_query = @"INSERT INTO publications
( 
UPLOADER , 
FILENAME , 
TITLE , 
AUTHORS , 
KEYWORDS , 
FILE_DATA , 
INSERTDATE  ) 
VALUES
( '{0}' , '{1}' , '{2}' , '{3}' , '{4}' , @FILE_DATA , '{5}' ) ",
staffid, filemd5, title, author, keyword, DateTime.Now );
using( FbConnection myConnection = utility.connect_to_fbserver() )
{
    myConnection.Open();
    FbTransaction myTransaction = myConnection.BeginTransaction();
    FbCommand myCommand = new FbCommand();
    myCommand.CommandText = insert_query;
    myCommand.Connection = myConnection;
    myCommand.Transaction = myTransaction;
    myCommand.Parameters.Add( "@FILE_DATA", FbDbType.Binary, filearray.Length, "FILE_DATA" );
    myCommand.Parameters[0].Value = filearray;
    // Execute query
    myCommand.ExecuteNonQuery();
    // Commit changes
    myTransaction.Commit();
    // Free command resources in Firebird Server
    myCommand.Dispose();
}

然而，我应该提到的是，在Mark Rottevell的评论之后，下面的firebird查询构造给出了Dynamic SQL Error SQL error code = -303 internal error

string insert_query = @"INSERT INTO publications
( 
UPLOADER , 
FILENAME , 
TITLE , 
AUTHORS , 
KEYWORDS , 
FILE_DATA , 
INSERTDATE  ) 
VALUES
( @UPLOADER , @FILENAME, @TITLE , @AUTHORS, @KEYWORDS, @FILE_DATA , @INSERTDATE) ";
using( FbConnection myConnection = utility.connect_to_fbserver() )
{
    myConnection.Open();
    FbTransaction myTransaction = myConnection.BeginTransaction();
    FbCommand myCommand = new FbCommand();
    myCommand.CommandText = insert_query;
    myCommand.Connection = myConnection;
    myCommand.Transaction = myTransaction;
    myCommand.Parameters.Add( "@UPLOADER", FbDbType.Text, staffid.Length, "UPLOADER" );
    myCommand.Parameters.Add( "@FILENAME", FbDbType.Text, filemd5.Length, "FILENAME" );
    myCommand.Parameters.Add( "@TITLE", FbDbType.Text, title.Length, "TITLE" );
    myCommand.Parameters.Add( "@AUTHORS", FbDbType.Text, author.Length, "AUTHORS" );
    myCommand.Parameters.Add( "@KEYWORDS", FbDbType.Text, keyword.Length, "KEYWORDS" );
    myCommand.Parameters.Add( "@FILE_DATA", FbDbType.Binary, filearray.Length, "FILE_DATA" );
    myCommand.Parameters.Add( "@INSERTDATE", FbDbType.Text, today.Length, "INSERTDATE" );
    myCommand.Parameters[0].Value = staffid;
    myCommand.Parameters[1].Value = filemd5;
    myCommand.Parameters[2].Value = title;
    myCommand.Parameters[3].Value = author;
    myCommand.Parameters[4].Value = keyword;
    myCommand.Parameters[5].Value = filearray;
    myCommand.Parameters[6].Value = today;
        // Execute query
    myCommand.ExecuteNonQuery();
    // Commit changes
    myTransaction.Commit();
    // Free command resources in Firebird Server
    myCommand.Dispose();
}
相关文章：