Andy Jarrett // Code. Develop. Create.

Generate CFML code to create a query from a query

I've just realised I am terrible at writing good, descriptive subjects for my blog posts so here is a (bit of a) better description of what I mean.Last week I needed to use a query from a live DB to do some work on offline. Usually I use MySQL so I take a backup from the night before and run it locally so I am nearly up-to-date on my dev machine but this DB was MSSQL and I haven't gotten around to getting that setup on Parallels. Also there was no need to download 'X' amount of tables and stored procedures as all I wanted to actually use was one query output.So what I've created is a function which generates the CFML for a queryNew() statement needed to re-create the query you want to work on.<cffunction name="createQuery" access="public" output="false" returntype="struct" hint="I take a query and return the code to regenerate it."> <cfargument name="inQry" required="true" type="query" /> <cfargument name="fileNameAndLoc" default="" type="string" /> <cfargument name="withDump" default="false" type="boolean" /> <cfset var qry = arguments.inQry /> <cfset var i = 0 /> <cfset var cols = "" /> <cfset var textQuery = structNew() /> <cfset var saveFile = "" /> <cfset var ltc = chr(60) /> <cfif len(trim(arguments.fileNameAndLoc))> <cfset saveFile = arguments.fileNameAndLoc /> </cfif> <cfset textQuery.hasErrors = 0/> <cftry> <cfsavecontent variable="textQuery.code"><cfoutput>#ltc#!--- First we create the query --->#chr(13)##ltc#cfset newQry = queryNew('#qry.columnList#') />#ltc#!--- Now start populating the query --->#chr(13)#<cfloop from="1" to="#qry.recordCount#" index="i">#ltc#cfset queryAddRow(newQry) />#chr(13)#<cfloop list="#qry.columnList#" index="cols"> #ltc#cfset querySetCell(newQry, '#cols#', '#qry[cols][i]#') />#chr(13)#</cfloop></cfloop> <cfif arguments.withDump> #ltc#!--- Dump the query --->#chr(13)##ltc#cfdump var="##newQry##" label="Query Dump" /> </cfif></cfoutput> </cfsavecontent> <cfif len(saveFile)> <cffile action="write" file="#saveFile#" output="#textQuery.code#" attributes="normal" mode="777" /> </cfif> <cfcatch type="any"> <cfset textQuery.hasErrors = 1/> <cfset textQuery.error = cfcatch.ErrorCode &" "& cfcatch.Message /> </cfcatch> </cftry> <cfreturn textQuery /></cffunction> So in action we would have the following which would create a new .cfm page called "test.cfm" as well as outputting the results to the screen <cfquery datasource="test" name="qryTest"> SELECT id,sex,name FROM test</cfquery><cfset myVar = createQuery(qryTest, expandPath('test.cfm'), true)><cfif NOT myVar.hasErrors> <cfoutput>#myVar.code#</cfoutput></cfif>In the generated test.cfm you would see<cfset newQry = queryNew('ID,NAME,SEX') /><cfset queryAddRow(newQry) /><cfset querySetCell(newQry, 'ID', '1') /><cfset querySetCell(newQry, 'NAME', 'Andy') /><cfset querySetCell(newQry, 'SEX', 'male') /><cfset queryAddRow(newQry) /><cfset querySetCell(newQry, 'ID', '2') /><cfset querySetCell(newQry, 'NAME', 'Helen') /><cfset querySetCell(newQry, 'SEX', 'Female') /><cfdump var="#newQry#" label="Query Dump" />

I’m here, learning and working away. If you liked this content and want to keep me going, consider buying me a coffee. Your support keeps this site running and the coffee brewing! ☕️