How to use stored procedures/functions (workaround).
NHibernate: 2.1.0
Oracle: 10g
---------------------------------------
1. Stored procedures without output parameters within named queries.You can execute stored procedures without output parameters within named queries:
Code:
<?xml version="1.0"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="PlanMI.Common" namespace="PlanMI.Common.Entities">
<sql-query name="InsertOrderQuery">
begin pr_gga_ins_order(:userName, :itemKey, :itemVersionNo, :quantity, :date); end;
</sql-query>
</hibernate-mapping>
Code:
IQuery query = CurrentSession.GetNamedQuery(QueryNames.InsertOrderQuery);
query.SetParameter("userName", userName);
query.SetParameter("itemKey", oi.Key.Key);
query.SetParameter("itemVersionNo", string.IsNullOrEmpty(oi.Key.ItemVersionNo) ? "" : oi.Key.ItemVersionNo.Trim());
query.SetParameter("quantity", oi.Quantity);
query.SetParameter("date", oi.DeliveryDate);
query.UniqueResult();
2. Stored procedures with output parameters, functions within named queries.NHibernate does not support stored procedures with output parameters and functions with Oracle. You need to do modifications in source code.
LimitationsStored procedure: can not contains more than one output parameter, output parameter must be first within procedure parameters collection, output parameter must have SYS_REFCURSOR data type.
Stored function: can not contains output parameters, return value must have SYS_REFCURSOR data type.
ModificationsYou need add output parameter to command parameter collection if within named query determined statement for stored procedure with output parameter or function execution.
2.a. NHibernate.Loader.Loader.PrepareQueryCommand method changes.
Set callable variable value:
Code:
bool callable = queryParameters.Callable;
Callable variable indicates that you want execute stored procedure with ouput parameter or function.
Invoke dialect.RegisterResultSetOutParameter method after parameter value binding:
Code:
if (useLimit && dialect.BindLimitParametersFirst)
{
colIndex += BindLimitParameters(command, colIndex, selection, session);
}
colIndex += BindParameterValues(command, queryParameters, colIndex, session);
#region PRIVATE MODIFICATION
if (callable)
{
colIndex = dialect.RegisterResultSetOutParameter(command, colIndex);
}
#endregion
After that we can implement method for output parameter adding in specific dialect class (Oracle10gDialect, MsSql2008Dialect, etc.)
2.b. RegisterResultSetOutParameter method signature changes.
Change "statement" variable type to IDbCommand in NHibernate.Dialect.Dialect.RegisterResultSetOutParameter method.
Change "statement" variable type to IDbCommand in NHibernate.Dialect.InformixDialect.RegisterResultSetOutParameter method.
2.c. Implement RegisterResultSetOutParameter method in specific dialect (Oracle10gDialect, for example).
You need create output parameter.
Set OracleDbType of output parameter to OracleDbType.RefCursor
Set Direction of output parameter to ParameterDirection.ReturnValue if you need to execute function or ParameterDirection.Output if you need to execute stored procedure with output parameter (determined by "call function"/"call procedure" statement in command text).
Change command text to stored procedure/function name.
Set command.CommandType to CommandType.StoredProcedure.
Code:
#region PRIVATE MODIFICATION
private const string CALL_FUNCTION_PATTERN = "call function";
private const string CALL_PROCECURE_PATTERN = "call procedure";
public override int RegisterResultSetOutParameter(IDbCommand command, int position)
{
#region Output parameter
IDbDataParameter outputParameter = command.CreateParameter();
// Set OracleDbType
PropertyInfo oracleDbTypeInfo = outputParameter.GetType().GetProperty("OracleDbType");
object refCursor = Enum.Parse(oracleDbTypeInfo.PropertyType, "RefCursor");
oracleDbTypeInfo.SetValue(outputParameter, refCursor, null);
// Set ParameterDirection
bool isFunction = IsFunction(command.CommandText);
outputParameter.Direction = (isFunction) ? ParameterDirection.ReturnValue : ParameterDirection.Output;
command.Parameters.Insert(0, outputParameter);
#endregion
// Get command name
command.CommandText = GetCommandName(command.CommandText);
command.CommandType = CommandType.StoredProcedure;
return position;
}
private string GetCommandName(string commandText)
{
Match match = Regex.Match(commandText,
string.Format(@"\s*({0}|{1})\s+(?<commandName>.[^(]+)\(.*\)", CALL_FUNCTION_PATTERN, CALL_PROCECURE_PATTERN), RegexOptions.IgnoreCase);
if (match.Success)
return match.Groups["commandName"].Value;
else
throw new ArgumentException(string.Format("Incorrect format of command text: {0},\n expected '{1} fn_name(p1, pn)' or '{2} sp_name(p1, pn)'",
commandText, CALL_FUNCTION_PATTERN, CALL_PROCECURE_PATTERN));
}
private bool IsFunction(string commandText)
{
if (Regex.IsMatch(commandText, CALL_FUNCTION_PATTERN))
return true;
else if (Regex.IsMatch(commandText, CALL_PROCECURE_PATTERN))
return false;
else
throw new ArgumentException("'{0}' or '{1}' expected when callable is true");
}
#endregion
To execute stored procedure from named query you can use following statement: call procedure proc_name(:param1, :param2). You don't need to add ouput parameter.
To execute stored function from named query you can use following statement: call function func_name(:param1, :param2)
ExamplesExample 1. Get collection of entities by named query.
Named query
Code:
<?xml version="1.0"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="PlanMI.Common" namespace="PlanMI.Common.Entities">
<sql-query name="GetOpenOrdersQuery" callable="true">
<return alias="openOrder" class="OpenOrder"/>
call function fn_gga_get_open_orders(:itemKey)
</sql-query>
</hibernate-mapping>
Execution
Code:
IQuery openOrdersQuery = CurrentSession.GetNamedQuery(QueryNames.GetOpenOrdersQuery);
openOrdersQuery.SetParameter("itemKey", item.Key);
item.OpenOrders = openOrdersQuery.List<OpenOrder>();
Example 2. Get entity by named query with loader query-ref option.
Code:
<?xml version="1.0"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="PlanMI.Common" namespace="PlanMI.Common.Entities">
<class name="Group" table="FA_GGA_ADM_GROUPS">
<cache usage="read-write"/>
<id name="GroupId" column="GROUP_ID">
<generator class="increment" />
</id>
<property name="Status" column="GROUP_STATUS"/>
<loader query-ref="GetGroupByIdQuery"/>
</class>
<sql-query name="GetGroupByIdQuery" callable="true" cacheable="true">
<return alias="group" class="Group"/>
call function fn_gga_get_group(?)
</sql-query>
</hibernate-mapping>
You can use approach of point
1 but retrieve "Unexpected row count:" exception from NHibernate.
Oracle 10g does not return number of affected rows by stored procedure/function execution, so verification of affected rows inside NHibernate fail.
You can turn off verification of affected rows by following modifications:
3.a. Add NHibernate.Dialect.Dialect.AppropriateExpectation method.
Code:
/// <summary>
/// Appropriate expectation
/// </summary>
/// <param name="style">Check style</param>
/// <returns>Expectation</returns>
public virtual IExpectation AppropriateExpectation(ExecuteUpdateResultCheckStyle style)
{
return Expectations.AppropriateExpectation(style);
}
Override AppropriateExpectation method in specific dialect (Oracle10gDialect, for example)
Code:
public override IExpectation AppropriateExpectation(ExecuteUpdateResultCheckStyle style)
{
return Expectations.AppropriateExpectation(ExecuteUpdateResultCheckStyle.None);
}
Return expectation without verification.
3.c. NHibernate.Persister.Entity.AbstractEntityPersister modifications.
You need turn off verification in Insert, Update, Delete methods if stored procedure without output parameters execution expected. Callable attribute indicates it.
Code:
#region PRIVATE MODIFICATION
IExpectation expectation = null;
bool callable = IsInsertCallable(j);
if (callable)
expectation = Factory.Dialect.AppropriateExpectation(insertResultCheckStyles[j]);
else
expectation = Expectations.AppropriateExpectation(insertResultCheckStyles[j]);
#endregion
Code:
#region PRIVATE MODIFICATION
bool callable = IsUpdateCallable(j);
IExpectation expectation = null;
if (callable)
expectation = Factory.Dialect.AppropriateExpectation(updateResultCheckStyles[j]);
else
expectation = Expectations.AppropriateExpectation(updateResultCheckStyles[j]);
#endregion
Code:
#region PRIVATE MODIFICATION
bool callable = IsDeleteCallable(j);
IExpectation expectation = null;
if (callable)
expectation = Factory.Dialect.AppropriateExpectation(deleteResultCheckStyles[j]);
else
expectation = Expectations.AppropriateExpectation(deleteResultCheckStyles[j]);
#endregion
ExamplesExample 1. Execute stored procedures by class mapping sql-insert, sql-update options.
Code:
<?xml version="1.0"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="PlanMI.Common" namespace="PlanMI.Common.Entities">
<class name="CustomerSetting" table="FA_GGA_ADM_CUSTOMERSTGS">
<cache usage="read-write"/>
<composite-id>
<key-property name="CustKey" column="CUST_KEY"/>
<key-property name="ColumnId" column="COLUMN_ID"/>
</composite-id>
<property name="SqlStatement" column="SQL_STMT"/>
<sql-insert callable="true">
begin pr_gga_ins_cust_stg(?, ?, ?); end;
</sql-insert>
<sql-update callable="true">
begin pr_gga_upd_cust_stg(?, ?, ?); end;
</sql-update>
</class>
</hibernate-mapping>
Login
FAQ
Search