Pete Freitag Pete Freitag

Creating a Derby Datasource with ColdFusion Admin API

Updated on July 11, 2024
By Pete Freitag
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:



derby db adminapi coldfusion sql

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:

Fixinator

The Fixinator Code Security Scanner for ColdFusion & CFML is an easy to use security tool that every CF developer can use. It can also easily integrate into CI for automatic scanning on every commit.


Try Fixinator

CFBreak
The weekly newsletter for the CFML Community