How to add binary data and string data to firebird database simultaneously in c#

Question

I am currently using this code to attempt saving binary data (pdf file) and string data at the same time:

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();
}

This code above fails because the binary data is blank (although the other string data were inserted appropriately).

I can't believe I spent the whole day tweaking this (thanks to the poorly documented firebird ibphoenix pages)

What is the right way to do this?

You really shouldn't use `String.Format()` to add values into a query string, it leaves you open to SQL injection, use parameters for all values. — Mark Rotteveel, Nov 24 '15 at 08:46
Note that specifying `parBlob.Direction = ParameterDirection.Output` is wrong. The parameter direction is only relevant for calling stored procedures, and signifies output from the stored procedure to the caller (the client). The role of the parameter here is input. — Mark Rotteveel, Nov 24 '15 at 08:50

score 0 · Accepted Answer · answered Nov 24 '15 at 17:00

I finally got the following solution (thanks to comment by 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();
}

However, I should mention that following the comment by Mark Rottevell, the following firebird query construction gives 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();
}

How to add binary data and string data to firebird database simultaneously in c#

1 Answers1