CFSTOREDPROC beyond the docs
Tue September 8 '09 11:09 PM Category: ColdFusion , MSSQLA couple of weeks ago I underwent a level of frustration with ColdFusion that I think may people have endured. I was writing code that inserts values into a MSSQL database using a stored procedure and wrote a function that accepts parameters for both the insert and the update.
Generally the tag works goes something like this:
<!--- function inside of a getter cfc --->
<cffunction name='getrowwithid' access='remote' returntype='query'>
<cfargument type='numeric' required='yes' name='id' hint='I am the unique identifier'>
<cfstoredproc procedure='prc_getsomerows' datasource='#application.ds#'>
<cfprocparam cfsqltype='cf_sql_integer' value='#arguments.id#' null='id eq 0'>
<cfprocresult name='qryResult'>
</cfstoredproc>
<cfreturn qryResult/>
</cffunction>
What has been challenging to me is dealing with queries over the Java Data Base Connection (JDBC). Handling null values, working with no reference to the SQL procedure variables (@var) and working with exceptions from SQL. Recently I developed an application that required a name to be unique. This is quite normal in most enterprise level databases. There arose the question, when the name is a duplicate, how do we handle this?
- Should we return a string that says what the exception is and output the message to the end user? This seems kind of half assed since SQL will not log anything. How do we debug SQL errors?
- Should we throw the exception in SQL? Probably but the exception crosses over to a ColdFusion exception. How should we handle that?
- Should we use another procedure that returns zero results and do the logic in ColdFusion? So what happens when the database schema changes? We need to write more ColdFusion code AND SQL code.
I opted for a SQL exception and a function that procceses the exception inside of the cfcatch block. This could be logged on both the ColdFusion side and the SQL side. Now when we want to search the error for keywords and handle them accordingly we simply parse the ' [Macromedia][SQLServer JDBC Driver]' snippet from the error and output select messages (like this is a duplicate) and log everything.
I also have been dealing quite a bit of null values, which is common when attempting to build a query that serves many purposes. For instance if you want to do an add and update in the same procedure, you simply do not pass an id. As you see in the code above, the id is required and in the procedure tag I am checking for zero and passing null if the result is zero. This seems to overwrite the blank value so I can actually pass null instead of blank. Whenever I run this function for an id, null is zero. This will not work in every circumstance because not everything has a condition where you can pass it something as the identifier that means NULL. I am being strict, I know.
@CBSNews
@seattletimes
@GetColdFusiJobs
@CreativeTweet
@ptcentrum
@nytimes
@king5eveningmag
@cfbloggers
@Twitter_Tips
@coldfusion
Loading...
Recent Comments