ColdFusion RIA

ColdFusion 10 Charting - Funnel Chart and Playing with the Legend

Wed, 21 Mar 2012 12:20:00 -0500

A blog reader asked on the chart post yesterday about Funnel charts in ColdFusion 10. So, I whipped up a quick demo of the same. View Demo (Refresh screen if demo doesn't load the first time) You will see that there are two charts. The default render doesn't show the legend at a good place, and fortunately with ZingChart + CF10 functionality we are able to position it much better by specifying the legend attribute for cfchart. Let's look at the code.




	SELECT
		SUM(Total) As InvTotal,
		Vendor
	FROM
		Invoices
	GROUP BY Vendor
	ORDER BY SUM(Total) DESC




	legend = {
		"x":0, //Position
		"y":450, //Position
		"layout":"2x", //Change layout to be 2 lines
		"width":350, //Set width
		"shadow":true //Show a shadow	
	};
	
	
	background = {
		"background-color":"##F7F8E0"
	};

As you can see, we have a legend sturcture withsome values to specify its behavior. Using this we are able to reposition it, change layout, give some shadow. There are other things you can do, find the full variety of options in the ZingChart documentation.

ColdFusion 10 Charting - The new stuff

Tue, 20 Mar 2012 08:10:00 -0500

ColdFusion 10 includes a good number of charting enhancements including Client Side Charting using the ZingChart library. This is great news as we can look at ZingChart documentation to look at some of the new things you can do with Charts in ColdFusion 10.

I started playing around with this and did a quick demo of pie and bar charts with some of the new options.
View Demo (Free CF10 beta hosting from Hostek!. If it doesn't load, refresh page). Code is pretty simple, just had to look through ZingChart documentation to exact structure builds. Code Follows... SELECT SUM(Total) As InvTotal, Vendor FROM Invoices GROUP BY Vendor plotPie = { "slice":20, //Adds the blank circle in middle "offset":50, //Does not trigger "rules":[ //Change styling based on values { "rule":"%v <= 1000000", "background-color":"##01DF01", "border-color":"##01DF01", "border-width":2, "shadow":true, "shadow-distance":8, "shadow-color":"##6E6E6E" }, { "rule":"%v >= 1000000", "background-color":"##DF3A01", "border-color":"##DF3A01", "border-width":2, "shadow":true, "shadow-distance":8, "shadow-color":"##6E6E6E" } ] }; plotBar = { "value-box" : { //Add Value maximum for each bar "type" : "max", "text" : "Max: %v", "text-align" : "center" }, "rules":[ //Change styling based on values { "rule":"%v <= 1000000", "background-color":"##01DF01", "border-color":"##01DF01", "border-width":2, "shadow":true, "shadow-distance":8, "shadow-color":"##6E6E6E" }, { "rule":"%v >= 1000000", "background-color":"##DF3A01", "border-color":"##DF3A01", "border-width":2, "shadow":true, "shadow-distance":8, "shadow-color":"##6E6E6E" } ] }; background = { "background-color":"##F7F8E0" }; animate = { //Bars will slide up during load "effect":"slideup" }; arrows = [ //Puts arrow on graph { "from": {"x":100,"y":100}, "to" : {"x":270,"y":270}, "size":2, "label":"See This" //Nothing shown... } ];

Some of the new attributes we are using are the plot, animate, background attributes. The plot allows you to define rules which govern the styling of the data value elements of the graph. Using the rules you can set different coloring, shadow, border depending on the values. This would give you a lot of flexibility with graph display. We also find some ZingChart definitions that don't seem to have been implemented as of yet at least in the beta.

I will continue experimentation with additional charts to see what new things we can do using ZingChart.

ColdFusion 10 Beta and Windows 7 Install

Tue, 13 Mar 2012 15:16:00 -0500

Due to work keeping my quite busy along with a drop in how much coding I actually do while working means I have not been following CF 10 and CF community that closely. But I was pretty excited to find last week that the ColdFusion 10 (Zeus) beta has been out for a few weeks now. Time to code a bit more then! The first thing to do was install it on my new SSD (get one, its good stuff) with Windows 7. IIS was already setup for ColdFusion 9, so I just went ahead with the install. Everything seemed to be going well (lots of password warnings), but the administrator wouldn't open at the last step. Now, I hit a lot of issues installing CF9 on Windows 7 64-bit and I was hoping this was not a repetition of those issues. Thankfully it wasn't. The main change I had to made was to install the .NET Extensibility, ASP.NET and CGI features for World Wide Web Services under IIS. After that, I re-install CF 10 and everything was good to go. Logged into CF Admin, and this is nice, very nice. What I am talking about is actually the new update installer that Adobe has included now. For anybody working with ColdFusion in production environments, 1-click install removes a big headache. Apart from that, the new Clear Query Cache option will be one of my favorite enhancements. Same thing with ability to clear cache for specific paths. This means when doing a Production hot fix, I don't need to reset cache for the whole application. Anyway, if you have not yet download CF 10 beta, get it now:
ColdFusion 10 - Adobe Labs

Delete Duplicates using CTE in SQL Server

Thu, 22 Sep 2011 17:38:00 -0500

This is a blog post for future reference and this has proved useful to me over the past year ever (ever since I found it online). And it's time to transfer the method from email to blog. CTE is to me (from what little I know of advanced SQL concepts) a pretty powerful feature. It stands for Common Table Expression and is basically a temporary result set (table) of data that you can use within the same query execution. I do this via a two step process, first query verifies that the CTE is generated properly, second one uses the CTE to delete the Duplicates. Quite frankly, I still have no idea how exactly this works, I just know it has worked for me in the past. If any SQL Guru who comes upon this post wants to explain how this exactly works in the comments section it would be very appreciated.


/* Build CTE and select records to verify everything looks good */
WITH CTE AS
(
SELECT ID, RANK() OVER (PARTITION BY ID ORDER BY num) rnk
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) num
FROM ABC) 
X)
SELECT * FROM CTE
WHERE rnk > 1

/* After verification, build CTE again and now Delete the duplicates */
WITH CTE AS
(
SELECT ID, RANK() OVER (PARTITION BY ID ORDER BY num) rnk
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) num
FROM ABC) 
X)
DELETE FROM CTE
WHERE rnk > 1

The first query builds the CTE and we first verify that the built CTE is good, expected duplicates show up. The second query then deletes the duplicates. In both queries, you have to replace the "ID" and "name" parts of the query based on your table. And you might now always know what exactly to use here, well then experiment. I usually start with using the duplicate column in the "name" part. This has been of use particularly in cases where the tables have duplicate data but there is no PK (or a good PK) available to drive the deletion. And maybe there is an easier way too now.

Browser Detection using jQuery

Tue, 20 Sep 2011 18:00:00 -0500

After finally getting this blog back up online, the next step was to upgrade to the latest blogCFC version (which ships with the awesome theme you see). After being done with that, I noticed this is the first time I had used the blog with Chrome, and the one thing that didn't work in Chrome is display of the RSS feed. And no better way to remind myself how to accomplish this again in the future than blogging it! The RSS in IE/Firefox is styled (I believe because of the in built RSS reader?): The RSS in Chrome is not styled: One good solution to this would be to just use the Feedburner link for the RSS feed (http://feeds.feedburner.com/ColdFusionRIA) and it displays good in all browsers. But I saw it as a good opportunity to use jQuery to redirect the click based on the browser. So, now if you click the RSS link on the navigation, it will show the default blogCFC RSS in IE/Firefox but for Chrome it will go to the feedburner link. Now the main issue I encountered (and the solution is documented at various places on the internet) is that the built in jQuery browser detection doesn't differentiate between chrome and safari. You can see this at work here (try it with different browsers) On Chrome, the default output will detect safari. So some changes had to be made to that. Really simple to do using jQuery too. Let's look at the code.

$.each($.browser, function(i, val) { $("

" + i + " : " + val + "").appendTo( document.body );
		});
				
$("Browser info (New):").appendTo(document.body);

//Making some changes to detection code to account for chrome...
var userAgent = navigator.userAgent.toLowerCase();				
// Figure out what browser is being used
jQuery.browser = {
	version: (userAgent.match( /.+(?:rv|it|ra|ie|me)[\/: ]([\d.]+)/ ) || [])[1],
	chrome: /chrome/.test( userAgent ),
	safari: /webkit/.test( userAgent ) && !/chrome/.test( userAgent ),
	opera: /opera/.test( userAgent ),
	msie: /msie/.test( userAgent ) && !/opera/.test( userAgent ),
	mozilla: /mozilla/.test( userAgent ) && !/(compatible|webkit)/.test( userAgent )
};
					
$.each($.browser, function(i, val) {
    	$("

" + i + " : " + val + "").appendTo( document.body );
});

As you see, first we just use the default jQuery library to push browser information to the document. If you open it on chrome, you will see it actually says "safari: true". To detect chrome properly we override the jQuery browser part in the code and the new result will detect the difference between safari and chrome. After this, for the RSS link I just have to redirect the window opening based on whether it is chrome or not if($.browser.chrome){ }else{ }

cffileupload Tag - Uploading Multiple Files

Sat, 17 Sep 2011 13:59:00 -0500

Back in beta, I had made a post on this new tag in ColdFusion 9. It creates a Flash interface allowing the user to upload multiple files to the server. A pretty easy way to achieve something powerful. Back in beta, there was also a bug with the funtionality. It was that ColdFusion was not automatically passing back Success, Failure messages. With the release now, that has been taken care of, so we can revise the beta code.

Demo currently unavailable

Lets take a look at how simple the code is

fileupload1.cfm:

uploadSelectedFiles.cfm: Very Simple, very easy.

cffileupload Tag - Event Handlers

Fri, 16 Sep 2011 14:46:00 -0500

Continuing with exploring the new multi-file upload tag functionality in ColdFusion 9, this post will build on the previous post and add event handlers. We are able to work with 3 event handlers:

onComplete - Called after each file finishes uploading
onError - Called when a file upload errors out
onUploadComplete - Called when all the files finish uploading

Demo currently unavailable.
Lets take a look at the code.

The main changes are in our multi-file upload tag. In addition, I am using jQuery and CFmessagebox to show results of the event handlers. //Will be called when the file upload was successful function uploadSuccess(res){ var newID = Math.floor(Math.random()*1001); //Generate random number //Append our success message to results Div $("#results").append('Uploaded File : ' + res.FILENAME + ' '); //Hide the appended message then fade it in $("#id_" + newID).hide().fadeIn(); } function uploadError(res){ var newID = Math.floor(Math.random()*101); $("#results").append('Error Uploading File : ' + res.FILENAME + ' '); $("#id_" + newID).hide().fadeIn(); } function allDone(){ ColdFusion.MessageBox.show('finalmsg'); }

The JavaScript function uploadSuccess is called when a file finishes uploading and uploadError when an upload errors out. In both functions, a random integer is generated and jQuery is used to append a span element with that (hopefully) unique ID to the div and use some fancy animation to bring it into view. Our code to upload the files, remains the same.

ColdFusion provides us a simple and great implementation to provide users good feedback when implementing the multi-file upload functionality.

Installing ColdFusion 9 64-but on Windows 7 64-bit Experience

Tue, 16 Feb 2010 01:30:00 -0500

This weekend I got me a new laptop from Dell (Studio 15). I got it with 4 GB RAM (to me it has become a minumum now) which naturally meant going with a 64-bit OS. So, I now had this nice new laptop with Windows 7 x64 and once I was done cleaning out the bloatware, it was time to install ColdFusion. It turned out to be quite an experience and took much longer then I anticipated. But first, lets get one thing straight here, Adobe doesn't really provide any documentation on what you need to do to install CF properly on IIS 7.5. There is no, "verify these windows component are installed" document or screen. In any case, I had installed ColdFusion 9 on Windows 32-bit succesfully, and needless to say I felt (over)confident that I could do the same for Windows 7 64-bit. I started out by configuring Windows have the IIS6 modules installed, I knew I had to do that. Then I ran the ColdFusion 9 install, no errors, got to the end, "Press OK to start CF Admin", did that. The CF Admin screen came up and did not work. IIS 7 told me about missing extension configuration. I realized at that point something was missed in my IIS reconfiguration, and it turned out it was the ISAPI Extensions under World Wide Web Services options, so I turned all those things on, Windows reconfigured IIS for me. I opened IIS, saw tha the ISAPI extensions and filters were not installed. Ready to go right? Wrong. Still could not get to CF Admin. Ok, lets uninstall and install CF9 I thought, since now IIS has everything it needs for proper configuration. So I did that, uninstall then install and once again pressed "OK" at the end. Out comes the CF Admin page, "Click here" to continue configuration. Yay, victory was my first though. But then I noticed, something was not right. There was no Adobe logo, no images were showing up. Doesn't matter, I was able to open a .cfm page, so I continued with the configuration and then was logged into the Admin. Still no images. By the way, the Admin looks really bad if you can't see images. So, I closed the browser and opened a new IE window and went to //localhost/cfide/administraor/ and IIS 7 gives me an Error!! But thats where the CF Admin is located, but apparently IIS can't handle the request properly. Sigh, I did some googling. Verified that the ISAPI handlers for .cfm extensions were pointing to the right DLL. Someone suggested uninstalling CF, deleting all CF directories and installing again. So I dd that, but same error again. Still can't see images in CF Admin. Frustrating, already installed CF9 3 times. Verified IIS setup again, all looks good there. I uninstall CF9 again, restart computer, delete directories, install CF9 again and same issue again. At that point, google had helped me as much as it can. I try to use the server configuration tool but I can't remove the current flawed IIS configuration from it (some error about not able to remove things). Decide it time to start from scratch. So, I uninstall CF9, delete CF directories, restart computer. Uninstall IIS7 from windows, restart computer. Then reinstall IIS7 and restart computer. Then reinstall CF9 and Victory!! Everything works properly. Apparently, installing the ISAP Extensions after installing CF9 screwed up the installs and configurations in IIS. So, well, it worked now. Logged into CF Admin, //localhost/cfide/administrator/ resolved properly. But that was not the End of the Story. Apparently, setting up a MS Access Datasource on Windows 64-bit is another issue faced by various users. You would get an error about DRIVERPATH not defined. Well I thought, if CF can't set it up, I will set it up in Windows and use ODBC socket. But the issue is that the 64-bit Windows don't come with the relevant drivers needed to setup Access Datasource in Windows. After trying to figure out how to resolve this, googling and finding some information here and there, not much help was found. In the end it was realized, that the Windows\System32\odbcad32.exe file contained the proper drivers but the Windows\SysWOW64\odbcad32.exe did not contain the drivers. So the solution was to copy over the .exe from System32 to SysWOW64. I went through with that and it worked!! Even though, ColdFusion still gave a DRIVERPATH undefned error, the datasource was verified properly and I could run all my MS Access based demos. Whew, finally, was done with setting up Coldfusion 9 64-bit on Windows 7 64-bit.

ExtJS 3.0 Editor Grid with ColdFusion + Ext Toolbar Play

Mon, 14 Dec 2009 22:33:00 -0500

I hadn't looked at the Ext JS library in a while, but this weekend, I took a look at it with the idea of eventually building out a full CRUD grid using Ext JS and ColdFusion. It took some time, particularly trying to figure out the phantom records implementation, but its done now.

View the Demo

And as always, time to look at the code.

HTML, very simple. Just defining all our JS/CSS includes and our grid related HTML div Ext JS Grid with ColdFusion

Now the interesting stuff. The javascript.


Ext.onReady(function(){
	
	//This is our JSON record set which defines what kind of data will be present in the JSON passed back from our component.
	var users = Ext.data.Record.create([
	{name:'ID',allowBlank:false},
	{name:'FIRSTNAME',type:'string'},
	{name:'LASTNAME',type:'string'},
	{name:'DISPLAYNAME',type:'string'},
	{name:'USERNAME',type:'string'},
	{name:'USERACCOUNTINGCODE',type:'string'},
	{name:'PHONE',type:'string'}
	])
	
	//The Proxy object will specify URLs to call for different CRUD operations
	var proxy = new Ext.data.HttpProxy({
		method:'POST',				
		api:{
			read: 'Users.cfc?method=getUsers',//Our URL for reading the grid data
			create: 'Users.cfc?method=addUser',//For Adding a new User (future implementation)
			update: 'Users.cfc?method=editUser'//When a User is updated
		},
		//the function writeSuccess will be called when the Store finishes a succesful write (add/edit) to the server
		listeners: {
			write: {
				fn: writeSuccess
			}
		}
	})
	
	//Our JSON Writer, configuration used to updating/adding of records
	var writer = new Ext.data.JsonWriter({
		//We will only send the changed fields
		writeAllFields:false		
	})
	
    // create the Data Store
    var store = new Ext.data.JsonStore({
    	totalProperty:'DATASET',//This is how many total records are there in the set.
    	root:'ROWS',//The Root of the data.        
		proxy:proxy,
		id:'ID',
		successProperty:'SUCCESS',//What return string data will be set to true/false to let Ext know that server call was succesful or not
		autoSave:false,//we will use event listeners to call save manually
        remoteSort:true,//We will sort server side
        //Base Params are parameters passed in during every call
        baseParams:{            	
            	returnFormat: 'JSON',
            	start: '0',
            	limit: '50'
            },
        //We define the JSON Reader for the data. We also need to set the totalProperty, root and idProperty for the dataset here.
        reader: new Ext.data.JsonReader({
        		totalProperty:'DATASET',
        		root:'ROWS',
        		idProperty:'ID'												
        	},users
        ),
        //Fields read in
        fields: [
            'ID','FIRSTNAME','LASTNAME','DISPLAYNAME','USERNAME','USERACCOUNTINGCODE','PHONE'
        ],
		writer:writer,
		//Any Store execeptions will be caught here
		listeners: {			
			exception : function(proxy,type,action,options,res,rs) {
				alert(type);
			}
		}      
    });
	
	// Our Form Variable to be used for editor grid
	var varForm = Ext.form;
	
   	//We setup the Grid
	var cm = new Ext.grid.ColumnModel({
       columns:[
	   	new Ext.grid.RowNumberer(),//This will do numbering on the grid for us        
        {
			header: "ID",
			dataIndex:'ID',
			editable: false,
			width:50 
		},
		{			
            header: "First Name",
            dataIndex: 'FIRSTNAME',
            width: 100,
            hidden:false,            
            sortable: true,			
            editor: new varForm.TextField({
                allowBlank: false
            })
        },{
            header: "Last Name",
            dataIndex: 'LASTNAME',
            width: 100,
            hidden: false,
            sortable: true,
			editor: new varForm.TextField({
                allowBlank: false
            })
        },{
            header: "Display Name",
            dataIndex: 'DISPLAYNAME',
            width: 150,
            hidden: false,
            sortable: true,
			 editor: new varForm.TextField({
                allowBlank: false
            })
        },{
            header: "User Name",
            dataIndex: 'USERNAME',
            width: 100,
            hidden: false,
            sortable: true,
			editor: new varForm.TextField({
                allowBlank: false
            })
        },{
            header: "Contact",
            dataIndex: 'PHONE',
            width: 100,
            hidden: false,
            sortable: true,
			editor: new varForm.TextField({
                allowBlank: false
            })
        }]
    })
	
	 // create the editor grid
    var grid = new Ext.grid.EditorGridPanel({
        width:750,
        height:500,		              
        title:'Users',
        store: store,
        trackMouseOver:true,
        disableSelection:false,
        loadMask: true,
		stripeRows: true,
		collapsible: true,
		cm:cm,//Our column model
		frame:true,//Make it more nicer looking (?)
        clicksToEdit: 1,//One click on row data will bring on edit box
		renderTo:'editgrid',
		// paging bar on the bottom
       bbar: new Ext.PagingToolbar({
            pageSize: 50,
            store: store,
            displayInfo: true,
            displayMsg: 'Displaying Records {0} - {1} of {2}',
            emptyMsg: "No Records to display"            
        }),
		tbar: new Ext.Toolbar({
			items : [
				{
					xtype:'tbtext',
					text:'Welcome. Click Row Data to Edit.',
					itemId:'tbartext',
					id:'tbartext'
				}
			]
		}),
		 listeners: {
		 	afteredit: { //This listener event function will be called when user finishes editing data box and tabs/enters/clicks out.
		 		fn: editStore
		 	}
		}        
    });
	
	function editStore(e)
	{
		//Call our store's save event that will fire things off'
		this.store.save();		
	}
	
	//Our server call was succesful, display user feedback here
	function writeSuccess(store, action, result, res, rs)
	{
		//Get Toolbar Object
		var tbar = grid.getTopToolbar();
		//Remove current text
		tbar.remove("tbartext");
		//Add new text		
		tbar.addText({
			xtype:'tbtext',
			text:'Success !! ' + res.MESSAGE,
			itemId:'tbartextsuccess'
			}
		);
		//Redo layout to render new toolbar text
		tbar.doLayout();
	}
	
	//Default Sort set for the grid load call
	store.setDefaultSort('ID','ASC');
   
    // trigger the data store load
    store.load();	
	
});

Hopefully, the comments will help understand what is happening. But we will look at a few important aspects of the code. In the JSON Store definition, we have: successProperty:'SUCCESS', autoSave:false By default, the successProperly is set to "success". This is important, because our ColdFusion returned JSON will convert the a returned "success" variable to upppercase. By setting the autosave to false, we will have to make our own store.save() call to trigger the update process. For purposes of this blog post, it makes no difference what this is set to. But by setting it to false, one can allow for batch edits very easily instead of editing on each change.
Also, in each column definition we have added its editor definition. editor: new varForm.TextField({ allowBlank: false }) This will let us edit the column using a textbox, and we will not allow blank values for the column.
This example, implements the in-built EditGridPanel. The definition here has a couple of important things. One is the Toolbar we have specified. We will use this to show some simple user feedback. Other is the listeners. We have added a listener for the afteredit event.


listeners: {
 	afteredit: { 
 		fn: editStore
	 	}
	}

This event is triggered when a user changes any data in an editable cell. We are telling Ext to call the editStore function when this happens. And our edit store function, is well very simple here. Just calls the store's save method, triggering the update process.

Now, if we go up a bit and look at our proxy definition. //The Proxy object will specify URLs to call for different CRUD operations var proxy = new Ext.data.HttpProxy({ method:'POST', api:{ read: 'Users.cfc?method=getUsers',//Our URL for reading the grid data create: 'Users.cfc?method=addUser',//For Adding a new User (future implementation) update: 'Users.cfc?method=editUser'//When a User is updated }, //the function writeSuccess will be called when the Store finishes a succesful write (add/edit) to the server listeners: { write: { fn: writeSuccess } } }) The proxy object can be defined to let Ext know which URLs to call for different CRUD functions. In addition, we are also specifying a listener for the write event. The write event is called upon a succesful write (add and edit) to the server. We will use this to show returned server feedback to the user. /Our server call was successful, display user feedback here function writeSuccess(store, action, result, res, rs) { //Get Toolbar Object var tbar = grid.getTopToolbar(); //Remove current text tbar.remove("tbartext"); //Add new text tbar.addText({ xtype:'tbtext', text:'Success !! ' + res.MESSAGE, itemId:'tbartextsuccess' } ); //Redo layout to render new toolbar text tbar.doLayout(); } Our function here, manipulates the grid toolbar we have specified. First, we will remove our current toolbar text. We use the added text item's "itemId" that we specified for this. Then we add a new text item to the toolbar. After that, we have to call the toolbar's doLayout function, which forces Ext to recalculate the toolbar's render and hence it will show our new text.

Our CFC is good old ColdFusion code. SELECT ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone FROM Users ORDER BY #Arguments.sort# #Arguments.dir# UPDATE USERS SET #PreserveSingleQuotes(strUpdQuery)# WHERE id = #Arguments.id# SELECT ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone FROM Users WHERE ID = #Arguments.id# The new function that I added was the editUser function. It simply, deserializes the incoming JSON, to get good old CF from it. Check which field is being edited. Run update query and then return success (because well, I assume its always going to be succesful).

Now, the challenge that I faced while I was writing this code up, was what Ext calls "phantom records". The easiest way to explain a phantom record is that it is basically a new record that has been added to the Ext Store that has not been committed to the server yet. But of course, I wasn't adding records, I was just updating someone's First Name, but Ext was still passing it off as a phantom record. What this does is that, Ext calls the create URL we have defined in our proxy instead of the update URL. For a while, I couldn't figure it out. I looked through the source, Ext used the specified idProperty for a recordset to determine whether it was a phantom or not. But I had specified my idProperty properly in the JSONReader. I searched the forums, others have seen the same issue, best suggestion given was read the source. Eventually, I realized, that you can specify an "id" for the JSONStore. And, so I added the simple definition: id:'ID', to my JSONStore definition, and magic, it was working as intended. Great.

So, there we have, editor grid with ExtJS and ColdFusion.
Download Source (cfm/cfc/js files)

Questions? Comments? Leave a note here or contact me. Always glad to help.

Note: You might notice some javascript errors, especially if using Firebug. These are known bugs in the Ext JS library but don't seem to have any impact on the functionality.

Using jQuery based jqGrid with ColdFusion Revisited: Full CRUD grid with Advanced Searching

Mon, 07 Dec 2009 18:55:00 -0500

It has been a while since I made my last blog post as work has kept me very busy. Over the weekend I saw that a new jqGrid version had been released and that it contained in-built advanced searching (called multiple search). I also thought it was a good time to revisit my earlier blog post on a full CRUD grid and update it to 3.6 specifications.

This implementation uses the following jqGrid features:

Inbuilt Form based Add, Edit, Delete and Search functionality
Search is done using new Multiple Search feature
Exploration of a few additional colModel parameters

View the Demo : this is all realtime, any updates made will be reflected in the grid.
Now, lets look at the code

The HTML is still very simple.

Let's see the javascript that defines our grid.



		
	$(document).ready(function()
		{	
			$("#list").jqGrid(
			{
				url:'Users2.cfc?method=getUsers', //CFC that will return the users
				datatype: 'json', //We specify that the datatype we will be using will be JSON
				colNames:['ID','First Name','Last Name', 'Display Name','User Name'], //Column Names
				//The Column Model to define the data. Note you can make columns non sortable, specify width, alignment, etc.
				colModel :[
					{name:'id',index:'id', width:75, sorttype:"int",editable:false,searchoptions:{sopt:['eq','lt','le','gt','ge']}},
					{name:'FirstName',index:'FirstName', width:150, sorttype:"text",searchoptions:{sopt:['eq','bw','ew','cn']},
						editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}},
					{name:'LastName',index:'LastName', width:150, align:"left",sorttype:"text",searchoptions:{sopt:['eq','bw','ew','cn']},
						editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}},
					{name:'DisplayName',index:'DisplayName', width:150, align:"left",sorttype:"text",searchoptions:{sopt:['eq','bw','ew','cn']},
						editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}},
					{name:'UserName',index:'UserName', width:150,align:"left",sorttype:"text",searchoptions:{sopt:['eq','bw','ew','cn']},
						editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}}
				],//searchoptions parameter is used to limit the operators available during search
				pager: $('#pager'), //The div we have specified, tells jqGrid where to put the pager
				rowNum:20, //Number of records we want to show per page
				rowList:[20,30,40,50], //Row List, to allow user to select how many rows they want to see per page
				sortorder: "asc", //Default sort order
				sortname: "ID", //Default sort column
				viewrecords: true, //Shows the nice message on the pager
				imgpath: '/Test/themes/basic/images', //Image path for prev/next etc images
				caption: 'Users', //Grid Name
				recordtext: "Record {0} - {1} of {2}",//Pager information to show
				rownumbers: true,//Show row numbers
				rownumWidth: "40",//Width of the row numbers column
				sortable: true,
				height:'auto', //I like auto, so there is no blank space between. Using a fixed height can mean either a scrollbar or a blank space before the pager
				mtype:'POST',	
				toolbar:[true,"top"], //Shows the toolbar at the top. We will use it to display user feedback
				//The JSON reader. This defines what the JSON data returned from the CFC should look like
				jsonReader: {
					root: "ROWS", //our data
                    page: "PAGE", //current page
                    total: "TOTAL", //total pages
                    records:"RECORDS", //total records
					userdata:"USERDATA", //Userdata we will pass back for feedback
					cell: "", //Not Used
					id: "0" //Will default to first column
					},
				editurl:"Users2.cfc?method=addeditUser" //The Add/Edit function call				
				}				
			).navGrid('#pager',
				{
					search:true,searchtitle:"Search",//title set for hover over display
					edit:true,edittitle:"Edit User",
					add:true,addtitle:"Add User",
					del:true,deltitle:"Delete User"
				},
			// Edit Options. savekey parameter will keybind the Enter key to submit.
			{editCaption:"Edit User",edittext:"Edit",closeOnEscape:true, savekey: [true,13],errorTextFormat:commonError,width:"450"
				,reloadAfterSubmit:true,bottominfo:"Fields marked with (*) are required",top:"60",left:"70"}, 
			{addCaption:"Add User",closeOnEscape:true,savekey: [true,13],errorTextFormat:commonError,width:"450"
				,reloadAfterSubmit:true,bottominfo:"Fields marked with (*) are required",top:"60",left:"70"}, //Add Options
			{url:"Users2.cfc?method=delUser",caption:"Delete User",closeOnEscape:true,errorTextFormat:commonError,top:"60",left:"70",
				reloadAfterSubmit:true}, //Delete Options
			 //Search Options. multipleSearch parameter lets it know we are going to use the new advanced search feature
			{errorTextFormat:commonError,Find:"Search",closeOnEscape:true,caption:"Search Users",multipleSearch:true,closeAfterSearch:true}
			);			
		
		//Function will be called when add/edit encounters an error. The returned message is what will be shown to user	
		function commonError(data)
		{			
			return "Error Occured during Operation. Please try again";
		}
			
		}
	);

Hopefully the comments in the code help understand what is going on. I am taking advantage of new colModel definitions that we can specify to help the multiple search feature. You can see this in the searchoptions parameter specification. The sopt array array specifies which operators can be associated with the search field, so for example, ID which is an integer can only handle equal, less than, less than equal to, greater than and greater than equal to operators. While the rest of the search fields which are text fields use operators more suited to text based searching.

We are also using the formoptions parameter and passing the elmprefix value. This value we have passed will be appended to our input elements. In this case we will use it to tell the user which fields are required.

In addition, we have specified additional parameters for the Add/Edit/Delete options. These include specifying the savekey (this is set to Enter, so pressing Enter will submit the Add/Edit form), closeOnEscape is set to true (you can close those modal dialogs with the Esc key). We have also specified our error handler for Add/Edit which is the function commonError. It will return a generic error message to the user. In order to use the multiple search implementation in jqGrid we have specified the multipleSearch parameter to true (so simple it is).

We also have our CFC component that will form the bridge between our grid and database.



	
	
		
	    
	    
	    
	    
	    
	    
		
		
		
		
		
		
			
		
		
		
			
			
				
						
		
		
		
			SELECT
				ID, FirstName, LastName, DisplayName, UserName
			FROM
				Users
			
				#PreserveSingleQuotes(strSearch)#
			
			ORDER BY #sidx# #sord#				
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
					
			
						
		
		
		
		
		
		
		
		
		
		
	
	
	
		
		
		
				
		
		
		
		
		
		
		
		
					
			
			
				
					UPDATE
						Users
					SET
						FirstName = '#Arguments.FirstName#',
						LastName = '#Arguments.LastName#',
						DisplayName = '#Arguments.DisplayName#',
						UserName = '#Arguments.UserName#'						
					WHERE
						ID = #Val(Arguments.ID)#
				
			
				
				
				
				
			
			
		
			
							
			
				INSERT INTO USERS(FirstName,LastName,DisplayName,UserName)
				VALUES ('#Arguments.FirstName#','#Arguments.LastName#','#Arguments.DisplayName#','#Arguments.UserName#')
			
			
				
				
				
				
			
			
		
		
		
		
		
		
		
	
	
			
		
		
		
		
		
		
		
		
			
			
				DELETE FROM Users WHERE ID = #Val(Arguments.id)#
			
		
			
			
			
		
		
		
		
		
		
		
		
	
	
	
		
		
		
		
		
		
		
		
		
		
			
			
			
			
			
			
			
				
					
				
									
				
			
			
		
		
		
				
	
	
	
		
		
		
		
		
		
		
			
		
		
		
			switch(Arguments.strOp)
			{
				case "eq":
					//ID is numeric so we will check for that
					if(Arguments.strField EQ "id")
					{
						searchVal = "#Arguments.strField# = #Arguments.strValue#";
					}else{
						searchVal = "#Arguments.strField# = '#Arguments.strValue#'";
					}
					break;				
				case "lt":
					searchVal = "#Arguments.strField# < #Arguments.strValue#";
					break;
				case "le":
					searchVal = "#Arguments.strField# <= #Arguments.strValue#";
					break;
				case "gt":
					searchVal = "#Arguments.strField# > #Arguments.strValue#";
					break;
				case "ge":
					searchVal = "#Arguments.strField# >= #Arguments.strValue#";
					break;
				case "bw":
					searchVal = "#Arguments.strField# LIKE '#Arguments.strValue#%'";
					break;
				case "ew":					
					searchVal = "#Arguments.strField# LIKE '%#Arguments.strValue#'";
					break;
				case "cn":
					searchVal = "#Arguments.strField# LIKE '%#Arguments.strValue#%'";
					break;
			}

Source Code (cfm/cfc) Here.
Feel free to contact me if you have any questions and I will try to help you out.

Playing with jQuery UI : Part 1

Mon, 31 Aug 2009 13:25:00 -0500

I downloaded the jQuery UI library a few weeks back (should have done it earlier too). I started playing around with it, but work has been so busy did not get much time to look at it. Finally, found some time to do a basic tabs, accordians, sliders, progress bar demo. Its very basic, good start to learing how some of the things work.

View Demo

The progress bar demo is probably the most complex one, so code for it follows.

For the progress bar, the main idea was to find a way to increment the value of the bar. This is done using the setTimeout method.



		//Set Progress Bar
		$(function(){
			$("#progressbar").progressbar({
				value:0
			});
			$("#progressvalue").html("0");
			//Use setTimeout to call checkValue function
			setTimeout(checkValue,500);
		});

		//Get current value, increment it if needed to update Progressbar.
		//Recursive function calls using setTimeout
		function checkValue(){
			var currentValue = $("#progressbar").progressbar("option","value");
			currentValue = currentValue + 20;
			if (currentValue <= 100) {
				updateValue(currentValue);
				$("#progressvalue").html(currentValue);
				setTimeout(checkValue,500);
			}else
			//100% loaded, now load sliders.cfm page
			{
				$("#content").load("sliders.cfm");
			}
		}
		//Show the progress bar value
		function updateValue(newValue){
			$("#progressbar").progressbar("option","value",newValue);
		}

So setTimeout is called every 500 ms, as long as the value is less than 100. You probably have to change the code a bit if your increments are not by values that would make the bar reach a value of 100. When the value reaches 100 we use the jQuery load function to load our sliders.cfm page.

I hope to build on this more, especially want to see how the tabs integrate with my jqGrid examples and also interacting with ColdFusion pages.

Registration for CFinNC Conference Open (Free ColdFusion based Conference)

Tue, 25 Aug 2009 15:17:00 -0500

I am personally from Charlotte, so when the CFinNC Conference was announced, it was very exciting. I wanted to get to CFUnited this year but could not make it because of a very busy work schedule, so a weekend conference a few hours away was awesome. And on top of everything it is Free. Let me say that again, it is Free.

They put out a provisional list of speakers and its a very good list. A lot of people who I wanted to listen to in previous conferences will be there.

So head on over to cfinnc.com and register for it.

jqGrid 3.5 with jQuery UI ThemeRoller Themes Demos

Sun, 02 Aug 2009 14:54:00 -0500

jqGrid 3.5 was released yesterday. It includes lots of good features, including a new rendering engine for faster loading. It also now supports jQuery UI ThemeRoller themes, so to start off itself you have access to variety of styles and can create your own easily too.

So I went ahead and created a demo that shows the grid in all these themes.
View Demo

How I did this style selection switch, follows

The creation of grid hasn't changed and all that script remains the same, I have blogged about it before. The main thing to look at here is using ColdFusion with jQuery to implement the style switching. There are probably others (and maybe better) ways to do this, but here I describe how I came about doing it.

The first thing to do was to make it scaleable, that means I don't have to know the names of the themes and don't have to change any code if a theme based on ThemeRoller is added in the future. We use good old cfdirectory for that and read all the ThemeRoller theme subdirectories from the css directory within our path. Then I build the form using cfselect and cfinput.

Themes:
Current Theme:

The form field csBane will be used to read the selected theme and load it. For that we have a short javascript function that loads a css file. I found this function on the net.


function load(url_, /*optional*/ media_) 
{
// We are preventing loading a file already loaded
var _links = document.getElementsByTagName("link");
var _head = document.getElementsByTagName("head")[0];                

// Loop through the length of the links
for( i = 0; _links.length > i; i++)
{
    // If the href is already present, remove it
    if (_links[i]["href"] == url_)
    {
        _head.removeChild(_links[i]);
    }
}

// Optional parameters check
var _media = media_ === undefined || media_ === null ? "all" : media_;

// Build link element
var _elstyle = document.createElement("link");
_elstyle.setAttribute("rel", "stylesheet");
_elstyle.setAttribute("type", "text/css");
_elstyle.setAttribute("media", _media);
_elstyle.setAttribute("href", url_);

// Add style
_head.appendChild(_elstyle);
}

Next we need a little bit of script in our main file to read the selected stylesheet and load it and also to submit our form when the style selection is changed.



sel = document.cssForm.cssName.value;
$("#cssList").change(function()
{
	var selStyle = $("#cssForm option:selected").val();
	document.cssForm.cssName.value = selStyle;
	document.cssForm.submit();
});
	var loadVar = "css/" + sel + "/jquery-ui-1.7.2.custom.css";
	load(loadVar);

So here we read the selected theme and load it. We also bind the change function to our cfselect and set the cssName value to the selected option and submit the form.
Update: Demo is back Online.

ColdFusion 9 cfgrid with ORM: Part 2 - Full CRUD Grid

Thu, 30 Jul 2009 14:35:00 -0500

Previously I blogged on getting a normal cfgrid running with ColdFusion. This post will look at expanding that to a full CRUD grid using ORM.

In addition to ColdFusion, I am also using jQuery and jqModalspecifically to display the modal boxes. Main reason being that I was itching to do some jQuery stuff.

Code follows.

The first thing to be done is add some extra buttons to our paging toolbar for the Add, Edit, Delete functions. Ext JS makes this easy for us. The function gridSmash() is called on page load and it makes our adjustments to the grid.


function gridSmash()
{
	grid = ColdFusion.Grid.getGridObject('usersgrid');
	cols = grid.getColumnModel();
	configA = cols.config;
	//This will add the Row Numbering to our grid
	newcolumn = new Ext.grid.RowNumberer();
	temp = configA.splice(0,0,newcolumn);
	bbar = grid.getBottomToolbar();

	//Add buttons to the paging toolbar
	bbar.add('-', {
	pressed: false,
	enableToggle:false,
	text: 'Add',
		icon:'css/add.png',
	cls: 'x-btn-text-icon',
		handler:addShow
	},
	'-',{
		pressed: false,
	enableToggle:false,
	text: 'Edit',
		icon:'css/edit.png',
	cls: 'x-btn-text-icon',
		handler:editShow
	},
	'-',{
		pressed: false,
	enableToggle:false,
	text: 'Delete',
		icon:'css/delete.png',
	cls: 'x-btn-text-icon',
		handler:deleteShow
	}
	);
	bbar.doLayout();
}

We will use the default editing provided in ColdFusion for cfgrid. For that we just need to define the selectMode of the grid to edit and the onChange function for the grid.

The other important part is our UsersAccess.cfc. We add functions to take care of our add, edit and delete functions.

The full source has all the code, including all the jqModal things going on, stylesheets, etc. Download It. Update: ORM Demos are currently Unavailable.

Quick Tip : When Working with jQuery and jqModal

Sun, 26 Jul 2009 19:26:00 -0500

This has stumped me twice, so best thought to make a blog post, for my own records and also for anybody who gets stuck at the same place.

Over the weekend, I have been working with jQuery and specifically jqModal for a full fledged CRUD Grid using ORM. For some of the alerts/forms I was using jqModal to show them. Everything worked fine on Firefox, but IE8 had issues. After an hour or so of searching, I realized that I had faced a similar issue when working with jqModal on IE before.

The problem is that since I do not wirte a lot of HTML in my day to day coding, I generally omit the DOCTYPE sepcification from my pages. This causes interesting issues on IE. So, the solution was to specify the DOCTYPE and everything was working smoothly in IE too. My DOCTYPE specification that solved these issues was:

See the difference:
DOCTYPE NOT Present
DOCTYPE Present