Membuat Query Editor Berbasis Web Dengan Coldfusion

Mungkin mas bro, sudah sering melihat pembuatan Query Editor berbasis Web, :)

Disini saya hanya ingin berbagi ilmu bagaimana cara membuat Query Editor Berbasis Web dengan Bahasa Pemrograman Coldfusion,

Langkah Pertama :
Masukkan Source Code Dibawah ini kedalam editor yang mas bro punya.

<cfoutput>
<cfscript>
ServiceFactory = CreateObject("java", "coldfusion.server.ServiceFactory");
</cfscript>
<title>SQL Runner / Coldfusion Version #ServiceFactory.LicenseService.getMajorVersion()#</title>
<cfparam name="convert_TO_Excell" default="NO">
<CFPARAM Name="Sort" Default="Entry">
<cftry>
<CFOBJECT ACTION="CREATE"
TYPE="JAVA"
CLASS="coldfusion.server.ServiceFactory"
NAME="factory">

<cfscript>
dsService=factory.getDataSourceService();
dsFull=dsService.getDatasources();
dsNames=StructKeyArray(dsFull);
DS_List=QueryNew("Entry,Value");
</cfscript>
<cfset tmp=arraysort(dsNames,"textnocase")>
<CFLOOP INDEX="i" FROM="1" TO="#ArrayLen(dsNames)#">
<cfset tmp="dsFull.#dsNames[i]#.driver">
<cfset mydriver=evaluate(tmp)>
<cfif listfindnocase("MSSQLServer",mydriver) or listfindnocase("Oracle",mydriver) or listfindnocase("ODBCSocket",mydriver)>
<cfset tmp=QueryAddRow(DS_List)>
<cfset tmp=QuerySetCell(DS_List,"Entry",dsNames[i])>
<cfif mydriver eq 'ODBCSocket'>
<cfset tmp=QuerySetCell(DS_List,"Value","ODBCSocket")>
<cfelse>
<cfset tmp=QuerySetCell(DS_List,"Value",mydriver)>
</cfif>
</cfif>
</CFLOOP>
<cfcatch>
<CFREGISTRY ACTION=GETALL NAME="DS_List" TYPE="ANY" SORT="#Sort#" BRANCH="HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources">
</cfcatch>
</cftry>
<cfset DSList = valuelist(DS_LIST.Entry)>
<cfset DSDriver = valuelist(DS_LIST.Value)>
<cfscript>
function getDSNs() {
var factory = createObject("java","coldfusion.server.ServiceFactory");
return factory.getDataSourceService().getNames();
}

function dumpVarList(variable) {
    var delim="#Chr(13)##Chr(10)#";
    var var2dump=arguments.variable;
    var label = "";
    var newdump="";
    var keyName="";
    var loopcount=0;
 
    if(arrayLen(arguments) gte 2) delim=arguments[2];
    if(arrayLen(arguments) gte 3) label=arguments[3];
 
    // THE VARIABLE IS A SIMPLE VALUE, SO OUTPUT IT
    if(isStruct(var2dump)) {
   
        for(keyName in var2dump) {
            if(isSimpleValue(var2dump[keyName])) {
if (keyname contains "DRIVER")writeOutput(var2dump[keyName]);              
            }
        }
    }      
    return;
}
</cfscript>
<cfform name="frmSQL" action="#CGI.Script_Name#?#CGI.Query_String#" method="post" preservedata="true">
<table width="100%">
<cfinput type="hidden" name="convert_TO_Excell" value="#convert_TO_Excell#">
<tr>
<td>Select DataSource</td>
<td>&nbsp;:&nbsp;</td>
<td>
<cfparam name="reqsess_dsn" default="">
<cfselect name="reqsess_dsn">
<cfloop array=#getDSNs()# index="name">
<option value="#name#" <cfif reqsess_dsn Eq name>selected</cfif>>#name#<!--- &nbsp;[#dumpVarList(getdsn(name))#]--->
</cfloop>
</cfselect>
</td>
</tr>
<tr>
<td>View Output By</td>
<td>&nbsp;:&nbsp;</td>
<td>
<cfparam name="view_output" default="applet">
<cfselect name="view_output">
<option value="table" title="Semua Browser Support (KONEKSI CEPAT)" <cfif view_output Eq "table">selected</cfif>>Table
<cfif val(ServiceFactory.LicenseService.getMajorVersion()) GT 7>
<option value="applet" title="Browser Harus Support Dengan Java (KONEKSI CEPAT)" <cfif view_output Eq "applet">selected</cfif>>Applet
<option value="flash" title="Browser Harus Support Dengan Flash (KONEKSI SEDANG)" <cfif view_output Eq "flash">selected</cfif>>Flash
<option value="html" title="Semua Browser Support (KONEKSI SEDANG)" <cfif view_output Eq "html">selected</cfif>>Html
</cfif>
</cfselect>
</td>
</tr>
<tr><td  colspan="5">Query</td></tr>
<tr><td  colspan="5"><cftextarea name="txtSQL"  rows="20" cols="100%" style="width: 100%; height: 280px;; font-family: courier;" class="codepress sql linenumbers-off" id="sql"></cftextarea></td></tr>
<tr><td nowrap colspan="5">
<cfinput type="Button" name="cmdProcess" value="Process Query" onclick="javascript:process_query();">&nbsp;&nbsp;<cfinput type="Button" name="cmdConvert_Excell" value="Convert To Excell" onclick="javascript:convert_excell();">
</td></tr>
</table>
<Script>
function convert_excell()
{
document.frmSQL.convert_TO_Excell.value ="YES";
document.frmSQL.submit();
}
function process_query()
{
document.frmSQL.convert_TO_Excell.value ="NO";
document.frmSQL.submit();
}
</Script>
</cfform>
<br><br>
<cfform name="Frm_Query" enablecab="yes" format="html">

<cfif isdefined("txtSQL")>
<Cfset TxtRunSql = "#evaluate(DE(preservesinglequotes(txtSQL)))#">
<cftry>
<cfquery name="qRes" datasource="#reqsess_dsn#">
#evaluate(DE(preservesinglequotes(TxtRunSql)))#
</cfquery>

<cfif isdefined("txtSQL") and (lcase(left(txtSQL,6)) Neq "insert" OR lcase(left(txtSQL,6)) Neq "update" OR lcase(left(txtSQL,6)) Neq "delete")>
<cfif IsDefined("qRes.recordcount")>
#qRes.recordcount# found<br><br>
<cfif qRes.recordcount>
<cfif convert_TO_Excell NEQ "YES">
<cfif ServiceFactory.LicenseService.getMajorVersion() GT 7>
<Cfif UCASE(TRIM(view_output)) NEQ "TABLE">
<cfinclude template="cfgrid.cfm">
<Cfelse>
<cftable query="qRes" colspacing="2" border="1" htmltable colheaders>
<cfloop index="iFld" list="#qRes.columnlist#"><cfcol header = "<b>#iFld#</b>" align = "Center" text= "#evaluate("qRes.#iFld#")# &nbsp;"></cfloop>
</cftable>
</Cfif>
<cfelse>
<cftable query="qRes" colspacing="2" border="1" htmltable colheaders>
<cfloop index="iFld" list="#qRes.columnlist#"><cfcol header = "<b>#iFld#</b>" align = "Center" text= "#evaluate("qRes.#iFld#")#"></cfloop>
</cftable>
</cfif>
<cfelse>
<cfheader name="content-disposition" value="inline; filename=QUERY_TO_EXCELL[#DateFormat(now(),"ddmmyyyy")#].xls">
<cfcontent type="application/msexcel">
                            <style>
                             .XLSNIP
                            {mso-style-parent:style0;
                            color:black;
                            font-size:8.0pt;
                            font-family:Verdana, sans-serif;
                            mso-font-charset:0;
                            mso-number-format:"\@";
                            vertical-align:top;
                            border-top:none;
                            border-right:none;
                            background:white;
                            mso-pattern:auto none;
                            white-space:normal;}
                            </style>
<table border="1" cellspacing="0" cellpadding="0">
<tr>
<cfloop index="iFld" list="#qRes.columnlist#">
<td bgcolor="##C0C0C0">#iFld#</td>
</cfloop>
</tr>
<cfloop query="qRes">
<cfset icur=qRes.currentrow>
<tr>
<cfloop index="iFld" list="#qRes.columnlist#">
<cfif IsDate(evaluate("qRes.#iFld#[#icur#]"))>
<td>#dateformat(evaluate("qRes.#iFld#[#icur#]"),"dd/MMM/yyyy")#</td>
<cfelse>
<td class="XLSNIP">#evaluate("qRes.#iFld#[#icur#]")#</td>
</cfif>
</cfloop>
</tr>
</cfloop>
</table>
<cfinput type="hidden" name="convert_TO_Excell" value="NO">
</cfif>
</cfif>
</cfif>
</cfif>
<cfcatch>
<cfrethrow>
<cfloop collection = #cfcatch# item = "c"> <br><cfif IsSimpleValue(cfcatch[c])>#c# = #cfcatch[c]#</cfif> </cfloop>
</cfcatch>
</cftry>
</cfif>
</Cfform>
</cfoutput>

Langkah Kedua :
Simpan Source Code yang sudah mas bro buat dengan nama misalkan QueryEditor.cfm, Lalu mas bro jalankan file yang sudah mas bro simpan. misalkan dengan mengetikkan alamat browsernya seperti ini http://localhost:8500/QueryEditor.cfm

Maka Hasilnya akan seperti ini

Contoh Filenya Bisa Di Download Di Sini Query Editor

Selamat Mencoba Mas Bro... :)