Ask a Jedi: Complete Spry CRUD Example
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.
Comments
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
Fixed. I'll keep your comment though. :)
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)
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.
I agree window and modal would be nice but if your using cf8 you can always just import the cfwindow js and use that :)
Raymond ...I'm jumping on the wagon with Alan
Ditto search for ...
"I want get too detailed here " ...
I must not have had enough coffee. ;) Thanks.
Hi RayThis 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.
Martin - what browser? Also - be sure Dreamweaver didn't muck with the code. DW likes to do that. ;)
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.
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.
AHA!! My library was only 1.6Upgrading 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.
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. ;)
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/index2.cfm
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.com/spry/dynamic_spry.zip...
There is also another demo that adds a "photocart" to Spry's picture gallery.
Hope it would help
Massimo
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.
@JohnSure, 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
@Pauloops 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
@Martin - I have no idea where it is. Adobe.com maybe?
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?
@Paul - Um - I'm thinking you would need to duplicate it. AFAIK.
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.
Now, if we can get a validation box to be sure they want to delete, I would be happy.
Consider it your homework. ;)
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?
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.