query, dump, query, dump - is this better?

This post is more than 2 years old.

I'm working on an application where I don't (yet) have access to the database schema. It's a complex application with lots of data and isn't the most... how shall I say it.. well structured set of data I've even seen. To help me dig into the database and figure out what's going where, I'm doing a bunch of cfquery calls followed up by a cfdump.

While working, I accidentally found myself typing something that wasn't valid ColdFusion, but it just felt right at the time:

<cfdump query="select * from thetable" top=5 label="get test 5">

What do you think? Would that be useful? I know a large majority of my dumps are queries, but they tend to be queries I'm going to keep using. This is one of those rare times where I won't be keeping most of my queries around once I'm done digging.

P.S. If top and label are new to you, check out my ode to a dump blog post.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Joe Mastroianni posted on 8/26/2011 at 12:59 AM

I could see the use in certain cases (such as the one you find yourself in now). But generally it's as you said, if I'm dumping a resultset, it's to see what a query I'm keeping is doing.

Comment 2 by John Walker posted on 8/26/2011 at 1:03 AM

Not often. You generally have the query in your code already and need it. I am the one that requested cfdump at a Meet the Experts at MAX, can't recall if that was Allaire or Macromedia days.....

Comment 3 by MikeG posted on 8/26/2011 at 1:09 AM

have you tried cfdbinfo

Comment 4 by Raymond Camden posted on 8/26/2011 at 1:23 AM

Todd Sharp suggested this in IM, but since he signed off, I'm going to pretend it was my idea. This works too:

<cfdump var="#new Query(sql='select * from tblblogentries').execute().getResult()#" top=5>

Comment 5 by Raymond Camden posted on 8/26/2011 at 1:23 AM

MikeG - no - because I really need the data + structure.

Comment 6 by Peter Boughton posted on 8/26/2011 at 2:07 AM

Why don't you just query the database directly?

Or, if you did want to do it in CF, I'd rather have:
<cfdbpreview table="thetable" top=5 />

Comment 7 by Raymond Camden posted on 8/26/2011 at 2:08 AM

Um... "I'm working on an application where I don't (yet) have access to the database schema."

Comment 8 by Peter Boughton posted on 8/26/2011 at 2:13 AM

I don't understand that statement. If CF can access it, what's stopping you from accessing it?

Comment 9 by Raymond Camden posted on 8/26/2011 at 2:17 AM

Eh? CF can access it because it has the DSN info. I don't have CF Admin access, and even if I did, the password is encrypted.

Comment 10 by Peter Boughton posted on 8/26/2011 at 2:18 AM

To be more specific: you must have a datasource setup for CF to access it, right?

Which means anything else with a JDBC driver can use the same settings as the datasource.

So use something like SQL Explorer ( http://sqlexplorer.org ) and you can query directly, with existing settings.

Is there something I'm missing? :/

Comment 11 by Raymond Camden posted on 8/26/2011 at 2:20 AM

No CF Admin access. :)

Comment 12 by Peter Boughton posted on 8/26/2011 at 2:24 AM

Ok, well this is the point where I'd get whoever is in control of that access to do one of:
a) provide the password
b) setup a user
c) provide a current db diagram

Trying to work against a database without knowledge of what it contains is just crazy.

Comment 13 by Raymond Camden posted on 8/26/2011 at 2:30 AM

Ok, you're kinda missing the point here. ;) Yes, it's best to use a db tool. My point was - in cases where you need to do some quick testing, I've done cfquery/cfdump before. Either way - we all dev differently. :)

Comment 14 by Peter Boughton posted on 8/26/2011 at 2:33 AM

I didn't miss that point, I just don't understand accepting bad conditions. :P

If I was stuck with them regardless, I'd create a cfdbpreview tag to do the query, sql, and dumping for me in one short command.

Comment 15 by Phillip Senn posted on 8/26/2011 at 3:08 AM

I have a keyboard shortcut that's defined as: {Ctrl+C},{End},{Enter},<cfdump var="#{Ctrl+V}#">{Enter}<cfabort>

Comment 16 by Tof posted on 8/26/2011 at 4:19 AM

I know you are interested in the data as well, but for the schema part, fastest way to output that stuff is to query the information_schema tables, which outline all of you database metadata. Sp_help is a time saver too.

Comment 17 by Jaana Gilbert posted on 8/26/2011 at 4:37 AM

My favorite way of getting db info when I don't have direct access to the database via Enterprise Manager etc. :)

Get tables from the database (MSSQL):

SELECT * FROM INFORMATION_SCHEMA.Tables
WHERE Table_Type = 'BASE TABLE'
ORDER BY Table_Name

And then you loop through your tables and get columns

SELECT * FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ii#">
ORDER BY ORDINAL_POSITION

Comment 18 by Tof posted on 8/26/2011 at 5:02 AM

@jaana,

What's the point of doing query 1 and looping over it if query 2 already has the table name in it? :-) Is it because you want to limit the result set to tables only and exclude view? In that case you're better off joining the 2 tables on table_Name.

Comment 19 by Jaana Gilbert posted on 8/26/2011 at 5:09 AM

@Tof,
These are 2 queries on 2 different pages, I just brought them both in to the post. I use the first query in a dropdown to do filtering on the fly and only showing certain data on the page. Old code that I haven't altered in years. Yes you can have just a single query with a join, this is just something I've had running for a long time.

Comment 20 by Raymond Camden posted on 8/26/2011 at 5:10 AM

@Psenn - don't forget you can do <cfdump var="..." abort>

Comment 21 by Andreas Schuldhaus posted on 8/26/2011 at 8:03 AM

Umm, no. While I find that it would be tempting and on a first glance it seems very convenient to querydump or dumpquery a recordset using cfdump only, talking in terms of separation of concerns I don't believe that ColdFusion should provide this feature. Cfdump would also have to accept additional attributes like "database", "dbtype", "username", "password" to fulfill this task.

As @John said, usually you have the query somewhere in your code already. So I think I can live very well with cfdump only dumping out vars that already exist in the code.

Comment 22 by Michael Zock posted on 8/26/2011 at 8:41 AM

"I don't have CF Admin access, and even if I did, the password is encrypted. "

Don't forget about this little backdoor (saved me a lot of trouble the last time I had to migrate an inherited CF server)
http://paulalkema.com/post....

Comment 23 by Ian Turton posted on 8/26/2011 at 3:31 PM

Taking the <cfdump ... abort> idea (i.e. quite often you'll want to abort straight after a dump), what about reversing the 'dump point' (or for me putting it in a natural place), by having the ability to do <cfquery name="qBlah" dump>. Or, for more control, <cfquery name="qBlah" dump abort brewUp>.

Comment 24 by Raymond Camden posted on 8/26/2011 at 3:33 PM

Personally I'd rather clutter up cfdump with a bunch of attributes since it's a multipurpose debug tool. :)

Comment 25 by John Walker posted on 8/26/2011 at 4:12 PM

Ray, doesn't the IDE you use allow you to view datasources? I am still an old Homesite+ hold out. The datasource tab allows you to look through the tables based on the CF server's datasources. I hope CF Builder has that too?

Comment 26 by Raymond Camden posted on 8/26/2011 at 4:54 PM

@John: Yep, it does. If you have CF Admin or RDS access.

Comment 27 by Sharon posted on 8/28/2011 at 3:40 AM

I've been monkeying around with the idea of a simple dumpQuery() function (in my base.cfc) with the ability to dump any query based on a URL variable. It would only work if session.isDebugMode=true and you know the query name (and, of course, you've gotten around to adding the function to the query code). I'm thinking it would be super-duper helpful when trying to debug on production boxes (where you can't replicate the error on dev, of course) to display debug information without having to edit code.

Comment 28 by Phillip Senn posted on 8/29/2011 at 2:52 AM

What we need in the next version of ColdFusion is a way to make instantaneous communications easier (ala node.js). ColdFusion was the early adopter of database html. Now we need the makers of ColdFusion make event broadcasting easy.

Comment 29 by Raymond Camden posted on 8/29/2011 at 5:30 AM

What exactly do you mean by instantaneous communications? Sounds like something already covered by LCDS or Blaze, which plays well with ColdFusion.

Comment 30 by Phillip Senn posted on 8/29/2011 at 10:58 PM

This video
http://www.bennadel.com/blo...
has been haunting me. I realize that this is the future of the web. It's the next Ajax!
So that's what I mean by instantaneous communications.

Comment 31 by Raymond Camden posted on 8/30/2011 at 1:48 AM

This is no slight to NodeJS - but you can do this with Blaze/LCDS and CF as well.