Problem with CoreLab.Oracle.EnterpriseLibrary.dll

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
labate
Posts: 48
Joined: Tue 17 Jan 2006 13:57
Location: Switzerland, Sion

Problem with CoreLab.Oracle.EnterpriseLibrary.dll

Post by labate » Thu 17 Jan 2008 16:55

Hi,

I uses Microsoft Enterprise Library, version 3.1 (May 2007) and OraDirect .NET 4.35 Trial.

At runtime when the Database object is created, I've got this exception:
System.ArgumentException: The type 'CoreLab.Oracle.EnterpriseLibrary.OracleDatabase, CoreLab.Oracle.EnterpriseLibrary, Version=1.0.2938.17306, Culture=neutral, PublicKeyToken=null' cannot be resolved. Please verify the spelling is correct or that the full type name is provided.
at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.AssemblyQualifiedTypeNameConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
at System.ComponentModel.TypeConverter.ConvertFrom(Object value)
at Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DbProviderMapping.get_DatabaseType()
at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseCustomFactory.CreateObject(IBuilderContext context, String name, IConfigurationSource configurationSource, ConfigurationReflectionCache reflectionCache)
at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.ConfiguredObjectStrategy.BuildUp(IBuilderContext context, Type t, Object existing, String id)
at Microsoft.Practices.ObjectBuilder.SingletonStrategy.BuildUp(IBuilderContext context, Type typeToBuild, Object existing, String idToBuild)
at Microsoft.Practices.ObjectBuilder.BuilderStrategy.BuildUp(IBuilderContext context, Type typeToBuild, Object existing, String idToBuild)
at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.ConfigurationNameMappingStrategy.BuildUp(IBuilderContext context, Type t, Object existing, String id)
at Microsoft.Practices.ObjectBuilder.BuilderBase`1.DoBuildUp(IReadWriteLocator locator, Type typeToBuild, String idToBuild, Object existing, PolicyList[] transientPolicies)
at Microsoft.Practices.ObjectBuilder.BuilderBase`1.BuildUp(IReadWriteLocator locator, Type typeToBuild, String idToBuild, Object existing, PolicyList[] transientPolicies)
at Microsoft.Practices.ObjectBuilder.BuilderBase`1.BuildUp[TTypeToBuild](IReadWriteLocator locator, String idToBuild, Object existing, PolicyList[] transientPolicies)
at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.EnterpriseLibraryFactory.BuildUp[T](IReadWriteLocator locator, IConfigurationSource configurationSource)
at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.EnterpriseLibraryFactory.BuildUp[T](IConfigurationSource configurationSource)
at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.NameTypeFactoryBase`1.CreateDefault()
at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase()
at
etc...
When configuring the EntlLib data configuration, with the EntLib Tool, I had to add the CoreLab assembly. This results in the following sections:

Code: Select all

  
    
      
    
  
  
    
    
  
The file CoreLab.Oracle.EnterpriseLibrary.dll is an assembly that I've built with VS2005 as it was only installed as a Visual Studio solution.

Do you see something wrong?
Thanks

labate
Posts: 48
Joined: Tue 17 Jan 2006 13:57
Location: Switzerland, Sion

Post by labate » Thu 17 Jan 2008 17:32

Hello,

After some "googling" I found why the assembly cannot be resolved. I forgot to add a reference to it in my data access layer project. I only added the two Enterprise libraries but not the CoreLab one. :?

After a rebuild and a new test I have another issue:
I have to call a stored proc:

Code: Select all

DbCommand command = db.GetStoredProcCommand("pck_pws.GetConfigurationId");
db.AddInParameter(command, "computerName", DbType.String, computerName);
db.AddParameter(command, "returnValue", DbType.Int32, ParameterDirection.ReturnValue, null, DataRowVersion.Current, null);    // HERE
Note that I use AddParameter to define a return value of my stored proc, and so I didn't set an object (last parameter is null).

This causes the following error:
System.NullReferenceException: Object reference not set to an instance of an object.
at CoreLab.Oracle.EnterpriseLibrary.OracleDatabase.ConfigureParameter(DbParameter param, String name, DbType dbType, Int32 size, ParameterDirection direction, Boolean nullable, Byte precision, Byte scale, String sourceColumn, DataRowVersion sourceVersion, Object value) in C:\Program Files\CoreLab\OraDirect.NET2\Enterprise Library\Src\CS\OracleDatabase.cs:line 98
at Microsoft.Practices.EnterpriseLibrary.Data.Database.CreateParameter(String name, DbType dbType, Int32 size, ParameterDirection direction, Boolean nullable, Byte precision, Byte scale, String sourceColumn, DataRowVersion sourceVersion, Object value)
at CoreLab.Oracle.EnterpriseLibrary.OracleDatabase.AddParameter(DbCommand command, String name, DbType dbType, Int32 size, ParameterDirection direction, Boolean nullable, Byte precision, Byte scale, String sourceColumn, DataRowVersion sourceVersion, Object value) in C:\Program Files\CoreLab\OraDirect.NET2\Enterprise Library\Src\CS\OracleDatabase.cs:line 91
at Microsoft.Practices.EnterpriseLibrary.Data.Database.AddParameter(DbCommand command, String name, DbType dbType, ParameterDirection direction, String sourceColumn, DataRowVersion sourceVersion, Object value)
I open OracleDatabase.cs and saw the following code:

Code: Select all

protected override void ConfigureParameter(DbParameter param, string name, DbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value) {
      param.DbType = dbType;
      ((OracleParameter)param).OracleDbType = OracleDatabase.TypeToOracleDbType(value.GetType());    // <-- HERE
      param.Size = size;
      param.Value = (value == null) ? DBNull.Value : value;
      param.Direction = direction;
      param.IsNullable = nullable;
      param.SourceColumn = sourceColumn;

      if (sourceVersion == DataRowVersion.Default)
        sourceVersion = DataRowVersion.Current;
      param.SourceVersion = sourceVersion;
    }
The offending line is where you make "value.GetType()" with a null value for value.
However two lines below you test "... (value == null) ...", so null is a valid value for value.

Could you check that code or tell me another way to set return value parameters for a stored proc ? Thanks.

Tomorrow I will fix the code in OracleDatabase.cs and give you back my results.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Fri 18 Jan 2008 13:51

To pass a parameter to the function and obtain a return value you can use the following sample code.

Code: Select all

static void CallProc()
{
  //Prepare connection and command
  OracleConnection myConn = new OracleConnection("User Id=Scott;Password=tiger;Data Source=Ora");
  myConn.Open();
  OracleCommand myCommand = new OracleCommand("GETTOTALSALARY", myConn);
  myCommand.CommandType = System.Data.CommandType.StoredProcedure;
  //Prepare parameters manually
  OracleParameter myInParam = myCommand.Parameters.Add("JobParam", "SALESMAN");
  OracleParameter myReturParam = new OracleParameter();
  myReturParam.Direction = System.Data.ParameterDirection.ReturnValue;
  myCommand.Parameters.Add(myReturParam);
  //Execute the function and render result
  myCommand.ExecuteNonQuery();
  Console.WriteLine(myReturParam.Value);
  myConn.Close();
  Console.ReadLine();
}
The offending line is where you make "value.GetType()" with a null value for value.
However two lines below you test "... (value == null) ...", so null is a valid value for value.
You can insert this line of code to fix the problem:

Code: Select all

value = (value == null) ? DBNull.Value : value;
In the next build we will fix the problem.

labate
Posts: 48
Joined: Tue 17 Jan 2006 13:57
Location: Switzerland, Sion

Post by labate » Wed 30 Jan 2008 09:45

Hello Alexey,

Thank you for your answer. Sorry for not having responded sooner.

Based on your suggestion, I wrote the following code:

Code: Select all

DbCommand command = db.GetStoredProcCommand("pck_pws.GetConfigurationId");
db.AddInParameter(command, "in_configurationName", DbType.String, computerName);

DbParameter returnValueParam = command.CreateParameter();
returnValueParam.DbType = DbType.Int32;
returnValueParam.Direction = ParameterDirection.ReturnValue;
returnValueParam.ParameterName = "returnValue";
command.Parameters.Add(returnValueParam);

db.ExecuteNonQuery(command);
configurationId = returnValueParam.Value;
My stored proc accepts a string and returns an integer.

In the code above, the returned value is always null even if the stored proc has returned a non-null value like 85 for example.

I also tried the following code that is quite the same as the code above and that produces the same (erroneous) result:

Code: Select all

DbCommand command = db.GetStoredProcCommand("pck_pws.GetConfigurationId");
db.AddInParameter(command, "in_configurationName", DbType.String, computerName);
db.AddParameter(command, "returnValue", DbType.Int32, ParameterDirection.ReturnValue, 
        String.Empty, DataRowVersion.Default, DBNull.Value);

db.ExecuteNonQuery(command);
configurationId = db.GetParameterValue(command, "returnValue");
Then when debugging the command object, I found out that before execution I had 2 parameters defined (1 input and 1 return parameters), and after execution the command object is attached to 3 parameters!!

I found the input param and the return param (that is always null), but found an extra parameter called "RESULT", of type Decimal and with the expected value!!

I didn't found any documentation about that. Could you tell me if it is CoreLab that sets automatically a RESULT parameter when the stored proc returns something or is it a feature of Enterprise Library 3.1?

Why the standard way of getting a return parameter with ParameterDirection.ReturnValue is not working?

Thank you very much.

labate
Posts: 48
Joined: Tue 17 Jan 2006 13:57
Location: Switzerland, Sion

Post by labate » Wed 30 Jan 2008 10:02

Just made some more tests and found that when I name the return parameter "result" instead of "returnValue" I catch the expected return value of the stored proc in that parameter, like in the following code:

Code: Select all

DbCommand command = db.GetStoredProcCommand("pck_pws.GetConfigurationId");
db.AddInParameter(command, "in_configurationName", DbType.String, computerName);
db.AddParameter(command, "result", DbType.Int32, ParameterDirection.ReturnValue, String.Empty, DataRowVersion.Default, DBNull.Value);

db.ExecuteNonQuery(command);
configurationId = db.GetParameterValue(command, "result");
There is now no extra "RESULT" parameter created.

The only minor problem is that its type is String instead of Int32. I will continue to search because I think this may come from the EnterpriseLibrary.dll sources provided by CoreLab. When the value is null the type is converted by default in Varchar.

Could you tell me if the word "result" is a reserved word for naming parameters of direction ReturnValue?

Thanks.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Wed 30 Jan 2008 14:46

Please send me your project to reproduce the problem.
Include the SQL script of the mentioned stored procedure.

labate
Posts: 48
Joined: Tue 17 Jan 2006 13:57
Location: Switzerland, Sion

Post by labate » Thu 31 Jan 2008 08:30

Hello,

I had to modify CoreLab.Oracle.EnterpriseLibrary.OracleDatabase because when I use AddParameter with a ReturnValue parameter, it was always set to varchar and string.
This is because when the value parameter is null (typical with a return value), its type is set to varchar.

Code: Select all

  private static OracleDbType TypeToOracleDbType(Type type) {
            switch (Type.GetTypeCode(type)) {
                case TypeCode.Char:
                case TypeCode.String:
                case TypeCode.DBNull:                  // <-- HERE
                    return OracleDbType.VarChar;
I changed the code for ReturnValue and Output parameter, to instead derive the type from the DbType:

Code: Select all

            if (value != null) {
                ((OracleParameter) param).OracleDbType = TypeToOracleDbType(value.GetType());
                param.Value = value;
            }
            else {
                if (direction == ParameterDirection.Input || direction == ParameterDirection.InputOutput)
                    ((OracleParameter) param).OracleDbType = TypeToOracleDbType(DBNull.Value.GetType());
                else
                    ((OracleParameter) param).OracleDbType = TypeToOracleDbType(DbTypeToType(dbType));             // <-- HERE

                param.Value = DBNull.Value;
            }
... with a DbTypeToType method that is something like:

Code: Select all

        private static Type DbTypeToType(DbType dbType) {
            switch (dbType) {
                case DbType.String:
                    return typeof (string);
                case DbType.UInt64:
                    return typeof (UInt64);
                case DbType.Int64:
                    return typeof (Int64);
                case DbType.Int32:
                    return typeof (Int32);
ETC...
This way my return parameter is set to the type I set in the DbType.
Tell me if you want the whole code of OracleDatabase.cs.
Thanks.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 31 Jan 2008 14:29

We are investigating the issue.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 31 Jan 2008 14:51

Could you please send your project and database objects?
Please include OracleDatabase.cs file with your modification.

Post Reply