Service-Oriented Architecture (SOA)

I recently found a well-written article about Service-Oriented Architecture (SOA) from Microsoft. Although it is not directly related to ColdFusion, the article gives a nice guidance on how to architect applications to deal with service-based collaboration. 

Here's the link: http://msdn.microsoft.com/en-us/library/ff648490.aspx

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;