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.
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
Archived Comments
Credits #3:
The guy who showed you CodePress ;)
I'm teasing of course...
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.
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.
Incredibly cool app and use of the new CF8 features. Do you plan on releasing this on riaforge?
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?
@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.
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).
You know what would be cool? Modifying this so you can update data elements within the grid.
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?
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.
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.
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?
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.
Dale, so by "full stop", all you mean is that you put a dot in your DSN name, right?
Yes datasoucrce name had a full stop in it.
Actually the databsae and datasource were both the domain name
For example coldfusionjedi.com
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.
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.
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.
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.
Guys - see the new code here:
http://www.coldfusionjedi.c...
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
Not that I know of. If you have trouble, I'll recommend what I told others - play with cfdbinfo outside of my app.
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.
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.
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
Since the result is a query, you could always use QofQ to filter em out.
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.