how to configure connection to the database in clouds services

I have surfed the web for two days looking for a better way to configure my application that I am uploading to AppFog so that it successfully connects to the mysql database in the cloud.

I have created the mysql service and I did bind it to the application. I also created tables and put some data on them using the local console ( using af tunnel mydatabase) in linux

but my apps does not seem to find the database. My application uses JDBC ... I used the database username and password and databasename given to me from the console (those funny chars in the screen) and it did not work. so I put my own credentials, still, no success...

I tried using the url that points to my application at the port given to the on the console, but still... actually, I put in the details the showed on the console to connect to the database in the cloud after deploying ... but my app seems not to find my database and its tables....

I do not know what is wrong...

please help..

This is the code I used to try this connection:

    try {
            String connectionURL = "jdbc:mysql://http://someapp.aws.af.cm:10000 /OnlinePassword";   

            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection(connectionURL, USER, PASS);
            statement = conn.createStatement();
        }

Solution

Use appfog environment variable called VCAP_SERVICES to automatically detect your MySQL database credentials and connection info. You can take advantage of this to display all the info which will be in JSON format and use it to connect your app to MySQL database. You may try this.

First create a servlet and retrieve your database connection using a context attribute as follows:

public class DbInfoServlet extends HttpServlet 
{
  public void doGet(HttpServletRequest request,HttpServletResponse response) 
      throws ServletException, IOException 
  {
    request.setAttribute("jsonContent", java.lang.System.getenv("VCAP_SERVICES"));
    request.getRequestDispatcher("/index.jsp").view.forward(request, response);
  }
}

Then create a index.jsp file to display you DB connection info as follows:

<body>
  <p>
    <!-- Click here to display DB info -->
    <a href="/DbInfoServlet"></a>
    <!-- Display of your DB connection and credentials info is here. This will show in JSON document format
     -->
    <c:out value="${jsonContent}" />
  </p>
</body>

Use hostname(i.e. IP), port, name(i.e. database name), username, password parameters from JSON document content to modify your JDBC connection code as follows:

try {
      String connectionURL = "jdbc:mysql://hostname:3306/name";   
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection(connectionURL, username, password);
      statement = conn.createStatement();
    }

If the above doesn't work, simply ensure that you are using the latest version of MySQL-connector-java jar file.

Have fun!