Creating a Derby Datasource with ColdFusion Admin API

I am working on some example code for some CFUG managers who are demoing our ColdFusion WAF product at their groups. I wanted the demo to be very easy to setup, so I decided to use Apache Derby for the database, since it is embedded with CF8.

My install script will create a datasource, create the DB, tables, and populate it with example data.

Here's how you can create a ColdFusion Derby Datasource on the fly:

<cfparam name="form.admin_user" default="admin">
<cfparam name="form.admin_password" default="">
<cfset loginSuccessful = CreateObject("component","cfide.adminapi.administrator").login(form.admin_password, form.admin_user)>
<cfif loginSuccessful>
    <cfset datasource = CreateObject("component", "cfide.adminapi.datasource")>
    <cfset datasource.setDerbyEmbedded(
				name=application.ds,
				database="/path/to/store/database/",
				isnewdb=true
					)>
    <cfoutput>Created Datasource Named: #XmlFormat(application.ds)#</cfoutput>
    <cfset verify = datasource.verifyDsn(application.ds, true)>
    <cfoutput>DataSource Verified: #XmlFormat(verify)#</cfoutput>
<cfelse>
    <p>Invalid Username or Password.</p>
</cfif>

There are only a two required arguments of the setDerbyEmbedded function in the datasource Admin API, the name and the database. The name is simply the name of the datasource to create, and the database is going to be a file system path to the location where the database will be stored.

We are also passing in an optional argument isnewdb which creates a new database at the path specified. There are several other optional arguments that you should specify in most cases (such as the select, update, delete, insert permissions). You can see all the arguments by browsing to: /CFIDE/componentutils/cfcexplorer.cfc?METHOD=getcfcinhtml&PATH=/cfide/adminapi/datasource.cfc&NAME=CFIDE.adminapi.datasource#method_setDerbyEmbedded

The next step is to create a table in the database, we can do that with a CREATE TABLE SQL statement:

<cfquery datasource="#application.ds#">
  CREATE TABLE users
    (user_id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
    username VARCHAR(25),
    password VARCHAR(25),
    date_created date DEFAULT CURRENT_DATE)
</cfquery>

Here are some more resources on Apache Derby:

• CREATE TABLE Docs
• Derby's Built in Datatypes
• DerbyCFC - Sam Farmer's CFC for creating derby datasources (formerly on riaforge as derbycfc)
• Charlie Arehart's Derby Resource List