|
Recent
Articles |
Why SQL Gets It
There has been some discussion about MySQL's recent moves to clarify the targeting between MySQL Community Server & MySQL Enterprise Server. Essentially, MySQL wants to make it clear(er) that...
Understanding Live Data Backups With SBAdmin
As the need for system availability increases, especially in 24 hour operations, the backup "window" must decrease to prevent data from changing while being backed up. Backing up "live" data can cause...
Building A Bug Tracking Database
Whenever you're working with new software, and especially if you're working in development, you're bound to encounter bugs. A good bug tracking system is essential. You might choose to employ standardised bug tracking software, or you might prefer to build...
MySQL FULLTEXT Indexing & Searching
MySQL has supported FULLTEXT indexes since version 3.23.23. VARCHAR and TEXT Columns that have been indexed with FULLTEXT can be used with special SQL statements that perform the full text search...
Review: Refactoring Databases
Scott Ambler and Pramod Sadalage wrote Refactoring Databases, they say, "to share their experiences and techniques at evolving database schemas via refactoring". The book, particularly in the thorough list of refactorings detailed in later chapters, reveals them...
Sql Server Interview Questions on File Server and...
One of major things in two of this approach is of working with data but both of these works in different manner. In File Server Database approach the data is stored in the file and user have to take data directly from the file. When any change is to be made it is directly on...
Installing Essentials 2007 On A Remote Instance...
Dide you know can use a remote SQL server for the databases? It's true, but there are a few things to be aware of... SQL Reporting 2005 SP1 must be installed and configured on the SCE server - it cannot...
|
|
|
 |
|
08.27.07
Database Explorer - ColdFusion 8 Sample App
 By Raymond Camden
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
Comments
About the Author:
Raymond Camden, ray@camdenfamily.com. http://ray.camdenfamily.com. Raymond Camden is Vice President of Technology for roundpeg, Inc. A long
time ColdFusion user, Raymond has worked on numerous ColdFusion books
and is the creator of many of the most popular ColdFusion community web
sites. He is an Adobe Community Expert, user group manager, and the
proud father of three little bundles of joy.
|
