Creating a Derby Datasource with ColdFusion Admin API

August 05, 2009

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(
    <cfoutput>Created Datasource Named: #XmlFormat(application.ds)#</cfoutput>
    <cfset verify = datasource.verifyDsn(application.ds, true)>
    <cfoutput>DataSource Verified: #XmlFormat(verify)#</cfoutput>
    <p>Invalid Username or Password.</p>

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#">
    username VARCHAR(25),
    password VARCHAR(25),
    date_created date DEFAULT CURRENT_DATE)

Here are some more resources on Apache Derby:

Related Entries

1 person found this page useful, what do you think?


Thank you, Pete! Very usefull post!
Apache Derby is best project for open source relational database for implemented entirely in Java..

Post a Comment


Spell Checker by Foundeo

Recent Entries


did you hack my cf?