1 Reply Latest reply on Nov 24, 2006 5:56 PM by prk166

    Reusing SQL

    sean69 Level 1
      I'm trying to write a function that will call an SQL query from a file, what I have works, but I was wondering if there was a more elegant way to do it? - I am particularly concerned with the "dump" part. another query to do the dump does not sit well with me.

      >> application.cfm
      application.ServerRoot = "#Replace(GetCurrentTemplatePath(), "/Application.cfm", "", "all")#";

      >> Call the function
      <cfset #RunSql("GetAppVars","GetAppVars")#>

      >> contents from sample sql file
      select * from app_ApplicationVars

      >> the function
      <cffunction name="RunSql" returntype="any" output="yes">
      <!--- the name of the query --->
      <cfargument name="QueryName" type="string" required="yes">
      <!--- the sql data file to get --->
      <cfargument name="SqlData" type="string" required="yes">
      <!--- whether or not to dump(debug) the query --->
      <cfargument name="DumpVar" type="string" required="no">
      <cfquery name="#QueryName#" datasource="#application.DataSource#" >
      <cfinclude template="#Application.ServerRoot#/assets_sql/#SqlData#.sql">
      </cfquery>
      <cfif IsDefined("DumpVar")>
      <cfquery name="DumpQuery" datasource="#application.DataSource#" >
      <cfinclude template="#Application.ServerRoot#/assets_sql/#SqlData#.sql">
      </cfquery>
      <cfdump var="#DumpQuery#">
      </cfif>
      </cffunction>