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.
June 28 2007 by 
Adam
