OK, so first we have to create the new database, which is very easy as explained by Ben Forta in his Blog. The only thing it is actually easier because the final release does have the create checkbox so you do not have to add create=true to the advanced settings string.(Pictured Below)
Once you complete the steps above, your database is ready to use. So now we create some tables and views. First, I wanted to check the existence of tables and views so I can drop items if they exists. This is easy now with the new <cfdbinfo> tag, which lets you retrieve information about a data source, including details about the database, tables, queries, procedures, foreign keys, indexes, and version information about the database, driver, and JDBC. (info on cfdocs).
I then use the results to check if the tables I want to create currently exists, good practice as you are building your DB, because if you try to create a table that already exists the process will break. So therefore, I save the results to a list that I later use to check for existence (I wanted to just use ListFind rather then write a query of a query everytime I wanted to check). After that, I just used SQL code to create my tables and views. To find out more about Apache Derby go to Apache Derby Docs, I used the manuals to see how the CREATE STATEMENTS had to be formed.
I believe the code below explains it all. Now there is no more need for Access Databases for small projects, Woo Hoo!!!!! I included an image of the output after the code ....
<!--- Use cfdbinfo to get the tables of the database --->
<cfdbinfo type="tables" datasource="myDerbyDB" name="dbdata">
<!--- Check if USERS table exists --->
<cfquery name="tableExists" dbtype="query">SELECT * FROM dbdata WHERE TABLE_TYPE='TABLE'</cfquery>
<cfquery name="viewExists" dbtype="query">SELECT * FROM dbdata WHERE TABLE_TYPE='VIEW'</cfquery>
<cfscript>
// Save results into list values to check for existance
tables=valueList(tableExists.TABLE_NAME);
views=valueList(viewExists.TABLE_NAME);
</cfscript>
<!--- Drop Views First Due to Dependency Check --->
<cfif listFindNoCase(views,'VIEW_CARS_RELATED')>
<cfquery name="dropView1" datasource="myDerbyDB">DROP VIEW VIEW_CARS_RELATED</cfquery>
</cfif>
<cfif listFindNoCase(views,'VIEW_CARS_ALL')>
<cfquery name="dropView1" datasource="myDerbyDB">DROP VIEW VIEW_CARS_ALL</cfquery>
</cfif>
<!--- Drop Tables if exists --->
<cfif listFindNoCase(tables,'CARS')>
<cfquery name="dropTables" datasource="myDerbyDB">DROP TABLE CARS</cfquery>
</cfif>
<cfif listFindNoCase(tables,'COMPANY')>
<cfquery name="dropTables" datasource="myDerbyDB">DROP TABLE COMPANY</cfquery>
</cfif>
<!--- Create Tables --->
<cfquery name="createTables1" datasource="myDerbyDB">
CREATE TABLE CARS
(
CARID INT NOT NULL GENERATED ALWAYS AS IDENTITY( START WITH 1,INCREMENT BY 1),
COMPANYID INT NOT NULL,
CARNAME VARCHAR(100),
PRIMARY KEY (CARID)
)
</cfquery>
<cfquery name="createTables2" datasource="myDerbyDB">
CREATE TABLE COMPANY
(
COMPANYID INT NOT NULL GENERATED ALWAYS AS IDENTITY( START WITH 1,INCREMENT BY 1),
COMPANYNAME VARCHAR(100),
PRIMARY KEY (COMPANYID)
)
</cfquery>
<!--- Create Views --->
<cfquery name="createView1" datasource="myDerbyDB">
CREATE VIEW VIEW_CARS_RELATED
AS
SELECT CARS.CARID,CARS.CARNAME,COMPANY.COMPANYNAME
FROM CARS
INNER JOIN COMPANY ON COMPANY.COMPANYID=CARS.COMPANYID
</cfquery>
<cfquery name="createView2" datasource="myDerbyDB">
CREATE VIEW VIEW_CARS_ALL
AS
SELECT CARS.CARID,CARS.CARNAME,COMPANY.COMPANYNAME
FROM CARS
LEFT OUTER JOIN COMPANY ON COMPANY.COMPANYID=CARS.COMPANYID
</cfquery>
<!--- Loop to create the first 10 cars --->
<cfloop from="1" to="10" index="i">
<cfquery name="insertCars" datasource="myDerbyDB">
INSERT INTO CARS (COMPANYID,CARNAME) VALUES (#i#,'CAR_#i#')
</cfquery>
</cfloop>
<!--- Loop to create the first 10 companies --->
<cfloop from="1" to="10" index="i">
<cfquery name="insertCompanies" datasource="myDerbyDB">
INSERT INTO COMPANY (COMPANYNAME) VALUES ('COMPANY_#i#')
</cfquery>
</cfloop>
<!--- Add one more record without relation to a company --->
<cfquery name="insertCar" datasource="myDerbyDB">
INSERT INTO CARS (COMPANYID,CARNAME) VALUES (0,'CAR_11')
</cfquery>
<!--- Get Cars with Companies --->
<cfquery name="getCars1" datasource="myDerbyDB">SELECT * FROM VIEW_CARS_RELATED</cfquery>
<cfquery name="getCars2" datasource="myDerbyDB">SELECT * FROM VIEW_CARS_ALL</cfquery>
<!--- Dump the queries --->
<cfdump var="#getCars1#">
<cfdump var="#getCars2#">
