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
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:
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:
- Mastering CFQUERYPARAM - July 24, 2008
- Getting ColdFusion SQL Statements from SQL Server Trace - June 16, 2008
- INFORMATION_SCHEMA Support in MySQL, PostgreSQL - February 18, 2008
- Cheat Sheet Roundup - Over 30 Cheatsheets for developers - September 1, 2005
- Performance of database tag schemas - June 20, 2005
- Why is my cron.daily script not running?
- Announcing FuseGuard Version 3
- CFSummit 2017
- Java Unlimited Strength Crypto Policy for Java 9 or 1.8.0_151
- Java 9 Security Enhancements
- Upcoming CFML Conferences in April 2017
- CFSummit 2016 Slides
- Securing Legacy CFML - dev.Objective() 2016 Slides