Welcome!

You will be redirected in 30 seconds or close now.

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

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
SYS-CON Events announced today that Ericsson has been named “Gold Sponsor” of SYS-CON's @ThingsExpo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. Ericsson is a world leader in the rapidly changing environment of communications technology – providing equipment, software and services to enable transformation through mobility. Some 40 percent of global mobile traffic runs through networks we have supplied. More than 1 billion subscribers around the world re...
There is an ever-growing explosion of new devices that are connected to the Internet using “cloud” solutions. This rapid growth is creating a massive new demand for efficient access to data. And it’s not just about connecting to that data anymore. This new demand is bringing new issues and challenges and it is important for companies to scale for the coming growth. And with that scaling comes the need for greater security, gathering and data analysis, storage, connectivity and, of course, the...
We’ve worked with dozens of early adopters across numerous industries and will debunk common misperceptions, which starts with understanding that many of the connected products we’ll use over the next 5 years are already products, they’re just not yet connected. With an IoT product, time-in-market provides much more essential feedback than ever before. Innovation comes from what you do with the data that the connected product provides in order to enhance the customer experience and optimize busi...
SYS-CON Events announced today that Peak 10, Inc., a national IT infrastructure and cloud services 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. Peak 10 provides reliable, tailored data center and network services, cloud and managed services. Its solutions are designed to scale and adapt to customers’ changing business needs, enabling them to lower costs, improve performance and focus inter...
Increasing IoT connectivity is forcing enterprises to find elegant solutions to organize and visualize all incoming data from these connected devices with re-configurable dashboard widgets to effectively allow rapid decision-making for everything from immediate actions in tactical situations to strategic analysis and reporting. In his session at 18th Cloud Expo, Shikhir Singh, Senior Developer Relations Manager at Sencha, will discuss how to create HTML5 dashboards that interact with IoT devic...
So, you bought into the current machine learning craze and went on to collect millions/billions of records from this promising new data source. Now, what do you do with them? Too often, the abundance of data quickly turns into an abundance of problems. How do you extract that "magic essence" from your data without falling into the common pitfalls? In her session at @ThingsExpo, Natalia Ponomareva, Software Engineer at Google, will provide tips on how to be successful in large scale machine lear...
The increasing popularity of the Internet of Things necessitates that our physical and cognitive relationship with wearable technology will change rapidly in the near future. This advent means logging has become a thing of the past. Before, it was on us to track our own data, but now that data is automatically available. What does this mean for mHealth and the "connected" body? In her session at @ThingsExpo, Lisa Calkins, CEO and co-founder of Amadeus Consulting, will discuss the impact of wea...
A critical component of any IoT project is the back-end systems that capture data from remote IoT devices and structure it in a way to answer useful questions. Traditional data warehouse and analytical systems are mature technologies that can be used to handle large data sets, but they are not well suited to many IoT-scale products and the need for real-time insights. At Fuze, we have developed a backend platform as part of our mobility-oriented cloud service that uses Big Data-based approache...
trust and privacy in their ecosystem. Assurance and protection of device identity, secure data encryption and authentication are the key security challenges organizations are trying to address when integrating IoT devices. This holds true for IoT applications in a wide range of industries, for example, healthcare, consumer devices, and manufacturing. In his session at @ThingsExpo, Lancen LaChance, vice president of product management, IoT solutions at GlobalSign, will teach IoT developers how t...
We're entering the post-smartphone era, where wearable gadgets from watches and fitness bands to glasses and health aids will power the next technological revolution. With mass adoption of wearable devices comes a new data ecosystem that must be protected. Wearables open new pathways that facilitate the tracking, sharing and storing of consumers’ personal health, location and daily activity data. Consumers have some idea of the data these devices capture, but most don’t realize how revealing and...
Digital payments using wearable devices such as smart watches, fitness trackers, and payment wristbands are an increasing area of focus for industry participants, and consumer acceptance from early trials and deployments has encouraged some of the biggest names in technology and banking to continue their push to drive growth in this nascent market. Wearable payment systems may utilize near field communication (NFC), radio frequency identification (RFID), or quick response (QR) codes and barcodes...
Artificial Intelligence has the potential to massively disrupt IoT. In his session at 18th Cloud Expo, AJ Abdallat, CEO of Beyond AI, will discuss what the five main drivers are in Artificial Intelligence that could shape the future of the Internet of Things. AJ Abdallat is CEO of Beyond AI. He has over 20 years of management experience in the fields of artificial intelligence, sensors, instruments, devices and software for telecommunications, life sciences, environmental monitoring, process...
You think you know what’s in your data. But do you? Most organizations are now aware of the business intelligence represented by their data. Data science stands to take this to a level you never thought of – literally. The techniques of data science, when used with the capabilities of Big Data technologies, can make connections you had not yet imagined, helping you discover new insights and ask new questions of your data. In his session at @ThingsExpo, Sarbjit Sarkaria, data science team lead ...
The IETF draft standard for M2M certificates is a security solution specifically designed for the demanding needs of IoT/M2M applications. In his session at @ThingsExpo, Brian Romansky, VP of Strategic Technology at TrustPoint Innovation, will explain how M2M certificates can efficiently enable confidentiality, integrity, and authenticity on highly constrained devices.
The IoTs will challenge the status quo of how IT and development organizations operate. Or will it? Certainly the fog layer of IoT requires special insights about data ontology, security and transactional integrity. But the developmental challenges are the same: People, Process and Platform. In his session at @ThingsExpo, Craig Sproule, CEO of Metavine, will demonstrate how to move beyond today's coding paradigm and share the must-have mindsets for removing complexity from the development proc...
Manufacturers are embracing the Industrial Internet the same way consumers are leveraging Fitbits – to improve overall health and wellness. Both can provide consistent measurement, visibility, and suggest performance improvements customized to help reach goals. Fitbit users can view real-time data and make adjustments to increase their activity. In his session at @ThingsExpo, Mark Bernardo Professional Services Leader, Americas, at GE Digital, will discuss how leveraging the Industrial Interne...
In his session at @ThingsExpo, Chris Klein, CEO and Co-founder of Rachio, will discuss next generation communities that are using IoT to create more sustainable, intelligent communities. One example is Sterling Ranch, a 10,000 home development that – with the help of Siemens – will integrate IoT technology into the community to provide residents with energy and water savings as well as intelligent security. Everything from stop lights to sprinkler systems to building infrastructures will run ef...
Whether your IoT service is connecting cars, homes, appliances, wearable, cameras or other devices, one question hangs in the balance – how do you actually make money from this service? The ability to turn your IoT service into profit requires the ability to create a monetization strategy that is flexible, scalable and working for you in real-time. It must be a transparent, smoothly implemented strategy that all stakeholders – from customers to the board – will be able to understand and comprehe...
You deployed your app with the Bluemix PaaS and it's gaining some serious traction, so it's time to make some tweaks. Did you design your application in a way that it can scale in the cloud? Were you even thinking about the cloud when you built the app? If not, chances are your app is going to break. Check out this webcast to learn various techniques for designing applications that will scale successfully in Bluemix, for the confidence you need to take your apps to the next level and beyond.
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 cloud...