I always forget about this until I start working with ColdFusion and MSSQL again. You're plugging along, and suddenly a page that was previously working starts throwing an error:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver]Unsupported data conversion.
Relax, your code is (likely) fine. This typically happens when you add a column to your table and use "SELECT *" in the query. Somewhere along the line the list of columns is cached and since the list of columns you're selecting in the query hasn't changed ColdFusion chokes when what it receives doesn't match what it expects.
The fix couldn't be simpler – just toss a semi-colon at the end of your CFQUERY and watch your problems go away. If you make another change just remove the semi-colon.
So a query that looks like this:
<CFQUERY NAME="qryUsers" DATASOURCE="dsn">
SELECT * FROM users
</CFQUERY>
Becomes:
<CFQUERY NAME="qryUsers" DATASOURCE="dsn">
SELECT * FROM users;
</CFQUERY>
I fully expect that I'll be stumbling across this page in 6 months.
This is basically a port of the WordPress paginate_links function from PHP to ColdFusion, with a few enhancements. The original function was written by Michael D Adams (trac ticket).
Changes include the ability to control page size via a query string parameter, the ability to restrict the maximum page size (overriding the value of page_size) and a threshold (show_all_threshold) for displaying all pages. show_all_threshold is useful in cases where you may have a small number of pages and would like to display links to each of them.
Sample Usage
<cfparam name="url.pagenum" default="12" />
<cfparam name="url.nrecords" default="20" />
<cfoutput>
paginate_links(link_format="?pagenum=%PAGE%", end_size=2, mid_size=3, current=#url.pagenum#, total=260, page_size=#url.nrecords#)
#paginate_links(link_format="?pagenum=%PAGE%", end_size=2, mid_size=3, current=url.pagenum, total=260, page_size=url.nrecords)#
paginate_links(link_format="?pagenum=%PAGE%", end_size=2, mid_size=3, show_all_threshold=15, current=#url.pagenum#, total=350, page_size=#url.nrecords#)
#paginate_links(link_format="?pagenum=%PAGE%", end_size=2, mid_size=3, show_all_threshold=15, current=url.pagenum, total=350, page_size=url.nrecords)#
paginate_links(link_format="?pagenum=%PAGE%", end_size=2, mid_size=3, current=#url.pagenum#, total=300, page_size=#url.nrecords#
#paginate_links(link_format="?pagenum=%PAGE%", end_size=2, mid_size=3, current=url.pagenum, total=300, page_size=url.nrecords)#
</cfoutput>
Output
paginate_links(link_format="?pagenum=%PAGE%", end_size=2, mid_size=3, current=12, total=260, page_size=20)
« Previous12345678910111213Next »
paginate_links(link_format="?pagenum=%PAGE%", end_size=2, mid_size=3, show_all_threshold=15, current=12, total=350, page_size=20)
« Previous12…9101112131415…1718Next »
paginate_links(link_format="?pagenum=%PAGE%", end_size=2, mid_size=3, current=12, total=300, page_size=20)
« Previous12…9101112131415Next »
Code
<cffunction name="paginate_links" access="public" output="false" returntype="any" hint="Return links to paginated results, heavily based on paginate_links() <http://trac.wordpress.org/ticket/3159> from WordPress <http://wordpress.org/> by Michael D Adams <http://blogwaffe.com/>">
<cfargument name="link_format" required="YES" type="string" default="" hint="Link format. Use %PAGE% for the page number placeholder (required), %SHOW% for the paging size (optional)" />
<cfargument name="prev_next" required="YES" type="boolean" default="true" hint="Show previous/next links" />
<cfargument name="prev_text" required="YES" type="string" default="« Previous" hint="Text for 'Previous Page' link" />
<cfargument name="next_text" required="YES" type="string" default="Next »" hint="Text for 'Next Page' link" />
<cfargument name="end_size" required="YES" type="numeric" default="1" hint="How many numbers on either end including the end" />
<cfargument name="mid_size" required="YES" type="numeric" default="2" hint="How many numbers to either side of current not including current" />
<cfargument name="current" required="YES" type="numeric" default="" hint="Currently active page" />
<cfargument name="total" required="YES" type="numeric" default="1" hint="Total number of results. Number of pages will be calculated by total/page_size" />
<cfargument name="page_size" required="YES" type="numeric" default="20" hint="Number of results to show per page" />
<cfargument name="max_page_size" required="YES" type="numeric" default="50" hint="Maximum number of results to show per page, to prevent abuse." />
<cfargument name="show_all" required="YES" type="boolean" default="false" hint="Show all page numbers (ignore end_size, mid_size)" />
<cfargument name="show_all_threshold" required="YES" type="numeric" default="0" hint="Show all page numbers ONLY if the total number of pages is <= this value. 0 to ignore." />
<cfargument name="prev_class" required="YES" type="string" default="page-numbers pn-prev" hint="Class(es) to use for Previous Page link" />
<cfargument name="next_class" required="YES" type="string" default="page-numbers pn-next" hint="Class(es) to use for Next Page link" />
<cfargument name="current_class" required="YES" type="string" default="page-numbers pn-current" hint="Class(es) to use for Next Page link" />
<cfargument name="dots_class" required="YES" type="string" default="page-numbers pn-dots" hint="Class(es) to use for dots span" />
<cfargument name="page_numbers_class" required="YES" type="string" default="page-numbers" hint="Class(es) to use for page numbers" />
<cfargument name="return_type" required="YES" type="string" default="text" hint="Valid types are array, text" />
<cfscript>
var page_links = ArrayNew(1);
var link = '';
var n = 0;
var dots = false;
var num_pages = Ceiling( arguments.total / arguments.page_size ); // Total number of pages
// Sanity checks
if( arguments.end_size LT 0 ) {
arguments.end_size = 1;
}
if( arguments.mid_size LTE 0 ) {
arguments.mid_size = 2;
}
if( arguments.max_page_size ) {
arguments.page_size = Min( arguments.page_size, arguments.max_page_size );
}
// If we are within our show all threshold, enable the display of all pages
if( arguments.show_all_threshold GTE num_pages ) {
arguments.show_all = true;
}
// Add Previous Page link if current page is 2+, and we want to show next/prev links
if( arguments.prev_next AND arguments.current AND arguments.current GT 1 ) {
link = ReplaceNoCase( arguments.link_format, '%PAGE%', arguments.current - 1 );
link = ReplaceNoCase( link, '%SHOW%', arguments.page_size );
ArrayAppend(page_links, '<a class="#arguments.prev_class#" href="#link#">#arguments.prev_text#</a>' );
}
// Build internal page number links
for( n = 1; n LTE num_pages; n=n+1 ) {
if( n EQ arguments.current ) {
ArrayAppend( page_links, '<span class="#arguments.current_class#">#n#</span>' );
dots = true;
} else {
if( arguments.show_all OR ( n LTE arguments.end_size OR ( arguments.current AND n GTE arguments.current - arguments.mid_size AND n LTE arguments.current + arguments.mid_size ) OR n GT num_pages - arguments.end_size ) ) {
link = ReplaceNoCase( arguments.link_format, '%PAGE%', n );
link = ReplaceNoCase( link, '%SHOW%', arguments.page_size );
ArrayAppend( page_links, '<a class="#arguments.page_numbers_class#" href="#link#">#n#</a>' );
dots = true;
} else if( dots AND NOT arguments.show_all ) {
ArrayAppend( page_links, '<span class="#arguments.dots_class#">...</span>' );
dots = false;
}
}
}
// Add Next Page link if current page LT total, and we want to show next/prev links
if( arguments.prev_next AND arguments.current AND arguments.current LT num_pages ) {
link = ReplaceNoCase( arguments.link_format, '%PAGE%', arguments.current + 1 );
link = ReplaceNoCase( link, '%SHOW%', arguments.page_size );
ArrayAppend(page_links, '<a class="#arguments.next_class#" href="#link#">#arguments.next_text#</a>' );
}
if( arguments.return_type EQ "array" ) {
return page_links;
} else {
return ArrayToList(page_links, "");
}
</cfscript>
</cffunction>
Another helper function for ColdFusion, this one to replicate PHP's array_merge which allows you to merge multiple arrays. In this case I'm only concerned with associative arrays, which ColdFusion calls structs.
CFMX provides StructAppend which will merge two (and only two) structures, optionally overwriting keys. I always want to overwrite keys, and I want to merge unlimited structures.
<cfscript>
function struct_merge() {
var base = {};
var i = 1;
for( i = 1; i LTE ArrayLen(arguments); i=i+1 ) {
if( IsStruct(arguments[i]) ) {
StructAppend(base, arguments[i], true);
}
}
return base;
}
</cfscript>Usage
<cfscript>
one = {
a = "a",
b = "b",
c = "c",
d = "d",
e = "e"
};
two = {
1 = "one",
2 = "two",
3 = "three",
4 = "four",
a = "one_SetByTwo",
c = "three_SetByTwo"
};
three = {
a = "one_SetByThree",
2 = "b_SetByThree"
};
new = struct_merge(one, two, three);
</cfscript>
<cfdump var=#new#>Result:
Almost anyone who has ever heard me talk about about ColdFusion has undoubtedly heard me say it sucks. It's slowly improving (CFMX 8 finally supports JavaScript-style arithmetic operators), but it has a ways to go. For instance it doesn't support ternary operators.
Normal if-else assignment loop
if( $today_is_saturday ) {
$weekend = true;
} else {
$weekend = false;
}Assignment using a ternary operator
$weekend = $today_is_saturday ? true : false;
But I digress. There is also no easy way to tell if a variable is empty, unlike PHP's empty() construct which will return true for any of the following:
- "" (an empty string)
- 0 (0 as an integer)
- "0" (0 as a string)
- NULL
- FALSE
- array() (an empty array)
- var $var; (a variable declared, but without a value in a class)
In ColdFusion you need a User Defined Function (UDF) to accomplish this, and I've written one that mimics this as closely as possible. CF doesn't have a null data type, and if it receives one it'll convert it to an empty string.
The Code
Copy and paste this somewhere inside your code and call it using empty(varName). The varName variable must be defined.
<cfscript>
function empty(val) {
/**
* Return TRUE if one of the following conditions
* for a defined variable is met:
* - A *trimmed* string is empty
* - An array or struct is empty
* - A query has a recordcount of 0
* - A bool is false
* - A number is 0
*
* For all other values including IsDate(testVar) it returns false.
*
* Similar to the php empty() function, www.php.net/empty
*
* @param val Variable to test. (Required)
* @return Returns a boolean.
* @author Corey Gilmore (http://coreygilmore.com/)
*
*/
if( IsSimpleValue(val) ) {
if( IsDate(val) ) {
return false; // no validation here
} else if( IsNumeric(val) ) {
return YesNoFormat(val EQ 0);
} else if( IsBoolean(val) ) {
return NOT YesNoFormat(val);
} else {
// assume string
return NOT YesNoFormat( Len(Trim(val)) );
}
} else {
if( IsArray(val) ) {
return NOT YesNoFormat( ArrayLen(val) );
} else if( IsStruct(val) ) {
return StructIsEmpty(val);
} else if( IsQuery(val) ) {
return NOT val.recordcount;
}
}
return false;
}
</cfscript>
