Web DevCenter    
 Published on Web DevCenter (http://www.oreillynet.com/javascript/)
 See this if you're having trouble printing code examples


ColdFusion MX on Mac OS X, Part 4

by Dick Applebaum
01/17/2003

Well, since we last got together, events have overtaken us. In Part 3, we had just gotten our port of ColdFusion MX (CFMX) Linux to run on the Mac.

On Jan 7, 2003 Macromedia announced that the combination of JRun and CFMXJ2EE are available for the Mac.

This changes everything!

JRun is a J2EE-Certified application server. It can be used to deploy enterprise-level applications on the Mac.

CFMX for J2EE (CFMXJ2EE) is our old friend, in a new package, that can be used to develop ColdFusion enterprise-level applications.

CFMX for J2EE applications, developed on the Mac, can be deployed on several platforms that support CFMX for J2EE in a production environment.

As of this writing, Macromedia has not announced a CFMX for J2EE production product for the Mac (more on that later).

Related Reading

Switching to the Mac: The Missing Manual
By David Pogue

We still don't have a complete solution for the Mac, but we are a lot closer than we were on January 6, 2003.

In this article, we will transition from the unsupported port to the fully-supported development environment using CFMX for J2EE and JRun.

With a wink, a nod, and a tip of the hat, we will bid the ugly port a fond good day!

Our new system is superior in every way. Just to whet your appetite a little:

There are many more advantages, and we will cover them in future articles.

Another thing we will do is structure the articles so that they focus on a single topic (as much as is practical). We are attempting to make the articles smaller, independent, and more concise. This should reduce the lead time between articles, and make them more responsive to the interests of our readers.

Lofty goals? Yeah, but let's give it a try. To assure that we are all starting at the same place, I will assume that:

  1. You are running Mac OS X 10.2 (Jaguar) or better.
  2. You have installed JRun for Mac OS X.
  3. You have deployed CFMX for J2EE on JRun on a server named cfmx1 (that's the name I used -- you can substitute your server name for cfmx1 wherever it appears in this article).

Something that every good CF developer needs is a way to interactively manipulate databases. The sole technical topic of this article is to install ViennaSQL and interface it to the PointBase database shipped with CFMKJ2EE.

Install ViennaSQL GUI SQL Client

One of the most powerful features of ColdFusion MX is its ability to easily interface many SQL database systems. One thing that is not provided is a general-purpose GUI SQL client -- a program that we can use interactively to create and maintain SQL databases and tables, and to develop and test our SQL queries.

As we will see, some database systems come with their own custom GUI SQL clients -- this is fine if these are the only database systems you use. What do you do, however, if you need to use a database system that has no interactive GUI SQL client?

We are fortunate that several general-purpose GUI SQL clients exist, and are available as open source. The one we are going to use is ViennaSQL. It is totally written in Java, so it will run without change on Mac OS X. It can interface any database that has a JDBC driver. All of the databases we will use with CFMX have JDBC drivers -- we will be setting up these in due course.

Author's Note: JDBC stands for Java Data Base Connectivity. It is an open standard to define a consistent means for Java programs to interface databases. By furnishing a JDBC driver, database providers allow their databases to be used by any Java program; in our case, CFMX (through <cfquery> tags), and ViennaSQL.

Let's get started!

1. Go to the ViennaSQL Web site and download the binary of ViennaSQL.

2. There is no real installation procedure for ViennaSQL, other than that any JDBC drivers used must be accessible to Java. Make a copy of the PointBase driver file and put it where ViennaSQL can find it. (In the following, substitute your server name for cfmx1.) Copy pbserver42RE.jar from:

 /Applications/JRun4/servers/cfmx1/cfusion/WEB-INF/cfusion/lib

to:

/Library/Java/Extensions

3. We are going to tour ViennaSQL with the ColdFusion CFSnippets database, so you need to start ColdFusion MX (CFMXJ2EE).

4. Once ColdFusion MX has started, log on to the CFMX Administrator through a browser window.

5. From the Data and Services menu, select Data Sources.

6. From the data sources displayed, select CFSnippets for editing.

The display shows the basic detail about the CFSnippets, as defined to CFMX. (This data source was automatically configured as part of the CFMX install.) We will use this information to define data source connection to ViennaSQL.

7. Double-click on the vienna.jar file that you downloaded above.

8. After a few moments, the ViennaSQL window appears. From the window's File menu, select Configure....

9. A window pops up displaying the ViennaSQL options. Click on the Connections tab.

10. A list of the available connections (data sources) will appear. Click the New Connection icon (upper right).

11. A new connection window pops up, and a blank connection definition is displayed. Most of the information requested is exactly the same as what is shown in the CFMX Administrator data source. You can copy the information from the CFMX Administrator, or from the text below. Be careful, as the driver class and JDBC URL appear in a different order between the two systems (CFMX and ViennaSQL). Once the information is complete, click the Test button to validate the connection. Note: the password for all the CFMX-installed databases is PBPUBLIC.

Connection name: CFSnippets
Driver class: com.pointbase.jdbc.jdbcUniversalDriver
URL (no breaks): jdbc:pointbase:cfsnippets,database.home=/Applications/JRun4/ servers/cfmx1/cfusion/WEB-INF/cfusion/db
User name: PBPUBLIC
Password: PBPUBLIC

12. A Connection Test window pops up. If all went well, the display shows that the JDBC driver was loaded and a connection was made to the database. Click the Dismiss button to dismiss this window.

13. In the New connection window, click OK to save the connection we just defined and tested. The New connection window disappears and the ViennaSQL options window is re-displayed, showing the CFSnippets connection we just defined.

14. In the ViennaSQL options window, click OK to exit the configuration process. The main ViennaSQL window is re-displayed.

15. In the main ViennaSQL window, select CFSnippets from the connection drop-down (upper right -- it shows [Disconnected]). The window will re-display with a message at the bottom saying that you are connected to the CFSnippets database.

16. From the window's menu bar select Query ---> Schema. A Select schema popup window appears.

17. The Select schema window shows two items: PBPUBLIC and POINTBASE. The PointBase item will display system components available in the entire PointBase system. The PBPUBLIC item will display everything that is available to the PBPUBLIC user for this database. (Many database systems allow different users to have different access privileges to the same database.) Select the PBPUBLIC item to display the components defined to the creator of the currently-connected (CFSnippets) database. Click OK. The Schema PBPUBLIC window pops up.

18. In the Schema PBPUBLIC window, click the Employees Table in the left pane; the right pane changes to display the definitions for the Employees Table.

19. Move the Schema PBPUBLIC window to the side so we can view both it and the Main ViennaSQL window.

20. In the main ViennaSQL window, enter the following SQL Query into the bottom pane. Then click the Execute (right arrow) icon (top, fifth from the left).

SELECT    LastName, FirstName, Phone
FROM      Employees
WHERE     Department='Sales'
ORDER BY  LastName, FirstName

21. The query results are shown in the upper pane.

We have only scratched the surface of the powerful, general-purpose ViennaSQL GUI SQL client. We can develop and test our SQL queries, interactively, against test or live data. Once the queries are validated, we can literally copy/paste them to our CFMX programs. The Query we just executed was our (soon-to-be famous) Phone List.

There is much more you can do with ViennaSQL: create databases, create or alter tables, create views, etc. (depending on the capability of the specific database system). A more comprehensive treatment of ViennaSQL is beyond the scope of this article, but I suggest you review the documentation and spend some time experimenting with the program. It is time well invested and will be paid back many times over.

It needs to be said that using a powerful, interactive tool like ViennaSQL requires a certain discipline. It is possible, with a few keystrokes, to irretrievably delete (or modify) all of the records in a table, or even drop entire databases. Proceed responsibly!

I should also note that CFMX, ViennaSQL, and other programs can access the databases concurrently -- though some database systems may not support advanced RDBMS features such as transaction isolation, locking, rollback, and deadlock detection/avoidance. CFMX contains features that help prevent problems that can occur with concurrent database access and modification; other languages may not offer similar protection. My advice? You should limit your concurrent database access and modifications to the lowest-common-denominator of what is supported by the underlying database system and the individual programs and programming languages used to access it.

We will not cover the setup of ViennaSQL connections for each individual database system that we interface from this point on. But the procedure is the same for each database -- only the connection details are different. The connection details can be copy/pasted between the CFMX Administrator and ViennaSQL Connection configurator. If you plan to use the database at all, take a few moments and set up a ViennaSQL connection.

Where Do We Go from Here?

There are quite a few other databases that can be interfaced to our CFMX Mac OS X developer system, and many other related topics that we can explore. Here's your chance to contribute! Below is a list of topics that are on the "To Do" list. We want you, the readers, to prioritize them for us. If you don't see the topic you want, feel free to add to the list.

  1. Use the >> and << buttons to move topics between Available and Chosen Topics.
  2. Use the Up and Dn buttons to change priority of Chosen Topics.
  3. Use the New Topic button and text box to enter a New Topic.
  4. When finished, email your selection with the Send Your Topic List button.
Available Topics   Chosen Topics, Prioritized  


 

 




 

Dick Applebaum has been doing Web development since 1997, primarily with ColdFusion -- including his latest challenge to port ColdFusion MX to Mac OS X.


Return to the Web Development DevCenter.

Copyright © 2009 O'Reilly Media, Inc.