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.

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.

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.

I'm always asking what peoples phone numbers are in the office, we currently don't have any internal communications (like an intranet) so I thought I'd create a handy phone book application in AIR.

With FLEX of AIR applications I often wireframe them up with the data model in mind first. If you know what data you are going to display, and the format and delivery mechanism of that data, it can often have a large impact on the design and layout of your application.

In this instance I was just returning a simple query object of users and their phone numbers and a thumbnail image.

The CFC

My preferred server language is ColdFusion, so my service is a CFC object.

In my example I'm using an MS SQL database, so I have included the creation script here:

Now that we know what the data will look like we can build the GUI front end.

My display layer is going to be a canvas, with another canvas inside it, to create a bordered effect.

Then I have a DataGrid, with a click event that will call an AS function. This will control the displaying of an image that corresponds to the user being clicked. Its always nice to see who you want to call!

My 'userImage'has a showEffect attribute, that uses an image fadeIn method. It fades in the first image called, but not any others, I've had a play around with this, and I can't get it to fade in subsequent images, so if anyone has any ideas let me know!

Lastly I have added some chrome controls, as I will be removing the standard chrome, and building my own.

Now, on to the functions.

This article deals with creating a SQL stored procedure for Updating a record.

In each of these stored procedure templates I am declaring a variety of documentation parameters in the header.

I've found these handy in the past when you are working in a team environment, or when you go back to a procedure at a later date. Its much easier to read a simple description in the header, than trawl through the SQL code looking for what it is doing.

So, this declares the procedure name, any parameters and return codes, and also details what it does, and who made it.

In a modified version of this I also hold the SVN revision number here.

The next block of code performs a select on the sysObjects table (part of the Master database). It is checking for the existence of itself. If it finds itself, it will drop the procedure. Note that throughout all of these scripts we are telling the user at each stage what is going on, by printing useful english output back to the screen.

By now we have identified whether or not the procedure previously existed, and if it did, we have dropped it, so we know that we are all good to go. So to create our Update procedure, we print out a message to the user, then using the "CREATE PROCEDURE" command we create our procedure.

At this point you substitute the "@field" value with your field name, and the [datatype] and (datasize) with the correct values. Just list your fields one after another, seperating with a comma. As this is creating an Update stored procedure I will list any of the values to update in the query here.

After that we create the SQL code, as per usual. We have an Update statement, using the variables declared above in the SQL variable declaration (@var). Just write out your update like you normally would here. Then we check for any errors, and return a success message if it all worked ok!

By using a script like this I've found that its really simple to have a repeatable standard process that is easy to implement across a team of developers, ensuring you get the same results, no matter who writes the query. It is also very useful if you have a seperate implementation team, as these scripts are re-runnable, they clear up after themselves.

Download the full template here.