Syntax error in declared-sql
learningfast Mar 12, 2004 3:32 PMHi - can anyone help to resolve the following problem?
2004-03-12 15:31:38,406 DEBUG [org.jboss.ejb.plugins.LogInterceptor] InvokeHome: findBySubscriberNbr(1, 2, 4,000000754)
2004-03-12 15:31:38,406 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCDeclaredSQLQuery.SubscriberSummary#findBySubscriberNbr] Executing SQL: SELECT DISTINCT o.id, o.group_id, o.group_nbr, o.plan_name, o.last_name, o.first_name, o.middle_name, o.address1, o.city, o.state_cd, o.postal_cd, o.thru_dt FROM dbo.vSubscriberSummary o WHERE o.group_id IN (?) AND o.subscriber_nbr = ?
2004-03-12 15:31:38,406 TRACE [org.jboss.ejb.plugins.cmp.jdbc.JDBCDeclaredSQLQuery.SubscriberSummary#findBySubscriberNbr] Set parameter: index=1, jdbcType=VARCHAR, value=1, 2, 4
2004-03-12 15:31:38,406 TRACE [org.jboss.ejb.plugins.cmp.jdbc.JDBCDeclaredSQLQuery.SubscriberSummary#findBySubscriberNbr] Set parameter: index=2, jdbcType=VARCHAR, value=000000754
2004-03-12 15:31:38,506 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCDeclaredSQLQuery.SubscriberSummary#findBySubscriberNbr] Find failed
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Syntax error converting the nvarchar value '1, 2, 4' to a column of data type int.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.getRow(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplResultSet.positionCursor(Unknown Source)
at com.microsoft.jdbc.base.BaseResultSet.next(Unknown Source)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:181)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:111)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.execute(JDBCFindEntitiesCommand.java:38)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntities(JDBCStoreManager.java:579)
at org.jboss.ejb.plugins.CMPPersistenceManager.findEntities(CMPPersistenceManager.java:311)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.findEntities(CachedConnectionInterceptor.java:322)
at org.jboss.ejb.EntityContainer.findLocal(EntityContainer.java:613)
This occurs in a CMP finder where the query is defined as follows:
<query> <query-method> <method-name>findBySubscriberNbr</method-name> <method-params> <method-param>java.lang.String</method-param> <method-param>java.lang.String</method-param> </method-params> </query-method> <declared-sql> <select> <distinct/> <alias>o</alias> </select> <from/> <where><![CDATA[o.group_id IN ({0}) AND o.subscriber_nbr = {1}]]></where> </declared-sql> </query>
o.group_id is defined as Integer and the query works correctly when the first parameter {0} contains only a single integer passed in a String. However, when this parameter contains a list of integers passed in a String i.e. (1, 2, 4) as shown above, the error occurs. The second parameter {1} contains '000000754' whether it works or fails.
My intent is to create a query that accomplishes the same as the following SQL:
SELECT * FROM SubscriberSummary AS o WHERE o.group_id IN (1,2,4) AND o.subscriber_nbr = '000000754';
I used declared-sql because I could not find a way to do this with EJB-QL or JBOSS-QL.
Any help would be appreciated - Dave