2 Replies Latest reply on Jul 21, 2002 7:31 PM by Roberto Gonzalez Rocha

    <declared-sql><other> generaded SQL fails

    Roberto Gonzalez Rocha Newbie

      I submit this to sf/jboss bug traker but..

      I'm using 3.0.1RC1 with JDK1.4.1Beta and mySQL.
      Seems like the &lt;declared-sql&gt;&lt;other&gt; generated
      SQL miss a space before the &lt;other&gt; SQL sentence

      The query is defined on jbosscmp-jdbc.xml as:

      &lt;query&gt;
      &lt;description/&gt;
      &lt;query-method&gt;

      &lt;method-name&gt;findByClientInRange&lt;/method-name&gt;
      &lt;method-params&gt;

      &lt;method-param&gt;java.lang.Integer&lt;/method-param&gt;

      &lt;method-param&gt;java.lang.Integer&lt;/method-param&gt;

      &lt;method-param&gt;java.lang.Integer&lt;/method-param&gt;
      &lt;/method-params&gt;
      &lt;/query-method&gt;

      &lt;declared-sql&gt;
      &lt;where&gt;&lt;![CDATA[client={0}]]&gt;&lt;/where&gt;
      &lt;order&gt;&lt;![CDATA[DATE ASC]]&gt;&lt;/order&gt;
      &lt;other&gt;&lt;![CDATA[LIMIT {1},{2}]]&gt;&lt;/other&gt;
      &lt;/declared-sql&gt;
      &lt;/query&gt;


      I tried adding myself spaces before/after the sql but jboss removes them.
      The server throws the error:


      2002-07-20 19:44:04,101 DEBUG
      [org.jboss.ejb.plugins.cmp.jdbc.JDBCDeclaredSQLQuery.Call.findByClientInRange]
      Executing SQL: SELECT ID FROM CALL WHERE client=? ORDER
      BY DATE ASCLIMIT ?,?
      2002-07-20 19:44:04,160 DEBUG
      [org.jboss.ejb.plugins.cmp.jdbc.JDBCDeclaredSQLQuery.Call.findByClientInRange]
      Find failed
      java.sql.SQLException: Syntax error or access
      violation: You have an error in your SQL syntax near
      'ASCLIMIT 0,100' at line 1
      at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)
      at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(Unknown Source)
      at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
      at
      org.gjt.mm.mysql.PreparedStatement.executeQuery(Unknown
      Source)
      at
      org.jboss.resource.adapter.jdbc.local.LocalPreparedStatement.executeQuery(LocalPreparedStatement.java:289)
      at
      org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:107)
      at
      org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.execute(JDBCFindEntitiesCommand.java:40)
      at
      org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntities(JDBCStoreManager.java:549)
      at
      org.jboss.ejb.plugins.CMPPersistenceManager.findEntities(CMPPersistenceManager.java:348)
      at
      org.jboss.resource.connectionmanager.CachedConnectionInterceptor.findEntities(CachedConnectionInterceptor.java:323)
      at
      org.jboss.ejb.EntityContainer.findLocal(EntityContainer.java:610)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native
      Method)
      at ......