Datasources

How to define a Datasource

To execute queries you need a datasource definition, a datasource definition point this a specific local or remote datasource, there are different ways to do so.

Create a Datasource in the Administrator

The most common way is to define a Datasource is in the Lucee Server or Web Administrator. The only difference between the Web and Server Administrator is, that datasources defined in the Server Administrator are visible to all web contexts and datasources defined in the Web Administrator only to the current web context. In your Administrator go to the Page "Services/Datasource", in the section "create new Datasource" choose a name for your datasource and the type of your Datasource, for example "MySQL".

create datasource

On the following page you can define settings to connect to your datasource, the look and feel of this page depends on the datasource type used. After saving this page you get back to the overview page and you will get a feedback if Lucee was able to connect to your datasource or not.

Create a Datasource in the Application.cfc

You cannot only define a datasource in the Lucee Administrator, you can also do this in the Create an Application.cfc, the easiest way to do so, is to create first a datasource in the Administrator (see above) and then go to the detail view of this datasource by clicking the "edit button"

select datasource

At the bottom of the detail page you find a box that will look like this

datasource application definition

You can simply copy the code inside the box to your Create an Application.cfc body, Lucee will pick then this definition. After that you can delete the datasource you have defined in the Administrator.

Advanced

Like you can see, the code for a datasource definition has this pattern (class, connectionString, username and password)

this.datasources["myds"] = {
	  class: 'org.gjt.mm.mysql.Driver'
	, connectionString: 'jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true'
	, username: 'root'
	, password: "encrypted:5120611ea34c6123fd85120a0c27ab23fd81ea34cb854"
};

but alternatively you can also use this pattern

this.datasources["myds"] = {
         // required
        type: 'mysql'
        , host: 'localhost'
        , database: 'test'
        , port: 3306
        , username: 'root'
        , password: "encrypted:5120611ea34c6123fd85120a0c27ab23fd81ea34cb854"

    <span class="c">// optional</span>
    <span class="p">,</span> <span class="nv">connectionLimit</span><span class="p">:</span> <span class="o">-</span><span class="m">1</span> <span class="c">// how many connections are allowed maximal (-1 == infiniti)</span>
    <span class="p">,</span> <span class="nv">connectionTimeout</span><span class="p">:</span><span class="m">1</span> <span class="c">// connection timeout in minutes (0 == connection is released after usage)</span>
    <span class="p">,</span> <span class="nv">blob</span><span class="p">:</span> <span class="nv">false</span> <span class="c">// enable blob</span>
    <span class="p">,</span> <span class="nv">clob</span><span class="p">:</span> <span class="nv">false</span> <span class="c">// enable clob</span>
    <span class="p">,</span> <span class="nv">storage</span><span class="p">:</span> <span class="nv">false</span> <span class="c">// allow to use this datasource as a session/application storage</span>
    <span class="p">,</span> <span class="nv">timezone</span><span class="p">:</span> <span class="s1">&#39;CET&#39;</span> <span class="c">// if set Lucee change the environment timezone</span>
    <span class="p">,</span> <span class="nv">custom</span><span class="p">:</span> <span class="p">{</span><span class="nv">useUnicode</span><span class="p">:</span><span class="nv">true</span><span class="p">,</span><span class="nv">characterEncoding</span><span class="p">:</span><span class="s1">&#39;UTF-8&#39;</span><span class="p">}</span> <span class="c">// a struct that contains type specific settings</span>



};

Default Datasource

With the Create an Application.cfc you can also define a default datasource that is used if not "datasource" attribute is defined with the tag cfquery, cfstoredproc, cfinsert, cfupdate, ..., simply do the following

this.defaultdatasource = "myds"; // "this.datasource" is supported as well

In that case the datasource "myds" is used, if there is no datasource defined. Instead of defining a datasource name, you can also define the datasource directly as follows

this.datasource = {
	  class: 'org.gjt.mm.mysql.Driver'
	, connectionString: 'jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=true'
	, username: 'root'
	, password: "encrypted:5120611ea34c6123fd85120a0c27ab23fd81ea34cb854"
};