Hi,
I am trying to execute a query which I wrote in my .hbm.xml in which I am passing 2 input parameters 1 String and 2nd one an Object array.
When I try to run the the query missing in or out parameter for the 2nd argument in the Object Array.
The query in Basket.hbm.xml is
Code:
<query name="com.dnaO2.infra.persistence.domain.IBasketDOMgr.selectBasketByUserIdAndProductId">
<![CDATA[from com.dnaO2.infra.persistence.bean.Basket as basket
where basket.userid = ? and basket.productid in (?)]]>
</query>
I am passing 2 arguments in my NamedQuery 1 String object and 2nd an object array as following: -
Code:
IBasketDOMgr basketDAOMgr = (IBasketDOMgr)GlobalSrvLocator
.get().getDomainObjectManager(IBasketDOMgr.class);
Object [] arr = new Long[2];
arr[0] = new Long(1002);
arr[0] = new Long(1005);
basketDAOMgr.selectBasketByUserIdAndProductId("xyzabc",arr);
The issue is the 2nd argument it is referring as an object.
Is there any way such that I can specify the parameters in the array in the query.
This is quite urgent can somebody help me.
Please, suggest me the correct query to run my query.
Thanks In Advance.
Hibernate version:3.1
Mapping documents:Basket.hbm.xml
Code between sessionFactory.openSession() and session.close():
public Collection selectBasketByUserIdAndProductId(String strUserId,
Object [] productId) throws PersistenceException;
IBasketDOMgr basketDAOMgr = (IBasketDOMgr)GlobalSrvLocator
.get().getDomainObjectManager(IBasketDOMgr.class);
Object [] arr = new Long[2];
arr[0] = new Long(1002);
arr[0] = new Long(1005);
basketDAOMgr.selectBasketByUserIdAndProductId(USER_NAME,arr);
}
Full stack trace of any exception that occurs:
2006-03-09 14:11:28,238 (org.hibernate.util.JDBCExceptionReporter) - could not execute query [select basket0_.basketid as basketid0_, basket0_.itemno as itemno0_, basket0_.userid as userid0_, basket0_.productid as productid0_, basket0_.skucode as skucode0_, basket0_.createdate as createdate0_ from basket basket0_ where basket0_.userid=? and (basket0_.productid in (?))]
java.sql.SQLException: Missing IN or OUT parameter at index:: 2
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1680)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3279)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3328)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2150)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:300)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:146)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1093)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at com.dnaO2.infra.persistence.service.QryImpl.execute(QryImpl.java:26)
at com.dnaO2.infra.persistence.service.SelectWithNameQry.invokeSqlCommand(SelectWithNameQry.java:16)
at com.dnaO2.infra.persistence.service.SrvLocatorImpl$ManagerDelegate.invoke(SrvLocatorImpl.java:86)
at $Proxy0.selectBasketByUserIdAndProductId(Unknown Source)
at com.dnaO2.basket.test.BasketFacadeTest.testRemoveBasket(BasketFacadeTest.java:129)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.textui.TestRunner.doRun(TestRunner.java:116)
at junit.textui.TestRunner.doRun(TestRunner.java:109)
at junit.textui.TestRunner.run(TestRunner.java:72)
at com.dnaO2.basket.test.BasketFacadeTest.main(BasketFacadeTest.java:198)
2006-03-09 14:11:28,238 (org.hibernate.util.JDBCExceptionReporter) - SQL Error: 17041, SQLState: null
2006-03-09 14:11:28,238 (org.hibernate.util.JDBCExceptionReporter) - Missing IN or OUT parameter at index:: 2
2006-03-09 14:11:28,238 (org.hibernate.jdbc.JDBCContext) - after autocommit
2006-03-09 14:11:28,238 (org.hibernate.jdbc.ConnectionManager) - aggressively releasing JDBC connection
2006-03-09 14:11:28,238 (org.hibernate.jdbc.ConnectionManager) - closing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
2006-03-09 14:11:28,238 (org.hibernate.connection.DriverManagerConnectionProvider) - returning connection to pool, pool size: 1
2006-03-09 14:11:28,238 (org.hibernate.impl.SessionImpl) - closing session
2006-03-09 14:11:28,238 (org.hibernate.jdbc.ConnectionManager) - connection already null in cleanup : no action
Name and version of the database you are using:Oracle 9i
The generated SQL (show_sql=true):true
Debug level Hibernate log excerpt:
2006-03-09 14:11:28,035 (org.hibernate.hql.ast.ErrorCounter) - throwQueryException() : no errors
2006-03-09 14:11:28,035 (org.hibernate.hql.antlr.HqlSqlBaseWalker) - select << begin [level=1, statement=select]
2006-03-09 14:11:28,035 (org.hibernate.hql.ast.tree.FromElement) - FromClause{level=1} : com.dnaO2.infra.persistence.bean.Skuproductxref (skuProductxref) -> skuproduct0_
2006-03-09 14:11:28,035 (org.hibernate.hql.ast.tree.FromReferenceNode) - Resolved : skuProductxref -> (skuproduct0_.productid, skuproduct0_.skucode, skuproduct0_.partnerid)
2006-03-09 14:11:28,035 (org.hibernate.hql.ast.tree.DotNode) - getDataType() : comp_id -> org.hibernate.type.ComponentType@7124af
2006-03-09 14:11:28,035 (org.hibernate.hql.ast.tree.DotNode) - Unresolved property path is now 'comp_id.product'
2006-03-09 14:11:28,035 (org.hibernate.hql.ast.tree.FromReferenceNode) - Resolved : skuProductxref.comp_id -> skuproduct0_.productid
2006-03-09 14:11:28,035 (org.hibernate.hql.ast.tree.DotNode) - getDataType() : comp_id.product -> org.hibernate.type.ManyToOneType(com.dnaO2.infra.persistence.bean.Product)
2006-03-09 14:11:28,051 (org.hibernate.hql.ast.tree.DotNode) - dereferenceShortcut() : property productid in com.dnaO2.infra.persistence.bean.Skuproductxref does not require a join.
2006-03-09 14:11:28,051 (org.hibernate.hql.ast.tree.DotNode) - Unresolved property path is now 'comp_id.product.productid'
2006-03-09 14:11:28,051 (org.hibernate.hql.ast.tree.FromReferenceNode) - Resolved : skuProductxref.comp_id.product -> skuproduct0_.productid
2006-03-09 14:11:28,051 (org.hibernate.hql.ast.tree.DotNode) - getDataType() : comp_id.product.productid -> org.hibernate.type.LongType@2a6ff
2006-03-09 14:11:28,051 (org.hibernate.hql.ast.tree.FromReferenceNode) - Resolved : skuProductxref.comp_id.product.productid -> skuproduct0_.productid
2006-03-09 14:11:28,051 (org.hibernate.hql.antlr.HqlSqlBaseWalker) - select : finishing up [level=1, statement=select]
2006-03-09 14:11:28,051 (org.hibernate.hql.ast.HqlSqlWalker) - processQuery() : ( SELECT ( FromClause{level=1} skuproductxref skuproduct0_ ) ( where ( = ( skuproduct0_.productid ( skuproduct0_.productid ( skuproduct0_.productid (skuproduct0_.productid, skuproduct0_.skucode, skuproduct0_.partnerid) comp_id ) product ) productid ) ? ) ) )
2006-03-09 14:11:28,051 (org.hibernate.hql.ast.HqlSqlWalker) - Derived SELECT clause created.
2006-03-09 14:11:28,051 (org.hibernate.hql.ast.util.JoinProcessor) - Using FROM fragment [skuproductxref skuproduct0_]
2006-03-09 14:11:28,051 (org.hibernate.hql.antlr.HqlSqlBaseWalker) - select >> end [level=1, statement=select]
2006-03-09 14:11:28,051 (org.hibernate.hql.ast.AST) - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT' querySpaces (skuproductxref)
+-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
| +-[SELECT_EXPR] SelectExpressionImpl: 'skuproduct0_.productid as productid19_, skuproduct0_.skucode as skucode19_, skuproduct0_.partnerid as partnerid19_' {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=skuProductxref,role=null,tableName=skuproductxref,tableAlias=skuproduct0_,origin=null,colums={,className=com.dnaO2.infra.persistence.bean.Skuproductxref}}}
| \-[SQL_TOKEN] SqlFragment: 'skuproduct0_.availabilitystatus as availabi4_19_, skuproduct0_.isvisible as isvisible19_, skuproduct0_.modifyuser as modifyuser19_, skuproduct0_.modifydate as modifydate19_, skuproduct0_.createdate as createdate19_'
+-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[skuProductxref], fromElementByTableAlias=[skuproduct0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
| \-[FROM_FRAGMENT] FromElement: 'skuproductxref skuproduct0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=skuProductxref,role=null,tableName=skuproductxref,tableAlias=skuproduct0_,origin=null,colums={,className=com.dnaO2.infra.persistence.bean.Skuproductxref}}
\-[WHERE] SqlNode: 'where'
\-[EQ] BinaryLogicOperatorNode: '='
+-[DOT] DotNode: 'skuproduct0_.productid' {propertyName=productid,dereferenceType=4,propertyPath=comp_id.product.productid,path=skuProductxref.comp_id.product.productid,tableAlias=skuproduct0_,className=com.dnaO2.infra.persistence.bean.Skuproductxref,classAlias=skuProductxref}
| +-[DOT] DotNode: 'skuproduct0_.productid' {propertyName=productid,dereferenceType=ROOT_LEVEL,propertyPath=comp_id.product.productid,path=skuProductxref.comp_id.product,tableAlias=skuproduct0_,className=com.dnaO2.infra.persistence.bean.Skuproductxref,classAlias=skuProductxref}
| | +-[DOT] DotNode: 'skuproduct0_.productid' {propertyName=product,dereferenceType=2,propertyPath=comp_id.product,path=skuProductxref.comp_id,tableAlias=skuproduct0_,className=com.dnaO2.infra.persistence.bean.Skuproductxref,classAlias=skuProductxref}
| | | +-[ALIAS_REF] IdentNode: '(skuproduct0_.productid, skuproduct0_.skucode, skuproduct0_.partnerid)' {alias=skuProductxref, className=com.dnaO2.infra.persistence.bean.Skuproductxref, tableAlias=skuproduct0_}
| | | \-[IDENT] IdentNode: 'comp_id' {originalText=comp_id}
| | \-[IDENT] IdentNode: 'product' {originalText=product}
| \-[IDENT] IdentNode: 'productid' {originalText=productid}
\-[PARAM] ParameterNode: '?' {ordinal=0, expectedType=org.hibernate.type.LongType@2a6ff}
2006-03-09 14:11:28,051 (org.hibernate.hql.ast.ErrorCounter) - throwQueryException() : no errors
2006-03-09 14:11:28,051 (org.hibernate.hql.ast.QueryTranslatorImpl) - HQL:
from com.dnaO2.infra.persistence.bean.Skuproductxref as skuProductxref
where skuProductxref.comp_id.product.productid=?
2006-03-09 14:11:28,051 (org.hibernate.hql.ast.QueryTranslatorImpl) - SQL: select skuproduct0_.productid as productid19_, skuproduct0_.skucode as skucode19_, skuproduct0_.partnerid as partnerid19_, skuproduct0_.availabilitystatus as availabi4_19_, skuproduct0_.isvisible as isvisible19_, skuproduct0_.modifyuser as modifyuser19_, skuproduct0_.modifydate as modifydate19_, skuproduct0_.createdate as createdate19_ from skuproductxref skuproduct0_ where skuproduct0_.productid=?
2006-03-09 14:11:28,051 (org.hibernate.hql.ast.ErrorCounter) - throwQueryException() : no errors
2006-03-09 14:11:28,051 (org.hibernate.engine.query.HQLQueryPlan) - HQL param location recognition took 0 mills (
from com.dnaO2.infra.persistence.bean.Skuproductxref as skuProductxref
where skuProductxref.comp_id.product.productid=?
)
2006-03-09 14:11:28,051 (org.hibernate.impl.SessionFactoryImpl) - Checking 0 named SQL queries
Hibernate session Factory is...org.hibernate.impl.SessionFactoryImpl@1642bd6
2006-03-09 14:11:28,098 (org.hibernate.impl.SessionImpl) - opened session at timestamp: 4677196546256896
.2006-03-09 14:11:28,160 (org.hibernate.jdbc.JDBCContext) - opening user JDBC connection, application must close it
2006-03-09 14:11:28,160 (org.hibernate.connection.DriverManagerConnectionProvider) - total checked-out connections: 1
2006-03-09 14:11:28,160 (org.hibernate.connection.DriverManagerConnectionProvider) - opening new JDBC connection
2006-03-09 14:11:28,176 (org.hibernate.connection.DriverManagerConnectionProvider) - created connection to: jdbc:oracle:thin:@192.168.150.122:1521:dna, Isolation Level: 2
2006-03-09 14:11:28,176 (com.dnaO2.infra.persistence.service.SrvLocatorImpl$ManagerDelegate) - session.isConnected() :--> true
2006-03-09 14:11:28,191 (org.hibernate.engine.query.QueryPlanCache) - located HQL query plan in cache (
from com.dnaO2.infra.persistence.bean.Basket as basket
where basket.userid = ? and basket.productid in (?)
)
2006-03-09 14:11:28,191 (org.hibernate.engine.query.QueryPlanCache) - located HQL query plan in cache (
from com.dnaO2.infra.persistence.bean.Basket as basket
where basket.userid = ? and basket.productid in (?)
)
2006-03-09 14:11:28,191 (org.hibernate.engine.query.HQLQueryPlan) - find:
from com.dnaO2.infra.persistence.bean.Basket as basket
where basket.userid = ? and basket.productid in (?)
2006-03-09 14:11:28,191 (org.hibernate.engine.QueryParameters) - parameters: [atul, [Ljava.lang.Long;@159e6e8]
2006-03-09 14:11:28,191 (org.hibernate.engine.QueryParameters) - named parameters: {}
2006-03-09 14:11:28,191 (org.hibernate.jdbc.AbstractBatcher) - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2006-03-09 14:11:28,191 (org.hibernate.jdbc.ConnectionManager) - opening JDBC connection
2006-03-09 14:11:28,191 (org.hibernate.connection.DriverManagerConnectionProvider) - total checked-out connections: 2
2006-03-09 14:11:28,191 (org.hibernate.connection.DriverManagerConnectionProvider) - opening new JDBC connection
2006-03-09 14:11:28,207 (org.hibernate.connection.DriverManagerConnectionProvider) - created connection to: jdbc:oracle:thin:@192.168.150.122:1521:dna, Isolation Level: 2
2006-03-09 14:11:28,207 (org.hibernate.SQL) - select basket0_.basketid as basketid0_, basket0_.itemno as itemno0_, basket0_.userid as userid0_, basket0_.productid as productid0_, basket0_.skucode as skucode0_, basket0_.createdate as createdate0_ from basket basket0_ where basket0_.userid=? and (basket0_.productid in (?))
Hibernate: select basket0_.basketid as basketid0_, basket0_.itemno as itemno0_, basket0_.userid as userid0_, basket0_.productid as productid0_, basket0_.skucode as skucode0_, basket0_.createdate as createdate0_ from basket basket0_ where basket0_.userid=? and (basket0_.productid in (?))
2006-03-09 14:11:28,207 (org.hibernate.jdbc.AbstractBatcher) - preparing statement
2006-03-09 14:11:28,207 (org.hibernate.type.StringType) - binding 'atul' to parameter: 1
2006-03-09 14:11:28,207 (org.hibernate.type.LongType) - binding '[Ljava.lang.Long;@159e6e8' to parameter: 2
2006-03-09 14:11:28,207 (org.hibernate.type.LongType) - could not bind value '[Ljava.lang.Long;@159e6e8' to parameter: 2
2006-03-09 14:11:28,238 (org.hibernate.jdbc.AbstractBatcher) - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-03-09 14:11:28,238 (org.hibernate.jdbc.AbstractBatcher) - closing statement
2006-03-09 14:11:28,238 (org.hibernate.util.JDBCExceptionReporter) - could not execute query [select basket0_.basketid as basketid0_, basket0_.itemno as itemno0_, basket0_.userid as userid0_, basket0_.productid as productid0_, basket0_.skucode as skucode0_, basket0_.createdate as createdate0_ from basket basket0_ where basket0_.userid=? and (basket0_.productid in (?))]
java.sql.SQLException: Missing IN or OUT parameter at index:: 2
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1680)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3279)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3328)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2150)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:300)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:146)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1093)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at com.dnaO2.infra.persistence.service.QryImpl.execute(QryImpl.java:26)
at com.dnaO2.infra.persistence.service.SelectWithNameQry.invokeSqlCommand(SelectWithNameQry.java:16)
at com.dnaO2.infra.persistence.service.SrvLocatorImpl$ManagerDelegate.invoke(SrvLocatorImpl.java:86)
at $Proxy0.selectBasketByUserIdAndProductId(Unknown Source)
at com.dnaO2.basket.test.BasketFacadeTest.testRemoveBasket(BasketFacadeTest.java:129)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.textui.TestRunner.doRun(TestRunner.java:116)
at junit.textui.TestRunner.doRun(TestRunner.java:109)
at junit.textui.TestRunner.run(TestRunner.java:72)
at com.dnaO2.basket.test.BasketFacadeTest.main(BasketFacadeTest.java:198)
2006-03-09 14:11:28,238 (org.hibernate.util.JDBCExceptionReporter) - SQL Error: 17041, SQLState: null
2006-03-09 14:11:28,238 (org.hibernate.util.JDBCExceptionReporter) - Missing IN or OUT parameter at index:: 2
2006-03-09 14:11:28,238 (org.hibernate.jdbc.JDBCContext) - after autocommit
2006-03-09 14:11:28,238 (org.hibernate.jdbc.ConnectionManager) - aggressively releasing JDBC connection
2006-03-09 14:11:28,238 (org.hibernate.jdbc.ConnectionManager) - closing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
2006-03-09 14:11:28,238 (org.hibernate.connection.DriverManagerConnectionProvider) - returning connection to pool, pool size: 1
2006-03-09 14:11:28,238 (org.hibernate.impl.SessionImpl) - closing session
2006-03-09 14:11:28,238 (org.hibernate.jdbc.ConnectionManager) - connection already null in cleanup : no action
Thanks,
Manjith A.
Login
FAQ
Search
