Accessing Derby from Creator

Yes, I'm a NetBeans guy, but since Creator 2's been released, I can't stop playing with it. Since I've also been playing with Derby a bit lately, and Derby isn't one of the preconfigured database server types that ships with Creator, I thought a short blog entry might be in order.

Getting Started

1. Download the latest official Derby release (10.1.2.1 at the time of this writing) and extract the archive.
2. Set the DERBY_INSTALL environment variable to point to your Derby installation location. For me this is D:\db-derby-10.1.2.1-bin.

Start the Derby Server

1. Open a command prompt and switch to the $DERBY_INSTALL/frameworks/NetworkServer/bin directory.
2. Execute the setNetworkServerCP script, which sets the necessary Derby jars on the classpath.
3. Execute the startNetworkServer script to start Derby. You should then see something like the following:
   
   

Add Derby Database Type to Creator

1. Start Creator and switch to the Server's window (Ctrl+Alt+S).
2. Right-click the Data Sources node and choose Edit Database Server Types.
3. Click New and browse to the $DERBY_INSTALL/lib directory and select derbyclient.jar.
4. Set the Display Name to Derby.
5. Click Suggest to retrieve the Driver Class Name (org.apache.derby.jdbc.ClientDriver).
6. Enter the following for the URL Template: jdbc:derby://#HOSTNAME:1527/#DATABASE. The completed dialog should look as follows.
   
   

Add a Derby Data Source

1. Right-click the Data Sources node and choose Add Data Source. Set the following:
   • Data Source Name: derbyDB.
   • Server Type: Derby.
   • Database Name: derbyDB
   • User ID: APP
   • Password: APP
   • Database URL: jdbc:derby://localhost:1527/derbyDB;create=true
   • Validation Table: SYS.SYSCHECKS
     
   The completed dialog should look as follows:
   
   
   
2. Click Test Connection:
   
   
   
   
3. Then click OK and Add to add the derbyDB data source.
4. By default, we see all the database schemas, including the sytem schemas. Right-click derbyDB and choose Modify Data Source.
5. Switch to the Schemas tab and click Get Schemas.
6. Click Clear and then select just the APP schema.
   
   
   
   
7. Click Modify to close the Modify Data Source dialog.

Execute a SQL Script

1. Customers.sql will create 3 tables and populate them with data - save it somewhere on your hard drive. Right-click the derbyDB data source and select View Data. Click the folder icon to Import a File containing SQL commands and open the Customers.sql file.
2. Click Run Query. You'll see output like the following:
   
   
   
   
3. Now right-click the derbyDB data source and select Refresh.
4. Expand the Tables node to see the 3 tables that were created.
   
   
   
   
5. Right-click the CUSTOMER_TBL and choose View Data:
   
   
   

Use the Data Source in an Application

1. Create a new JSF Web Application project named DerbyData.
2. Drag a Table component onto Page1.
3. Switch to the Servers window (Ctrl+Alt+S) and drag the CUSTOMER_TBL from the derbyDB data source onto the table component on Page1.
4. Run the project (Ctrl+F5).