1 Reply Latest reply on Mar 28, 2006 7:25 PM by Datta Mahabalagiri

    Table not found in statement  when using MSSQL

    Datta Mahabalagiri Newbie

      I am getting this exception when executing a Statement from a Stateless Session Bean.
      EJBException:; nested exception is: javax.ejb.EJBException: Unable to get price: java.sql.SQLException: Table not found in statement [SELECT price FROM price WHERE product_code = ?]

      This is where it fails (in Bean class):
      DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/MSSQLDS");
      return ds.getConnection();
      ps = conn.prepareStatement(
      "SELECT price FROM price WHERE product_code = ?");

      I have suspicion its still pointing to HSSQL instead of MSSQL database. Table price does exist in MSSQL db. How do I point to MSSQL?





      mssql-ds.xml in $JBOSS_HOME/server/default/deploy


      <check-valid-connection-sql>SELECT 1 FROM sysusers WHERE (uid = 1)</check-valid-connection-sql>

      <type-mapping>MS SQLSERVER2000</type-mapping>



        • 1. Re: Table not found in statement  when using MSSQL
          Datta Mahabalagiri Newbie

          I made it work. First, I deleted the default DataSource configuration that comes with JBoss4 ($JBOSS_HOME/server/default/deploy/hsqldb-ds.xml).
          Second, I changed the name for Datasource lookup from
          DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/MSSQLDS");
          DataSource ds = (DataSource) context.lookup("java:jdbc/MSSQLDS");

          It helped me identify the correct JNDI name when I listed JNDI resources from JMX console. It is very clear from the view below:

          java: Namespace
          +- SecurityProxyFactory (class: org.jboss.security.SubjectSecurityProxyFactory)
          +- java (class: org.jnp.interfaces.NamingContext)
          +- DefaultJMSProvider (class: org.jboss.jms.jndi.JNDIProviderAdapter)
          +- comp (class: javax.naming.Context)
          +- jdbc (class: org.jnp.interfaces.NamingContext)
          | +- MSSQLDS (class: org.jboss.resource.adapter.jdbc.WrapperDataSource)