JDBC can't connect to mysql database on openshift

Question

I managed to set up MySQL database on OpenShift with phpMyAdmin and all. I was told the host name and port my for my database are $OPENSHIFT_MYSQL_DB_HOST and $OPENSHIFT_MYSQL_DB_PORT respectively, which I put in my context.xml file like this:

<context-param>
        <param-name>driver</param-name>
        <param-value>com.mysql.jdbc.Driver</param-value>
    </context-param>
    <context-param>
        <param-name>url</param-name>
        <param-value>jdbc:mysql://$OPENSHIFT_MYSQL_DB_HOST:$OPENSHIFT_MYSQL_DB_PORT/burgerjoint</param-value>
    </context-param>
    <context-param>
        <param-name>user</param-name>
        <param-value>admin******</param-value>
    </context-param>
    <context-param>
        <param-name>password</param-name>
        <param-value>*********</param-value>
    </context-param>

The code to set up the connection is:

public void contextInitialized(ServletContextEvent event) {
    // Connect
    String driver = event.getServletContext().getInitParameter(PARAM_DRIVER);
    String url = event.getServletContext().getInitParameter(PARAM_URL);
    String username = event.getServletContext().getInitParameter(PARAM_USERNAME);
    String password = event.getServletContext()
        .getInitParameter(PARAM_PASSWORD);

    try {
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, username,
            password);

        event.getServletContext().setAttribute(ATTR_CONNECTION, connection);


    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    }

but the problem is that the connection is null on the server and I don't understand why. Did I do something wrong? The code works when I try it on localhost. And as far as I can tell, I have all necessary libraries:

Thanks for the help :)

Update

I've modified the Connection code as follows:

{
    if (connection != null) return connection;

    try
    {
        Properties dbProperties = new Properties();
        InputStream input = DatabaseUtil.class.getClassLoader().getResourceAsStream(DB_PROPERTIES_FILE);
        dbProperties.load(input);
        String url = "";
        if (appIsDeployed)
        {
        String host = System.getenv("$OPENSHIFT_MYSQL_DB_HOST");
        String port = System.getenv("$OPENSHIFT_MYSQL_DB_PORT");
        String name = "burgerjoint";
        url = "jdbc:mysql://" + host + ":" + port + "/" + name;
        } else
        {
        url = dbProperties.getProperty(PARAM_URL);
        }
        String driver = dbProperties.getProperty(PARAM_DRIVER);
        String username = dbProperties.getProperty(PARAM_USERNAME);
        String password = dbProperties.getProperty(PARAM_PASSWORD);

        Class.forName(driver);
        connection = DriverManager.getConnection(url, username, password);

but it still gives null connection. The values of System.getenv("$OPENSHIFT_MYSQL_DB_HOST") and System.getenv("$OPENSHIFT_MYSQL_DB_PORT") are null.

Solution 1

This is incorrect:

jdbc:mysql://$OPENSHIFT_MYSQL_DB_HOST:$OPENSHIFT_MYSQL_DB_PORT/burgerjoint

That dollar sign suggests that you think a proper host and database name will be substituted, but that's not the case.

Code the host and database name to see that it works, then learn about .properties files to externalize it.

Solution 2

Remove the Dollar signs :

   String host = System.getenv("OPENSHIFT_MYSQL_DB_HOST");
   String port = System.getenv("OPENSHIFT_MYSQL_DB_PORT");

And it will work great (I tested it on my machine and on my OpenShift) .

Comments

[W.K.S]

Sorry, I'm a little confused: In the .properties file - can I write $OPEN_MYSQL_DB_HOST or should I use System.getenv($OPENSHIFT_MYSQL_DB_HOST) in the conneciton class to find the proper hostname?

[duffymo]

Keep it simple until it works: Just get the value that the variable is pointing to and enter that. Then worry about externalizing it. Somebody know the proper host and database name. I'd get it.

[W.K.S]

I did what you suggested but the values I get are null and the connection doesn't work.

[W.K.S]

I managed to find out the actual values by running this command on Termian: rhc app ssh -a {app-name}

[duffymo]

If you still get null connection with the correct host and database and port, then perhaps you haven't been GRANTed permission to access the server from that IP address. MySQL allows control at that level. Your only recourse in that case is to talk to the DBA owner and get the goods.

[W.K.S]

It works. I found out the values using that command, plugged them in to the code and it works fine now. Thanks :)

[AnonymousDev]

I am getting null value can you tell me the reason why I am getting null value

[AnonymousDev]

I am getting null value upon executing String host = System.getenv("OPENSHIFT_MYSQL_DB_HOST"); String port = System.getenv("OPENSHIFT_MYSQL_DB_PORT"); String username = System.getenv("OPENSHIFT_MYSQL_DB_USERNAME"); String password = System.getenv("OPENSHIFT_MYSQL_DB_PASSWORD");