Configuring MySQL with JBoss 4

[davecasserly]

Firstly, the error im getting is:

[STDOUT] java.sql.SQLException: Table not found: USERS_TBL in statement [SELECT * FROM users_tbl]

Im executing this using the code:

try {
 Context ctx = new InitialContext();
 Object ob = ctx.lookup("java:comp/env/jdbc/MySqlDS");
 connectionPool = (DataSource) ob;

 conn = connectionPool.getConnection();
 Statement s = conn.createStatement();
 res = s.executeQuery("SELECT * FROM users_tbl");


} catch (Exception e) {
 System.err.println(e.toString());
}

If i use the code below everything works fine:

try{
 Class.forName("com.mysql.jdbc.Driver");
 String url = "jdbc:mysql://localhost:3306/davidjc2_jspforums";
 conn = DriverManager.getConnection(url, "davidjc2_webuser","123qwe");

 Statement s = conn.createStatement();
 res = s.executeQuery("SELECT * FROM users_tbl");

}catch(Exception e){
 System.err.println(e.toString());
}

In ejb-jar.xml i have:

<resource-ref>
 <description>The MySQL Database</description>
 <res-ref-name>jdbc/MySqlDS</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
 </resource-ref>

Which leads me to believe its something to do with my configuration.

In standardjaws.xml i have:

<datasource>java:/MySqlDS</datasource>
<type-mapping>mySQL</type-mapping>

In login-config.xml i have:

<application-policy name = "MySqlDbRealm">
<authentication>
<login-module code =
 "org.jboss.resource.security.ConfiguredIdentityLoginModule"
 flag = "required">
<module-option name ="principal">myusername</module-option>
<module-option name ="userName">myusername</module-option>
<module-option name ="password">mypassword</module-option>
<module-option name ="managedConnectionFactoryName">
 jboss.jca:service=LocalTxCM,name=MySqlDS
</module-option>
</login-module>
</authentication>
</application-policy>

In mysql-ds.xml in deploy directory i have:

<datasources>
 <local-tx-datasource>
<jndi-name>jdbc/MySqlDS</jndi-name>
 <connection-url>jdbc:mysql://localhost:3306/davidjc2_jspforums</connection-url>
 <driver-class>com.mysql.jdbc.Driver</driver-class>
 <user-name>myusername</user-name>
 <password>mypassword</password>
 </local-tx-datasource>

</datasources>

Can anyone help with this please?

[aloubyansky]

The datasource will bound under java:/jdbc/MySqlDS. You should use jboss.xml to map jdbc/MySqlDS to the actual jndi name.

[davecasserly]

How would i map it to the jndi name of java:comp/env/jdbc?

Is this possible? Recommended?

I read about the fact that everything under java: is available only to the JVM.

I thought mapping datasources under java:comp/env/jdbc was the common trend.

Thanks
Dave

[aloubyansky]

You can do it with jboss.xml resource-ref element. The DTD is jboss_3_2.dtd.