Select into from ColdFusion query object

So I got a question from a friend of mine who had a query returned in ColdFusion and wanted to do a "select into" with the query object. He found out how to do it one statement. Check it out.

<cfquery datasouce="ds">
SELECT COLUMN_1,COLUMN_2,COLUMN_3
INTO DATABASE_TABLE
FROM QUERYOBJECT.THEQUERY
</cfquery>

Default on bit column in sql.

alter table tablename
add constraint DF_tablename_columnname default ((0)) for columnname

Merging cfdocuments into pdf without creating a file

So I was working with a dms and wanted to try not writing and reading pdf files but instead using the cf and db to display the data. I struggled with invoking cfpdfparam source attribute on cfpdf action="merge" because it doesn't take straight up binary, which is what cfdocument is returning. So I created a pointer variable and passed that in and it accepted. Kinda wierd it wouldn't accept it but oh well. Check it out.

<!--- get the pages --->
<cfset pages = beans.docService.getPagesByVersionID(versionID=versionID)/>

<!--- create array to hold cfdocuments --->
<cfset documents = []/>

<!--- loop pages and create cfdocuments --->
<cfloop query="pages">

   <cfdocument name="pdf_name" format="pdf" pageheight="#pages.height#"           pagewidth="#pages.width#" pagetype="#pages.doc_page_type_Name#"                     orientation="pages.document_orientation_type_name#">

<!--- get the page layout --->
            <cfset layout[id] = beans.docService.getLayoutByID(pages.layout_id)                     />

<!--- get xmltransform--->
            <cfsilent>
                     <cfset html[id] = xmltransform(xml,layout[id].xslt) />
            </cfsilent>

<!--- set margins --->
            <cfdocumentsection
      marginbottom="#pages.bottom_margin#"
      marginleft="#pages.left_margin#"
      marginright="#pages.right_margin#"
      margintop="#pages.top_margin#">

<!--- output data --->
                    <cfoutput>#html[id]#</cfoutput>

            </cfdocumentsection>

   </cfdocument>

<!--- add binary cfdocument to the  --->
<cfset arrayappend(documents,pdf_name) />

</cfloop>


<!--- merge cfdocuments into 1 pdf. this will return some binary --->
<cfpdf action="merge" name="final">

   <cfloop from="1" to="#arraylen(documents)#" index="i">

<!--- set pointer for cfpdfparam source, this part took me forever to figure out because the source attribute is looking for a pointer variable. you can't just pass in the binary  --->
            <cfset pointer = documents[i]/>

            <cfpdfparam source="pointer">

   </cfloop>

</cfpdf>

<!--- output the pdf --->
<cfcontent type="application/pdf" variable="#tobinary(final)#" reset="No"  >

The litte things

As I logged into my bank accounts online I realized how much of the little things are missing from a page. Details such as label tags on radio and checkboxes, titles on links, and directions steps in a wizard are just to name a few. When working with presentation, people remember every little detail. So remember to think of the little things.

Coldfusion writing simple xslt

I think it would be kinda cool to not have to write xslt as much any more, so a coworker and I created some helper functions to handle tables for us. So instead of writing xslt to create a table tag where we can pump xml into we created coldfusion functions with which can shoot args and write the xslt for us.

Here we call a beginTable() with which we can give it attributes.

<cffunction name="beginTable" access="public" returntype="string" output="false" >

     <cfset var local = {} />

<cfset arguments = getArguments(arguments) />

<cfsavecontent variable="local.string">
<cfoutput>
<xsl:call-template name="beginTable">

#renderXSLTParams(argumentCollection=arguments)#

</xsl:call-template>
</cfoutput>
</cfsavecontent>

<cfreturn local.string>

</cffunction>

First we can getArguments which handles our common attributes of the tag we are trying to create. getArguments() can be used to set out defaults but of simplicity we just loop a list of common attributes.

<cffunction name="getArguments" access="public" output="false" returntype="struct">
     <cfargument name="args" required="true" />

<cfset var local = {} />

<cfset local.args = arguments.args />

<cfloop list="class,style,title,alt,id,height,width,colspan,cellspacing,cellpadding,align,border,src,value" index="local.i">
<cfset local.args[lcase(local.i)] = getKey(lcase(local.i),local.args,"#local.i#") />
</cfloop>

<cfreturn local.args />

</cffunction>

Second, we write the xlst beginTable template and create the xslt params using renderXSLTParams.

<cffunction name="renderXSLTParams" access="public" returntype="string" output="false" >

<cfset var local = {} />
<cfset var i = "" />

<cfsavecontent variable="local.string">
<cfoutput>
<cfloop collection="#arguments#" item="i">
 <xsl:with-param name="#lcase(i)#">
  <cfif (FindNoCase('%',arguments[i],1) or FindNoCase('px',arguments[i],1) )>
   <xsl:text>#arguments[i]#
  <cfelse>
   <xsl:value-of select="#lcase(arguments[i])#"/>
  </cfif>
 </xsl:with-param>
</cfloop>
</cfoutput>
</cfsavecontent>

<cfreturn local.string/>

</cffunction>

That takes care of creating the xslt snippet for the begin table tag, now we have to load the tag at the bottom of the xslt so we can call the template and invoke the params. To do this we output the load function which contains all our template snippets.

<cffunction name="load" access="public" output="false" returntype="string" >

<cfset var local = {}/>

<cfsavecontent variable="local.string">
<cfoutput>
<xsl:template name="beginTable">
 #getTemplateParams()#
 <xsl:text disable-output-escaping="yes"><</xsl:text>table
  #getXSLTParams()#
 <xsl:text disable-output-escaping="yes">></xsl:text>
</xsl:template>

 <xsl:template name="endTable">
   <xsl:text disable-output-escaping="yes"><</xsl:text>/table
</xsl:template>

         </cfoutput>
</cfsavecontent>

<cfreturn local.string/>

</cffunction>

In the load function we call getTemplateParams() to create our template params.


<cffunction name="getTemplateParams" access="public" returntype="string" output="false" >

<cfset var local = {}/>
<cfset var i = ""/>

<cfset arguments = getArguments(arguments)/>

<cfsavecontent variable="local.string">
<cfoutput>

<cfloop collection="#arguments#" item="i">

 <xsl:param name="#lcase(i)#"/>

</cfloop>

</cfoutput>

</cfsavecontent>

<cfreturn local.string/>

</cffunction>

Next we call getXSLTParams(). to actually invoke the xslt params

<cffunction name="getXSLTParams" access="public" returntype="string" output="false" >

<cfset var local = {}/>
<cfset var i = ""/>

<cfset arguments = getArguments(arguments)/>

<cfsavecontent variable="local.string">
<cfoutput>

<cfloop collection="#arguments#" item="i">
<xsl:if test="$#lcase(i)# != '' ">
  #lcase(i)# ='
</xsl:if>
</cfloop>

</cfoutput>
</cfsavecontent>

<cfreturn local.string/>

</cffunction>

And that should do it.

Accessing a queries row by row number

query.column[rownumber]

I was asked to do a task which involved a select box with arrows on each side of it. The arrows were to be used for the next and previous record in the select box when the page first rendered. For awhile I have wanted to use a query row number to access data directly and now get the chance to.

I used the current selected id and loop the query just got the above and below row from the current row by accessing the query struct.

Example:

<cfset the_id="124"/>
<cfset above_id=""/>
<cfset below_id=""/>

<cfloop query="the_query">

   <cfif the_id = the_query.id>

        <cfset above_id= the_query.id[currentrow + 1] />

        <cfset below_id= the_query.id[currentrow - 1] />
        <cfbreak />
   </cfif>
</cfloop>

javascript for each loop

I ran into an issue today with the javascript for each loop. The for each loop would run one extra time and return the value "each" for the variable "field" in example below.

Example 1
for(var field in array){
//some code
}

To solve this I switched up the for each loop to do it this way instead.

Example 2
array.each(function(field){
//some code
});

Not sure what the difference is between Example 1 and Example 2.