ColdFusion: Taking Advantage of Java Classes

ColdFusion is built on top of Java; and it has plumbing to access Java classes. This is pretty cool. That means we can extend ColdFusion's capability.

Here is an example on how to utilize Java's Stack class. What we need to do is to create an object from Java class in question. Here, we created a Stack. As you can see, we can immediately call and utilize its methods.

<cfset objStack = CreateObject("java","java.util.Stack").init() />
<cfdump var="#objStack#" />

<cfdump var="#objStack.empty()#" />
<cfset objStack.push("a") />
<cfset objStack.push("b") />
<cfset objStack.push("c") />
<cfdump var="#objStack.empty()#" />

<cfdump var="#objStack.peek()#" />

<!--- The top element is always one --->
<cfdump var="#objStack.search('c')#" />

NOTE: Often, when we work with electronic payments, the third-party may require us to use certain algorithm for encryption. In this case, Java has more options to choose from than the ones already built in ColdFusion. So, take advantage of Java classes.

Reference:

• Java - Stack class
• Java - Cipher class

ColdFusion: Scale Attribute in <cfprocparam>

Want to insert a numerical value that contains decimal using <cfprocparam>, but the decimal portion is not showed up in the database? Most likely, we forgot to specify scale attribute. scale attribute is used to specify number of decimal places. If not specified, the default value is 0.

<cfstoredproc procedure="spuInsertBoxLength" datasource="#myDataSource#">
    <cfprocparam cfsqltype="CF_SQL_FLOAT" scale="2" value="123.45" />
</cfstoredproc>

Reference:

• cfprocparam

ColdFusion: Converting Records From Excel to Query

Have you ever needed to get data from Excel to ColdFusion to be manipulated? Before ColdFusion 9, reading records from Excel was cumbersome. <cfspreadsheet> makes the task handy. You can convert the records to spreadsheet object, query, CSV, or even HTML.

Here is an example on how to read an Excel spreadsheet and convert its rows to ColdFusion query.

<cfspreadsheet  
    action = "read" 
    src = "spreadsheet.xls" 
    sheet = "1"
    excludeHeaderRow = "true" 
    headerrow = "1" 
    query = "queryName"
    rows = "2-10" />
 
<cfdump var="#queryName#" />

Attribute rows is useful during development. Instead of reading all records in once, you can just output several records for testing/debugging. For more information on other attributes, please see the reference below.

Reference:

• cfspreadsheet

ColdFusion: returnCode Attribute in <cfstoredproc>

Have you ever wondered how to use returnCode attribute in <cfstoredproc>? If returnCode is set to true, it will return a status code from SQL RETURN statement. The status code could be an SQL error number, number of affected rows, or any integer number that is meaningful for you.

There are two ways to retrieve status code:

• cfstoredproc.statusCode
• result_var.statusCode --result_var is defined in result attribute

Even though both ways will generate the same result, it is better to use result attribute because if you dump the variable, the structure will give you an extra info. about caching.

Tip: Even though you do not have return code in your stored proc., turning on returnCode will not hurt. Later you can always go back to your stored proc. to add return code.

-- This stored proc. demonstrates how we could use RETURN statement.
CREATE PROCEDURE spu_customer_update
 @customer_id INT = 0
 , @email  VARCHAR(100) = ''
AS
BEGIN
 SET NOCOUNT ON;
 
 DECLARE @updated_rowcount INT = 0;
 
 IF @customer_id <> 0 AND @email <> ''
 BEGIN
  UPDATE customer
  SET email = @email
  WHERE customer_id = @customer_id;
  
  -- @@ROWCOUNT returns the number of rows affected by the last statement.
  -- If the UPDATE statement success, @@ROWCOUNT will be 1.
  SET @updated_rowcount = @@ROWCOUNT;
 END;
 
 RETURN @updated_rowcount;
END;

<cfstoredproc 
  procedure="spu_customer_update" 
  datasource="mydatabase"
  result="proc_result"
  returnCode="true">
 <cfprocparam cfsqltype="cf_sql_integer" value="12345" />
 <cfprocparam cfsqltype="cf_sql_varchar" value="myemail@email.com" />
 
</cfstoredproc>

<cfdump var="#proc_result#" />

References:

• @@ROWCOUNT (Transact-SQL)
• cfstredproc

T-SQL: Useful Tips

SCOPE_IDENTITY()	To retrieve identity value from the last inserted record, use SCOPE_IDENTITY() instead of @@IDENTITY.
EXISTS	To gain better performance, use EXISTS instead of IN. BONUS: SQL Server: JOIN vs IN vs EXISTS - the logical difference
SET NOCOUNT ON	Use it in a stored proc. It reduces network traffic by eliminating sending "done" messages to client.
@@ROWCOUNT	It returns number of rows affected by the last statement.
RETURN	It is usually used for returning status/error code. RETURN can be used to exit from a stored proc. Any statements that follow RETURN are not executed.
@@ERROR	It returns the error number for the last statement. Zero (0) means no errors.
ERROR_NUMBER()	The result of ERROR_NUMBER() is the same as @@ERROR. BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH -- All of these must be used inside CATCH. SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH;

T-SQL: Variable Assignment Using SET vs. SELECT

Use SET instead of SELECT to make a variable assignment because it is ANSI standard and recommended in SQL Server doc.

References:

• SET @local_variable (Transact-SQL)
• T-SQL: SET vs SELECT when assigning variables
• Differences between SET and SELECT in SQL Server

-- You could assign variable with SELECT.
DECLARE @total_rows INT = 0;
SELECT @total_rows = COUNT(*) FROM customer;
PRINT @total_rows;

-- However, it is better to use SET.
DECLARE @total_rows INT = 0;
SET @total_rows = (SELECT COUNT(*) FROM customer); -- Parentheses around SELECT statement is required.
PRINT @total_rows;

ColdFusion: Immediate If vs. Ternary Operator

ColdFusion 9 introduces a new operator called ternary operator. Unlike iif(), ternary operator looks cleaner and less confusing.
See the snippet below. Pay attention on the quotes.

<cfparam name="condition" default="true">

<cfset var_1 = "TRUE">
<cfset var_2 = "FALSE">

<cfoutput>
 <!--- Use de() and quote will not evaluate the variable --->
 immediate if: #iif( (condition), de('var_1'), de('var_2') )#
 <br />
 <!--- Use quote 'var_1' or de(var_1) will evaluate the variable --->
 immediate if: #iif( (condition), 'var_1', 'var_2' )#
 <br />

 <!--- Use quote 'var_1' will not evaluate the variable --->
 ternary operator: #( (condition) ? 'var_1' : 'var_2' )#
 <br />
 <--- No quote var_1 will evaluate the variable --->
 ternary operator: #( (condition) ? var_1 : var_2 )#
</cfoutput>

ColdFusion: argumentCollection vs. attributeCollection

Passing arguments/attributes via collection is very handy. However, you might get an unexpected error. This error could be caused by using argumentCollection or attributeCollection incorrectly.
See the code below and read the comments. I hope that helps! :)

<!--- Define a function to accept string and return it back to the caller --->
<cffunction name="echo" returntype="string" output="false">
 <cfargument name="aString" type="string" required="false" default="" />
 
 <cfreturn toString(arguments.aString) /> 
</cffunction>

<!--- Create a structure to hold an argument that will be passed to echo() --->
<cfset args_echo = structNew() />
<cfset args_echo.aString = "Hello World!" />

<!--- Call echo() and pass the argument via argumentCollection --->
<!--- NOTE: Notice that the argument name is "argumentCollection", instead of attributeCollection.
 In other words, if it is a function/method then use argumentCollection. --->
<cfoutput>#echo(argumentCollection=args_echo)#</cfoutput>


<!--- Create a structure implicitly (require CF8 and up); and declare attribute name and its value --->
<cfset args = {var="#args_echo#", label="args"} />

<!--- Call <cfdump> and pass the attributes via attributeCollection --->
<!--- NOTE: Notice that the attribute name is "attributeCollection", instead of argumentCollection.
 In other words, if it is a tag then use attributeCollection. --->
<cfdump attributeCollection="#args#" />

Blogger: Syntax Highlighting

I use SyntaxHighlighter by Alex Gorbatchev to display source code in my blog. Here is a snippet to display ColdFusion code. Place the code in <pre> tag and replace all < character with <. That's it!

<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shCore.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shAutoloader.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shBrushColdFusion.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shBrushCss.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJScript.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPlain.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shBrushSql.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shBrushXml.js"></script>

<link rel="stylesheet" type="text/css" href="http://alexgorbatchev.com/pub/sh/current/styles/shCore.css"></link>
<link rel="stylesheet" type="text/css" href="http://alexgorbatchev.com/pub/sh/current/styles/shThemeDefault.css"></link>

<script type="text/javascript">
    SyntaxHighlighter.config.bloggerMode = true;
    SyntaxHighlighter.all();
</script>

<pre class="brush: coldfusion">
    <cfoutput>#DateFormat(Now(), "mm/dd/yyyy")#</cfoutput>
</pre>