4 Replies Latest reply on Feb 26, 2009 10:53 AM by Peter Johnson

    MySQL Datasource on JBoss

    Jotnarta Zabin Newbie

      Hi All

      Any one know how to configure a mysql datasource in JBoss??? I am using JBoss 5 and mysql 5, I tried everything, and read everything, :argh:, can anyone help me , pleassssssssse... I always get the following error

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


      following is my mysql-ds.xml file:
      <?xml version="1.0" encoding="UTF-8"?>
      
      <datasources>
      
       <local-tx-datasource>
      
       <jndi-name>DefaultDS</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>


      and following is my client:
      <%@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();
       //out.println( "Connection: " + con. );
      
       Statement st = con.createStatement();
       //DatabaseMetaData md = con.getMetaData();
      
       out.println(" Username: " + md.getUserName());
      
       ResultSet rs = st.executeQuery("select * from questions");
       //ResultSet rs = md.getTables(null, null, "%", null);
      
       while(rs.next()) {
       out.println("<br> " + rs.getString("ID"));
       System.out.println(rs.getString(3));
       }
       rs.close();
       st.close();
      
       out.println("DONE");
       } catch (Exception e) {
       out.println("Exception thrown " + e );
       //e.printStackTrace();
       } finally {
       if (con != null) {
       con.close();
       }
       }
      %>


      Another questions is, do i have to change something in other files?? and where can i find the log for JBoss server???

      Any help please???

        • 1. Re: MySQL Datasource on JBoss
          Vicky Kak Master

           

          "jotnarta" wrote:

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


          Are you sure that there exists a table by name "questions"?

          and where can i find the log for JBoss server???

          Why don't you check the faq's ???
          check at $JBOSS_HOME/server//log/ location for the server log.


          • 2. Re: MySQL Datasource on JBoss
            Jotnarta Zabin Newbie

            Thank you buddy, am sure that table questions do exist in my schema.... :(, but why the program don't see it ??????????????

            • 3. Re: MySQL Datasource on JBoss
              Vicky Kak Master

              Ok, configure the sql client with the same details as you have in -ds.xml file and see if you can execute the same query through it, you can use squirrel as client.
              Another thing which you can try is to have a simple jdbc program and check if the same statement works Or not.
              What errors do you see in the server.log?

              • 4. Re: MySQL Datasource on JBoss
                Peter Johnson Master

                Wow, deja-vu all over again.

                What operating system are you using? On some OSes MySQL is case sensitive.

                If you are still havingproblems, do this: run 'mysql' from a command line, logging in with user name and password from the *-ds.xml file, then enter 'use project', and then enter "show tables'. Post the entire interaction, starting with the 'mysql' command line and ending with the response from 'show tables'.