ODP.NET

Hi, I'm testing the Managed provider and NHibernate and I found that the Managed Provider has problems with comments in sql.

I have AutoCommentSql option enabled in NHibernate configuration and it adds a comment at the beginning of sql like /* [expression] */ select
With comment the oracle command does not return any rows, when I remove the comment I get the result.

For now the workaround is to set AutoCommentSql to false.

Here it is my Oracle Managed Driver:
public class OracleManagedDataClientDriver : ReflectionBasedDriver, IEmbeddedBatcherFactoryProvider
{
private const string driverAssemblyName = "Oracle.ManagedDataAccess";
private const string connectionTypeName = "Oracle.ManagedDataAccess.Client.OracleConnection";
private const string commandTypeName = "Oracle.ManagedDataAccess.Client.OracleCommand";
private static readonly SqlType GuidSqlType = new SqlType(DbType.Binary, 16);
private readonly PropertyInfo oracleCommandBindByName;
private readonly PropertyInfo oracleDbType;
private readonly object oracleDbTypeRefCursor;

/// <summary>
/// Initializes a new instance of <see cref="OracleManagedDataClientDriver"/>.
/// </summary>
/// <exception cref="HibernateException">
/// Thrown when the <c>Oracle.ManagedDataAccess</c> assembly can not be loaded.
/// </exception>
public OracleManagedDataClientDriver()
: base(
"Oracle.ManagedDataAccess.Client",
driverAssemblyName,
connectionTypeName,
commandTypeName)
{
System.Type oracleCommandType = ReflectHelper.TypeFromAssembly("Oracle.ManagedDataAccess.Client.OracleCommand", driverAssemblyName, false);
oracleCommandBindByName = oracleCommandType.GetProperty("BindByName");

System.Type parameterType = ReflectHelper.TypeFromAssembly("Oracle.ManagedDataAccess.Client.OracleParameter", driverAssemblyName, false);
oracleDbType = parameterType.GetProperty("OracleDbType");

System.Type oracleDbTypeEnum = ReflectHelper.TypeFromAssembly("Oracle.ManagedDataAccess.Client.OracleDbType", driverAssemblyName, false);
oracleDbTypeRefCursor = System.Enum.Parse(oracleDbTypeEnum, "RefCursor");
}

/// <summary></summary>
public override bool UseNamedPrefixInSql
{
get { return true; }
}

/// <summary></summary>
public override bool UseNamedPrefixInParameter
{
get { return false; }
}

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

/// <remarks>
/// This adds logic to ensure that a DbType.Boolean parameter is not created since
/// ODP.NET doesn't support it.
/// </remarks>
protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType)
{
// if the parameter coming in contains a boolean then we need to convert it
// to another type since ODP.NET doesn't support DbType.Boolean
switch (sqlType.DbType)
{
case DbType.Boolean:
base.InitializeParameter(dbParam, name, SqlTypeFactory.Int16);
break;
case DbType.Guid:
base.InitializeParameter(dbParam, name, GuidSqlType);
break;
default:
base.InitializeParameter(dbParam, name, sqlType);
break;
}
}

protected override void OnBeforePrepare(IDbCommand command)
{
base.OnBeforePrepare(command);

// need to explicitly turn on named parameter binding
// http://tgaw.wordpress.com/2006/03/03/ora-01722-with-odp-and-command-parameters/
oracleCommandBindByName.SetValue(command, true, null);

CallableParser.Detail detail = CallableParser.Parse(command.CommandText);

if (!detail.IsCallable)
return;

command.CommandType = CommandType.StoredProcedure;
command.CommandText = detail.FunctionName;
oracleCommandBindByName.SetValue(command, false, null);

IDbDataParameter outCursor = command.CreateParameter();
oracleDbType.SetValue(outCursor, oracleDbTypeRefCursor, null);

outCursor.Direction = detail.HasReturn ? ParameterDirection.ReturnValue : ParameterDirection.Output;

command.Parameters.Insert(0, outCursor);
}

#region IEmbeddedBatcherFactoryProvider Members

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

#endregion
}

Edited by: Invincible on Oct 2, 2012 7:50 AM