Ask a Jedi: Complete Spry CRUD Example

This post is more than 2 years old.

Walter asks:

I was wondering if you can help, I am trying to get my feet wet with Spry and CF, you have many blog posts that refer to this but I wanted to know if you can point me in (or have an example) of a : Add / List / Amend / Delete solution using Spry and CF, to a database. I have searched and cannot find help on this. I have managed to get CF and Spry to work, but just reading XML files, and am having a lot of trouble, getting Spry, ColdFusion and Database to play nicely.

Spry really shines when it comes to 'Get the data and display it' site, but if you want a full "back and forth" type example, you need to do a bit more work. I still think it's easy, but you are right, there isn't a lot out there that shows examples of that. Here is a quick one I whipped up. I've included the code in a zip and it should work fine out of the box as I didn't use a database. (That's the only hackish part of this - but I wanted something quick and dirty to play with.)

So let's start off with what our demo is going to cover. We have a database (again, not really) of products. Each product has an ID, Name, and price. Our RIA will:

  • List the products
  • Have a delete link
  • Have a form to let you update products
  • Let you add a new product

So let's first cover the basic listing of products, which is trivial in Spry. I wont get too detailed here as the main focus of this blog entry is on CRUD.

We begin by simply creating a dataset:

var baseurl = "data.cfc?method=getProducts&returnFormat=json&queryFormat=column"; var mydata = new Spry.Data.JSONDataSet(baseurl,{path:"DATA", pathIsObjectOfArrays:true,useCache:false}); mydata.setColumnType("price","number");

I'm pointing to a CFC named data.cfc. I'm using ColdFusion 8 so I can use the super-cool returnFormat support. I've specified that the price value is a number column. So far so good. Now let's look at the display.

<div spry:region="mydata"> <p> <table width="100%" border="1"> <tr> <th spry:sort="name" style="cursor: pointer;">Name</th> <th spry:sort="price" style="cursor: pointer;">Price</th> <th>Delete</th> </tr> <tr spry:repeat="mydata" spry:setrow="mydata" spry:select="red" spry:hover="hot"> <td style="cursor: pointer;">{name}</td> <td style="cursor: pointer;">{price}</td> <td><a href="javascript:deleteRecord('{id}')" onclick="return confirm('Are you sure?')">X</a></td> </tr> </table> <a href="javascript:clearForm()">Add Record</a> </p> </div>

<span spry:detailregion="mydata"> <form name="editform"> <input type="hidden" name="id" value="{id}"> Name: <input type="text" name="name" value="{name}"><br /> Price: <input type="text" name="price" value="{price}"><br /> <input type="button" value="Save" onclick="saveRecord()"> </form> </span>

We begin with a fairly typical table to list our products. The only thing special really is the delete link. I've tied that to a JavaScript function named deleteRecord. Don't worry about the Add Record link just yet.

Moving on down the page, the form works both as a display for the products as well as our way of editing information.

So if we ignore adding records, we can focus on deleting and editing. Let's talk about deleting first as it's simple. To delete a record, we simply want to make an AJAX request with the proper information. We used deleteRecord() to handle this, so let's take a look at that code:

function deleteRecord(id) { Spry.Utils.loadURL("get", "data.cfc?method=deleteProduct&id=" + id, false, reloadData()); }

I use Spry.Utils.loadURL to - obviously - load a URL. In this case I simply hit my same CFC and pass the ID to delete. The false in this case signifies that I want the call to be synchronous. Why? Well when I'm done I want to reload the data so it makes sense to ensure the back end is done deleting. I could have deleted the record from my local copy. That would be a bit quicker than getting all the data again. But I kinda figured this was a bit safer. (Frankly, I still feel very new to the AJAX world and I reserve the right to change my mind on what's "best" at any moment. :)

The last argument, reloadData(), simply says to run this function when the AJAX call is done. This function is rather simple:

function reloadData(){ mydata.loadData(); }

All this means is that Spry should reload the dataset. Note that when I created the dataset earlier, I specifically told Spry not to cache the results.

So that's deleting. For editing (and adding) I want to submit a form. If you remember, my form used a button that called saveRecord(). That JavaScript looks like so:

function saveRecord() { Spry.Utils.submitForm('editform',reloadData(),{url:'data.cfc?method=saveProduct'}); } </script>

This uses another built-in Spry function, submitForm, to send in a collection of form data. One thing to keep in mind when using this function - you must give your form, and your form fields, name values. You can't just use IDs.

So how do I support adding a record? All I did was write a simple JavaScript function to clear the form fields. Since the form was tied to Spry, I did this by setting the current row to an invalid value:

function clearForm(){ mydata.setCurrentRow(-1); }

I have some misgivings about this. It seems to work ok, but I'm not sure that what I've done is kosher.

So that's basically it. The CFC - as I said - is a bit of a hack. In order to make a demo that would work "out of the box", I used the Application scope to create fake data. Adding, Editing, and Deleting simply manipulates this application variable. My getProducts method converts the variable to a query object on the fly. Please keep that in mind if you play with this code. "Real" code would use a database instead.

Download attached file.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Alan posted on 5/13/2008 at 6:20 PM

Ray - search this post for "I've specific that price is a number column." and after you fix it you can delete this comment. :) Alan

Comment 2 by Raymond Camden posted on 5/13/2008 at 6:25 PM

Fixed. I'll keep your comment though. :)

Comment 3 by Roberto posted on 5/13/2008 at 6:52 PM

Spry is simply fantastic for manage data! :o)

But it still has got not much widgets (modal & draggable window, tree... ). :o(

I wish in the next version :o)

Comment 4 by Rick Smith posted on 5/13/2008 at 7:53 PM

Good stuff Ray... thanks!

In addition to the modal window, I'm hoping for collapsible panels from left to right in addition to the current top to bottom.

Comment 5 by Mike posted on 5/13/2008 at 7:55 PM

I agree window and modal would be nice but if your using cf8 you can always just import the cfwindow js and use that :)

Comment 6 by Edward Beckett posted on 5/13/2008 at 10:26 PM

Raymond ...

I'm jumping on the wagon with Alan

Ditto search for ...

"I want get too detailed here " ...

Comment 7 by Raymond Camden posted on 5/13/2008 at 10:34 PM

I must not have had enough coffee. ;) Thanks.

Comment 8 by Martin posted on 5/14/2008 at 3:42 PM

Hi Ray

This is a great post and something I've been wondering about too.Unfortunatley I am having trouble getting your code to work properly.

The selected row info is not appearing in the editForm detailRegion correctly. When I click on the first row (Y-Wing) I get the X-Wing details in the detail region, the other rows do not return anything in the detailRegion.

I have had this problem before, with my own code, and never found a solution. It was ONLY a problem with returnFormat = json and NOT with XML.

.... do you have any ideas????

I don't know if it's relevant but I am using CF8 developer edition running through Apache 2.2 on Vista.

Comment 9 by Raymond Camden posted on 5/14/2008 at 4:04 PM

Martin - what browser? Also - be sure Dreamweaver didn't muck with the code. DW likes to do that. ;)

Comment 10 by Martin posted on 5/14/2008 at 4:12 PM

Woah! That's frreaky ... how did you know I use DW???? You really ARE a Jedi!!!!

I am testing in Firefox 2.0 and I have Firebug installed too.

FWIW I actually like to manually write the code in DW rather than get DW to do it for me.

[off topic] <whinge>I am using DW MX 2004 which doesn't have built in Spry support anyway. I refuse to pay the UK upgrade price because it's more than double the US price</whinge> hee hee.

Comment 11 by Raymond Camden posted on 5/14/2008 at 4:17 PM

Odd - I thought I saw you say DW.

Check your Spry libraries. Make sure they 1.6.1. I didn't include the Spry files as I assumed folks would have them.

Comment 12 by Martin posted on 5/14/2008 at 4:30 PM

AHA!! My library was only 1.6
Upgrading to 1.6.1 (2008/02/23) fixed the problem - cool!

...probably should have been able to fix that myself really (hanging head in shame..)

Thanks Ray.

Comment 13 by Raymond Camden posted on 5/14/2008 at 4:33 PM

Heh, well I tend to always assume folks are running the latest, but it wouldn't have hurt if I had bothered to mention it. ;)

Comment 14 by michael grove posted on 6/3/2008 at 11:08 PM

Not sure where to post this spry question. We have been trying to impletent 2 spry fade efects together to create a lightbox effect. Basically onclick opens 2 hidden divs using the fade effect. That part works fine. Closing them is the issue. if you press the close link to reverse the effects it takes the divs back to 0% but does not restore the hide value. Am I missing somthing. http://www.idzyns.com/index...

Comment 15 by Massimo Foti posted on 6/4/2008 at 3:44 PM

Sorry to be so late to the party. I've had a full CRUD CF/Spry demo app available on my website since quite some time. I used it for my presentations (conferences and user groups). You can grab it from here:
http://www.massimocorner.co...

There is also another demo that adds a "photocart" to Spry's picture gallery.

Hope it would help

Massimo

Comment 16 by Paul posted on 6/17/2008 at 1:39 PM

Can you add an argument to the JSON method call, e.g. (see uppercase ARGUMENT in below code.

var mydata = new Spry.Data.JSONDataSet("model/sale/saleGateway.cfc?method=getJSONSalesByCity(ARGUMENT)&returnFormat=json&queryFormat=column",
{path:"DATA", pathIsObjectOfArrays:true});

Tried it...didn't work..help appreciated.

Comment 17 by Martin posted on 6/17/2008 at 5:02 PM

@John

Sure, you can add an argument to your query string.
Make sure your cfc has access="remote"
I would also add that if you are exposing a cfc as a web service then Ray wrote a great article on Ajax security ... which I can't seem to find just now (oops) - maybe he can chime in with the URL.

Martin

Comment 18 by Martin posted on 6/17/2008 at 5:04 PM

@Paul

oops sorry that was for you (not John)... trying to do too many things at once.... if only I was able to multi task like Paris Hilton :-)

Martin

Comment 19 by Raymond Camden posted on 6/17/2008 at 9:15 PM

@Martin - I have no idea where it is. Adobe.com maybe?

Comment 20 by Paul posted on 6/17/2008 at 11:56 PM

Cheers Martin. What is the syntax for adding the argument?

Comment 21 by Paul posted on 6/21/2008 at 4:32 AM

Don't worry, got it sorted myself:-)
New question: I need a print button, that opens a new page and prints out a printer-friendly version of the spry dataset. Do I need to repeat all the spry data configuration on the new page or can the new page pick up the new data via caching or some other way?

Comment 22 by Raymond Camden posted on 6/21/2008 at 3:46 PM

@Paul - Um - I'm thinking you would need to duplicate it. AFAIK.

Comment 23 by Phillip Senn posted on 8/15/2008 at 7:51 AM

The DOM is connected to the - Javascript.
The Javascript connected to the - Spry script. The Spry script connected to the - ColdFusion script. The ColdFusion script connect to the SQL script.

Comment 24 by scott posted on 10/14/2008 at 1:11 AM

Now, if we can get a validation box to be sure they want to delete, I would be happy.

Comment 25 by Raymond Camden posted on 10/14/2008 at 1:25 AM

Consider it your homework. ;)

Comment 26 by gammajack posted on 4/28/2009 at 11:42 PM

Ray,

When getting data from my database using your described methods, the column header returned in the JSON object (the label of the ordered value pair) comes back as all caps, so where you have "<td style="cursor: pointer;">{price}</td>", I have to make it "<td style="cursor: pointer;">{PRICE}</td>". Any idea why that might be happening or if it's supposed to be that way?

Comment 27 by Raymond Camden posted on 4/28/2009 at 11:45 PM

That's just how CF is representing the data. I see it more with structs than queries, but it may be applying here. For structs you can ensure lowercase by using bracket notation:

<cfset foo["name"] = "Ray">

versus

<cfset foo.name = "Ray"> This one will end up being foo.NAME.