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?

      ejb-jar.xml:

      <ejb-name>Pricing</ejb-name>
      com.tutorial.interfaces.PricingHome
      com.tutorial.interfaces.Pricing
      <ejb-class>com.tutorial.ejb.PricingBean</ejb-class>
      <session-type>Stateless</session-type>
      <transaction-type>Container</transaction-type>
      <resource-ref>
      <res-ref-name>jdbc/MSSQLDS</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
      <res-sharing-scope>Shareable</res-sharing-scope>
      </resource-ref>


      jboss.xml:

      <ejb-name>Pricing</ejb-name>
      <jndi-name>Pricing</jndi-name>
      <local-jndi-name>PricingLocal</local-jndi-name>


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


      <local-tx-datasource>
      <jndi-name>MSSQLDS</jndi-name>
      <connection-url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=EXAMPLE</connection-url>
      <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
      <user-name>sa</user-name>

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

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

      </local-tx-datasource>



      Thanks.
      Datta

        • 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");
          to
          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)