Database Explorer - ColdFusion 8 Sample Application

The Database Explorer is a simple application that allows you to browse databases and write SQL. You can select a datasource, browse the tables (and columns), and then write simple SQL statements and see what they return. You can also export the results into an Excel file. Now let me go into detail on how the application works.

Click for bigger picture.

The application starts off by asking you to authenticate to your local ColdFusion server. The application supports both 'simple' CF Administrators protected by one password or systems that require a username and a password. The login template makes use of two <CFWINDOW> tags. The first one is used to host the main login form. The second one is used to display an error in case authentication fails. Authentication is provided by the Admin API.

Once logged in, index.cfm creates a simple layout using the new <CFLAYOUT> and <CFLAYOUTAREA> tags. The initial layout consists of a thin top bar and a main page underneath it. The top bar is used to display the datasources. The Admin API provides this list. The "Hide System Tables" option does just that – let you hide system tables. I'll explain that a bit more later on in the document.

Once you select a datasource, the bottom page is "pushed" to main.cfm. This is done using the ColdFusion.navigate function. Note that if you reload the entire application and your session is still active, this will happen automatically. The file main.cfm also makes use of <CFLAYOUT> and <CFLAYOUTAREA> to create a layout with three sections. Please note that I originally let you close the left pane. There was a bug with this, so I removed that functionality. You can resize it though.

The first section is the table list. This table list is created from the new <CFDBINFO> tag. I first get a list of tables, and then for each table I get a list of columns. This is all fed into the new HTML/AJAX version of CFTREE. For database columns I list the name as well as the type and note if the column is the primary key. When displaying tables, I check and see if you wanted to hide system tables. I hide these tables by checking if the table_type value is "System Table." While this works fine for MySQL, it does not work well for SQL Server.

The second pane is the SQL Editor. I'm using a third party JavaScript library named CodePress to provide SQL syntax highlighting.

Underneath the textarea used for SQL are three buttons. I used the new <CFTOOLTIP> to provide tool tips for these buttons. One is used to execute the SQL. The next one is used to execute the SQL and export to Excel. I ran into a bug using <CFCONTENT> inside a <CFLAYOUTAREA>, therefore the JavaScript is a bit different when exporting versus just executing. The last button is used to generate CF code that you can cut and paste into a template.

If you do just execute the code, the results pane is loaded with the SQL using ColdFusion.navigate again. The results pane simply executes your SQL. This SQL is wrapped in a CFTRY to catch any bad SQL statements. If your SQL returned data and you aren't exporting, I use the new HTML/AJAX <CFGRID> to display the results. If you are exporting, I generate a simple HTML table and use <CFCONTENT> to serve it up to Excel.

Credits:

The icons I used come from famfamfam.com

The syntax highlighter comes from CodePress

Download attached file.

Archived Comments

Comment 1 by todd sharp posted on 8/15/2007 at 6:21 AM

Credits #3:

The guy who showed you CodePress ;)

Comment 2 by todd sharp posted on 8/15/2007 at 6:22 AM

I'm teasing of course...

Comment 3 by Chad posted on 8/15/2007 at 4:55 PM

Ray, how many hours do you have in this app?

The way you describe it, CF had a nifty little tag to accomplish every feature of this app.

CFWindow
CFLayout
CFTree
CFDBinfo (i am going to have to check this one out!)
CFContent

I wonder if anyone has compiled a list of new Tags and Functions in CF8. I had no idea about CFDBinfo.

Comment 4 by Raymond Camden posted on 8/15/2007 at 5:05 PM

dbinfo has gotten little to no attention. Now I will admit that it isn't a tag you will use often - but it is pretty darn cool.

How many hours... maybe 10-15. But a good part of that was me learning stuff (this app was written before CF8 went final), and a good part was 'polish', like adding images, etc. The core part could be done in one hour by someone who knows the tags.

Comment 5 by Sam Farmer posted on 8/15/2007 at 6:06 PM

Incredibly cool app and use of the new CF8 features. Do you plan on releasing this on riaforge?

Comment 6 by Bruce posted on 8/15/2007 at 6:45 PM

Ray -

Tested this out on my CF 8 box. Worked fine with my data sources that point to SQL Server databases. However, I encountered an error when trying it with a data source for a Microsoft Access database.

This line fails in main.cfm:

The error occurred in C:\ColdFusion8\wwwroot\CF8Test\dbexplorer\main.cfm: line 44

42 :
43 : <cflayoutarea position="left" size="250" name="tabletree" title="Tables" splitter="true">
44 : <cfdbinfo datasource="#session.dsn#" name="tables" type="tables">
45 :
46 : <cfif tables.recordCount>

I tried using the cfdbinfo tag in my own test page with the datasource pointing to my Microsoft Access database and I get this error:

rror Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented

The error occurred in C:\ColdFusion8\wwwroot\CF8Test\teststfmpresenter.cfm: line 9

7 :
8 : <body>
9 : <cfdbinfo datasource="stfm_presenter" name="tables" type="tables">
10 :
11 : <cfquery datasource="stfm_presenter" name="test">

The driver is Microsoft Access. Testing using the datasource with a cfquery tag works fine.

Any idea why cfdbinfo fails for me when the datasource is one with a driver of Microsoft Access?

Comment 7 by Raymond Camden posted on 8/15/2007 at 7:25 PM

@Bruce: Smells like a bug. I'd file it at http://www.adobe.com/go/wish. I don't test on Access.

@Sam: You are the third person to ask this. I didn't see the point as it was mostly a POC (proof concept), but, what the heck.

Comment 8 by Bruce posted on 8/15/2007 at 7:39 PM

Ray - I should have researched cfdbinfo tag and Microsoft Access further before posting the above problem I had. The following is from the CF 8 Release Notes in the known issues section:

Quote

The cfdbinfo tag does not support data sources created with the Microsoft Access driver (which depends on the ODBC Socket
driver).

Unquote

Thanks again for posting the code. I learned alot just reading through your code about how to use the various CF 8 tags (cfdbinfo, cftree, cfgrid, etc).

Comment 9 by David C-L posted on 8/16/2007 at 2:26 AM

You know what would be cool? Modifying this so you can update data elements within the grid.

Comment 10 by Dale Fraser posted on 8/24/2007 at 3:06 PM

Hey Ray,

This is a really cool tool, especially as I don't know of any other way to browse the Apache Derby databases. It would be extra cool if it allowed you to create databases in Apache Derby.

Anyway, I can't get it to work! I donwloaded it and extracted it, I can browse it, login and then in IE: I get a javascript error and when I select a datasource nothing happens. In FireFox I don't get any javascript errors, but when I try to select a DB nothing happens.

I'm probably doing something stupid, any ideas?

Comment 11 by Dale Fraser posted on 8/24/2007 at 3:10 PM

I knew I was stupid.

Make sure you have a CFIDE mapping or virtual directory.

PS: This is good, it should be built into CF Administrator.

Comment 12 by Dale Fraser posted on 8/24/2007 at 3:16 PM

Two bugs.

1. The SQL Editor is only about two lines high in IE7 under Vista, works fine in Firefox.
2. The system will not show tables for datasources that contain a full stop.

Comment 13 by Raymond Camden posted on 8/24/2007 at 5:10 PM

I don't have Vista so I can't really test it. I do have IE7... but IE7 is evil. ;) If anyone knows a good fix for IE7 then I'll happily include it in the code.

2. I use dbinfo - so if the table doesn't show up - you need to log a bug w/ Adobe. What are "full stops" in datasources?

Comment 14 by Dale Fraser posted on 8/25/2007 at 3:40 AM

Ray,

1. Since about 80% of people are on IE7, might be a good idea to get things working with it but I don't know a fix, however it still works in Ie7, just can't see more than 2 lines of your SQL statement.

2. the datasource was called "camden.com" for example, I changed it to not have a full stop and it worked.

Having the domain as the datasouce is probably not a good idea. But I thought i'd let you know.

Comment 15 by Raymond Camden posted on 8/27/2007 at 5:27 PM

Dale, so by "full stop", all you mean is that you put a dot in your DSN name, right?

Comment 16 by Dale Fraser posted on 8/28/2007 at 3:22 AM

Yes datasoucrce name had a full stop in it.

Actually the databsae and datasource were both the domain name

For example coldfusionjedi.com

Comment 17 by Raymond Camden posted on 8/28/2007 at 5:11 PM

I've never heard it referred to as a 'full stop'. To me it is just a period. ;) Ok, I'll test locally to see if I can reproduce.

Comment 18 by Raymond Camden posted on 8/28/2007 at 5:16 PM

Sorry, I can't reproduce this. I made a DSN named blog.com and it worked fine. Perhaps you can do some testing on your own with cfdbinfo and see if you find a particular problem.

Comment 19 by Dale Fraser posted on 8/29/2007 at 3:10 AM

Ok,

I will, it's using Sql Express 2005, i'll see what happens.

FYI: http://en.wikipedia.org/wik...

If you had said Period I would have had no idea what you were talking about.

Comment 20 by Dale Fraser posted on 8/29/2007 at 3:12 AM

Ohh,

Brain wave! The database it's self also had a . in it. Perhaps the problem is there rather that the datasource, I will test.

Comment 21 by Raymond Camden posted on 8/29/2007 at 7:52 PM

Guys - see the new code here:

http://www.coldfusionjedi.c...

Comment 22 by Kebab Dylan posted on 8/29/2007 at 9:42 PM

are there any known issues with Oracle? I installed this but when i choose a datasource i just get the loading message and nothing happens. We are running 10g I believe

Comment 23 by Raymond Camden posted on 8/29/2007 at 10:06 PM

Not that I know of. If you have trouble, I'll recommend what I told others - play with cfdbinfo outside of my app.

Comment 24 by Chad posted on 8/30/2007 at 4:30 PM

I created a similar app a while back although not as purty as yours. The one thing I didn't see mentioned in yours is the ability to save queries. I added some AJAX code where I could save my common queries and then have them in a drop-down list when I need them.

Comment 25 by Charlie Arehart posted on 9/5/2007 at 1:47 AM

Hey Ray, great stuff as always. One thing: I notice that the download in this entry is the old one, and only if one reads the comments would they learn to go to the later entry to get the new one. I suppose there may be value in keeping the old one for some reason, but perhaps you could put a box at the bottom of this entry pointing out the other (or change the download here to the later, and have the box offer the older version for whatever reason someone may have to want it).

Just so people know of the difference (I had errors running the older, and was reading the comments to see what others had experienced when I saw the link.)

Just trying to help, as always.

Comment 26 by cf_kiddie posted on 8/8/2008 at 5:26 PM

Hi Ray

In MSSQL 2005, cfdbinfo type="tables" shows the system views and system tables also. It would be great if there is one parameter that we can specify for hiding them.

cf_kiddie

Comment 27 by Raymond Camden posted on 8/8/2008 at 5:29 PM

Since the result is a query, you could always use QofQ to filter em out.

Comment 28 by James posted on 9/15/2008 at 10:34 PM

Does this Supports OBDC Datasources?

I'm guessing no, since a datasource of mine that is set as an OBDC doesn't display. Actually I get the error: Error retrieving Markup for element main: Error Executing Database Query.

Funny, Access and MySQL work just fine. Anyhow, thanks Ray.