<--- --------------------------------------------------------------------------------------- ----
	
	Blog Entry:
	Merging ColdFusion SQL Debugging And Query Params With Javascript
	
	Author:
	Ben Nadel / Kinky Solutions
	
	Link:
	http://www.bennadel.com/index.cfm?dax=blog:459.view
	
	Date Posted:
	Jan 5, 2007 at 10:31 PM
	
---- --------------------------------------------------------------------------------------- --->


// Check to see if the CF SQL functions have already been defined. I am doing this
// because I don't know how it will react if someone clicks on the bookmark twice.
if (typeof( ShowCFDebugSQL ) == "undefined"){
 
	// This grabs the ColdFusion SQL debug data from the ColdFusion debugging
	// output at the bottom of the page and displays the SQL with the query
	// params replaced back into the SQL.
	function ShowCFDebugSQL(){
		var arrTable = document.getElementsByTagName( "table" );
		var arrPre = null;
		var objCFDebugTable = null;
		var arrRawData = new Array();
		var objRawData = null;
		var objFrame = document.createElement( "textarea" );
		var i = 0;
 
		// Loop over the table looking for the one that houses all of the ColdFusion
		// script debugging.
		for (i = 0 ; i < arrTable.length ; i++){
 
			// We will know that we found the correct table if this table's class
			// name is "cfdebug".
			if (
				arrTable[ i ].className &&
				(arrTable[ i ].className == "cfdebug")
				){
 
				// Store this table reference.
				objCFDebugTable = arrTable[ i ];
 
				// Don't check any more, we have the only
				// table reference that we really need.
				break;
			}
 
		}
 
 
		// Check to see if the ColdFusion debugging table was found.
		if (objCFDebugTable){
 
			// Get the pre tags that are contained within the ColdFusion
			// debugging table. All of the SQL statements are contained
			// within PRE tags.
			arrPre = objCFDebugTable.getElementsByTagName( "pre" );
 
			// Loop over the PRE looking for the one that contains SQL.
			for (i = 0 ; i < arrPre.length ; i++){
 
				// Check for SQL markup. This is not fool-proof, but it seems
				// to be working nicely for my purposes.
				if( arrPre[ i ].innerHTML.match( new RegExp( "SELECT|INSERT|DELETE|JOIN|WHERE", "i" ) ) ){
 
					// Create an object to store the SQL code and param data.
					objRawData = new Object();
 
					// Store the SQL text; this will be the inner HTML of the
					// PRE tag that we just found.
					objRawData.SQL = arrPre[ i ].innerHTML;
 
					// Store empty params for a default value.
					objRawData.Params = "";
 
					// Check for CODE element. Depending on how the browser parses it's DOM,
					// the CODE element might be the next or white space element might be
					// next. Therefore, check both the next sibling and the next next sibling.
					if (
						(arrPre[ i ].nextSibling.nodeType == 1) &&
						(arrPre[ i ].nextSibling.tagName.toLowerCase() == "code")
						){
 
						// We found the CODE element to be the next sibling.
						// Check to make sure this CODE node contains query params.
						if (arrPre[ i ].nextSibling.innerHTML.match( new RegExp( "^Query Param", "i" ) )){
							objRawData.Params = arrPre[ i ].nextSibling.innerHTML;
						}
 
					} else if (
						(arrPre[ i ].nextSibling.nextSibling.nodeType == 1) &&
						(arrPre[ i ].nextSibling.nextSibling.tagName.toLowerCase() == "code")
						){
 
						// We found the CODE element to be the next NEXT sibling.
						// Check to make sure this CODE node contains query params.
						if (arrPre[ i ].nextSibling.nextSibling.innerHTML.match( new RegExp( "^Query Param", "i" ) )){
							objRawData.Params = arrPre[ i ].nextSibling.nextSibling.innerHTML;
						}
 
					}
 
					// Check to see if we have found any query params.
					if (objRawData.Params.length > 0){
 
						// Parse the query param raw data into an object array and store
						// the array back into the params key.
						objRawData.Params = ParseParams( objRawData.Params );
 
						// Add this to our SQL data array.
						arrRawData[ arrRawData.length ] = objRawData;
 
					} else {
 
						// Alert that no query params were found.
						alert(
							"I found a SQL query that does not have query params:\n\n" +
							objRawData.SQL.replace( new RegExp( "\\s+", "g" ), " " ).substring( 0, 150 ) + "...\n\n" +
							"This will not be included in the result."
							);
 
					}
 
				}
 
			}
 
 
			// Check to see if we found any SQL / query param data.
			if (arrRawData.length > 0){
 
				// We now have to prepare to output the data. Start setting up the
				// TEXTAREA frame styles.
				objFrame.style.backgroundColor = "#FAFAFA";
				objFrame.style.border = "4px solid #FAB60A";
				objFrame.style.color = "#000000";
				objFrame.style.fontFamily = "monospace, verdana";
				objFrame.style.fontSize = "12px";
				objFrame.style.height = "500px";
				objFrame.style.left = "0px";
				objFrame.style.padding = "15px 15px 15px 15px";
				objFrame.style.position = "absolute";
				objFrame.style.top = "0px";
				objFrame.style.width = "90%";
 
				// Allow double-clicking the textarea frame to remove itself.
				objFrame.ondblclick = function(){ this.parentNode.removeChild( this ); };
 
				// Add an intro data.
				objFrame.value += "+------------------------------------------------------------------------+\n";
				objFrame.value += "| Kinky Solutions ColdFusion SQL Debug Parser |\n";
				objFrame.value += "| Written by Ben Nadel @ 2007 |\n";
				objFrame.value += "| http://www.bennadel.com |\n";
				objFrame.value += "+------------------------------------------------------------------------+\n\n";
				objFrame.value += "**** DOUBLE-CLICK TO CLOSE TEXT AREA ****\n\n\n";
 
 
				// Loop over the SQL statements that we have found and parsed.
				for (i = 0 ; i < arrRawData.length ; i++){
 
					// Add sql text to the textarea. When doing this, parse the
					// query param values back into the SQL raw data. Then, replace
					// the escaped < and > values back into the SQL statement.
					objFrame.value += ReplaceParams(
						arrRawData[ i ].SQL,
						arrRawData[ i ].Params
						).replace(
							new RegExp( "<", "gi" ), "<"
						).replace(
							new RegExp( ">", "gi" ), ">"
						);
 
					// Add a horizontal rule for the next SQL statement.
					objFrame.value += "\n\n\n+--- END SQL STATEMENT --------------------------------------------------+\n\n\n";
 
				}
 
				// Add to the textarea to the document.
				document.getElementsByTagName( "body" )[ 0 ].appendChild(
					objFrame
					);
 
			}
 
		} else {
 
			// No debugging table was found.
			alert(
				"I could not find the ColdFusion debugging information.\n" +
				"Try turnning on debugging and running again."
				);
 
		}
 
	}
 
 
	// This takes the raw SQL data and the array of query params and
	// replaces the "?" in the raw SQL with the corresponding params.
	function ReplaceParams( strSQL, arrParams ){
		var intCounter = 0;
 
		return(
			strSQL.replace(
				new RegExp( "\\?", "g" ),
				function(){
					var strReturn = "";
 
					// Check to see if we have an available parameter.
					if (intCounter < arrParams.length){
 
						// Initialize the return value to the be the param value.
						strReturn = arrParams[ intCounter ].Value;
 
						// Check for a non-numeric type. For non-numeric types, we
						// want to wrap the value in single quotes.
						if (!arrParams[ intCounter ].Type.match( new RegExp( "INT|NUMERIC|FLOAT|DECIMAL|DOUBLE|BIT|REAL", "i" ) )){
 
							// Wrap the value in single quotes. Also, be sure to replace
							// single quotes with double quotes so as to maintain single
							// quotes within text fields.
							strReturn = (
								"'" +
								strReturn.replace(
									new RegExp( "'", "g" ),
									"''"
									) +
								"'"
								);
						}
 
						// Increment counter to hit the next argument.
						intCounter++;
 
					}
 
					// Return the raw data with param replaced back into it.
					return( strReturn );
 
				}
				)
			);
	}
 
 
	// This takes the RAW query param data and parses it into an orderly
	// array of objects the break it out into ID (param index), type,
	// and value.
	function ParseParams( strData ){
		var arrParams = new Array();
 
		strData.replace(
			new RegExp(
				"Parameter\\s#(\\d+)\\(([\\w_]+)\\)\\s*=\\s*([^\\r\\n]*)",
				"gi"
				),
			function( $0, $1, $2, $3 ){
				var objParam = new Object();
 
				objParam.ID = $1;
				objParam.Type = $2;
				objParam.Value = ($3).replace( new RegExp( "<br\\s*/?>$", "gi" ), "" );
 
				// Add this one to the array.
				arrParams[ arrParams.length ] = objParam;
			}
			);
 
		// Return the params array.
		return( arrParams );
	}
 
}