Shaun Mccran

My digital playground

26
A
U
G
2009

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.

view plain print about
1<cfscript>
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.

view plain print about
1<cffunction name="updateTask" access="remote" returntype="string" hint="Creates a record for timesheet tasks">
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.

TweetBacks
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
cfGothChic's Gravatar you could make the function even more universal if you passed the table name in as an argument
# Posted By cfGothChic | 26/08/09 17:38
Steve Bryant's Gravatar With apologies for hawking my own (free) wares, DataMgr will do this (and more) and will appropriately cfqueryparam each variable, ignoring keys that don't represent a column in the table.

http://datamgr.riaforge.org/
# Posted By Steve Bryant | 27/08/09 00:39
Shaun Mccran's Gravatar Thanks for the ideas, I hadn't thought of passing the table name in as a struct key. Also I'll check out the RIA forge project, will be interesting to see what you have put together.
# Posted By Shaun Mccran | 27/08/09 11:41
stella's Gravatar That was a nice tutorial on how to insert struct into a database that too using keys. I would love try this code for my http://www.myessay.biz/case-study/ blog. Recently i have started writing articles on coding. This was a good start to me.
# Posted By stella | 16/06/15 03:13
Fendi Replica's Gravatar He formed his way up the ladder http://www.careerbliss.co.uk beneath the application Monsieur Marchel, a box maker. Afterwards his afterlife in 1892, Vuitton’s son, Georges Vuitton took the cast to the next level, and created the acclaimed LV cipher book which we see so abundant of today. From then, the Louis Vuitton cast grew into apple acclaimed affluence covering and affairs brand. Today, the cast has broadcast above just affluence covering appurtenances but into ready-to-wear accoutrement and accessories for both men and women.You don’t accept to be a appearance enthusiast to admit a Louis Vuitton Replica bag. As one of the a lot of accepted cipher printed handbags out there today, Louis Vuitton http://www.prattvillelodge.org are classic, around-the-clock pieces of art. You are apprenticed to acquisition a bent bag for your affairs as they appear in a ambit of silhouettes and sizes. Prices alpha at about $950 for the Speedy 25.
# Posted By Fendi Replica | 18/06/15 19:50
site internet's Gravatar 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.
# Posted By site internet | 03/10/15 07:05
Rehabilitation's Gravatar 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.
# Posted By Rehabilitation | 07/10/15 05:53
marketing's Gravatar 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.
# Posted By marketing | 12/10/15 02:32
metal roof's Gravatar Get a wide range of packing and moving companies at helloenquiry.in. We endeavour to furnish the best packers and movers companies in your city at ease of Internet.....
# Posted By metal roof | 13/10/15 03:06
Employment Attorneys's Gravatar price enough for me. In my opinion, if all webmasters and bloggers made good content material as you did, the internet can be a lot more useful than ever before.
# Posted By Employment Attorneys | 18/10/15 05:35
Build My List 2.0 Bonus's Gravatar 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.
# Posted By Build My List 2.0 Bonus | 06/11/15 22:25
naturalhomecleaning.net's Gravatar Future often research about for the most effective curiosity charge. It doesn't really make a difference what type of home mortgage often shop about as well as review attention rates. There is actually one issue that 1 home loan could be distinct for an additional so feel the phrases as well as situations.
# Posted By naturalhomecleaning.net | 10/11/15 20:11
cccam server's Gravatar I want you to thank for your time of this wonderful read!!! I definitely enjoy every little bit of it and I have you bookmarked to check out new stuff of your blog a must read blog....
# Posted By cccam server | 18/12/15 23:45
cccam server's Gravatar Aan items binnen HRnetwerk.nl kunnen geen enkele rechten worden ontleend. Gebruik is op eigen risico, HRnetwerk.nl kan niet aansprakelijk worden gesteld voor het gebruik. Niet bestemd voor commercieel gebruik....
# Posted By cccam server | 22/12/15 04:21
buy real followers's Gravatar the table name in as a struct key. Also I'll check out the RIA forge project, will be interesting
# Posted By buy real followers | 23/12/15 05:30
Back to top