3 Replies Latest reply on Sep 7, 2004 5:10 AM by Darran Lofthouse

    I have oracle database but it looks for HypersonicSQL Databa

    ashishabrol Newbie

      Hi Everyone -

      I have a very weird problem. In the ejbCreate method of my BMP I am calling insertRow method which uses the namespace "java:/comp/env/jdbc/ESPDS" to establish connection with the Oracle server. But when I test the connection properties from DatabaseMetaData I realize that it is actually using the HyperSonicSQl database rather than my Oracle database. I am stuck . Please help me out.


      public JobStartPK ejbCreate (Long caseId,Long checkType, Long data, Long findings, Long jobId,String counInvolved, String jobDetail, String jobSummary, String screenScope, String status) throws CreateException{
      try {
      this.caseId = caseId;
      this.checkType = checkType;
      this.counInvolved = counInvolved;
      this.data = data;
      this.findings = findings;
      this.jobDetail = jobDetail;
      this.jobId = jobId;
      this.jobSummary = jobSummary;
      this.screenScope = screenScope;
      this.status = status;

      insertRow (caseId, checkType, data, findings, jobId, counInvolved, jobDetail, jobSummary, screenScope,status);
      }catch (Throwable e){
      throw new EJBException (this.getClass ( ) + " ejbCreate: " + e.getMessage ( ));
      }

      return null;
      }



      private void insertRow (Long caseId, Long checkType, Long data, Long findings, Long jobId,
      String counInvolved, String jobDetail, String jobSummary, String screenScope, String status
      ) throws SQLException, Throwable{
      Connection conn = null;
      PreparedStatement ps = null;

      try{
      String sSelect = "INSERT INTO ESP.JOB_START (CASE_ID , CHECKTYPE,"
      + "DATA ,FINDINGS ,JOB_ID ,COUN_INVOLVED ,JOB_DETAIL ,JOB_SUMMARY ,SCREEN_SCOPE "
      + ", STATUS) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
      System.out.println("sSelect="+ sSelect);
      Context initContext = new InitialContext();
      DataSource ds = (DataSource)initContext.lookup("java:/comp/env/jdbc/ESPDS");

      conn = ds.getConnection();
      testConnection(conn);
      ps = conn.prepareStatement (sSelect);
      ps.setLong (1, caseId.longValue ( ));
      ps.setLong (2, checkType.longValue ( ));
      ps.setLong (3, data.longValue ( ));
      ps.setLong (4, findings.longValue ( ));
      ps.setLong (5, jobId.longValue ( ));
      ps.setString (6, counInvolved);
      ps.setString (7, jobDetail);
      ps.setString (8, jobSummary);
      ps.setString (9, screenScope);
      ps.setString (10, status);
      System.out.println("12");
      ps.executeUpdate ( );
      System.out.println("14");
      ps.close ( );
      conn.close ( );
      }catch (Exception e){e.printStackTrace();}

      finally{
      if (ps != null){
      try {
      ps.close ( );
      }catch (Exception e){e.printStackTrace();}
      }

      if (conn != null){
      try {
      conn.close ( );
      }catch (Exception e){e.printStackTrace();}
      }
      }
      }


      /** Test the JDBC connection to the database and report the
      * product name and product version.
      */

      public void testConnection(Connection connection ) {
      System.out.println();
      System.out.println("Testing database connection ...\n");
      if (connection == null) {
      System.out.println("Test failed.");
      return;
      }
      try {
      DatabaseMetaData dbMetaData = connection.getMetaData();
      String productName =
      dbMetaData.getDatabaseProductName();
      String productVersion =
      dbMetaData.getDatabaseProductVersion();
      String driverName = dbMetaData.getDriverName();
      String driverVersion = dbMetaData.getDriverVersion();
      System.out.println();
      System.out.println("Product name: " + productName);
      System.out.println("Product version: " + productVersion);
      System.out.println("Driver Name: " + driverName);
      System.out.println("Driver Version: " + driverVersion);
      ResultSet rs = dbMetaData.getSchemas();
      ResultSet rs1 = null;
      while(rs.next()) {
      System.out.println(rs.getString(1));
      System.out.println(
      "The following tables are available in the "+rs.getString(1)+" schema:");
      rs1 = dbMetaData.getTables(null,rs.getString(1),"%",null);
      while(rs1.next()) {
      System.out.println(" "+ rs1.getString(3)+" : "+rs1.getString(4));
      }
      }
      } catch(SQLException sqle) {
      System.err.println("Error connecting: " + sqle);
      }
      }

      ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
      I have described the BMP in the ejb-jar.xml in the following fashion-


      Entity Bean ( BMP )
      <display-name>JobStartEntity</display-name>
      <ejb-name>JobStartEntity</ejb-name>
      <local-home>com.hill_assoc.esp.BMP.JobStartEntityLocalHome</local-home>
      com.hill_assoc.esp.BMP.JobStartEntityLocal
      <ejb-class>com.hill_assoc.esp.BMP.JobStartEntityBean</ejb-class>
      <persistence-type>Bean</persistence-type>
      <prim-key-class>com.hill_assoc.esp.BMP.JobStartPK</prim-key-class>
      False
      <resource-ref>
      <res-ref-name>jdbc/ESPDS</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
      <res-sharing-scope>Shareable</res-sharing-scope>
      </resource-ref>




      ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
      I get the following error because it looks for the table JOB_START in the HSQL Database but my table is in the Oracle database-

      16:38:12,769 INFO [STDOUT] sSelect=INSERT INTO JOB_START (CASE_ID , CHECKTYPE,DATA ,FINDINGS ,JOB_ID ,COUN_INVOLVED ,JOB_DETAIL ,JOB_SUMMARY ,SCREEN_SCOPE , STATUS) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      16:38:12,831 INFO [STDOUT] Testing database connection ...
      16:38:12,863 INFO [STDOUT] Product name: HSQL Database Engine
      16:38:12,863 INFO [STDOUT] Product version: 1.7.1
      16:38:12,863 INFO [STDOUT] Driver Name: HSQL Database Engine Driver
      16:38:12,863 INFO [STDOUT] Driver Version: 1.7.1
      16:38:12,863 INFO [STDOUT] 12
      16:38:12,863 INFO [STDOUT] java.sql.SQLException: Table not found: ESP.JOB_START in statement [INSERT INTO ESP.JOB_START (CASE_ID , CHECKTYPE,DATA ,FINDINGS ,JOB_ID ,COUN_INVOLVED ,JOB_DETAIL ,JOB_SUMMARY ,SCREEN_SCOPE , STATUS) VALUES (1, 2, 3, 4, 5, 'c', 'jd', 'js', 'ss', 'st')]
      16:38:12,863 INFO [STDOUT] at org.hsqldb.Trace.getError(Unknown Source)