SQLException

[jotnarta]

Hi All
I am testing datasource usage in JBoss with mysql database. I've created the configuration file, and run the client, but I got the following exception:

Exception thrown java.sql.SQLException: Table not found in statement [select * from quest_choices]

Table quest_choices exists and contains data, but I don't know why I got this error. I am using JBoss 5 and mysql 5.1.7, any help will be highly apprecieated...

Thanx

[jotnarta]

Any help please??????

[peterj]

Based on the information you provided, it would appear you did something wrong. If you fix that, it should work.

I could be more specific, but you did not provide the *-ds.xml file, nor the code in question.

When posting XML text or source code, please enclose the text in UBBCode "code" tags - you can do this by selecting the text and clicking the Code button above the editor window. Also, click the Preview button to ensure that the formatting is correct and the XML text shows up before posting.

[jotnarta]

Ok, following is my msql-ds.xml file, and the configuration to connect to MySQL database, specifically, mysql schema:

<?xml version="1.0" encoding="UTF-8"?>

<datasources>

 <local-tx-datasource>

 <jndi-name>DefaultDS</jndi-name>

 <connection-url>jdbc:mysql://localhost:3306/mysql</connection-url>

 <driver-class>com.mysql.jdbc.Driver</driver-class>

 <user-name>root</user-name>

 <password>admin</password>

 <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>

 <metadata>

 <type-mapping>mySQL</type-mapping>

 </metadata>

 </local-tx-datasource>

</datasources>

and following is the jsp file that I am trying to connect my schema which was defined in the previous xml file:

<%@page import="java.util.*,javax.naming.*,javax.sql.DataSource,java.sql.*"%>
<%
 DataSource ds = null;
 Connection con = null;
 //Statement st = null;
 InitialContext ic;

 try {
 ic = new InitialContext();
 ds = (DataSource) ic.lookup("java:/DefaultDS");
 con = ds.getConnection();
 Statement st = con.createStatement();
 ResultSet rs = st.executeQuery("select * from db");

 while(rs.next()) {
 out.println("<br> " + rs.getString("ID"));
 }
 rs.close();
 st.close();

 out.println("DONE");
 } catch (Exception e) {
 out.println("Exception thrown " + e );
 //e.printStackTrace();
 } finally {
 if (con != null) {
 con.close();
 }
 }
%>

Do you have any comments ????

[peterj]

First, you called the data source DefaultDS - this is the same name used for the data source in hsqldb-ds.xml. Did you remove that file? If not, then your data source probably did not deploy and you code is attempting to access the hsql database.

Second, the code you posted is looking ofr a table named 'db' while your earlier post mentioned table 'quest_choices'. Therefore I suspect that this is not the code causing the error.

Now I am biting my tongue really hard to keep from commenting on seeing server-side script code in a JSP. (ouch)

[jotnarta]

Thank you Peter. I removed the file called hsql-db.xml, I changed my mysql-db.xml datasource to be MyDB, and I changed my jsp file.

mysql-ds.xml file:

<?xml version="1.0" encoding="UTF-8"?>

<datasources>

 <local-tx-datasource>

 <jndi-name>MyDB</jndi-name>

 <connection-url>jdbc:mysql://localhost:3306/project</connection-url>

 <driver-class>com.mysql.jdbc.Driver</driver-class>

 <user-name>root</user-name>

 <password>admin</password>

 <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>

 <metadata>

 <type-mapping>mySQL</type-mapping>

 </metadata>

 </local-tx-datasource>

</datasources>

I changed my jsp file to be:

try {
 ic = new InitialContext();
 ds = (DataSource) ic.lookup("java:/MyDB");
 con = ds.getConnection();
 Statement st = con.createStatement();
 ResultSet rs = st.executeQuery("select * quest_chices");

 while(rs.next()) {
 out.println("<br> " + rs.getString("ID"));
 }
 rs.close();
 st.close();

 out.println("DONE");
 } catch (Exception e) {
 out.println("Exception thrown " + e );
 //e.printStackTrace();
 } finally {
 if (con != null) {
 con.close();
 }
 }

and guess what?? When I run the jsp file i got Exception thrown javax.naming.NameNotFoundException: MyDB not bound ...

What i noticed here, is that when I change my datasource other than DefaultDS, I'll get NameNotFoundException>>>>

Any help please??

[peterj]

I copied your code into one of my projects. Of course I changed the database and table name to match something I had. And everything worked. One caveat: I did not use server-side script in a JSP - instead I placed the code in a servlet and passed the resulting string to the JSP which accessed if via EL.

A few suggestions.

1) Do not call the datasource DefaultDS. Your other post that used MyDS is better.
2) Do not remove hsqldb-ds.xml - the DefaultDS is needed by several JBoss AS services. (You can replace it, but it takes some work. Ch15 of JBoss in Action provides the details)
3) Do what I did - put your code in a servlet, pass the string to the JSP and use EL. Not mandatory, but saner.

Here is a recap of everything you have to do, starting with a fresh install of JBoss AS 5.0.0.GA:

1) Copy the mysql JDBC JAR file to server/default/lib
2) Copy your mysql-ds.xml file to server/default/deploy
3) Copy your WAR file to server/default/deploy
4) Start the app server and access the JSP

Good luck.

And please do not spam the forums any more.