Pete Freitag Pete Freitag

Creating a Derby Datasource with ColdFusion Admin API

coldfusiondatabases

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:


Like this? Follow me ↯

Creating a Derby Datasource with ColdFusion Admin API was first published on August 05, 2009.

If you like reading about derby, db, adminapi, coldfusion, or sql then you might also like:

FuseGuard Web App Firewall for ColdFusion

The FuseGuard Web Application Firewall for ColdFusion & CFML is a high performance, customizable engine that blocks various attacks against your ColdFusion applications.

Comments

Thank you, Pete! Very usefull post!
by inj on 08/05/2009 at 2:50:21 PM UTC
Apache Derby is best project for open source relational database for implemented entirely in Java..
by Sanket Panchal on 10/14/2009 at 6:43:24 AM UTC