Using a named parameter multiple times in a native SQL query results in invalid parameter binding (exception in some drivers)
Description
Environment
Attachments
Activity
Fabio Maulo July 30, 2011 at 2:10 PM
Closed after final release of NH3.2.0GA
cremor July 25, 2011 at 2:12 PM
Many thanks for the fix!
Richard Brown July 23, 2011 at 7:32 PM
Fixed (across all Dialects) - fixes the following tests in Oracle:
NHibernate.Test.Component.Basic.ComponentTest.TestNamedQuery
NHibernate.Test.FilterTest.DynamicFilterTest.CriteriaQueryFilters
NHibernate.Test.FilterTest.DynamicFilterTest.HqlFilters
NHibernate.Test.FilterTest.DynamicFilterTest.ManyToManyFilterOnCriteria
NHibernate.Test.FilterTest.DynamicFilterTest.ManyToManyFilterOnQuery
NHibernate.Test.FilterTest.DynamicFilterTest.ManyToManyOnCollectionLoadAfterHQL
NHibernate.Test.Hql.HQLFunctions.Cast
NHibernate.Test.Legacy.FooBarTest.NamedParams
NHibernate.Test.NHSpecificTest.ManyToOneFilters20Behaviour.Fixture.ExplicitFiltersOnCollectionsShouldBeActiveWithEagerLoad
NHibernate.Test.NHSpecificTest.NH1098.FilterParameterOrderFixture.CriteriaParameterOrder
NHibernate.Test.NHSpecificTest.NH1098.FilterParameterOrderFixture.QueryWithNamedParameters
NHibernate.Test.NHSpecificTest.NH1098.FilterParameterOrderFixture.QueryWithPositionalParameter
NHibernate.Test.NHSpecificTest.NH1293.Fixture.Criteria_Does_Not_Equal_To_HQL
NHibernate.Test.NHSpecificTest.NH1490.Fixture.Incorrect_SQL_Translated_Params_Bug
NHibernate.Test.NHSpecificTest.NH1864.Fixture.Bug
NHibernate.Test.NHSpecificTest.NH1864.Fixture.FilterOnOffOn
NHibernate.Test.NHSpecificTest.NH1864.Fixture.FilterQueryTwice
NHibernate.Test.NHSpecificTest.NH1868.Fixture.Bug
NHibernate.Test.NHSpecificTest.NH1868.Fixture.FilterOnOffOn
NHibernate.Test.NHSpecificTest.NH1868.Fixture.FilterQuery3
NHibernate.Test.NHSpecificTest.NH1868.Fixture.FilterQueryTwice
NHibernate.Test.NHSpecificTest.NH1908.Fixture.QueryPropertyInBothFilterAndQuery
NHibernate.Test.NHSpecificTest.NH1908.Fixture.QueryPropertyInBothFilterAndQueryUsingWith
NHibernate.Test.NHSpecificTest.NH1908ThreadSafety.Fixture.UsingFiltersIsThreadSafe
NHibernate.Test.NHSpecificTest.NH1920.Fixture.Can_Query_With_Collection_Size_Condition
NHibernate.Test.NHSpecificTest.NH1927.Fixture.CriteriaWithEagerFetch
NHibernate.Test.NHSpecificTest.NH1927.Fixture.HqlWithEagerFetch
NHibernate.Test.NHSpecificTest.NH1990.Fixture.FetchingBySubqueryFilterParameters
NHibernate.Test.NHSpecificTest.NH1990.Fixture.FetchingBySubqueryFilterParametersAndPositionalParameters
NHibernate.Test.NHSpecificTest.NH1990.Fixture.FetchingBySubqueryFilterParametersAndPositionalParametersAndNamedParameters
NHibernate.Test.NHSpecificTest.NH2318.Fixture.HqlTrimFunctionsWithParameters
NHibernate.Test.QueryTest.DetachedQueryFixture.ExecutableNamedQuery
NHibernate.Test.SqlTest.Query.SelfReferencingCollectionLoadTest.LoadCollection
NHibernate.Test.SubclassFilterTest.DiscrimSubclassFilterTest.FiltersWithSubclass
Richard Brown July 23, 2011 at 4:17 PM
I suspect all the Oracle supplied drivers would fail the same way (but I cannot confirm). The System.Data.OracleClient.dll is obsolete now I believe (I don't think Microsoft support it any more). If someone confirms that (another JIRA), we could remove it from NH (one less headache).
The current fix creates the correct parameters, but then fails during the binding. The fix needs to remove the parameters 'after' the parameter binding.
In addition, I see no reason (someone could correct me?) that we shouldn't remove the 'extra' parameters in the DriverBase.cs - just because Oracle is the only driver that complains doesn't make the current implementation OK.
Julian Maughan July 23, 2011 at 2:40 PM
Thanks Cremor. I'm actually trying to find out which of NHibernate's Oracle drivers should be changed. Which one are you using? Do you know if the others have the same parameter validation behaviour?
OracleDataClientDriver (Oracle.DataAccess.dll)
OracleClientDriver (System.Data.OracleClient.dll)
OracleLiteDataClientDriver (Oracle.DataAccess.Lite_w32.dll)
The attached test executes a native SQL query in which a named parameter is used multiple times. This results in multiple parameters (with equal values) in the parameters collection of the command object. But these parameters are not all used in the generated plain SQL query (all parameters are named equally there).
Seems like this isn't a problem when using SQLite, but the OracleDataClientDriver throws an exception (ORA-01036: illegal variable name/number).