NHibernate 3.2 GA and Parameter Name error

NHibernate 3.2 GA and Parameter Name error

Postby debop » Thu 15 Sep 2011 18:09

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.
debop
 
Posts: 4
Joined: Wed 10 Aug 2011 01:07
Location: Seoul

Postby Shalex » Fri 16 Sep 2011 11:43

Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby JomKippur » Tue 20 Sep 2011 23:07

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
JomKippur
 
Posts: 17
Joined: Wed 31 Aug 2011 15:46

Postby Shalex » Thu 22 Sep 2011 14:20

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:
JomKippur wrote:
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.
JomKippur wrote: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;".
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby JomKippur » Tue 27 Sep 2011 16:36

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
JomKippur
 
Posts: 17
Joined: Wed 31 Aug 2011 15:46

Postby Shalex » Thu 29 Sep 2011 14:47

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.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby JomKippur » Thu 29 Sep 2011 15:04

Thanks!

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

Greetings,
JK
JomKippur
 
Posts: 17
Joined: Wed 31 Aug 2011 15:46

Postby Shalex » Tue 04 Oct 2011 08:21

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;"?
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby JomKippur » Tue 04 Oct 2011 10:11

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
JomKippur
 
Posts: 17
Joined: Wed 31 Aug 2011 15:46

Postby Shalex » Fri 07 Oct 2011 05:52

Usage of "OracleUtils.OracleClientCompatible = true;" is caused by change in NHibernate 3.2: http://www.devart.com/forums/viewtopic.php?t=21676.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Tue 25 Oct 2011 08:02

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 .
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

NHibernate Driver

Postby rstacey481 » Mon 23 Jan 2012 11:56

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
    }
}
rstacey481
 
Posts: 1
Joined: Mon 23 Jan 2012 10:52

Postby Shalex » Mon 30 Jan 2012 09:45

Thank you for sharing your solution. We will check your code and post here about the results.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Mon 06 Feb 2012 15:48

We will update http://www.devart.com/blogs/dotconnect/index.php/nhibernate_and_oracle.html according to rstacey481's suggestion (UseNamedPrefixInParameter will return false).
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle