1 Reply Latest reply on Mar 13, 2004 6:47 AM by Alexey Loubyansky

    Syntax error in declared-sql

    learningfast Newbie

      Hi - 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