jboss - mysql Table not found in statement

[Alessandro Ilardo]

Hi there,
I'm trying to establish a connection from an EJB module running on Jboss 4 to a Mysql 5 database.

<datasources>
  <local-tx-datasource>
    <jndi-name>jdbc/tmpSavingsAcc</jndi-name>
    <connection-url>jdbc:mysql://127.0.0.2:3306/tmp</connection-url>
    <driver-class>org.gjt.mm.mysql.Driver</driver-class>
    <user-name>user</user-name>
    <password>temp</password>
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
    <!-- should only be used on drivers after 3.22.1 with "ping" support
    <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
    -->
    <!-- sql to call when connection is created
    <new-connection-sql>some arbitrary sql</new-connection-sql>
      -->
    <!-- sql to call on an existing pooled connection when it is obtained from pool - MySQLValidConnectionChecker is preferred for newer drivers
    <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
      -->
 
    <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
    <metadata>
       <type-mapping>mySQL</type-mapping>
    </metadata>
  </local-tx-datasource>
</datasources>

private javax.sql.DataSource getSavingsAccountDB() throws javax.naming.NamingException {
        javax.naming.Context c = new javax.naming.InitialContext();
        return (javax.sql.DataSource) c.lookup("java:comp/env/jdbc/tmpSavingsAcc");
    }
 
//make the db connection
private void makeConnection(){
        try{
            con = getSavingsAccountDB().getConnection();
        }catch(Exception ex){
            throw new EJBException("Unable to connect to database. " +
                    ex.getMessage());
        }
    }
 
    // database methods
    private void insertRow(String id, String firstName,
            String lastName, BigDecimal balance) throws SQLException{
        makeConnection();
        String insertStatement = "insert into savingsaccount values ( ? , ? , ? , ? )";
        PreparedStatement prepStmt = con.prepareStatement(insertStatement);
        
        prepStmt.setString(1, id);
        prepStmt.setString(2, firstName);
        prepStmt.setString(3, lastName);
        prepStmt.setBigDecimal(4, balance);
        
        prepStmt.executeUpdate();
        prepStmt.close();
        releaseConnection();
    }

When I try to run the modue from a simple client I get the following error:
Exception is caught: EJBException:; nested exception is:
javax.ejb.EJBException: ejbCreate: Tabella non trovata in statement [insert into savingsaccount values ( ? , ? , ? , ? )]

It basically says that the table has not been found in the statement.
The query shoulb be ok, I did some test in the mysql client. It may not have the default database set. But in the jdbc parameters it has it (after the url).

Does anyone have an idea on what my problem is?
Thank in advance.

[Alessandro Ilardo]

I partially solved my problem, which was related to JBoss datasource configuration.

http://wiki.jboss.org/wiki/Wiki.jsp?page=ConfigDataSources

Partially because the ejb correclty connects to MySQL, but I've got an error from the JBoss init, so I'm going to continue this topic on JBoss forum.

[Jeanne Boyarsky]

Alessandro,
I'm just linking to the JBoss thread in case anyone reading this wants to follow the topic over there.