Daryl's ColdFusion Notes

SQL Server db_executor Role

2011-06-23T17:21:00.000-05:00

OK, so SQL Server still doesn't have a db_executor role.

Here's a way to add one to any given database:

 
if not exists (select * from sys.database_principals where name='db_executor' and type='R')
 create role db_executor

declare @name sysname
declare @sql nvarchar(250)

declare cur cursor for
 select name
 from sys.procedures
 order by name

open cur
fetch next from cur into @name
while @@FETCH_STATUS = 0 begin
 set @sql = N'grant execute on OBJECT::'+@name+N' to db_executor'
 print @sql
 exec sp_executesql @sql
 fetch next from cur into @name
end

close cur
deallocate cur

You'll need to re-run this every time you create a new stored procedure. You could expand on this to loop through all non-Master databases, or to use the DDL trigger feature of SQL Server 2005+, like this:

 
create trigger add_executor_role
on database
for create_procedure
as begin
 [sql from above goes here]
end

(For more on DDL triggers, see Using DDL Triggers in SQL Server 2005 to Capture Schema Changes)

CreateODBCDate Retains Time Data

2010-06-01T11:00:00.002-05:00

Had an interesting "WTF moment" over the weekend. I tried to prevent a batch job from running on Memorial Day by using the following code:


<cfif dateCompare(createOBDCDate(now()), "May 31, 2010") is 0>
    <cfabort />
</cfif>

But this failed to stop the job from running..? So I investigated further:


<cfoutput>
<cfset today = createOBDCDate(now()) />
#today#<br />
#createODBCDate("May 31, 2010")#<br />
#datecompare(today, "May 31, 2010")#
</cfoutput>

Which produced:


{d '2010-05-31'}
{d '2010-05-31'}
1

Okay... so those dates are different. WTF?


<cfoutput>
<cfset today = createOBDCDate(now()) />
#today#<br />
#timeformat(today)#<br />
#createODBCDate("May 31, 2010")#<br />
#datecompare(today, createODBCDate("May 31, 2010"))#
</cfoutput>

This returned:


{d '2010-05-31'}
12:11 PM
{d '2010-05-31'}
1

So... while CreateODBCDate() returns a formatted SQL date-only string, it invisibly retains the time information. Wow.

I wound up doing it the hard way:


<cfset today = createDate(year(now()), month(now()), day(now()))>

ListFirst, ListRest in SQL Server

2009-01-26T09:08:00.002-06:00

So, this has probably been done a million times before, but I needed ColdFusion-style ListFirst and ListRest functions for creating a stored procedure in a database that violates First Normal Form. <sigh/>

Note that the behavior varies slightly from CF. The delimiter is forced to be a comma, and multiple consecutive delimiters are treated as multiple elements. (CF eating 3 commas at a time always annoyed me.)

So, here's the DDL for them:


CREATE FUNCTION [dbo].[listFirst] (@list nvarchar(4000))
RETURNS nvarchar(4000)
AS BEGIN
   DECLARE @pos int
   DECLARE @ret nvarchar(4000)
   SET @pos = charindex(',', @list)
   IF @pos > 0
       SET @ret = left(@list, @pos-1)
   ELSE
       set @ret = @list
   RETURN @ret
END


CREATE FUNCTION [dbo].[listRest] (@list nvarchar(4000))
RETURNS nvarchar(4000)
AS BEGIN
   DECLARE @pos int
   DECLARE @ret nvarchar(4000)
   SET @pos = charindex(',', @list)
   IF @pos > 0
       SET @ret = substring(@list, @pos+1, len(@list)-@pos)
   ELSE
       SET @ret = ''
   RETURN @ret
END

As a side note, it just occurred to me that you can probably work around the multiple consecutive delimiter thing by adding whitespace, then trimming:


<cfloop list="#replace(myList, ",", ", ", "ALL")#" index="item">
   <cfset item=trim(item) />
   ...
</cfloop>

Why didn't I think of that years ago?

Automatically Fix Case Sensitivity Issues

2009-01-21T13:56:00.004-06:00

When you're converting from Windows, with a case-insensitive file system, to just about any other operating system, you'll likely run into file naming issues.

If you have a file named MyInclude.cfm, and you try to use it in ColdFusion as <cfinclude template="myInclude.cfm" /> (note the lowercase "m"), this will work fine in Windows, but then fail on the new OS.

I've written a small template to automagically repair most of these problems. and you can download it here: http://www.cfprimer.com/download.cfm?ffFile=_referenceFixer.cfm

To plagarize mercilessly from its comments:


Purpose:

 Fix file and directory name references in files, for transitioning from Windows (with case
 insensitive filenames) to another OS (eg Linux) with case sensitive file names.

 NOTE: If you have two different files (perhaps in different directories) with the same name,
 but different case, this will not work as well as hoped.  (All references will get set to 
 one or the other case.)
 
Use:

 Place _referenceFixer.cfm in a document directory and load.
 Template will start from its current directory and proceed to find all file and directory 
 names in that directory and its subdirectories.
 
 If "updateFiles" is set to True, the files will be rewritten and references fixed.
 
 "fileExtensions" should be set to list the files whose references should be checked
 and (if updateFiles is true) fixed, eg. ".cfm,.html".
 
 Templates beginning with an underscore character ("_") will be skipped.
 
 Do NOT leave this on production servers..!

ColdFusion 8 Professional with Windows IIS7 64-Bit

2009-01-20T14:46:00.011-06:00

ColdFusion 8 will run on 64-bit Windows with a 64-bit JRE, but only if you pay for Enterprise. While the ColdFusion 8 32-bit installer will run on Windows 64, the IIS connector fails miserably. I worked around this problem by using the IIS Connector from the 64-bit installation with a ColdFusion instance running with its 32-bit installation.

Fist, install the "normal" ColdFusion 32-bit version, but select "Built-in web server (Development use only)" on the Configure Web Servers/Websites screen:

Next, install the 64-bit version of ColdFusion, with the following options:

On the "Install Type" screen, choose "Developer Edition":
On the "Subcomponent Installation" screen, install no complementary services.
On the Installation Directory screen, choose "ColdFusion64" (or any dir other than where you installed 32-bit ColdFusion)
On the Configure Web Servers/Websites screen, select "All IIS websites" (which is the default).

At this point, you may be able to hit http://127.0.0.1/CFIDE/administrator/index.cfm to configure your ColdFusion instance. If not, check the following items:

Run regedt32, and verify that the ImagePath attribute in the ColdFusion 8 Application Server service (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ColdFusion 8 Application Server) is set to "C:\ColdFusion8\runtime\bin\jrunsvc.exe"
Ensure that the "ColdFusion 8 Application Server" service is started. If there's a problem, open a command prompt, CD \Coldfusion8\bin, and run CFSTART. Look for errors.
Open C:\ColdFusion8\runtime\servers\coldfusion\SERVER-INF\jrun.xml. Search for "JRunProxyService"; make sure the "deactivated" attribute is "false" and that the "port" attribute is "51011". (If you need to fix either of these, ColdFusion must be restarted.)
Make sure the 64-bit connector is also pointing to 51011: in the file C:\ColdFusion64\runtime\lib\wsconfig\1\jrun_iis6_wildcard.ini file, the "bootstrap" entry should be set to "bootstrap=127.0.0.1:51011". If you need to fix this, IIS must be restarted.

Note that you'll never actually start the 64-bit instance, but it must be left in place because you're using its web server connector.

As a side note-- if you try to start ColdFusion 8 Professional in a 64-bit JVM (with either installation type), ColdFusion will revert to a "Developer" license.

Update: If the above doesn't work for you, see Jason Holden's Post on the same subject.

ColdFusion: Fast Query to File Export

2009-01-16T12:25:00.007-06:00

A semi-common data processing task is to take the result of a query and write it to a file. People usually do this in ColdFusion something like this:

<cfsetting requesttimeout="1200" enablecfoutputonly="yes">
<cfset tab = chr(9)>

<cfquery datasource="Northwind" name="qProducts">
  SELECT p1.ProductID, p1.ProductName, case when p2.productname> 'B' then p2.productName else null end productName2
  FROM Products p1
  CROSS JOIN Products p2
  CROSS JOIN Products p3
  CROSS JOIN Products p4
  WHERE p1.ProductName <> 'foo'
  ORDER BY p1.ProductID
</cfquery>

<cfloop query="qProducts">
  <cfset line = ProductID
        & tab & ProductName
        & tab & ProductName2>
  <cffile action="append"
        file="/out.txt"
        output="#line#"
        addNewLine="yes"
    >
</cfloop>
<cfsetting enablecfoutputonly="no">

This works fine for small result sets. In my case, however, I Cartesian-joined the Northwind "Products" table to itself a few times, so that this query returns 35 million rows; the page above runs for ~3 minutes on my laptop and then dies with a java.lang.OutOfMemoryError.

I'd previously written a set of ColdFusion tags that could access JDBC directly from ColdFusion and process the resultset one row at a time, thereby avoiding out-of-memory errors:

<cfsetting requesttimeout="1200" enablecfoutputonly="yes">
<cfset tab = chr(9)>

<!--- cf_jdbcQuery will return a java ResultSet object instead of a ColdFusion Query object --->
<cf_jdbcquery datasource="Northwind" name="qProducts"><cfoutput>
  SELECT p1.ProductID, p1.ProductName, case when p2.productname> 'B' then p2.productName else null end productName2
  FROM Products p1
  CROSS JOIN Products p2
  CROSS JOIN Products p3
  CROSS JOIN Products p4
  WHERE p1.ProductName <> 'foo'
  ORDER BY p1.ProductID
</cfoutput></cf_jdbcquery>

<cf_jdbcqueryloop query="qProducts">
  <cfset line = qProducts.ProductID
        & tab & qProducts.ProductName
        & tab & qProducts.ProductName2>
  <cffile action="append"
        file="/out.txt"
        output="#line#"
        addNewLine="yes"
    >
</cf_jdbcqueryloop>
<cfsetting enablecfoutputonly="no">

This ran for 20mins (until it hit the timeout),writing about 37MB to the output file. Still... 20 minutes? I think we can do better.

So, I wrote a Java class to do this in a very small loop, to see the difference in speed. The Java source file ("FastResultsetToFile.java") is this:

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
* @author Daryl Banttari
* Released to the public domain.  Do with it what you will!
*/
public class FastResultsetToFile {

  public static final byte[] CRLF = { 13, 10 };

  /**
   * Very quickly sends data from an any size query to an output file.
   *
   * @throws IOException
   * @throws SQLException
   */
  public static int exportResultsetToFile(ResultSet rs, String fileName, String delim, String charset) throws IOException, SQLException {
      int rowcount = 0;
      File file = new File(fileName);
      byte[] delimBytes = delim.getBytes(charset);
      byte[] CRLF = "\r\n".getBytes(charset);
      BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(file));
      try {
          ResultSetMetaData rsmd = rs.getMetaData();
          int numCols = rsmd.getColumnCount();
          // iterate over resultset
          while (rs.next()) {
              // write each column
              for (int i = 1; i <= numCols; ++i) {
                  String s = rs.getString(i);
                  if (s != null) {
                      out.write(s.getBytes(charset));
                  }
                  if (i == numCols) {
                      // end of row, write CRLF
                      out.write(CRLF);
                  }
                  else {
                      // write delimiter between data
                      out.write(delimBytes);
                  }
              }
              rowcount++;
          }
      }
      finally {
          if (out != null) {
              out.flush();
              out.close();
          }
          if (rs != null) {
              rs.close();
          }
      }
      return rowcount;
  }

}

To compile this, save the FastResultsetToFile.java to disk, then type "javac FastResultsetToFile.java". This will produce a file called FastResultsetToFile.class. Depending on how you have ColdFusion installed, the file should be placed in either:

\coldfusionmx\wwwroot\WEB-INF\classes

or

\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\classes

The ColdFusion page looks like this:

<!--- cf_jdbcQuery will return a java ResultSet object instead of a ColdFusion Query object --->
<cf_jdbcQuery datasource="Northwind" name="qProducts">
  SELECT p1.ProductID, p1.ProductName, case when p2.productname> 'B' then p2.productName else null end productName2
  FROM Products p1
  CROSS JOIN Products p2
  CROSS JOIN Products p3
  CROSS JOIN Products p4
  WHERE p1.ProductName <> 'foo'
  ORDER BY p1.ProductID
</cf_jdbcQuery>

<cfset application.fastFileWriter = createObject("java", "FastResultsetToFile")>
<cfset application.fastFileWriter.exportResultsetToFile(qProducts, "/out.txt", chr(9), "UTF-8")>

Done.

This completes, writing all 35,153,041 rows into a 1.3 GB file in about 5 minutes. Not bad! If we'd let cf_jdbcQueryLoop complete, it'd have taken nearly 12 hours (based on time and file size ratio).

While you can do a lot right inside ColdFusion, it's important to remember that it's not the right tool for every job.

Multiple ColdFusion Instances NEQ "Faster"

2009-01-16T10:39:00.000-06:00

There's an oft-cited technote indicating that running with multiple instances somehow performs much better than running with a single instance, but no testing methodology is cited on the technote.

In my experience load testing, in the general case, the opposite is true-- you get better performance with fewer instances. In the specific case where you're doing a very large number of very small requests, the single-threaded nature of the datasource connection pool becomes a bottleneck, but it's easier (and less resource intensive) to work around that specific issue by using a modest number of identically-configured datasources, and choosing a datasource randomly at the start of the request.

If you're actually memory constrained, then you're better off using a single large 64-bit instance vs. a bunch of small 32-bit instances.

(I proved via load testing to one customer that their app had 10-15% better throughput when three of their four instances were disabled, and the techie that had pushed for Enterprise + multiple instances said, and I quote, "I don't believe you." Believe? A result that changes consistently and predictably when one variable changes somehow involves "belief"? What's happened to /science/ in this country?)

Oracle Connection Lock Timeout and ColdFusion 5

2005-06-25T10:13:00.000-05:00

If you're getting Connection Lock Timeouts or just generally poor performance with ColdFusion 5 using Oracle OCI drivers, here's why:

When ColdFusion 5 was being released, there was a bug in the Oracle OCI driver set that made simultaneous execution of multiple statements quite unreliable. (However, retrieving multiple resultsets could be done concurrently; this only applies to the time between when a statement is submitted for execution and when the first result row is available.) In the interest of stability, ColdFusion was made to single-thread it's access to that part of the driver; however, an undocumented switch was left behind for when that Oracle bug was fixed.

If you're running the OCI driver set, version 8.1.7 or newer, then you can remove this restriction.

The undocumented registry key that can be added to probably eliminate the "Oracle Connection Lock Timeout" and "Oracle Statement Lock Timeout" errors is:

HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\Server\ UseApplicationLockingForOracle8

Create this key and set it to a STRING value of "0" (zero) to remove statement locking. This feature is only available with ColdFusion 5, and the lock only applies versions of ColdFusion prior to ColdFusion MX. On Unix versions of ColdFusion 5, you need to stop ColdFusion, then carefully edit the cf.registry file to add this key to the correct place. (Or just use CFREGISTRY to add the key, then restart cfserver.)

JRun Clustering with Windows 2003 NLB

2005-06-22T11:07:00.000-05:00

There are a couple of tricky points to clustering ColdFusion or JRun with Windows Network Load Balancing enabled.

First, see Brandon Purcell's article for general information on JRun clustering with ColdFusion 6.1:
http://www.bpurcell.org/viewContent.cfm?ContentID=121

For ColdFusion 7 in Multi-Instance mode, use the ColdFusion Administrator to create the instances to cluster. Remember, each instance in a cluster has to have a unique name; I like to use the last part of the IP address in the name, e.g. "cfusion66". Once all instances are created, choose one ColdFusion 7 server to be your "cluster manager", and use the Administrator on that machine to create the cluster by registering the remote instances running on the other machines, then creating the actual cluster. Once that's done, you must restart all instances involved in the cluster, then use the WSCONFIG utility to re-install the Web server connector on every Web server involved. In order to access the "cfusion" instance to manage instances and clusters after that, you must go to the jrun4/servers/cfusion/cfusion-ear/cfusion-war/WEB-INF/jrun-web.xml and add an entry to point to your CFIDE folder, which may look like this:

<virtual-mapping>
<resource-path>/CFIDE</resource-path>
<system-path>C:/inetpub/wwwroot/CFIDE</system-path>
</virtual-mapping>

Then, you can access the cluster-manager ColdFusion 7 Administrator by pointing your browser to port 8300 on the server you want to manage.

I found the easiest / most reliable way to get this working is to use one interface in Multicast mode, and if necessary, add a static ARP entry to the router:

"Some routers require a static ARP entry because they do not support the resolution of unicast IP addresses to multicast media access control addresses. For example, Cisco routers require an ARP (address resolution protocol) entry for every virtual IP address. While Network Load Balancing uses Level 2 Multicast for the delivery of packets, Cisco's interpretation of the RFCs is that Multicast is for IP Multicast. So, when the router doesn't see a Multicast IP address, it does not automatically create an ARP entry, and one has to manually have to add it on the router."

(Source: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/clustering/nlbbp.mspx)

If you DO have two interfaces, make sure they're both attached to the same LAN segment, and that the NLB interfaces are the secondary interfaces.

If they're not on the same LAN segment, then the cluster will usually fail to synchronize with the other members, and you'll have severe problems getting session failover working reliably.

If the NLB interface is left as the primary interface, then connections to the JNDI port fail (the TCP connections are closed as right after they are opened, with no actual data transferred), and the instance manager shows "network error" for remote instances.

With multiple interfaces active, it may also be necessary to add one or more UnicastPeer attribute(s) in the ClusterManager section of jrun4\servers\{instance}\SERVER-INF\jrun.xml

That being said, I found that disabling the secondary NIC and using Multicast mode worked well, each and every time, without having to do anything beyond registering the remote instance and adding it to the cluster.

Thanks to Matt Stevanus for finding the network order dependency.

"Daryl" neq "Macromedia"

2005-06-20T10:26:00.000-05:00

Last Friday, 17 June 2005, was the final day of employment for me at Macromedia. On that day, I received a Fedex box containing my final paycheck, and my 5-year "thanks for staying" watch.

Seems someone at Macromedia is not without a sense of irony.

I'm now working for Webapper Services, LLC, the makers of SeeFusion, an excellent tool for monitoring what's going on inside your ColdFusion server at any given moment. Initially, I'm going to be doing onsite consulting, but the plan is that I'm going to get more and more involved in the SeeFusion product line, which I very much look forward to.

Macromedia Announces Plan to Make ColdFusion Harder

2005-04-01T11:11:00.000-06:00

In an effort to compete 'on a more level playing field', Macromedia announced today that it would be making ColdFusion substantially more difficult to use.

This unusual move was prompted, said Damon Jordahl, a product manager at Macromedia, by ColdFusion's continuing bad reputation as a 'toy' language. 'We've been getting a bad reputation for years,' he said, 'because people would look at ColdFusion, produce a working database driven report page in a couple of minutes, and discard it as being way to easy and fast to be considered as a "serious" language.'

Larry Wall, founder of the Perl language, applauded the move. 'For the longest time, Perl has been alone as the world's only self-encrypting language. I'm hoping that Macromedia will give me some real competition in this space.'

The success of ASP.NET was also a factor in the decision, when interviews with ASP developers revealed that one of the main reasons they use the language is its inefficiency. One anonymous ASP developer was cited as saying, 'Well, it takes weeks to get anything useful done in ASP. With ColdFusion, I can get entire working sites done in hours or maybe a few days. Where's the job security?'

Complexity also provides a convenient barrier to entry. 'Let's face it', concluded Jordahl, 'we've been attracting the novice developer for years. People who had never before written a line of machine instruction in their life. And novice programmers just don't have the experience to make scalable sites. Half the time they don't even index their database tables, and that's just a recipe for scalability disaster. But the programmers and the DBAs don't get the bad reputation. People always want to blame ColdFusion. We think that adding substantial complexity to the language will exclude these entry level people from the game, and allow ColdFusion to get the good reputation it deserves.'

Macromedia plans to ship the newly obfuscated ColdFusion language in its next release version.

(Happy April Fool's Day!)

CFDirectory can be Slow with Many Files

2005-03-30T15:20:00.000-06:00

CFDirectory can be rather slow when working with directories containing very many files (as in, thousands or tens of thousands of files.)

The underlying method used, java.io.File.listFiles(), does not allow a Java application to pass a filter to the underlying operating system, but rather forces the application to filter the directory listing on a case-by-case basis by creating a FileFilter object.

If the thousands of file names exist across a network share, the problem is compounded by the added latency of the network file system.

If you're only looking for, say, a date stamp for a single file, the entire directory is iterated looking for the file, and only then are the details of the file made available to the ColdFusion application.

If you only want information about a single file, it's fairly easy to get that directly via direct calls to Java from within ColdFusion. Here is a ColdFusion function that will efficiently get the date stamp from a single file:

<cfscript>
function getFileDate(fn) {
    theFile = createObject("java","java.io.File");
    theFile.init(fn);
    if( theFile.exists() ){
        theDate=createObject("java","java.util.Date");
        theDate.init(theFile.lastModified());
        dateString = 1+theDate.getMonth() & "/" & theDate.getDate() & "/" & 1900+theDate.getYear() & " " & theDate.getHours() & ":" & theDate.getMinutes() & ":" & theDate.getSeconds();
    }
    else {
        dateString="";
    }
    return dateString;
}
function getFileSize(fn) {
    theFile = createObject("java","java.io.File");
    theFile.init(fn);
    if( theFile.exists() ){
        return theFile.length();
    else {
        dateString="";
    }
    return dateString;
}
</cfscript>

TCP/IP Efficiency

2005-03-30T14:05:00.000-06:00

Ran into an interesting problem the other day.

Seems that the TCP connection between JRun (which underpins ColdFusion) and its Web server connector can get somewhat overwhelmed by the bursty nature of ColdFusion pages.

(Normally, ColdFusion doesn't send any data back to the Web server until the page is completely finished, as the last line of a template could be a CFLOCATION or CFHEADER tag, and once the HTTP header is sent, it can't be altered nor retracted.)

Using Ethereal to analyze the communication, it became clear that the JRPP connections (JRPP is the protocol used for JRun's Web server connectors to talk to JRun itself) were stalling because of something called "Silly Window Syndrome" avoidance (see RFC 1122), when the receive TCP buffer at the Web server was getting quickly filled. The stalling was prevented by increasing the TCP max window size in Windows by making a few registry settings.

Here's the contents of the registry file that adjusts the TCP max window size to roughly 128k (instead of the default ~17k):

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"GlobalMaxTcpWindowSize"=dword:00020148
"TcpWindowSize"=dword:00020148
"Tcp1323Opts"=dword:00000003

This also helped speed up my Internet downloads for my home PC. I'd recommend applying the change to any Windows box with more than 256MB RAM.

For more on TCP options under Windows, see
this TechNet article.

Avoid Evaluate()

2005-03-30T13:46:00.000-06:00

The ColdFusion Evaluate() function is often used to resolve dynamically named variables, such as form variables.

The evaluate function, however, forces ColdFusion to compile the expression being evaluated every time you call it, which can be a strain on performance, especially since parts of the compilation process are necessarily single-threaded.

Almost all instances of Evaluate() can be avoided by using structure syntax; instead of writing:

<cfset value=evaluate("form.field#i#")>

You can write:

<cfset value=form["field#i#"]>

Which is much faster, and better yet, simpler.

This doesn't work quite so well when looping over a query, however. If you use structure syntax to access a query column, you must also specify the row number. So, where this would work:

<cfoutput query="q">
#evaluate("q.#fieldname#")#<br>
</cfoutput>

You'd have to use q.currentrow to get the correct row when avoiding evaluate():
<cfoutput query="q">
#q[fieldname][q.currentrow]#<br>
</cfoutput>

Update: 7Apr2005

Classes created for evaluate() [and de() and setVariable()] are cached on a literal string basis (sorta like cached queries work for the exact string of the query), so the performance impact isn't quite so bad, so long as the literal string for evaluate() doesn't change every time.