If you ever had gotten stuck with writing insert and update statement for a table with a zillion columns, you can understand my pain.
And applications (most of them) must work with data. Select is cool, in fact, the dudes that crafted the SQL standards even created the almighty shortcut, *, which can select from 1 to a zillion columns. But how do you have a quick work around to update say a table with 150 columns? The truth, there is not shortcut. You have to do the grunt work.
ColdFusion has a CFUPDATE but I hate it like crazy. When your form has submit and cancel on it, you can imagine the hell. So I invented something. It might be a little crude but it works.
I crafted a function that takes a form struct, the table name and unique identifier. Right now, I only have a function that works with one identifier, extending it to support multiple identifiers won’t be a difficult task.
<cffunction name=“updateTable” returntype=“void” displayname=“Update Table”><cfargument name=“formStruct” type=“struct” required=“yes” hint=“This is the form object to be passed” /><cfargument name=“table” type=“string” required=“yes” hint=“This is the name of the table” /><cfargument name=“identifier” type=“string” required=“yes” hint=“This is the name of the unique identifier” /><cfargument name=“datasource” type=“string” required=“yes” hint=“This is the name of the datasource” />
<cfset var fs=arguments.formStruct />
<cfset StructDelete(fs,’fieldnames’) />
<cfloop index=“i” list=“#StructKeyList(fs)#”>
<cfquery name=“colType” datasource=“#arguments.datasource#”>
select name,xtype from syscolumns where name=’#i#’ and id=
(select id from sysobjects where name=’#arguments.table#’)
</cfquery>
<cfif colType.recordCount eq 1>
<cfquery name=“update” datasource=“#arguments.datasource#” result=“x”>
update #arguments.table# set #i#=
<cfif ListFind(‘173,175,61,34,239,99,231,58,35,167‘, colType.xtype) >
‘#StructFind(form,i)#’
<cfelseif ListFind(‘127,104,106,62,56,60,108,59,52,122‘, colType.xtype)>
#val(StructFind(form,i))+0#
</cfif>
Where #arguments.identifier#=’#StructFind(form,arguments.identifier)#’
</cfquery>
</cfif>
</cfloop>
</cffunction>
This function has only been tested on SQL Server 2000 and the present code can only work on SQL Server. The same concept can probably be extended to other databases for those who need to.
This is a proof of concept and it has not been secured against stuffs like, em, SQL injection and other bla bla.
