Handling BLOBs with a CFC

I recently had to write a component to handle writing BLOB data to a database, and reading it back out. I don't think it deserves a project on RIAForge/etc, but think that it's worth posting the code here for anyone who may find themselves needing something similar.

This code was used to store images in a SQL Server database field defined as an "image" type, and has not been tested for other data types or against other DBMS', but should work just as well storing other binary formats (audio, etc), and in nearly any DBMS.

I'll post a breakdown of each function, and then list the entire contents of the cfc at the end.

Read on for the code...

Setup: First we need to define the component, and set some private data; namely the datasource, table name, primary key name, and BLOB field name.

<cfcomponent name="blob">     <!--- private data --->     <!--- set default values, override with setProps() --->     <cfset this.tbl            = "myTable">     <cfset this.keyName        = "primary_key_id">     <cfset this.blobName       = "blob_field_name">     <cfset this.dsn            = "cf_datasource_name"> ... </cfcomponent>

setProps(): Though we're setting default values for our private data, we want to allow the user to override these values to suit their needs.

    <!--- allow changing of default private data through a method --->     <cffunction name="setProps" access="remote">         <cfargument name="table"        required="yes">         <cfargument name="keyName"       required="yes">         <cfargument name="blobName"      required="yes">         <cfargument name="datasource"   required="yes">         <cfset this.tbl            = arguments.table>         <cfset this.keyName        = arguments.keyName>         <cfset this.blobName       = arguments.blobName>         <cfset this.dsn            = arguments.datasource>     </cffunction>

addBlob(): This function updates this.blobName in this.table where this.keyName matches arguments.keyValue. Or in laymans terms, it takes as input the BLOB data, and the unique identifier for the record where you want to add your BLOB, and adds it (overwriting anything that may already exist there). Returns TRUE on success, or FALSE on error.

    <!--- insert the blob (via update) --->     <cffunction name="addBlob" access="remote">         <cfargument name="keyValue"        required="yes">         <cfargument name="blob_data"       required="yes">         <cftry>             <cfquery name="addBlob" datasource="#this.dsn#">                 update      #this.tbl#                 set         #this.blobName# = <cfqueryparam CFSQLType="CF_SQL_BLOB" value="#arguments.blob_data#">                 where       #this.keyName# = #arguments.keyValue#             </cfquery>             <cfcatch type="any">                 <cfreturn false> <!--- return false on error --->             </cfcatch>         </cftry>         <cfreturn true>     </cffunction>

nullifyBlob(): We need a function to remove the BLOB, of course. Simply pass in the primary key value, and it will return TRUE on success, or FALSE on error.

    <!--- NULLify the blob --->     <cffunction name="nullifyBlob" access="remote">         <cfargument name="keyValue" required="yes">         <cftry>             <cfquery name="nullifyBlob" datasource="#this.dsn#">                 update       #this.tbl#                 set          #this.blobname# = NULL                 where        #this.keyName# = #arguments.keyValue#             </cfquery>             <cfcatch type="any">                 <cfreturn false> <!--- return false on error --->             </cfcatch>         </cftry>         <cfreturn true>     </cffunction>

getBlob(): Last, but certainly not least, we need a method to get the BLOB data back out of the database. This function takes only the primary key value of the record you want to retrieve a BLOB from, and returns a STRUCT containing two keys: success which will indicate whether or not there was an error (TRUE or FALSE), and BLOB which contains the BLOB data, or an empty string if there was an error.

    <!--- return blob --->     <cffunction name="getBlob" access="remote">         <cfargument name="keyValue" required="yes">         <cfset variables.retVal = StructNew()>         <cftry>             <cfquery name="getBlob" datasource="#this.dsn#">                 select       #this.blobname#                 from         #this.tbl#                 where        #this.keyName# = #arguments.keyValue#             </cfquery>             <cfcatch type="any">                 <cfset variables.retVal.success = false> <!--- return false on error --->                 <cfset variables.retVal.blob = "">                 <cfreturn variables.retVal>             </cfcatch>         </cftry>         <cfset variables.retVal.success = true>         <cfset variables.retVal.blob = evaluate("getBlob.#this.blobname#")>         <cfreturn variables.retVal>     </cffunction>

And so to bring it all together...

blob.cfc:

<cfcomponent name="blob">     <!--- private data --->     <!--- set default values, override with setProps() --->     <cfset this.tbl            = "myTable">     <cfset this.keyName        = "primary_key_id">     <cfset this.blobName       = "blob_field_name">     <cfset this.dsn            = "cf_datasource_name">     <!--- allow changing of default private data through a method --->     <cffunction name="setProps" access="remote">         <cfargument name="table"        required="yes">         <cfargument name="keyName"       required="yes">         <cfargument name="blobName"      required="yes">         <cfargument name="datasource"   required="yes">         <cfset this.tbl            = arguments.table>         <cfset this.keyName        = arguments.keyName>         <cfset this.blobName       = arguments.blobName>         <cfset this.dsn            = arguments.datasource>     </cffunction>     <!--- insert the blob (via update) --->     <cffunction name="addBlob" access="remote">         <cfargument name="keyValue"        required="yes">         <cfargument name="blob_data"       required="yes">         <cftry>             <cfquery name="addBlob" datasource="#this.dsn#">                 update      #this.tbl#                 set         #this.blobName# = <cfqueryparam CFSQLType="CF_SQL_BLOB" value="#arguments.blob_data#">                 where       #this.keyName# = #arguments.keyValue#             </cfquery>             <cfcatch type="any">                 <cfreturn false> <!--- return false on error --->             </cfcatch>         </cftry>         <cfreturn true>     </cffunction>     <!--- NULLify the blob --->     <cffunction name="nullifyBlob" access="remote">         <cfargument name="keyValue" required="yes">         <cftry>             <cfquery name="nullifyBlob" datasource="#this.dsn#">                 update       #this.tbl#                 set          #this.blobname# = NULL                 where        #this.keyName# = #arguments.keyValue#             </cfquery>             <cfcatch type="any">                 <cfreturn false> <!--- return false on error --->             </cfcatch>         </cftry>         <cfreturn true>     </cffunction>     <!--- return blob --->     <cffunction name="getBlob" access="remote">         <cfargument name="keyValue" required="yes">         <cfset variables.retVal = StructNew()>         <cftry>             <cfquery name="getBlob" datasource="#this.dsn#">                 select       #this.blobname#                 from         #this.tbl#                 where        #this.keyName# = #arguments.keyValue#             </cfquery>             <cfcatch type="any">                 <cfset variables.retVal.success = false> <!--- return false on error --->                 <cfset variables.retVal.blob = "">                 <cfreturn variables.retVal>             </cfcatch>         </cftry>         <cfset variables.retVal.success = true>         <cfset variables.retVal.blob = evaluate("getBlob.#this.blobname#")>         <cfreturn variables.retVal>     </cffunction> </cfcomponent>

Happy Blobbing.

Posted in ColdFusion June 11 2007

Please use label tags!

Please, please, PLEASE use label tags! Accessibility has been on a lot of people's minds for a while now, and I'm surprised to see the lack of zeal for the HTML label tag.

Recently, people have wised up and made entire blocks clickable, not just the text within them, sometimes doubling, tripling, (or more!) the size of the rectangle representing the link. Generally you recognize one of these menus when you hover over the menu item's row and it or the link changes color, and your cursor changes to a hand. This is a great step forward in usability, and it pains me to see the forms equivalent being neglected like the red-headed step child.

Take for example, your average navigation menu. Mine will do: over to the right there. --->

When you hover your mouse over a menu item's row (list item), the row changes background color, the link changes color, and the mouse cursor changes to the "hand" to indicate that you are over a link.

Think about it: A check box is only about 15 pixels tall by 15 pixels wide. That's a very small rectangle and not only requires extra precision on the part of your average user, but may prove to be difficult for someone with poor eyesight or arthritis. A radio button is even smaller. In most cases, your checkbox is going to have some text next to it that describes what checking it accomplishes. Wrapping that text in a label tag will only take you a few more seconds, and exponentially grows the clickable rectangle for that form control.

Try clicking on the text labels:

Add some CSS to change the cursor, and you're in good shape:

label { cursor: pointer; }

Try clicking on the text labels:

Your users and I thank you!

Posted in Best Practices | HTML/CSS June 08 2007

CFMenuCal: Configuration!

I just couldn't resist playing with all of the sexy AJAX tags and CFWindow. I couldn't. I can't play with new syntaxes and not the new tags. So I put aside working on side-dish proximity checking and instead worked on this beautiful screen:

Click to enlarge

There is plenty of functionality in there that doesn't work, or works incorrectly. But the skeleton is there, and you can get an idea of where I'm headed with it. The code has been committed to the repository, but the demo has not been updated. So if you're eager to see it up close and personal, check it out! (Literally!)

I promise I will work on side dish proximity next, so that I can go ahead and call it an alpha release.

Promise.

Posted in AJAX | CFMenuCal | My projects | Scorpio June 07 2007

I’ll see you at CFUnited!

Today was a good day! The heavens parted and the CFGods shined a ray of light down on me, and fixed my project schedule, so now I can go to CFUnited! I've lined up a free place to crash, registered, and signed up for the lectures I want to attend.

I'm so new to the "scene" of CF Developer Bloggers that I wonder if anyone will even know who I am. Not that it will matter while I'm sitting in a corner alone drinking my beer perfecting my I wish I was a people-person stare. If you've got the guts, and you happen to recognize me, do say hello. I promise I won't bite.

Posted in CFUnited 07 | ColdFusion June 05 2007