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)