1 Reply Latest reply on Apr 5, 2003 10:27 AM by David Jencks

    Mysql problem- can not connect to the mysql

    amalp Newbie

      Hello,

      I am having problem with mysql database. I am trying to run AccountEJB application from SUN with mysql as my backend.

      PLEASE DO NOT FORGET I AM NOT EXPERT IN JBOSS OR EJB BUT I AM TRYING TO LEAN WITH A EXAMPLE.

      HERE IS MY SETUP UP TO THIS POINT.

      I am running jboss-3.03.0_tomcat-4.0.3



      I am including

      1. AccountBean
      2. ejb-jar.xml
      3. web.xml
      4. jboss.xml
      6. mysql-service.xml ( I add my database name, username, password and i copy it to ..server/default/deploy dir.)

      I only modified mysql-service.xml. Do i have to modify any other jboss files. Some people are saying i have to modify "standardjaws.xml, standardjbosscmp.xml"

      My AccountBean code is


      Please take a look at "setEntityContext" and "makeConnection" that is where problem comes.

      Here is the error


      10:43:19,950 INFO [Engine] AccountServlet: init
      10:43:19,950 INFO [STDOUT] AccountServlet: init()
      10:43:20,101 INFO [STDOUT] Got context
      10:43:20,311 INFO [STDOUT] Got referance
      10:43:20,371 INFO [STDOUT] Got referance to home object
      10:43:20,501 INFO [STDOUT] setEntityContext call....
      10:43:20,501 INFO [STDOUT] makeConnection call...
      10:43:20,501 INFO [STDOUT] Something went wrong within makeConnction call ....
      javax.naming.NameNotFoundException: MySqlDS not bound
      10:43:20,501 INFO [STDOUT] Came back from makeConnction call ...
      10:43:20,501 INFO [STDOUT] ejbCreate call
      10:43:20,501 INFO [STDOUT] insertRow call...
      10:43:20,511 ERROR [STDERR] Caught an exception.
      10:43:20,511 ERROR [STDERR] java.rmi.ServerException: ejbCreate: null; nested ex
      ception is:
      javax.ejb.EJBException: ejbCreate: null





      package com.ps.impl;
      import javax.ejb.EntityBean;
      import javax.ejb.EntityContext;
      import java.sql.*;
      import javax.sql.*;
      import javax.ejb.*;
      import javax.naming.*;
      import java.util.*;
      import java.rmi.*;


      public class AccountBean implements EntityBean
      {

      private EntityContext context;
      private String id;
      private String firstName;
      private String lastName;
      private double balance;
      private Connection con;
      //private String dbName = "java:/MySqlDS";

      /********************************************************************************/

      public void debit (double amount)
      {
      if (balance - amount < 0)
      {

      }
      else
      {
      balance = balance - amount;
      }
      }

      /*******************************************************************************/

      public void credit (double amount)
      {
      balance = balance + amount;
      }
      /*******************************************************************************/
      public String getFirstName()
      {
      return firstName;
      }

      /*******************************************************************************/

      public String getLastName()
      {
      return lastName;
      }

      /*******************************************************************************/

      public double getBalance()
      {
      return balance;
      }

      /********************************************************************************/

      public String ejbCreate(String id, String firstName, String lastName, double balance)
      throws CreateException
      {

      System.out.println("ejbCreate call");

      if (balance < 0.00) {
      throw new CreateException
      ("A negative initial balance is not allowed.");
      }

      try {
      insertRow(id, firstName, lastName, balance);
      } catch (Exception ex) {
      throw new EJBException("ejbCreate: " +
      ex.getMessage());
      }

      this.id = id;
      this.firstName = firstName;
      this.lastName = lastName;
      this.balance = balance;

      return id;
      }

      /********************************************************************************/

      public void ejbPostCreate(String id, String firstname, String lastname, double blance)
      {
      }

      /*********************************************************************************/

      public String ejbFindByPrimaryKey(String primaryKey)
      throws FinderException
      {

      System.out.println("ejbFindPrimaryKey call...");

      boolean result;


      try {
      result = selectByPrimaryKey(primaryKey);
      } catch (Exception ex) {
      throw new EJBException("ejbFindByPrimaryKey: " +
      ex.getMessage());
      }
      if (result) {
      return primaryKey;
      }
      else {
      throw new ObjectNotFoundException
      ("Row for id " + primaryKey + " not found.");
      }
      }

      /********************************************************************************/

      public Collection ejbFindByLastName(String lastName)
      throws FinderException
      {

      System.out.println("ejbFindByLastName call...");

      Collection result;

      try {
      result = selectByLastName(lastName);
      } catch (Exception ex) {
      throw new EJBException("ejbFindByLastName " +
      ex.getMessage());
      }

      if (result.isEmpty()) {
      throw new ObjectNotFoundException("No rows found.");
      }
      else {
      return result;
      }
      }


      /********************************************************************************/

      public Collection ejbFindInRange(double low, double high)
      throws FinderException
      {

      System.out.println("ejbFindRange call ....");

      Collection result;


      try {
      result = selectInRange(low, high);

      } catch (Exception ex) {
      throw new EJBException("ejbFindInRange: " +
      ex.getMessage());
      }
      if (result.isEmpty()) {
      throw new ObjectNotFoundException("No rows found.");
      }
      else {
      return result;
      }
      }


      /*********************************************************************************/

      public void ejbActivate()
      {

      System.out.println("ejbActivate call ...");

      id = (String)context.getPrimaryKey();
      }


      /*********************************************************************************/

      public void ejbLoad()
      {

      System.out.println("ejbLoad call ...");

      try {
      loadRow();
      } catch (Exception ex) {
      throw new EJBException("ejbLoad: " +
      ex.getMessage());
      }
      }


      /*********************************************************************************/

      public void ejbPassivate()
      {
      id = null;
      }

      /*********************************************************************************/

      public void ejbRemove()
      {

      System.out.println("ebjRemove call ...");
      try {
      deleteRow(id);
      } catch (Exception ex) {
      throw new EJBException("ejbRemove: " +
      ex.getMessage());
      }
      }

      /********************************************************************************/

      public void ejbStore()
      {

      System.out.println("ejbStore call ...");

      try {
      storeRow();
      } catch (Exception ex) {
      throw new EJBException("ejbLoad: " +
      ex.getMessage());
      }
      }

      /********************************************************************************/

      public void setEntityContext(EntityContext context)
      {

      this.context = context;

      System.out.println("setEntityContext call....");

      try {
      makeConnection();

      System.out.println("Came back from makeConnction call ...");

      } catch (Exception ex) {
      throw new EJBException("Unable to connect to database " +
      ex.getMessage());
      }
      }

      /********************************************************************************/

      public void unsetEntityContext()
      {

      System.out.println("unsetEntityContext call ...");

      try {
      con.close();
      } catch (SQLException ex) {
      throw new EJBException("unsetEntityContext: " + ex.getMessage());
      }
      }

      /*********************** Database Routines *************************/

      private void makeConnection() throws NamingException, SQLException
      {

      System.out.println("makeConnection call...");

      try
      {
      InitialContext ic = new InitialContext();
      DataSource ds = (DataSource) ic.lookup("java:/MySqlDS");
      con = ds.getConnection();
      }
      catch (Exception ex)
      {
      System.out.println("Something went wrong within makeConnction call .... " + ex);
      }

      }

      /******************************************************************************/

      private void insertRow (String id, String firstName, String lastName,
      double balance) throws SQLException {

      System.out.println("insertRow call...");

      String insertStatement =
      "insert into account values ( ? , ? , ? , ? )";
      PreparedStatement prepStmt =
      con.prepareStatement(insertStatement);

      prepStmt.setString(1, id);
      prepStmt.setString(2, firstName);
      prepStmt.setString(3, lastName);
      prepStmt.setDouble(4, balance);

      prepStmt.executeUpdate();
      prepStmt.close();
      }

      /*******************************************************************************/

      private void deleteRow(String id) throws SQLException {

      System.out.println("deleteRow call ...");

      String deleteStatement =
      "delete from account where id = ? ";
      PreparedStatement prepStmt =
      con.prepareStatement(deleteStatement);

      prepStmt.setString(1, id);
      prepStmt.executeUpdate();
      prepStmt.close();
      }

      /*******************************************************************************/

      private boolean selectByPrimaryKey(String primaryKey)
      throws SQLException {

      System.out.println("selectByPrimaryKey call...");

      String selectStatement =
      "select id " +
      "from account where id = ? ";
      PreparedStatement prepStmt =
      con.prepareStatement(selectStatement);
      prepStmt.setString(1, primaryKey);

      ResultSet rs = prepStmt.executeQuery();
      boolean result = rs.next();
      prepStmt.close();
      return result;
      }

      /*******************************************************************************/

      private Collection selectByLastName(String lastName)
      throws SQLException {

      System.out.println("selectByLastName call...");

      String selectStatement =
      "select id " +
      "from account where lastname = ? ";
      PreparedStatement prepStmt =
      con.prepareStatement(selectStatement);

      prepStmt.setString(1, lastName);
      ResultSet rs = prepStmt.executeQuery();
      ArrayList a = new ArrayList();

      while (rs.next()) {
      String id = rs.getString(1);
      a.add(id);
      }

      prepStmt.close();
      return a;
      }

      /*******************************************************************************/
      private Collection selectInRange(double low, double high)
      throws SQLException {


      System.out.println("selectInRange call ....");

      String selectStatement =
      "select id from account " +
      "where balance between ? and ?";
      PreparedStatement prepStmt =
      con.prepareStatement(selectStatement);

      prepStmt.setDouble(1, low);
      prepStmt.setDouble(2, high);
      ResultSet rs = prepStmt.executeQuery();
      ArrayList a = new ArrayList();

      while (rs.next()) {
      String id = rs.getString(1);
      a.add(id);
      }

      prepStmt.close();
      return a;
      }

      /*******************************************************************************/

      private void loadRow() throws SQLException {


      System.out.println("loadRow call ....");

      String selectStatement =
      "select firstname, lastname, balance " +
      "from account where id = ? ";
      PreparedStatement prepStmt =
      con.prepareStatement(selectStatement);

      prepStmt.setString(1, this.id);

      ResultSet rs = prepStmt.executeQuery();

      if (rs.next()) {
      this.firstName = rs.getString(1);
      this.lastName = rs.getString(2);
      this.balance = rs.getDouble(3);
      prepStmt.close();
      }
      else {
      prepStmt.close();
      throw new NoSuchEntityException("Row for id " + id +
      " not found in database.");
      }
      }

      /*******************************************************************************/

      private void storeRow() throws SQLException {

      System.out.println("storeRow call ...");


      String updateStatement =
      "update account set firstname = ? ," +
      "lastname = ? , balance = ? " +
      "where id = ?";
      PreparedStatement prepStmt =
      con.prepareStatement(updateStatement);

      prepStmt.setString(1, firstName);
      prepStmt.setString(2, lastName);
      prepStmt.setDouble(3, balance);
      prepStmt.setString(4, id);
      int rowCount = prepStmt.executeUpdate();
      prepStmt.close();

      if (rowCount == 0) {
      throw new EJBException("Storing row for id " + id + " failed.");
      }
      }

      }



      2. ejb-jar.xml


      <?xml version = '1.0' encoding = 'windows-1252'?>
      <!DOCTYPE ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN" "http://java.sun.com/dtd/ejb-jar_2_0.dtd">
      <ejb-jar>
      <enterprise-beans>

      Entity Bean ( BMP )
      <display-name>Account</display-name>
      <ejb-name>Account</ejb-name>
      com.ps.AccountHome
      com.ps.Account
      <ejb-class>com.ps.impl.AccountBean</ejb-class>
      <persistence-type>Bean</persistence-type>
      <prim-key-class>java.lang.String</prim-key-class>
      False

      </enterprise-beans>
      </ejb-jar>



      3. web.xml


      <?xml version = '1.0' encoding = 'windows-1252'?>
      <!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">
      <web-app>
      Empty web.xml file for Web Application

      <servlet-name>AccountServlet</servlet-name>
      <servlet-class>com.ps.AccountServlet</servlet-class>

      <servlet-mapping>
      <servlet-name>AccountServlet</servlet-name>
      <url-pattern>/accountservlet</url-pattern>
      </servlet-mapping>
      <session-config>
      <session-timeout>30</session-timeout>
      </session-config>
      <mime-mapping>
      html
      <mime-type>text/html</mime-type>
      </mime-mapping>
      <mime-mapping>
      txt
      <mime-type>text/plain</mime-type>
      </mime-mapping>
      <welcome-file-list>
      <welcome-file>index.jsp</welcome-file>
      <welcome-file>index.html</welcome-file>
      </welcome-file-list>
      </web-app>


      4. jboss.xml (i am not sure this is righ maybe this is the problem)

      <?xml version = '1.0' encoding = 'windows-1252'?>
      <!DOCTYPE jboss PUBLIC "-//JBoss//DTD JBOSS//EN" "http://www.jboss.org/j2ee/dtd/jboss.dtd">

      <resource-managers>
      <resource-manager res-class="">
      <res-name>MySqlDS</res-name>
      <res-jndi-name>java:/MySqlDS</res-jndi-name>
      </resource-manager>
      </resource-managers>


      5. mysql-service.xml

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

      <!-- ===================================================================== -->
      <!-- -->
      <!-- JBoss Server Configuration -->
      <!-- -->
      <!-- ===================================================================== -->



      <!-- ==================================================================== -->
      <!-- New ConnectionManager setup for mysql using 2.0.11 driver -->
      <!-- Build jmx-api (build/build.sh all) and view for config documentation -->
      <!-- ==================================================================== -->




      <!-- Include a login module configuration named MySqlDbRealm.
      Update your login-conf.xml, here is an example for a
      ConfiguredIdentityLoginModule:

      <application-policy name = "MySqlDbRealm">

      <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule" flag = "required">
      <module-option name = "principal">yourprincipal</module-option>
      <module-option name = "userName">yourusername</module-option>
      <module-option name = "password">yourpassword</module-option>
      <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=MySqlDS</module-option>
      </login-module>

      </application-policy>

      NOTE: the application-policy name attribute must match SecurityDomainJndiName, and the
      module-option name = "managedConnectionFactoryName"
      must match the object name of the ConnectionManager you are configuring here.
      -->
      <!--uncomment out this line if you are using the MySqlDbRealm above
      MySqlDbRealm
      -->

      <depends optional-attribute-name="ManagedConnectionFactoryName">
      <!--embedded mbean-->


      MySqlDS



      <config-property name="ConnectionURL" type="java.lang.String">jdbc:mysql://localhost:3306/pac</config-property>
      <config-property name="DriverClass" type="java.lang.String">org.gjt.mm.mysql.Driver</config-property>
      <!--set these only if you want only default logins, not through JAAS -->
      <config-property name="UserName" type="java.lang.String">t100</config-property>
      <config-property name="Password" type="java.lang.String">t200</config-property>




      <!--Below here are advanced properties -->
      <!--hack-->
      <depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper




      <depends optional-attribute-name="ManagedConnectionPool">
      <!--embedded mbean-->


      0
      50
      5000
      15
      <!--criteria indicates if Subject (from security domain) or app supplied
      parameters (such as from getConnection(user, pw)) are used to distinguish
      connections in the pool. Choices are
      ByContainerAndApplication (use both),
      ByContainer (use Subject),
      ByApplication (use app supplied params only),
      ByNothing (all connections are equivalent, usually if adapter supports
      reauthentication)-->
      ByContainer



      <depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager

      <depends optional-attribute-name="JaasSecurityManagerService">jboss.security:name=JaasSecurityManager

      java:/TransactionManager
      <!--make the rar deploy! hack till better deployment-->
      jboss.jca:service=RARDeployer





        • 1. Re: Mysql problem- can not connect to the mysql
          David Jencks Master

          It's hard to know what to post, but this is way too much. After locating the error.... MySqlDS not bound...

          Look in server.log for the section where mysql-service.xml is being deployed, and look for some problem. You may need to set the log level to debug in log4j-conf.xml. Usually how to fix the problem is pretty clear once you find the log entries, but if not post the section of the log starting from the first mention of mysql-service.xml to the end of deployment of mysql-service.xml