ColdFusion SQL Color Coder
Posted by
Brad Wood
Sep 03, 2008 21:41:00 UTC
A while back while building a monitoring tool for running processes on SQL Server 2005 I encountered the desire to color code SQL in the same manner of MS Query Analyzer (Or Management Studio) for HTML output. I hit up the CF-Talk list and Google for an existing ColdFusion implementation but got crickets. Not being one to give up, I created my own.Without much ado, here it is. It basically parses through the document looking at each token and coloring it accordingly. I used several MSDN articles to get lists of reserved tables, functions, procs, etc. as well as what colors to use.
It performs pretty well on decent sized chunks of SQL. If you get over several thousands lines, expect it to take a couple seconds to finish. Perhaps if I get a chance, I will work it into BlogCFC to have <code language="SQL"> blocks or something.
The code doesn't wrap very nicely, so if you actually want to see it, download the ZIP.
Example Output:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/* +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Name: ps_report_commitment_sent
Created by: JPG
Date: 10/18/2005
Purpose: Used to generate NLS Closing website Commitment Sent report
Modification History
===============================================================================================
Rev # Initials Date Purpose
===============================================================================================
#001 JPG 10/18/2005 Create
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */
ALTER PROCEDURE [dbo].[ps_report_commitment_sent]
@reporting_entity_id uniqueidentifier,
@entity_id_customer uniqueidentifier,
@start_date datetime=NULL,
@end_date datetime=NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @colorder varchar(4000)
SET @colorder = 'Order#|loan#|date_ordered|loan_officer|buyer|state|status' -- Order of columns for report
SELECT DISTINCT dbo.f_return_hot_link('ORDER', ob.ORDER_ID, 0, '') AS a_nls_order_num,
ol.loan_number as b_loan_number,
ob.datetime_created as c_date_ordered,
RTRIM(ISNULL(voelo.full_name,'No Loan Officer Specified'))as d_loan_officer,
@colorder AS _colorder,
FROM order_basic ob WITH(NOLOCK)
LEFT OUTER JOIN v_order_entity_simple voelo WITH(NOLOCK) ON ob.order_id = voelo.order_id
AND voelo.u_id_order_entity_type = '66CB46AE-8FEA-493B-991D-313ADFB1E609' --'Loan Officer'
AND voelo.sort_order = 1
AND voelo.system_status = 1
WHERE ob.entity_id_customer = @entity_id_customer
AND DATEDIFF(d,@start_date,ob.datetime_created)>=0
AND DATEDIFF(d,ob.datetime_created,@end_date)>=0
AND ob.entity_id_order_company = @reporting_entity_id
ORDER BY c_date_ordered, g_status
ENDset ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
set QUOTED_IDENTIFIER ON
go
/* +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Name: ps_report_commitment_sent
Created by: JPG
Date: 10/18/2005
Purpose: Used to generate NLS Closing website Commitment Sent report
Modification History
===============================================================================================
Rev # Initials Date Purpose
===============================================================================================
#001 JPG 10/18/2005 Create
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */
ALTER PROCEDURE [dbo].[ps_report_commitment_sent]
@reporting_entity_id uniqueidentifier,
@entity_id_customer uniqueidentifier,
@start_date datetime=NULL,
@end_date datetime=NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @colorder varchar(4000)
SET @colorder = 'Order#|loan#|date_ordered|loan_officer|buyer|state|status' -- Order of columns for report
SELECT DISTINCT dbo.f_return_hot_link('ORDER', ob.ORDER_ID, 0, '') AS a_nls_order_num,
ol.loan_number as b_loan_number,
ob.datetime_created as c_date_ordered,
RTRIM(ISNULL(voelo.full_name,'No Loan Officer Specified'))as d_loan_officer,
@colorder AS _colorder,
FROM order_basic ob WITH(NOLOCK)
LEFT OUTER JOIN v_order_entity_simple voelo WITH(NOLOCK) ON ob.order_id = voelo.order_id
AND voelo.u_id_order_entity_type = '66CB46AE-8FEA-493B-991D-313ADFB1E609' --'Loan Officer'
AND voelo.sort_order = 1
AND voelo.system_status = 1
WHERE ob.entity_id_customer = @entity_id_customer
AND DATEDIFF(d,@start_date,ob.datetime_created)>=0
AND DATEDIFF(d,ob.datetime_created,@end_date)>=0
AND ob.entity_id_order_company = @reporting_entity_id
ORDER BY c_date_ordered, g_status
ENDset ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Tags: ColdFusion, SQL
Justin G
Just wanted to let you know that there is a free text editor called notepad++ which <a href="http://notepad-plus.sourceforge.net/uk/site.htm" target ="_blank">Link to notepad ++ which has color coding for almost every language including SQL just not ColdFusion im sorry to say. At least not by default. But it is worth looking at, just put your SQL code or java, or C code in there and select language from the menu at top ie SQL. HTH It also allows you to create code coloring for any language you choose so if you decide to create color coding for CF let me know, I might just lend a hand.
Brad Wood
@Justin: I've heard of notepad++, but ever used it. I use Metapad, though I think it's a bit outdated now.
The thing about notepad++, is it still wouldn't let you programatically color code SQL to display in the output of a webpage would it?
Peter Boughton
Wow, another Metapad user! :D I thought I was the only one... well, I've introduced a few people to it, but other than that not found another soul that's been aware of it.
Other editor I use is jEdit, which also does colour coding for most languages. Again, wont do browser stuff.
You might want to investigate Jason Delmore's ColdFISH - it doesn't actually do SQL, but probably no reason why you couldn't add that ability to it...
Brad Wood
Yeah, ColdFISH is what Ray has baked into BlogCFC.
Here's a good metapad easter egg I found myself. Go to help > about. Double click the purple metapad icon. Double click again to reverse the effect.
Peter Boughton
Haha, that's cool. :D I can't believe I've never tried it - I'm sure I had a phase at one point of clicking around in About boxes.
suvro ghosh
Thanks the post from Justin was what I was looking for. One question how do I set up color code for my own variables, like say variables that start with xc_sd_.