|
From: Sean M. (JIRA) <nh...@gm...> - 2011-05-31 18:18:46
|
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
|