[nhibernate-issues] [NHJIRA] Created: (NH-2754) Exception "Invalid filter-parameter name format\r\n

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Exception "Invalid filter-parameter name format\r\nParameter name: filterParameterName" when using colons in quoted strings in sql-query T-SQL definitions
----------------------------------------------------------------------------------------------------------------------------------------------------------

                 Key: NH-2754
                 URL: http://216.121.112.228/browse/NH-2754
             Project:  NHibernate
          Issue Type: Bug
          Components: Core
    Affects Versions: 3.0.0.GA
            Reporter: Sean McElroy
            Priority: Major

I found a problem related to http://opensource.atlassian.com/projects/hibernate/browse/HHH-5932 in NH 3.0.0 GA -- when a sql-query exists that has a colon in the text (not just the HQL as the title of the referenced HHH issue has), and if you have a session filter enabled (which I do for multi-lingual support) the NHibernate loader appears to barf on it.  In the query below, replacing statements with colons in quoted strings, i.e. COALESCE('ACH:'...) with COALESCE('ACH') fixes the problem.

It appears additional supports needs to be added to support colons in quoted strings in sql-query statements.  The workaround I proposed above (remove colon from quoted string) works for my specific case, but I believe this is an issue that needs to be addressed for others who find themselves in the same scenario.

I can provide further source/information to reproduce the issue if needed.  I am using SQL Server 2008 R2 as my data source.

*** EXCEPTION THROWN: 
could not execute query
[ SELECT
	      COALESCE('ACH:' + pach.ReceivingRoutingNumber + '_' + pach.ReceivingAccountNumber,
	      COALESCE(pbp.PayeeKey,
	      COALESCE('Wire:' + pw.CreditRoutingNumber + '_' + pw.CreditAccountNumber,
	      NULL))) AS ID,
	      tcc.ID AS TransactionCategoryClassificationID,	
	       COUNT(pp.ID) AS PendingPaymentCount
      FROM core.ScheduledPaymentBase sp
      LEFT OUTER JOIN core.ScheduledPaymentACH spa ON (sp.ID = spa.ID)
      LEFT OUTER JOIN core.PaymentTemplateACH pta ON (pta.ID = spa.PaymentTemplateACHID)
      LEFT OUTER JOIN core.ScheduledPaymentBillPay spbp ON (sp.ID = spbp.ID)
      LEFT OUTER JOIN core.PaymentTemplateBillPay ptbp ON (ptbp.ID = spbp.PaymentTemplateBillPayID)
      LEFT OUTER JOIN core.ScheduledPaymentWire spw ON (sp.ID = spw.ID)
      LEFT OUTER JOIN core.PaymentTemplateWire ptw ON (ptw.ID = spw.PaymentTemplateWireID)
      LEFT OUTER JOIN core.PaymentTemplateItemBase ptib ON (ptib.PaymentTemplateBaseID IN (pta.ID, ptbp.ID, ptw.ID))
      LEFT OUTER JOIN core.PaymentTemplateItemACH ptia ON (ptia.ID = ptib.ID)
      LEFT OUTER JOIN core.PaymentTemplateItemBillPay ptibp ON (ptibp.ID = ptib.ID)
      LEFT OUTER JOIN core.PaymentTemplateItemWire ptiw ON (ptiw.ID = ptib.ID)
      LEFT OUTER JOIN core.PayeeACHAccount pach ON (spa.PayeeACHAccountID = pach.ID OR ptia.PayeeACHAccountID = pach.ID)
      LEFT OUTER JOIN core.PayeeBillPayAccount pbp ON (spbp.PayeeBillPayAccountID = pbp.ID OR ptibp.PayeeBillPayAccountID = pbp.ID)
      LEFT OUTER JOIN core.PayeeWireAccount pw ON (spw.PayeeWireAccountID = pw.ID OR ptiw.PayeeWireAccountID = pw.ID)
      INNER JOIN core.Payee p ON 
      (
        p.ID IN (
            SELECT ptib.PayeeID
            FROM core.PaymentTemplateItemBase ptib
            WHERE ptib.PaymentTemplateBaseID IN (pta.ID, ptbp.ID, ptw.ID)
        ) 
      )
      INNER JOIN core.TransactionCategoryClassification tcc ON tcc.ID = (
            SELECT tc.TransactionCategoryClassificationID 
            FROM core.TransactionCategory tc
            WHERE tc.ID = p.DefaultExpenseTransactionCategoryID
        )
      INNER JOIN core.PendingPaymentBase pp ON pp.ScheduledPaymentID = sp.ID AND [Status] = 1
      WHERE sp.SourceAccountID IN (SELECT ua.AccountID FROM core.UserAccount ua WHERE ua.UserID = ?)
      AND sp.Frequency > 0
      AND Deleted = 0
      GROUP BY COALESCE('ACH:' + pach.ReceivingRoutingNumber + '_' + pach.ReceivingAccountNumber,
      COALESCE(pbp.PayeeKey,
      COALESCE('Wire:' + pw.CreditRoutingNumber + '_' + pw.CreditAccountNumber,
      NULL))), tcc.ID
      HAVING COUNT(pp.ID) > 0 ]
  Name:UserID - Value:9223372036854775807
[SQL: SELECT
	      COALESCE('ACH:' + pach.ReceivingRoutingNumber + '_' + pach.ReceivingAccountNumber,
	      COALESCE(pbp.PayeeKey,
	      COALESCE('Wire:' + pw.CreditRoutingNumber + '_' + pw.CreditAccountNumber,
	      NULL))) AS ID,
	      tcc.ID AS TransactionCategoryClassificationID,	
	       COUNT(pp.ID) AS PendingPaymentCount
      FROM core.ScheduledPaymentBase sp
      LEFT OUTER JOIN core.ScheduledPaymentACH spa ON (sp.ID = spa.ID)
      LEFT OUTER JOIN core.PaymentTemplateACH pta ON (pta.ID = spa.PaymentTemplateACHID)
      LEFT OUTER JOIN core.ScheduledPaymentBillPay spbp ON (sp.ID = spbp.ID)
      LEFT OUTER JOIN core.PaymentTemplateBillPay ptbp ON (ptbp.ID = spbp.PaymentTemplateBillPayID)
      LEFT OUTER JOIN core.ScheduledPaymentWire spw ON (sp.ID = spw.ID)
      LEFT OUTER JOIN core.PaymentTemplateWire ptw ON (ptw.ID = spw.PaymentTemplateWireID)
      LEFT OUTER JOIN core.PaymentTemplateItemBase ptib ON (ptib.PaymentTemplateBaseID IN (pta.ID, ptbp.ID, ptw.ID))
      LEFT OUTER JOIN core.PaymentTemplateItemACH ptia ON (ptia.ID = ptib.ID)
      LEFT OUTER JOIN core.PaymentTemplateItemBillPay ptibp ON (ptibp.ID = ptib.ID)
      LEFT OUTER JOIN core.PaymentTemplateItemWire ptiw ON (ptiw.ID = ptib.ID)
      LEFT OUTER JOIN core.PayeeACHAccount pach ON (spa.PayeeACHAccountID = pach.ID OR ptia.PayeeACHAccountID = pach.ID)
      LEFT OUTER JOIN core.PayeeBillPayAccount pbp ON (spbp.PayeeBillPayAccountID = pbp.ID OR ptibp.PayeeBillPayAccountID = pbp.ID)
      LEFT OUTER JOIN core.PayeeWireAccount pw ON (spw.PayeeWireAccountID = pw.ID OR ptiw.PayeeWireAccountID = pw.ID)
      INNER JOIN core.Payee p ON 
      (
        p.ID IN (
            SELECT ptib.PayeeID
            FROM core.PaymentTemplateItemBase ptib
            WHERE ptib.PaymentTemplateBaseID IN (pta.ID, ptbp.ID, ptw.ID)
        ) 
      )
      INNER JOIN core.TransactionCategoryClassification tcc ON tcc.ID = (
            SELECT tc.TransactionCategoryClassificationID 
            FROM core.TransactionCategory tc
            WHERE tc.ID = p.DefaultExpenseTransactionCategoryID
        )
      INNER JOIN core.PendingPaymentBase pp ON pp.ScheduledPaymentID = sp.ID AND [Status] = 1
      WHERE sp.SourceAccountID IN (SELECT ua.AccountID FROM core.UserAccount ua WHERE ua.UserID = ?)
      AND sp.Frequency > 0
      AND Deleted = 0
      GROUP BY COALESCE('ACH:' + pach.ReceivingRoutingNumber + '_' + pach.ReceivingAccountNumber,
      COALESCE(pbp.PayeeKey,
      COALESCE('Wire:' + pw.CreditRoutingNumber + '_' + pw.CreditAccountNumber,
      NULL))), tcc.ID
      HAVING COUNT(pp.ID) > 0]

*** STACK TRACE:
   at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1683
   at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1585
   at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 2055
   at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 2037
   at NHibernate.Impl.SessionImpl.List[T](NativeSQLQuerySpecification spec, QueryParameters queryParameters) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 2024
   at NHibernate.Impl.SqlQueryImpl.List[T]() in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SqlQueryImpl.cs:line 163
   at Alkami.App.Core.Data.Dao.HibernateScheduledPaymentDao.FindPayeeClassificationScheduledPaymentCountByUser(Int64 userId) in C:\SRC\ithryv\Alkami.App\Source\Alkami.App.Core.Data\Dao\HibernateScheduledPaymentDao.cs:line 221
   at CompositionAopProxy_3f677a65d116437faa1e64951adc1a05.FindPayeeClassificationScheduledPaymentCountByUser(Int64 userId)
   at _dynamic_CompositionAopProxy_3f677a65d116437faa1e64951adc1a05.FindPayeeClassificationScheduledPaymentCountByUser(Object , Object[] )
   at Spring.Reflection.Dynamic.SafeMethod.Invoke(Object target, Object[] arguments) in c:\_svn\spring-net\tags\spring-net-1.3.1\src\Spring\Spring.Core\Reflection\Dynamic\DynamicMethod.cs:line 156
   at Spring.Aop.Framework.DynamicMethodInvocation.InvokeJoinpoint() in c:\_svn\spring-net\tags\spring-net-1.3.1\src\Spring\Spring.Aop\Aop\Framework\DynamicMethodInvocation.cs:line 100
   at Spring.Aop.Framework.Adapter.ThrowsAdviceInterceptor.Invoke(IMethodInvocation invocation) in c:\_svn\spring-net\tags\spring-net-1.3.1\src\Spring\Spring.Aop\Aop\Framework\Adapter\ThrowsAdviceInterceptor.cs:line 223

*** EXCEPTION SITE: FindPayeeClassificationScheduledPaymentCountByUser line 207-221 block where exception is thrown:

var session = SessionFactoryUtils.GetSession(Factory, true);
if (session == null)
	throw new InvalidOperationException("Unable to obtain an NHibernate session from the session factory");

session.FlushMode = FlushMode.Never;
var getPayeeClassificationScheduledPaymentCount = session.GetNamedQuery("GetPayeeClassificationScheduledPaymentCountByUser");
if (getPayeeClassificationScheduledPaymentCount == null)
	throw new InvalidOperationException("Unable to obtain the named query 'GetPayeeClassificationScheduledPaymentCountByUser'");

getPayeeClassificationScheduledPaymentCount
	.SetParameter("UserID", userId)
	.SetFirstResult(0)
	.SetMaxResults(1000);

return getPayeeClassificationScheduledPaymentCount.List<PayeeClassificationScheduledPaymentCount>();

**** SQL QUERY 'GetPayeeClassificationScheduledPaymentCountByUser' REFERENCED ABOVE:

	<sql-query name="GetPayeeClassificationScheduledPaymentCountByUser">
		<query-param name="UserID" type="bigint" />
		<return class="PayeeClassificationScheduledPaymentCount">
			<return-property column="ID" name="ID" />
			<return-property column="TransactionCategoryClassificationID" name="TransactionCategoryClassificationID" />
			<return-property column="PendingPaymentCount" name="PendingPaymentCount" />
		</return>
		<![CDATA[
			SELECT
	      COALESCE('ACH:' + pach.ReceivingRoutingNumber + '_' + pach.ReceivingAccountNumber,
	      COALESCE(pbp.PayeeKey,
	      COALESCE('Wire:' + pw.CreditRoutingNumber + '_' + pw.CreditAccountNumber,
	      NULL))) AS ID,
	      tcc.ID AS TransactionCategoryClassificationID,	
	       COUNT(pp.ID) AS PendingPaymentCount
      FROM core.ScheduledPaymentBase sp
      LEFT OUTER JOIN core.ScheduledPaymentACH spa ON (sp.ID = spa.ID)
      LEFT OUTER JOIN core.PaymentTemplateACH pta ON (pta.ID = spa.PaymentTemplateACHID)
      LEFT OUTER JOIN core.ScheduledPaymentBillPay spbp ON (sp.ID = spbp.ID)
      LEFT OUTER JOIN core.PaymentTemplateBillPay ptbp ON (ptbp.ID = spbp.PaymentTemplateBillPayID)
      LEFT OUTER JOIN core.ScheduledPaymentWire spw ON (sp.ID = spw.ID)
      LEFT OUTER JOIN core.PaymentTemplateWire ptw ON (ptw.ID = spw.PaymentTemplateWireID)
      LEFT OUTER JOIN core.PaymentTemplateItemBase ptib ON (ptib.PaymentTemplateBaseID IN (pta.ID, ptbp.ID, ptw.ID))
      LEFT OUTER JOIN core.PaymentTemplateItemACH ptia ON (ptia.ID = ptib.ID)
      LEFT OUTER JOIN core.PaymentTemplateItemBillPay ptibp ON (ptibp.ID = ptib.ID)
      LEFT OUTER JOIN core.PaymentTemplateItemWire ptiw ON (ptiw.ID = ptib.ID)
      LEFT OUTER JOIN core.PayeeACHAccount pach ON (spa.PayeeACHAccountID = pach.ID OR ptia.PayeeACHAccountID = pach.ID)
      LEFT OUTER JOIN core.PayeeBillPayAccount pbp ON (spbp.PayeeBillPayAccountID = pbp.ID OR ptibp.PayeeBillPayAccountID = pbp.ID)
      LEFT OUTER JOIN core.PayeeWireAccount pw ON (spw.PayeeWireAccountID = pw.ID OR ptiw.PayeeWireAccountID = pw.ID)
      INNER JOIN core.Payee p ON 
      (
        p.ID IN (
            SELECT ptib.PayeeID
            FROM core.PaymentTemplateItemBase ptib
            WHERE ptib.PaymentTemplateBaseID IN (pta.ID, ptbp.ID, ptw.ID)
        ) 
      )
      INNER JOIN core.TransactionCategoryClassification tcc ON tcc.ID = (
            SELECT tc.TransactionCategoryClassificationID 
            FROM core.TransactionCategory tc
            WHERE tc.ID = p.DefaultExpenseTransactionCategoryID
        )
      INNER JOIN core.PendingPaymentBase pp ON pp.ScheduledPaymentID = sp.ID AND [Status] = 1
      WHERE sp.SourceAccountID IN (SELECT ua.AccountID FROM core.UserAccount ua WHERE ua.UserID = :UserID)
      AND sp.Frequency > 0
      AND Deleted = 0
      GROUP BY COALESCE('ACH:' + pach.ReceivingRoutingNumber + '_' + pach.ReceivingAccountNumber,
      COALESCE(pbp.PayeeKey,
      COALESCE('Wire:' + pw.CreditRoutingNumber + '_' + pw.CreditAccountNumber,
      NULL))), tcc.ID
      HAVING COUNT(pp.ID) > 0
	    ]]>
	</sql-query>

*** RETURN CLASS DEFINITION PayeeClassificationScheduledPaymentCount

public class PayeeClassificationScheduledPaymentCount
{
	public virtual string ID { get; set; }
	public virtual long TransactionCategoryClassificationID { get; set; }
	public virtual int PendingPaymentCount { get; set; }
}

*** RETURN CLASS MAPPING

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Alkami.App.Core.Data.Entities" assembly="Alkami.App.Core.Data" schema="core">
	<class name="PayeeClassificationScheduledPaymentCount">
		<id name="ID" type="string" />
		<property name="TransactionCategoryClassificationID" type="Int64" not-null="true" />
		<property name="PendingPaymentCount" type="Int32" not-null="true" />
	</class>
</hibernate-mapping>

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[nhibernate-issues] [NHJIRA] Created: (NH-2754) Exception "Invalid filter-parameter name format\r\n

[nhibernate-issues] [NHJIRA] Created: (NH-2754) Exception "Invalid filter-parameter name format\r\nParameter name: filterParameterName" when using colons in quoted strings in sql-query T-SQL definitions