NHibernate 3.2 GA and Parameter Name error

[debop]

Using dotConnector for Oracle 6.50.214.0 and NHibernate 3.2 GA.

Not solve parameter naming error.


Test by QueryOver

[ SELECT this_.COMPANY_ID as COMPANY1_4_0_, this_.COMPANY_CODE as COMPANY2_4_0_, this_.COMPANY_NAME as COMPANY3_4_0_, this_.UpdateTimestamp as UpdateTi4_4_0_ FROM NH_COMPANY this_ WHERE this_.COMPANY_CODE = :p0 ]
Name:cp0 - Value:NHibernate
[SQL: SELECT this_.COMPANY_ID as COMPANY1_4_0_, this_.COMPANY_CODE as COMPANY2_4_0_, this_.COMPANY_NAME as COMPANY3_4_0_, this_.UpdateTimestamp as UpdateTi4_4_0_ FROM NH_COMPANY this_ WHERE this_.COMPANY_CODE = :p0]
----> Devart.Data.Oracle.OracleException : ORA-01008: not all variables bound


Other test (LINQ)

NHibernate.Exceptions.GenericADOException : could not execute query
[ select company0_.COMPANY_ID as COMPANY1_4_, company0_.COMPANY_CODE as COMPANY2_4_, company0_.COMPANY_NAME as COMPANY3_4_, company0_.UpdateTimestamp as UpdateTi4_4_ from NH_COMPANY company0_ where company0_.COMPANY_CODE=:p0 ]
Name:p1 - Value:REALWEB
[SQL: select company0_.COMPANY_ID as COMPANY1_4_, company0_.COMPANY_CODE as COMPANY2_4_, company0_.COMPANY_NAME as COMPANY3_4_, company0_.UpdateTimestamp as UpdateTi4_4_ from NH_COMPANY company0_ where company0_.COMPANY_CODE=:p0]
----> Devart.Data.Oracle.OracleException : ORA-01008: not all variables bound

set Direct = true.

Did I mistake ?

if NHibernate 3.1 GA, no problem.

[Shalex]

Please try setting OracleUtils.OracleClientCompatible = true;. This issue was discussed at http://www.devart.com/forums/viewtopic.php?t=21676.

[JomKippur]

Hi,
I don't know why you always send us (users) to that topic you mentioned.
Setting this OracleUtils.OracleClientCompatible = true; it's not a solution because it works for Nh 3.2 but not for simple Ado. If you have in your project mixed Nh 3.2 Dao's and regular Ado Dao's (I'm using Spring .NET and I have Nh and Ado Dao's) then you have an issue.

This works only with OracleUtils.OracleClientCompatible = false. Example of not working code:

Code: Select all

OracleUtils.OracleClientCompatible = true;
			OracleConnection oc = new OracleConnection();
			oc.ConnectionString = "pooling = false";
			oc.Direct = true;
			oc.Unicode = true;
			oc.AutoCommit = false;
			oc.Server = "10.60.1.183";
			oc.Sid = "XE";
			oc.UserId = "my_user";
			oc.Password = "my_passw";
			oc.Open();

			OracleCommand o2 = oc.CreateCommand();
			o2.ParameterCheck = true;
			o2.CommandText = "select propertyGuid from property where propertyId = :propertyId and propertyGuid = :propertyGuid";

			o2.Parameters["propertyGuid"].Value = "333";
			o2.Parameters["propertyId"].Value = "997";
= "333";

			var r = o2.ExecuteScalar();

Even though you change parameters to ":1" or "?" (as Oracle like ) you will get error.

I'm waiting for a solution

thanks
JK

[Shalex]

1. This is the problem of using "OracleUtils.OracleClientCompatible = true;" and "cmd.ParameterCheck = true;" simultaneously. As a workaround, please avoid using cmd.ParameterCheck = true; and add the parameters to the collection manually:

Code: Select all

         OracleCommand o2 = oc.CreateCommand();
         o2.ParameterCheck = true;
         o2.CommandText = "select propertyGuid from property where propertyId = :propertyId and propertyGuid = :propertyGuid";

         o2.Parameters["propertyGuid"].Value = "333";
         o2.Parameters["propertyId"].Value = "997";
= "333";

         var r = o2.ExecuteScalar();

-->

Code: Select all

         OracleCommand o2 = oc.CreateCommand();
         // o2.ParameterCheck = true;
         o2.CommandText = "select propertyGuid from property where propertyId = :propertyId and propertyGuid = :propertyGuid";

         o2.Parameters.Add(":propertyGuid", "333");
         o2.Parameters.Add(":propertyId", "997");

         var r = o2.ExecuteScalar();

We will post here about the results of our investigation.
2.

I don't know why you always send us (users) to that topic you mentioned.
Setting this OracleUtils.OracleClientCompatible = true; it's not a solution because it works for Nh 3.2 but not for simple Ado.

Our code does not remove the colon ":" from parameter's name in the cmd.Parameters collection when "OracleUtils.OracleClientCompatible = true;".

[JomKippur]

We will post here about the results of our investigation.

Is sth new to this case? I need Nh 3.2 working with your controls. It is not possible form me to add a colon to every simple Ado dao querie to every parameter ...

Greetings
JK

[Shalex]

The bug with parameters when OracleUtils.OracleClientCompatible=true in Direct mode is fixed. We will post here when the corresponding public build of dotConnect for Oracle is available for download.

[JomKippur]

Thanks!

Is it also possible to make your conrols working with NH 3.2 without setting "OracleUtils.OracleClientCompatible = true;" ?

Greetings,
JK

[Shalex]

JomKippur, we have sent an e-mail with the fixed internal build to your e-mail address.
Why do you want to avoid usage of "OracleUtils.OracleClientCompatible = true;"?

[JomKippur]

Hi,
"Why do you want to avoid usage of "OracleUtils.OracleClientCompatible = true;"?"

it's simple - for two reasons:
1) our company application has been working for 2 years on default setting of mentioned parameter (OracleUtils.OracleClientCompatible = false;) - our unit and integrated tests may not cover all cases and I have to do lots of tests to ensure everything is still working (if I had source code I would be able to estimate risk... )
2) it's a tricky solution

greetings,
JK

[Shalex]

Usage of "OracleUtils.OracleClientCompatible = true;" is caused by change in NHibernate 3.2: http://www.devart.com/forums/viewtopic.php?t=21676.

[Shalex]

New build of dotConnect for Oracle 6.50.237 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=22379 .

[rstacey481]

I'm pretty sure that this problem is all down to two simple things...

1. The fact that the Devart.Oracle.OracleParameter removes the ':' prefix from the ParameterName property.
2. The fact that the DevartOracleDriver for NHibernate returns true for the UseNamedPrefixInParameter property.

When you combine these settings with NHibernate you end up with parameters being removed from the Commands ParameterCollection that shouldn't be removed. This behaviour is due to the DriverBase RemoveUnusedCommandParameters method being unable to correctly identify parameter names in the collection.

Using the modified driver below all these vanishing parameter problems, well... vanish and everything seems to behave as it should. I have run some tests on this driver but they are by no means exhaustive so if anyone out there would like to test to destruction and modify accordingly I'd be interested in the results.

Code: Select all

using System.Data;
using System.Reflection;
using NHibernate.AdoNet;
using NHibernate.SqlTypes;
using NHibernate.Util;

namespace NHibernate.Driver
{

    public class DevartOracleDriver : ReflectionBasedDriver, IEmbeddedBatcherFactoryProvider
    {
        private const string AssemblyName = "Devart.Data.Oracle";
        private const string ConnectionTypeName = "OracleConnection";
        private const string CommandTypeName = "OracleCommand";
        private static readonly SqlType GuidSqlType = new SqlType(DbType.Binary, 16);
        private readonly PropertyInfo _oracleDbType;
        private readonly object _oracleDbTypeRaw;

        /// 
        /// Initializes a new instance of .
        /// 
        /// 
        /// Thrown when the Devart.Data.Oracle assembly can not be loaded.
        /// 
        public DevartOracleDriver()
            : base(AssemblyName, QualifiedName(ConnectionTypeName), QualifiedName(CommandTypeName))
        {
            System.Type parameterType = ReflectHelper.TypeFromAssembly(QualifiedName("OracleParameter"), AssemblyName, false);
            _oracleDbType = parameterType.GetProperty("OracleDbType");

            System.Type oracleDbTypeEnum = ReflectHelper.TypeFromAssembly(QualifiedName("OracleDbType"), AssemblyName, false);
            _oracleDbTypeRaw = System.Enum.Parse(oracleDbTypeEnum, "Raw");
        }

        /// 
        public override bool UseNamedPrefixInSql
        {
            get { return true; }
        }

        /// 
        public override bool UseNamedPrefixInParameter
        {
            get { return false; }
        }

        /// 
        public override string NamedPrefix
        {
            get { return ":"; }
        }

        protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType)
        {
            if (sqlType.DbType == DbType.Guid)
            {
                base.InitializeParameter(dbParam, name, GuidSqlType);
                _oracleDbType.SetValue(dbParam, _oracleDbTypeRaw, null);
            }
            else
                base.InitializeParameter(dbParam, name, sqlType);
        }

        private static string QualifiedName(string typeName)
        {
            return string.Format("{0}.{1}", AssemblyName, typeName);
        }


        #region IEmbeddedBatcherFactoryProvider Members

        System.Type IEmbeddedBatcherFactoryProvider.BatcherFactoryClass
        {
            get { return typeof(OracleDataClientBatchingBatcherFactory); }
        }

        #endregion
    }
}

[Shalex]

Thank you for sharing your solution. We will check your code and post here about the results.

[Shalex]

We will update http://www.devart.com/blogs/dotconnect/ ... racle.html according to rstacey481's suggestion (UseNamedPrefixInParameter will return false).