QoQ: Invalid Column Name Hack

I saw a question the other day about how to rename query columns with invalid names. Now in the poster's case, renaming really was not needed. Like most query ills it was solved with a simple bit of array notation: #queryName["column-name"][rowNumber]#

But in the process I did stumble upon another way to rename a column. In a pinch, you can use QueryAddColumn() to make a copy of it under a new name. Just pass in a reference to the query column in place of the array of values. Though this verges on the undocumented, it does work with both MX7 and CF8:

<cfset queryAddColumn(myQuery, "BetterColumnName", myQuery["Awful-Column-Name"]) />

Why does it work?
When you add a column with the QueryAddColumn() function, the values are passed in as an array. If you take a look at that function internally, you will see that the argument type is actually a java.util.List

<cfset funcObject = createObject("java", "coldfusion.runtime.QueryFunction")>
<cfdump var="#funcObject#" label="Query Functions">

Since ColdFusion arrays are java.util.List objects internally, that makes sense. But that also means that you can pass in any java.util.List object.

Now if you create a query, and examine one of its columns, you will discover the query column is also a java.util.List object. That means the QueryAddColumn() function will accept it the same way it would a ColdFusion array object.

<cfset myQuery = QueryNew("SomeColumn")/>

<cfoutput>
<b>Query Column Class:</b> #myQuery.SomeColumn.getClass().name#<br/>
<cfset interfaces = myQuery.SomeColumn.getClass().getInterfaces() />
<!--- Show the interfaces this class impements --->
<cfloop array="#interfaces#" index="inter">
     Implements: <cfdump var="#inter.getName()#" label="Interface"/><br/>
</cfloop>
</cfoutput>

So there you have it. Again, this is not documented behavior. But I thought it was an interesting find nonetheless.

.. Expand to Read Full Entry

OT: Null Characters Bring out the Beast

Once every blue moon, I am reminded of the fact that you cannot create a null character in ColdFusion. At least not in any of the ways you would expect.

When the moon rises, I quickly run through the logical possibilities chr(0), javacast("char", chr(0)), etcetera. Only to find they do not work. Each new attempt producing everything from a space character to an empty string. But no null character in sight.

As my frustration grows, my nails begin to lengthen. My flesh erupts in fur and my teeth transform into fangs. My nails gouge the keyboard as I begin to howl. Then I remember. There is a way. Somewhere in the tombs of google there is an ancient article on Null Characters in ColdFusion.

Searching google as quickly as my claws will allow, I find it. My fangs begin to recede. I try it. My fur transforms back into flesh. It works. I am safe for another few years.

Good thing too, because trying type with claws is pretty rough on the average keyboard. Well, I am off to the computer store to buy a new one. Maybe I will pick up two .. just in case.

(Yes, it has been that kind of a day. Grrrr)

.. Expand to Read Full Entry

ColdFusion: Experiment Converting MS Word to HTML/PDF - Part 2 Example

A brief addendum to Part 2. Here is an example of using a OpenXMLViewer.exe from a .bat file. Just update the directory to match the location of the OpenXMLViewer folder on your system.

Note, the cd (change directory) statement in the first line is important. If you have not added the OpenXMLViewer folder to your PATH environment variable, you must call the program from within the parent directory so it can locate the needed dependency files.

BAT File

@ECHO OFF
REM **********************************
REM %1 Full path to the source file *.docx
REM %2 Full path to output folder (ie directory not file name)
REM %3 Browser type (IE, FIREFOX or OPERA)
REM **********************************

cd % C:\tools\OpenXMLViewer\
OpenXMLViewer.exe %1 %2 %3

ColdFusion Code

<!---
Initialize file paths
--->
<cfset inputFilePath = ExpandPath("Introduction to Microsoft .NET Services.docx")>
<cfset outputFolder  = ExpandPath("test")>
<cfset pathToProgram = ExpandPath("openXMLViewerConvert.bat")>
<cfset browserType    = "FIREFOX">

<!---
OpenXMLViewer does not seem to create the output
folder if it does not exist. So ensure it exists
before doing the conversion.
--->
<cfif NOT DirectoryExists(outputFolder)>
<cfdirectory action="create" directory="#outputFolder#">
</cfif>

<!---
Do the conversion
arguments='/c "#pathToProgram#" "#inputFilePath#" "#outputFolder#" #browserType# 2>&1'
--->
<cfexecute name="c:\windows\system32\cmd.exe"
arguments='/c #pathToProgram# "#inputFilePath#" "#outputFolder#" #browserType# 2>&1'
timeout="120"
variable="result" />

<!---
Display the generated html file
--->
<cfdirectory action="list" directory="#outputFolder#" name="getHTMLFiles" filter="*html*">

<h3>Generated HTML</h3>
<cfoutput query="getHTMLFiles">
<a href="#ListLast(directory, '\/')#/#Name#">Display HTML (#Name#)</a>
</cfoutput>

<cfdump var="#result#" label="Results from Cfexecute">

.. Expand to Read Full Entry

ColdFusion: Experiment Converting MS Word to HTML/PDF (At Last) - Part 2

In a Part 1 I mentioned two interesting projects for converting docx files. The first one is the OpenXML Document Viewer Project, which can be used to convert docx files to html. The server version is a small executable that can be used with cfexecute. The small program accepts three arguments:

• source_file absolute path to the docx file to convert
• dest_path folder to place the converted files (html and images)
• browser_type used to generate a browser specific html file (IE, Firefox or Opera)

There is not much more to using the program than that. The installation is equally simple.

Installation:
1. Download the command line version for your operating system.
Example: OpenXMLViewer_Win_Cmd.zip

2. Unzip the files and copy the entire OpenXMLViewer subfolder to the desired location.
Example: I copied the subfolder to: c:\tools\OpenXMLViewer

3. If you are on windows, you must add the directory from step #2 to your PATH variable (or simply call the program from a .bat file instead). Due to some issues with the PATH value, I ultimately ended up using a .bat file.


Usage:
To use the converter, just initialize a few path variables then run the program with cfexecute.

Code:

<!---
 Initialize file paths
---> 
<cfset inputFilePath = ExpandPath("Introduction to Microsoft .NET Services.docx")>
<cfset outputFolder  = ExpandPath("test")>
<cfset pathToProgram = "C:\tools\OpenXMLViewer\OpenXMLViewer.exe">
<cfset browserType    = "FIREFOX">

<!---
 OpenXMLViewer does not seem to create the output
 folder if it does not exist. So ensure it exists
 before doing the conversion.
--->
<cfif NOT DirectoryExists(outputFolder)>
 <cfdirectory action="create" directory="#outputFolder#">
</cfif>

<!---
 Do the conversion
 arguments='/c "#pathToProgram#" "#inputFilePath#" "#outputFolder#" #browserType# 2>&1'
---> 
<cfexecute name="c:\windows\system32\cmd.exe"
 arguments='/c #pathToProgram# "#inputFilePath#" "#outputFolder#" #browserType# 2>&1'
 timeout="120"
 variable="result" />

<!---
 Display the generated html file
--->
<cfdirectory action="list" directory="#outputFolder#" name="getHTMLFiles" filter="*html*">

<h3>Generated HTML</h3>
<cfoutput query="getHTMLFiles">
 <a href="#ListLast(directory, '\/')#/#Name#">Display HTML (#Name#)</a>
</cfoutput>

<cfdump var="#result#" label="Results from Cfexecute">

I was pretty pleased with the results and was actually able to feed the html content into cfdocument to produce a reasonable facsimile in pdf format. Though that did take a while . I also noticed a few issues with some of those funky MS Word characters I know and love. I am still looking into how to fix that.

Convert to PDF:

<!---
because the .cfm script is not in the same directory
as the html file, first correct the relative image
paths for cfdocument
--->
<cfset pathToFile = ExpandPath("test/Introduction to Microsoft .NET Services.xhtml")>
<cfset content = Replace(FileRead(PathToFile), "word//media/", "test/word/media/", "all")>

<cfdocument format="pdf" filename="#ExpandPath('./convertedDocument.pdf')#" overwrite="true">
<cfoutput>#content#</cfoutput>
</cfdocument>

But overall the results were very good.

HTML / Source file: Introduction to .NET Services for Developers

PDF - With Funky Characters

Final Notes/Quirks:

1. OpenXMLConverter generates one type of file for Internet Explorer and another for Firefox and Opera. I believe the primary reason is because Word documents can contain vml. Internet Explorer is capable of displaying vml, but Firefox/Opera are not. So if you select the latter browser type, the vml is converted to svg.



2. From what I can tell OpenXMLConverter does not allow you to specify the name of the output file or the path to image directories. So unfortunately that means you must output the generated files to separate directories to avoid naming conflicts.
   


3. The OpenXMLConverter does not seem to create the output folder, if it does not exist. So you must ensure it exists before calling the program.
   

More about OpenXMLViewer and docx4j in Part 3.

.. Expand to Read Full Entry