NHibernate 3.2 GA and Parameter Name error
NHibernate 3.2 GA and Parameter Name error
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.
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.
Please try setting OracleUtils.OracleClientCompatible = true;. This issue was discussed at http://www.devart.com/forums/viewtopic.php?t=21676.
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:
Even though you change parameters to ":1" or "?" (as Oracle like ) you will get error.
I'm waiting for a solution
thanks
JK
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();
I'm waiting for a solution
thanks
JK
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:
We will post here about the results of our investigation.
2.
-->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();
2.
Our code does not remove the colon ":" from parameter's name in the cmd.Parameters collection when "OracleUtils.OracleClientCompatible = true;".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.
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
"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
Usage of "OracleUtils.OracleClientCompatible = true;" is caused by change in NHibernate 3.2: http://www.devart.com/forums/viewtopic.php?t=21676.
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 .
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 .
-
- Posts: 1
- Joined: Mon 23 Jan 2012 10:52
NHibernate Driver
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.
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
}
}
We will update http://www.devart.com/blogs/dotconnect/ ... racle.html according to rstacey481's suggestion (UseNamedPrefixInParameter will return false).