|
Example of inserting a Struct() into a database using keys |
||||||||
A while ago a colleague and I were working on a timesheet application in Flex. The idea was that you could commit a custom timebar object, generated in flex, and it would update the dataset in the back end using the ColdFusion flex gateway.
I came across the code recently, and decided to tidy it up a bit, and make the query dynamic, based on the Struct contents. The obvious limitation to this is that your Struct and your database schema have to match exactly.
I won't go into the Flex application here, but I've emulated its input arguments here with a pre-populated structure.
2 timesheetTask = StructNew();
3 StructInsert(timesheetTask, "employeeid", '36');
4 StructInsert(timesheetTask, "timesheetDT", '0');
5 StructInsert(timesheetTask, "projectid", '6');
6 StructInsert(timesheetTask, "weekid", '25');
7 StructInsert(timesheetTask, "taskid", '39');
8 StructInsert(timesheetTask, "hours", '8');
9 StructInsert(timesheetTask, "comment", 'Comments for this task live here');
10 StructInsert(timesheetTask, "szStatus", '1');
11 StructInsert(timesheetTask, "iFirstLineApproval", '23');
12 StructInsert(timesheetTask, "iSecondLineApproval", '34');
13 StructInsert(timesheetTask, "iCurrentApprover", '');
14 StructInsert(timesheetTask, "szRejectReason", '');
15 StructInsert(timesheetTask, "szDescription", '');
16
17 updateTimesheet = createObject("component", "timesheet");
18 updateTimesheet.updateTask(timesheetTask);
19</cfscript>
Notice that this code also calls the CFC object at the end. The data itself isn't massively important, it's a time object for recording tasks.
Next we have the function, which accepts a Struct() argument called 'taskStruct'. I then loop through the structure, and populate a SQL query using the keys from a collection. The only logic is a check to see if it is the last structure element, as this controls the ',' placement.
2 <cfargument name="taskStruct" type="struct" required="yes">
3 <cfset var count = 0>
4
5 <cfdump var="#arguments.taskStruct#">
6 <cfset variables.structSize = structCount(arguments.taskStruct)>
7
8 <cfquery datasource="#application.dsn#">
9 INSERT INTO [dbo].[timesheet]
10 (<cfloop collection="#arguments.taskStruct#" item="key">
11 [#key#]
12 <cfset count = count + 1>
13 <cfif count LT variables.structSize>,</cfif>
14 </cfloop>)
15
16 <cfset count = 0>
17
18 VALUES(<cfloop collection="#arguments.taskStruct#" item="key">
19 '#arguments.taskStruct[key]#'
20 <cfset count = count + 1>
21 <cfif count LT variables.structSize>,</cfif>
22 </cfloop>)
23 </cfquery>
24
25 <cfreturn true>
26 </cffunction>
That will insert your Struct into a database, in small and tidy manner. It was somewhere around here that we started using cfproperty tags, and creating strongly typed objects for Flex.
http://datamgr.riaforge.org/