Does GenericDatabase support binary data?

Topics: Data Access Application Block
Aug 13, 2009 at 7:01 PM
Edited Aug 13, 2009 at 7:03 PM

I am trying to use the Data block to connect to Oracle.  I tried the following setting in my app.config:

<add name="TestOraDB" connectionString="Data Source=TestOraDB; User Id=test; Password=test" providerName="Oracle.DataAccess.Client" />

Since I used the ODP.net data provider, the Data block was resolving it to the GenericDatabase type.  When I try to insert a row into a table, the binary data is not inserted.  It just inserts a null.

Insert Into TableA (VarcharData, BinaryColumn) Values ('SomeData', :BinaryData);

This just inserts 'SomeData' and null for the values.  There is no error thrown. 

When I run the same statement with System.Data.OracleClient in the ProviderName, it saves the binary data just fine.

Is this to be expected?

VT.

Aug 14, 2009 at 5:39 AM

I suggest you try it out using the raw ODP.Net first and see if you will encounter the same result.  If not, I suggest you use the ODP.NET database provider downloadable from the EntLibContrib project - http://entlibcontrib.codeplex.com/SourceControl/ListDownloadableCommits.aspx?ViewAll=true

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Aug 14, 2009 at 7:51 PM
AvanadeSupport wrote:

I suggest you try it out using the raw ODP.Net first and see if you will encounter the same result.  If not, I suggest you use the ODP.NET database provider downloadable from the EntLibContrib project - http://entlibcontrib.codeplex.com/SourceControl/ListDownloadableCommits.aspx?ViewAll=true

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

 An update:

I extended the DAAB to make my own OracleDatabase class that uses the Oracle Data Provider (ODP).  I copied all of the Oracle related classes in the DAAB and updated them to use the Oracle provider.  I configured my Test application to use the extension dll that I created.  When I try to insert into Oracle table using bind variables in the query the values of the parameters are always null when the query executes.

Here is some sample code:

Database db = DatabaseFactory.CreateDatabase("MyDB");
string sqlCommand = "Insert Into TableName Values (:A, :B)";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);db.AddInParameter(dbCommand, ":A", DbType.String, "ValueA");
db.AddInParameter(dbCommand, ":B", DbType.String, "valueB");
int recsAffected = db.ExecuteNonQuery(dbCommand);

The parameter values are becoming null when the query executes.

Why are the parameter values not getting passed on to the ODP?

VT.

 

Aug 17, 2009 at 4:36 AM

You might be encountering the same problem as with this - http://www.codeplex.com/entlib/Thread/View.aspx?ThreadId=17402.

Please post your own implementation of the database class if this doesn't solve your problem. 

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Aug 17, 2009 at 4:42 PM

The problem ended up being the same as mentioned in the thread above.  The 'Size' parameter is supposed to be for the Max size of the value.  If nothing is specified, the size should have defaulted to the actual size of 'Value'.  Instead it uses zero and inserts Null into the database.

The other frustrating thing about this is that the 'Size' parameter can not be set with the AddInParameter method.  It has to be set with the overloaded version of the AddParameter method of the Database class.  I ended up using that overloaded method and passing in a large number for the size parameter.

I am now able to use the GenericDatabase (just by specifying the providerName as Oracle.DataAccess.Client in the connectionString - add config setting.  I don't think I really need to extend the DAAB.  If I find a need to do so, I will try to remember to update this post.