Create datasource without lucee admin

dev support
1

How can we create a datasource from our Lucee/ColdFusion code without using the Lucee administration panel?

2

Sure.
In your Application.cfc add this:

this.datasources['mydatasource'] = { 
    database = "mydb", 
    host = "mysqldb", 
    port = "1433", 
    class = 'class.for.YourDriver' , 
    username = "dbuser", 
    password = "dbpwd"
};

than, you can write:

<cfquery datasource="mydatasource">

2 Likes
3

I recently had a need to do this dynamically as we are swapping to using containers and it is possible to add new customers (thus datasources) with in the application.

to do this we interrogate the database on container startup via the entrypoint shell script and retrieve a list of customer databases.

then I loop through the list appending to a text file the JSON required for each datasource (the file is JUST the datasource config only).
then we import the JSON file using CFCONFIG (we’re using the Ortus Solutions Commandbox Docker image)
For us we also need to create ORM CFCs for the customer database/tables and we do that in the same BASH script loop.

And to keep all the containers in-sync - we run a process in onRequestStart() to see if anything new has been added by a different container, using a REDIS cache as the keeper of the “known current state”.

The only downside is that if a new customer is added an ormReload() is required and that is single threaded and blocking… though we had the same “problem” prior to using containers… the downside to it happening in a container - is that a container is less well specced than a single server - so it does “pause” our app for a little while on that first request. We’re not adding new customers all that often… so it really isn’t a “real” problem… at least not one that anyone has ever complained about.

4

we can achieve adding datasource using code by the help of these methods,

configImport:

configImport( data=getConfig, type="server", password=request.SERVERADMINPASSWORD );

Administrator file :

   		adm = new Administrator('server', SERVERADMINPASSWORD);
        adm.updateDatasource(
            name: 'datasource1',
            newname: 'datasource2',
            type: 'MSSQL',
            host: '#msSQL.SERVER#',
            database: #msSQL.DATABASE#,
            port: #msSQL.PORT#,
            username: #msSQL.USERNAME#,
            password: #msSQL.PASSWORD#,
            connectionLimit: 100,
            connectionTimeout: 12,
            storage: false,
            blob: true,
            clob: true,
            verify: true
	    );

You can also set this in the Application.cfc as follows:

    this.datasources["MyDSN"] = {
    class: 'com.mysql.jdbc.Driver'
    , bundleName: 'com.mysql.jdbc'
    , bundleVersion: '5.1.40'
    , connectionString: 'jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true'
    , username: 'theUsername'
    , password: "encrypted:XXXX"

    // optional settings
    , blob:true // default: false
    , clob:true // default: false
    , connectionLimit:100 // default:-1
    };
3 Likes