Sunday 9 May 2021

Setting up a MariaDB JDBC datasource in ColdFusion 2021 in Application.cfc

G'day:

This is how I needed to set my datasource in Application.cfc to get ColdFusion 2021 to connect to MariaDB. I'm writing this because I could not - for the life of me - find this information in any one place in the docs. Nor could I find it anywhere else via Google. I have pieced this together from various sources (including Lucee docs, which were more helpful than the Adobe ColdFusion documentation), and want to put it in one place for my own future reference, or should anyone else need to know how to set up a MariaDB datasouce in ColdFusion using their JDBC driver. The params should also work for any other JDBC datasource.

Apologies for that fairly SEO-heavy opening paragraph.

OK, so it's this:

component {

    this.datasources["test"] = {
        host = "localhost",
        port = 3306,
        driver = "mariadb",
        class = "org.mariadb.jdbc.Driver",
        url = "jdbc:mariadb://localhost:3306/database_name?user=user_name&password=user_password",
        database = "database_name",
        username = "user_name",
        password = "user_password", 
        custom = {
            useUnicode = true,
            characterEncoding = "UTF-8"
        }
    }
    this.datasource = "test"
}

After checking up on what Sean says in the comment below, I was able to pare this back to:

this.datasources["test"] = {
    class = "org.mariadb.jdbc.Driver",
    url = "jdbc:mariadb://localhost:3306/cfmlindocker?useUnicode=true&characterEncoding=UTF-8",
    username = "cfmlindocker",
    password = server.system.environment.MYSQL_PASSWORD
}

It failed if I did not have the username and password keys; but I did not need them in the URL. And I can pass the custom stuff in the URL, rather than specifying them separately.

Cheers for encouraging me to investigate further, Sean!

I got the MariaDB jar file from Download and Install MariaDB Connector/J. I also found the class name for the URL in there. That jar should be dropped into [coldfusion directory]/]WEB-INF/lib

The driver value seems to just be a label.

Note that on Lucee they seem to use connectionString rather than url. I dunno if url also works, and CBA checking right now.

ColdFusion might give an unhelpful error if it does't like the datasource settings. Instead of saying "I don't like those datasource settings", or "they don't work", it says "Datasource test could not be found". It was easy to find, pal: it's right there in the Application.cfc file. I just had a param wrong. I especially saw this when I had connectionString rather then url.

I would normally store the password in the environment, not hard-code into a source-controlled file, eg: server.system.environment.TEST_DSN_PASSWORD (both ColdFusion and Lucee put any environment variables in server.system.environment. Or you could use an environment-specific non-source controlled config file or something. Just don't put it directly in Application.cfc

Here's some more SEO for you: so that was the Application.cfc configuration settings for ColdFusion 2021 to connect to a MariaDB database using their JDBC connection.

Righto.

--
Adam

at