Wednesday, July 9, 2008

Coldfusion CustomTag for Query batch processing

I have created my first coldfusion custom tag. The purpose of this tag to open the query batch processing feature provided by JDBC to the coldfusion developer. It is just build over the JDBC prepared statement. I tried to make it as close as regular coldfusion tags, so that their won't be much confusion in using the tag.
The sample code to use the custom tag is shown below:

<cf_preparedstmt datasource="webrequest" query="insert into Employee(id, name) values(?,?)">
<cf_batch>
<cf_batchparam position="1" value="26" type="INT">
<cf_batchparam position="2" value="name1" type="STRING">
</cf_batch>
<cf_batch>
<cf_batchparam position="1" value="27" type="INT">
<cf_batchparam position="2" value="name2" type="STRING">
</cf_batch>

</cf_preparedstmt>


The usage of this tag is almost similar to cfquery or cfstoredproc.

The source code for all the three tags are available here.

1. cf_preparedstmt:
<cfif thisTag.ExecutionMode is 'start'>

<!--- Arribute validation --->
<cfif not StructKeyExists(Attributes, "datasource") or len(Attributes.datasource) eq 0>
<cfthrow message="Attribute datasource missing in cf_preparedstmt tag">
</cfif>
<cfif not StructKeyExists(Attributes, "query") or len(Attributes.query) eq 0>
<cfthrow message="Attribute query missing in cf_preparedstmt tag">
</cfif>
<cfif StructKeyExists(Request, 'preparedstatement') and len(Request.preparedstatement)>
<cfthrow message="Nesting cf_preparedstmt tag is not allowed">
</cfif>

<!--- Create Connection and PreparedStatment --->
<cfscript>
factory = createObject("java", "coldfusion.server.ServiceFactory");
connection = factory.getDataSourceService().getDataSource(Attributes.datasource).getConnection();
Request.preparedstatement = connection.prepareStatement(Attributes.query);
</cfscript>

<cfelseif thisTag.ExecutionMode is 'end'>

<!--- Execute and close the resource --->
<cfscript>
Request.preparedstatement.executeBatch();
Request.preparedstatement.close();
Request.preparedstatement = "";
</cfscript>

</cfif>


2. cf_batch:
<cfif thisTag.ExecutionMode is 'start'>
<cfif not StructKeyExists(Request, 'preparedstatement')>
<cfthrow message="cf_batchparam tag should be used inside cf_prepatedstmt tag">
</cfif>

<cfelseif thisTag.ExecutionMode is 'end'>
<cfscript>
Request.preparedstatement.addBatch();
</cfscript>
</cfif>


3. cf_batchparam
<cfif thisTag.ExecutionMode is 'start'>

<!--- Arribute validation --->
<cfif not StructKeyExists(Attributes, 'position') or not IsNumeric(Attributes.position)>
<cfthrow message="Attribute position should be numeric">
</cfif>
<cfif not StructKeyExists(Attributes, 'value')>
<cfthrow message="The Required Attribute value is undefined">
</cfif>
<cfif not StructKeyExists(Request, 'preparedstatement')>
<cfthrow message="cf_batchparam tag should be used inside cf_prepatedstmt tag">
</cfif>

<!--- set values --->
<cfswitch expression="#Attributes.type#">
<cfcase value="BOOLEAN">
<cfset Request.preparedstatement.setBoolean(Attributes.position, Attributes.value)>
</cfcase>
<cfcase value="BYTE">
<cfset Request.preparedstatement.setByte(Attributes.position, Attributes.value)>
</cfcase>
<cfcase value="SHORT">
<cfset Request.preparedstatement.setShort(Attributes.position, Attributes.value)>
</cfcase>
<cfcase value="INT">
<cfset Request.preparedstatement.setInt(Attributes.position, Attributes.value)>
</cfcase>
<cfcase value="LONG">
<cfset Request.preparedstatement.setLong(Attributes.position, Attributes.value)>
</cfcase>
<cfcase value="FLOAT">
<cfset Request.preparedstatement.setFloat(Attributes.position, Attributes.value)>
</cfcase>
<cfcase value="DOUBLE">
<cfset Request.preparedstatement.setDouble(Attributes.position, Attributes.value)>
</cfcase>
<cfcase value="BIGDECIMAL">
<cfset Request.preparedstatement.setBigDecimal(Attributes.position, Attributes.value)>
</cfcase>
<cfcase value="STRING">
<cfset Request.preparedstatement.setString(Attributes.position, Attributes.value)>
</cfcase>
<cfdefaultcase>
<cfthrow message="The Required Attribute type is undefined">
</cfdefaultcase>
</cfswitch>
</cfif>



To make use of these tags save the source code in the file preparedstmt.cfm, batch.cfm, batchparam.cfm respectively and place it under the customtags folder specified in the coldfusion administrator.

Please provide your comments.