Click here to close now.



Welcome!

You will be redirected in 30 seconds or close now.

ColdFusion Authors: Yakov Fain, Maureen O'Gara, Nancy Y. Nee, Tad Anderson, Daniel Kaar

Related Topics: ColdFusion

ColdFusion: Article

Working with the Apache Derby Database and ColdFusion

A basic introduction

Early releases of Cold Fusion (under Allaire) focused on the Windows market and it was common to include example Access databases. It was common for entry-level developers to take this example to heart and build systems on Access back-ends. One didn't have to install, set up, admin, or most importantly pay for anything. With the beta release of ColdFusion 8, Adobe is providing support for an array of open source databases. Now using PostgreSQL, MySQL, or Apache Derby (formerly IBM Cloudscape) has been made that simpler for CF developers since JDBC drivers for all of them are included. Any of these solutions can be used for the free distribution of databases on either Windows or *nix. The same can't be said of Access. Adobe has made moving to open source more convenient by including Derby in the base install of Scorpio. It will now likely become common for systems to be distributed with Derby databases because of their operating system independence, royalty-free distribution, small footprint, and ease of installation. This article attempts to help early adopters try Derby out with ColdFusion 8.

Terminology
Apache Derby is a pure Java database that supports almost all of the SQL-92 standard. It fits in about 2MB of memory, a nice efficient footprint for memory-constrained environments like mobile applications (or your desktop masquerading as a server). With ColdFusion it runs on J2SE 1.6 and the shipping driver is a native protocol, a pure Java (level 4) driver. There are two flavors of Derby: embedded and network. Simply put, embedded is a direct-connect (you have to run it in the same JVM as the code connecting to it), single-client database while network adds networking and multi-client support.

The Embedded Database
Once you install Scorpio and find your way to the Data Sources section of the Administrator, you'll see four Derby Embedded example databases (and zero Access databases). The example databases are installed in /ColdFusion8/db. (Note: File references in this article assume that ColdFusion is installed at the root of your filesystem. If you're on Windows, the default install path is C:\ColdFusion8 and the references below should be changed accordingly.)

One of these examples is the CFBookClub database. One can query this database in the same way as any other in ColdFusion, using the CFQuery, CFQueryParam, CFStoredProc, and CFProcParam tags. Create a new page called testDatabase.cfm in your webroot and code:

<cfquery name="test" datasource="cfbookclub" >
select books.title
from app.books books
inner join app.authors authors on books.authorid=authors.authorid
where authors.lastname =
<cfqueryparam cfsqltype="cf_sql_varchar" value="Miro" />
</cfquery>

<cfdump var="#test#" />

Run it and you should see CF8's new query dump format which gives you metadata about the query including execution time.

Creating Embedded Databases
To create a new embedded database, enter the directory where you want the database files kept. Do not create a new directory yourself and then browse to it. Select or enter the directory you'd like to contain the database subdirectory. Now write the subdirectory name you'd like after this. For example, Browse to "C:\ColdFusion8\db\" then enter "mytestdatabase" in the entry box. The Database Folder field now reads "C:\ColdFusion8\db\mytestdatabase". (Figure 1)

If you're creating a new Derby database, open the Advanced tab and enter 'create=true' in the Connection String field. If the directory specified already exists and doesn't contain a Derby database, datasource creation will fail writing an error 'XBM0J' to the Derby log file (in /ColdFusion8/logs). (Figure 2)

It's not great interface design to have the file browser come up to help you locate the target directory when the activity fails if the directory already exists and doesn't contain a Derby database. The only time this will work is when you point to a directory that does contain a Derby database. If you want to create a datasource for a Derby database someone has given you, you can point the file browser at the directory that contains it. You must still open the Advanced tab and enter 'create=true' in the Connection String field. (Figure 3)

The ColdFusion server doesn't seem to like to let go of Derby embedded databases once you connect to them. Restarting the CF service is very slow after creating and verifying the new embedded Derby database. This is a bit problematical since it currently appears that the Advanced Options "Maintain connections across client requests," "Timeout," and "Interval" don't work: connections are maintained indefinitely. Restarting the server is the only way to release a connection. You can't have multiple connections open to an embedded database, so you can't use other tools (such as Eclipse or ij, discussed below) to work with it while running your CF application. This is one of many good reasons to think about using Derby Network instead.

Creating Network Databases
Creating a Derby network database is just as easy. First start up the Derby server: "java -jar /ColdFusion8/lib/derbynet.jar -start." Now go to CF Administrator and create a new Derby client datasource. There's currently a bug in this dialog, so in the Advanced/Connection String box, you'll have to start with a semicolon ";create=true" to get it to work. Hit submit and your new database directory will be created in the same directory in which the server started up (/ColdFusion8/lib). There's nothing to say that you have to run the Derby server on the same box as CF, in fact if you want your app to scale across multiple servers it's better not to.

Installing a Derby Network Server
While CF8 ships with the latest Derby (10.2.2), Adobe's not going to notify you when new features or minor patches come out. You'll have to go to Apache to keep up-to-date. (http://db.apache.org/derby/derby_downloads.html.) Download the latest version and unpack it on the server of your choice. Set the JAVA_HOME environment variable to the location of your Java runtime. If you want to use the runtime shipping with the CF server (JavaSE 1.6) to try this out, run 'set JAVA_HOME = [coldfusion install dir]\runtime' (Windows) or 'export JAVA_HOME="[coldfusion install dir]/runtime"' (*nix). (If you're smart and installing this on another machine, you should have downloaded the latest JRE from Sun and installed it there already.) Navigate to the Derby bin subdirectory and run the setNetworkServerCP script, which adds Derby's info to your CLASSPATH environment variable then run the startNetworkServer script. Scripts are included for *nix and Windows. Your Derby server will now tell you that it's listening on port 1527 and is ready to accept connections. When you use the ColdFusion Datasources dialog to tell the Derby server to make new databases (with the create=true directive), Derby will create new subdirectories in its startup directory (/Derby/bin directory) by default. When you want to stop your new server - you guessed it, run stopNetworkServer.


More Stories By Chip Temm

Over the past decade, Chip Temm moved from North America to Europe and on to Africa where his company anthroLogik solutions provided analysis and development services organizations across seven timezones. He is currently back in Washington, DC where "remote development" means working from home and "wildlife" means raccoon.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


@ThingsExpo Stories
Most people haven’t heard the word, “gamification,” even though they probably, and perhaps unwittingly, participate in it every day. Gamification is “the process of adding games or game-like elements to something (as a task) so as to encourage participation.” Further, gamification is about bringing game mechanics – rules, constructs, processes, and methods – into the real world in an effort to engage people. In his session at @ThingsExpo, Robert Endo, owner and engagement manager of Intrepid D...
With an estimated 50 billion devices connected to the Internet by 2020, several industries will begin to expand their capabilities for retaining end point data at the edge to better utilize the range of data types and sheer volume of M2M data generated by the Internet of Things. In his session at @ThingsExpo, Don DeLoach, CEO and President of Infobright, will discuss the infrastructures businesses will need to implement to handle this explosion of data by providing specific use cases for filte...
SYS-CON Events announced today that Fusion, a leading provider of cloud services, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Fusion, a leading provider of integrated cloud solutions to small, medium and large businesses, is the industry's single source for the cloud. Fusion's advanced, proprietary cloud service platform enables the integration of leading edge solutions in the cloud, including clou...
SYS-CON Events announced today that Commvault, a global leader in enterprise data protection and information management, has been named “Bronze Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Commvault is a leading provider of data protection and information management...
SYS-CON Events announced today that Alert Logic, Inc., the leading provider of Security-as-a-Service solutions for the cloud, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Alert Logic, Inc., provides Security-as-a-Service for on-premises, cloud, and hybrid infrastructures, delivering deep security insight and continuous protection for customers at a lower cost than traditional security solutions. Ful...
SYS-CON Events announced today that VAI, a leading ERP software provider, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. VAI (Vormittag Associates, Inc.) is a leading independent mid-market ERP software developer renowned for its flexible solutions and ability to automate critical business functions for the distribution, manufacturing, specialty retail and service sectors. An IBM Premier Business Part...
The cloud promises new levels of agility and cost-savings for Big Data, data warehousing and analytics. But it’s challenging to understand all the options – from IaaS and PaaS to newer services like HaaS (Hadoop as a Service) and BDaaS (Big Data as a Service). In her session at @BigDataExpo at @ThingsExpo, Hannah Smalltree, a director at Cazena, will provide an educational overview of emerging “as-a-service” options for Big Data in the cloud. This is critical background for IT and data profes...
Fortunately, meaningful and tangible business cases for IoT are plentiful in a broad array of industries and vertical markets. These range from simple warranty cost reduction for capital intensive assets, to minimizing downtime for vital business tools, to creating feedback loops improving product design, to improving and enhancing enterprise customer experiences. All of these business cases, which will be briefly explored in this session, hinge on cost effectively extracting relevant data from ...
As enterprises work to take advantage of Big Data technologies, they frequently become distracted by product-level decisions. In most new Big Data builds this approach is completely counter-productive: it presupposes tools that may not be a fit for development teams, forces IT to take on the burden of evaluating and maintaining unfamiliar technology, and represents a major up-front expense. In his session at @BigDataExpo at @ThingsExpo, Andrew Warfield, CTO and Co-Founder of Coho Data, will dis...
With the Apple Watch making its way onto wrists all over the world, it’s only a matter of time before it becomes a staple in the workplace. In fact, Forrester reported that 68 percent of technology and business decision-makers characterize wearables as a top priority for 2015. Recognizing their business value early on, FinancialForce.com was the first to bring ERP to wearables, helping streamline communication across front and back office functions. In his session at @ThingsExpo, Kevin Roberts...
SYS-CON Events announced today that Interoute, owner-operator of one of Europe's largest networks and a global cloud services platform, has been named “Bronze Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2015 at the Javits Center in New York, New York. Interoute is the owner-operator of one of Europe's largest networks and a global cloud services platform which encompasses 12 data centers, 14 virtual data centers and 31 colocation centers, with connections to 195 ad...
Eighty percent of a data scientist’s time is spent gathering and cleaning up data, and 80% of all data is unstructured and almost never analyzed. Cognitive computing, in combination with Big Data, is changing the equation by creating data reservoirs and using natural language processing to enable analysis of unstructured data sources. This is impacting every aspect of the analytics profession from how data is mined (and by whom) to how it is delivered. This is not some futuristic vision: it's ha...
WebRTC has had a real tough three or four years, and so have those working with it. Only a few short years ago, the development world were excited about WebRTC and proclaiming how awesome it was. You might have played with the technology a couple of years ago, only to find the extra infrastructure requirements were painful to implement and poorly documented. This probably left a bitter taste in your mouth, especially when things went wrong.
Learn how IoT, cloud, social networks and last but not least, humans, can be integrated into a seamless integration of cooperative organisms both cybernetic and biological. This has been enabled by recent advances in IoT device capabilities, messaging frameworks, presence and collaboration services, where devices can share information and make independent and human assisted decisions based upon social status from other entities. In his session at @ThingsExpo, Michael Heydt, founder of Seamless...
The IoT's basic concept of collecting data from as many sources possible to drive better decision making, create process innovation and realize additional revenue has been in use at large enterprises with deep pockets for decades. So what has changed? In his session at @ThingsExpo, Prasanna Sivaramakrishnan, Solutions Architect at Red Hat, discussed the impact commodity hardware, ubiquitous connectivity, and innovations in open source software are having on the connected universe of people, thi...
WebRTC: together these advances have created a perfect storm of technologies that are disrupting and transforming classic communications models and ecosystems. In his session at WebRTC Summit, Cary Bran, VP of Innovation and New Ventures at Plantronics and PLT Labs, provided an overview of this technological shift, including associated business and consumer communications impacts, and opportunities it may enable, complement or entirely transform.
There are so many tools and techniques for data analytics that even for a data scientist the choices, possible systems, and even the types of data can be daunting. In his session at @ThingsExpo, Chris Harrold, Global CTO for Big Data Solutions for EMC Corporation, showed how to perform a simple, but meaningful analysis of social sentiment data using freely available tools that take only minutes to download and install. Participants received the download information, scripts, and complete end-t...
For manufacturers, the Internet of Things (IoT) represents a jumping-off point for innovation, jobs, and revenue creation. But to adequately seize the opportunity, manufacturers must design devices that are interconnected, can continually sense their environment and process huge amounts of data. As a first step, manufacturers must embrace a new product development ecosystem in order to support these products.
Manufacturing connected IoT versions of traditional products requires more than multiple deep technology skills. It also requires a shift in mindset, to realize that connected, sensor-enabled “things” act more like services than what we usually think of as products. In his session at @ThingsExpo, David Friedman, CEO and co-founder of Ayla Networks, discussed how when sensors start generating detailed real-world data about products and how they’re being used, smart manufacturers can use the dat...
When it comes to IoT in the enterprise, namely the commercial building and hospitality markets, a benefit not getting the attention it deserves is energy efficiency, and IoT’s direct impact on a cleaner, greener environment when installed in smart buildings. Until now clean technology was offered piecemeal and led with point solutions that require significant systems integration to orchestrate and deploy. There didn't exist a 'top down' approach that can manage and monitor the way a Smart Buildi...