PDA

View Full Version : ColdFusion Query Data Reader



CutterBl
20 Nov 2008, 1:45 PM
Inspired by the CFJsonReader, originally writtin by John Wilson (Daemach)
http://extjs.com/forum/showthread.php?t=21408&highlight=cfjsonreader

This Custom Data Reader will take the JSON return of a ColdFusion
Query object, rather returned straight up, or via the ColdFusion
QueryForGrid() method.

The CFQueryReader constructor takes two arguments
@meta : object containing single key/value pair for the 'id' of each record
@recordType : field mapping object

The recordType object allows you to alias the returned ColdFusion column
name (which is always passed in upper case) to any 'name' you wish, as
well as assign a data type, which your ExtJS app will attempt to cast
whenever the value is referenced.

ColdFusion's JSON return, for a ColdFusion Query object, will appear in the
following format:



{"COLUMNS":["INTVENDORTYPEID","STRVENDORTYPE","INTEXPENSECATEGORIESID",
"STREXPENSECATEGORIES"],"DATA" :[[2,"Carpet Cleaning",1,"Cleaining"],
[1,"Cleaning Service",1,"Cleaining"]]}


The ColdFusion JSON return on any query that is first passed through
ColdFusion's QueryForGrid() method will return the object in the
following format:



{"TOTALROWCOUNT":3, "QUERY":{"COLUMNS":["MYIDFIELD","DATA1","DATA2"],
"DATA":[[1,"Bob","Smith"],[6,"Jim","Brown"]]}}


The Ext.data.CFQueryReader is designed to accomodate either format
automatically. You would create your reader instance in much the same
way as the CFJsonReader was created:



var myDataModel = [
{name: 'myIdField', mapping: 'MYIDFIELD'},
{name: 'data1', mapping: 'DATA1'},
{name: 'data2', mapping: 'DATA2'}
];

var myCFReader = new Ext.data.CFQueryReader({id:'MYIDFIELD'},myDataModel);


Notice that the 'id' value mirrors the alias 'mapping' of the record's field.

That's it! Enjoy...

10626

Darklight
21 Nov 2008, 1:31 AM
thanks man, I'll take a look at this soon!
i was always using cfJSON (http://www.epiphantastic.com/cfjson/) to return valid data for Ext. generating the right format on the server should be preferable, but if the performance of your extension is good i just might have to switch ;)

CutterBl
1 Dec 2008, 7:44 PM
Yeah, I used the CFJson.cfc prior to moving to CF 8. The primary app I work on, day to day, requires constant performance tuning for server overhead, so I figure the fewer objects the better, just let ColdFusion handle the JSON output, and let the client parse it out. And, if I pass the returnFormat to the method as an argument of an Ajax call, leaving the method as-is, then I can reuse that method from elsewhere within my app when needed.

CutterBl
11 Jan 2009, 7:47 PM
I found, by accident, that the previous example has an issue. Your CFQueryReader object initialization takes two arguments, the metadata and the fields. The metadata attribute typically will contain only one item, the 'id' attribute, to tell the store which column of data is the unique record identifier.

In my previous example, the 'id' attribute directly matched the 'name' attribute of the identity column. Although nothing was erroring at first, I found out the hard way (when creating a GroupingView on a Grid, using the CheckColumn plugin) that the 'id' attribute should be matched with the 'mapping' attribute of the identity column.



var myReader = new Ext.data.CFQueryReader({
id:'MYIDFIELD'
},[{
name: 'myIdField',
mapping: 'MYIDFIELD',
type: 'int'
},{
name: 'myValueField',
mapping: 'MYVALUEFIELD',
type: 'string'
}


I have corrected this in the original post as well.

crxtech
5 Mar 2009, 12:03 PM
I'm trying to use your reader and am not having any luck yet. I don't get any errors, the grid displays my viewConfig:{emptyText:''}, even thought the json is being returned with records... Am I not using your code correctly?


//reader
var gridReader = new Ext.data.CFQueryReader(
{id:'PTS_ID'},
[
{name: 'PTS_ID', mapping: 'PTS_ID', type: 'int'},
{name: 'Project_Number',mapping: 'Project_Number', type: 'string'},
{name: 'Project_Title', mapping: 'Project_Title', type: 'string'},
{name: 'Project_SDate', mapping: 'Project_SDate', type: 'date'},
{name: 'Project_EDate', mapping: 'Project_EDate', type: 'date'},
{name: 'Last_SynDate', mapping: 'Last_SynDate', type: 'date'},
{name: 'RO_Name', mapping: 'RO_Name', type: 'string'},
{name: 'CO_Name', mapping: 'CO_Name', type: 'string'}
]
);

//datastore
var gridDS = new Ext.data.Store({
url: 'JSON/projectListJSON.cfm?status='+URLvar, method:'GET',
reader: gridReader,
sortInfo:{field:'Project_Number', direction:'ASC'}
});


{"ROWCOUNT":13,"COLUMNS":["PTS_ID","PROJECT_NUMBER","PROJECT_TITLE","PROJECT_SDATE","PROJECT_EDATE","LAST_SYNDATE"
,"RO_NAME","CO_NAME"],"DATA":{...

CutterBl
6 Mar 2009, 4:01 AM
ColdFusion's return data automatically capitalizes the column names in the JSON, so you'll need to reflect that your config.

crxtech
6 Mar 2009, 7:01 AM
Thanks Cutter, I capitalized all the mappings, but its still the same. Do I need to do anything to my CM?
(Sorry, I don't know why it paste in so hard to read :()

//Column Model
var cm = new Ext.grid.ColumnModel([
{id:'PTS_ID', header: "PTS ID", dataIndex: 'PTS_ID', width:50, hidden:true},
{id:'Project_Number', header: "Project Number", dataIndex: 'Project_Number', width:85},
{id:'Project_Title', header: "Title", dataIndex: 'Project_Title'},
{id:'Project_SDate', header: "Start Date", dataIndex: 'Project_SDate', width:70, renderer: Ext.util.Format.dateRenderer('m/d/Y')},
{id:'Project_EDate', header: "End Date", dataIndex: 'Project_EDate', width:70, renderer: Ext.util.Format.dateRenderer('m/d/Y')},
{id:'Last_SynDate', header: "Last Updated", dataIndex: 'Last_SynDate', width:85, renderer: Ext.util.Format.dateRenderer('m/d/Y')},
{id:'RO_Name', header: "Region", dataIndex: 'RO_Name', width:75, hidden:true},
{id:'CO_Name', header: "Country", dataIndex: 'CO_Name', width:75, hidden:true}
]);
cm.defaultSortable = true;

CutterBl
6 Mar 2009, 7:19 AM
Two things, looking at your original post:


I notice you don't have returnFormat: 'JSON' specified in your store params. Do you define that as the return format directly at the CFC level? If not, how are you handling the conversion?
You chopped off the JSON set being returned. Can you show us a little more? It would be good to get an idea of the structure of the 'DATA' returned.

crxtech
6 Mar 2009, 8:44 AM
I just upgraded to CF8 and am using SerializeJSON(queryName, true); to create the JSON. Before with CF7 I was manually creating JSON in the format that extJS liked it... Needless to say it was ugly and slow.

Here is my current reader, store and full JSON:

reader/store:

//reader
var gridReader = new Ext.data.CFQueryReader(
{id:'PTS_ID'},
[
{name: 'PTS_ID', mapping: 'PTS_ID', type: 'int'},
{name: 'Project_Number',mapping: 'PROJECT_NUMBER', type: 'string'},
{name: 'Project_Title', mapping: 'PROJECT_TITLE', type: 'string'},
{name: 'Project_SDate', mapping: 'PROJECT_SDATE', type: 'date'},
{name: 'Project_EDate', mapping: 'PROJECT_EDATE', type: 'date'},
{name: 'Last_SynDate', mapping: 'LAST_SYNDATE', type: 'date'},
{name: 'RO_Name', mapping: 'RO_NAME', type: 'string'},
{name: 'CO_Name', mapping: 'CO_NAME', type: 'string'}
]
);

//datastore
var gridDS = new Ext.data.Store({
url: 'JSON/projectListJSON.cfm?status='+URLvar, method:'GET',
reader: gridReader,
sortInfo:{field:'Project_Number', direction:'ASC'}
});

The JSON:

{"ROWCOUNT":1,"COLUMNS":["PTS_ID","PROJECT_NUMBER","PROJECT_TITLE","PROJECT_SDATE","PROJECT_EDATE","LAST_SYNDATE","RO_NAME","CO_NAME"],"DATA":{"PTS_ID":[582],"PROJECT_NUMBER":["6340044"],"PROJECT_TITLE":["FY 03 EMERGENCY SUPPORT FOR ET"],"PROJECT_SDATE":["October, 01 2003 00:00:00"],"PROJECT_EDATE":["January, 11 2010 00:00:00"],"LAST_SYNDATE":["April, 06 2006 11:47:57"],"RO_NAME":["East Africa"],"CO_NAME":["ETHIOPIA"]}}

crxtech
6 Mar 2009, 12:26 PM
Turns out the JSON format that SerializeJSON(queryName, true); returns wasn't right for your custom reader. I changed it to SerializeJSON(queryName); and it works great loads the data 5x faster then my manual JSON creation that I was using in CF7.

I have one more question: Is there a way to use this to load form data?

I currently load form data like this, and I create the JSON manually in the format that extJS likes...

main.form.load({
url:'JSON/FmMainJson.cfm',
method:'GET',
waitTitle:'Please wait',
waitMsg:'Loading data',
success: function () {...

CutterBl
7 Mar 2009, 5:45 AM
Typically I'll have my CFC methods return their standard data types (query, struct, etc), so that I can use them through either Ajax or within regular application flow without having separate methods. To get my JSON return for Ajax methods, I'll pass the returnFormat:'JSON' argument in my Ajax calls.

As far as your form processing, you're handling it in much the same way I do. I might shorten the process by creating an object to pass in to the request:



var upd = record.data; // Ext record object's data property contains all the values
upd.method = 'myFormProcessor';
upd.returnFormat = 'JSON';

Ext.Ajax.request({
url:'/path/to/my/process.cfc',
params: upd, // previously defined js object
success: function(){
// success callback
},
failure: function(){
// failure callback
}
});

vurcease
7 Mar 2009, 7:26 AM
It's been ages since this thread was active. Either everyone is doing this differently now and I'm behind the times, or everyone has it working nicely with no problems - except me.

It looks like I'm 99% of the way there. My call returns JSON, but for some reason the store isn't getting loaded by the reader. When I examine the results with Firebug, there is one record in the JSON data set, but the getCount and getTotalCount methods on the store are showing zero, and the items array is empty. Here's my code - anyone have any ideas what's wrong here? My next steps are to dive into the Ext code itself to figure out where the JSON data gets passed into the items array - this is not my preferred task for the day.




var myDataModel = [
{name: 'messageTemplateId', mapping: 'MESSAGETEMPLATEID'},
{name: 'name', mapping: 'NAME'},
{name: 'format', mapping: 'FORMAT'},
{name: 'isApproved', mapping: 'ISAPPROVED'},
{name: 'comment', mapping: 'COMMENT'},
{name: 'subject', mapping: 'SUBJECT'},
{name: 'layout', mapping: 'LAYOUT'},
{name: 'body', mapping: 'BODY'}
];
var myCFReader = new Ext.data.CFQueryReader({id:'MESSAGETEMPLATEID'}, myDataModel);

// create the Data Store
var store = new Ext.data.JsonStore({
url: '/path/to/service.cfc'
,baseParams: {method:'getAll', returnFormat:'JSON'}
,fields: myDataModel
,reader: myCFReader
});

CutterBl
8 Mar 2009, 5:04 AM
@vurcease -

Simple fix. Define the store as a basic Ext.data.Store, and you don't need the fields attribute, as they're already defined in the reader definition.

vurcease
8 Mar 2009, 5:58 PM
Thanks Cutter, totally did the trick! I really appreciate your response.

crxtech
10 Mar 2009, 10:17 AM
Is there a way to use this query reader with a paging grid? CF doesn't include a total record count in the JSON.

CutterBl
10 Mar 2009, 11:02 AM
If you were to use the built in QueryConvertForGrid() method, then CF would automatically pass a TOTALROWCOUNT node in the JSON return, which would be a count of all records of the query being fed to the method. The returned records are a subset of the total query return you might pass to that function.

This works, and can quickly move someone with beginner's skill in the creation of a basic paging grid. However, dealing with large recordsets it would put a greater strain on the db server than is really necessary. That's where the reader can be very handy. You can define the struct being returned from your method, so you can get a total count to apply to a TOTALROWCOUNT key, while applying a paged query result to a QUERY key. This allows you to write a real paging query that is optimal for your db and architecture, while maintaining support for paging. Set your CFC method to return 'struct' (making the method reusable within your application) with access 'remote', then pass returnFormat:'JSON' as a parameter of your baseParams in your store configuration.

crxtech
11 Mar 2009, 10:22 AM
Cutter thanks for all your help and fast replies.

alexi
17 Apr 2009, 11:07 AM
I am using the CFQueryReader and 'QueryConvertForGrid' function in the .cfc.

Paging works fine except the last page shows 'Displaying 601 - 625 of 610 records' instead of 'Displaying 601 - 610 of 610 records.'

Any hints on how to fix this?

CutterBl
17 Apr 2009, 4:18 PM
I have a version 1.1 that's going through final testing right now, which might resolve your issues. Give me a day or two to finalize it and hopefully we can get you straightened out.

CutterBl
21 Apr 2009, 4:54 AM
OK folks, Justin Carter, Adam Bellas, and a few others have been helping me test some changes/updates that I've made to the CFQueryReader. These changes help to more completely round out the functionality of this custom reader, finally allowing 'root' and 'id' definitions in your reader config, properly addressing data accessors, allowing you to not have to upper case your mapping names in your field definitions, and other goodies that are more clearly outlined in the comments of the file. Some fairly extensive testing has already been done, but before I finalize the release of this (to the ux library and RIAForge, with examples) I would like to get some final testing from the broader CF-Ext community.

This update should be compatible for both Ext 2.x and 3.x



/*
* Ext JS Library 2.0
* Copyright(c) 2006-2007, Ext JS, LLC.
* licensing@extjs.com
*
* http://extjs.com/license
*
*******************************************
* Steve 'Cutter' Blades (CutterBl) no.junkATcutterscrossingDOTcom
* http://blog.cutterscrossing.com
*
* @@@ Ext.data.CFQueryReader v 1.1 [03.10.09] @@@
*
* Inspired by the CFJsonReader, originally writtin by John Wilson (Daemach)
* http://extjs.com/forum/showthread.php?t=21408&highlight=cfjsonreader
*
* This Custom Data Reader will take the JSON return of a ColdFusion
* Query object, rather returned straight up, or via the ColdFusion
* QueryConvertForGrid() method.
*
* The CFQueryReader constructor takes two arguments
* @meta : object containing key/value pairs for each record (See ArrayReader Config Options)
* @recordType : field mapping object
*
* ------------------------------------------------------------------------------------
* REVISON: [03.10.09]
* Through the 'meta' argument, you can now define which JSON node is the 'root',
* 'success', or the 'totalProperty', as well as the 'id'.
*
* If 'root' is undefined in the meta then it will look for a 'QUERY' node in the
* object root, otherwise it will assume the object root is the root of the JSON object.
*
* if the object root contains a 'TOTALROWCOUNT' node, it will apply it's value
* to the totalRowCount. If a 'totalProperty' is defined in the meta, it will
* take the node of that name value, and apply it's value to the totalRowCount. If
* neither is available, it will automatically count all records processed, and
* apply that count to the value of totalRowCount.
*
* With this revision, we also remove the requirement for using uppercase values for
* 'mapping' definitions
* -------------------------------------------------------------------------------------
*
* The recordType object allows you to alias the returned ColdFusion column
* name (which is always passed in upper case) to any 'name' you wish, as
* well as assign a data type, which your ExtJS app will attempt to cast
* whenever the value is referenced.
*
* ColdFusion's JSON return, for a ColdFusion Query object, will appear in the
* following format:
*
* {"COLUMNS":["INTVENDORTYPEID","STRVENDORTYPE","INTEXPENSECATEGORIESID",
* "STREXPENSECATEGORIES"],"DATA" :[[2,"Carpet Cleaning",1,"Cleaining"],
* [1,"Cleaning Service",1,"Cleaining"]]}
*
* The ColdFusion JSON return on any query that is first passed through
* ColdFusion's QueryConvertForGrid() method will return the object in the
* following format:
*
* {"TOTALROWCOUNT":3, "QUERY":{"COLUMNS":["MYIDFIELD","DATA1","DATA2"],
* "DATA":[[1,"Bob","Smith"],[6,"Jim","Brown"]]}}
*
* The Ext.data.CFQueryReader is designed to accomodate either format
* automatically. You would create your reader instance in much the same
* way as the CFJsonReader was created:
*
* var myDataModel = [
* {name: 'myIdField', mapping: 'MYIDFIELD'},
* {name: 'data1', mapping: 'DATA1'},
* {name: 'data2', mapping: 'DATA2'}
* ];
*
* var myCFReader = new Ext.data.CFQueryReader({id:'myIdField'},myDataModel);
*
* Notice that the 'id' meta value mirrors the alias 'name' of the record's field.
*
* You could also define your reader within the Store:
*
* var myStore = new Ext.data.Store({
* reader: new Ext.data.CFQueryReader({
* id:'myIdField', // matches the 'name' attribute of your id field
* root:'rootOfQueryJSON', // Case Specific
* totalProperty:'someTotalsNode' // Case Specific
* },[
* {name: 'myIdField', mapping: 'myIdField'},
* {name: 'data1', mapping: 'data1'},
* {name: 'data2', mapping: 'data2'}
* ]),
* url:'/path/to/some/remote/proxy.cfc',
* baseParams:{
* method:'myMethod',
* returnFormat:'JSON'
* someMethodArg: numericValue
* anotherMethodArg: 'stringValue'
* }
* });
*/

Ext.data.CFQueryReader = Ext.extend(Ext.data.ArrayReader, {
read : function(response){
var json = response.responseText;
var o = eval("("+json+")");
if(!o) {
throw {message: "CFQueryReader.read: Json object not found"};
}
return this.readRecords(o);
},

readRecords : function(o){
this.jsonData = o;
var s = this.meta, Record = this.recordType,
f = Record.prototype.fields, fi = f.items, fl = f.length;

if (!this.ef) {
if(s.successProperty) {
this.getSuccess = this.getJsonAccessor(s.successProperty);
}

if(s.root){
this.getRoot = this.getJsonAccessor(s.root + '.DATA');
this.getQueryRoot = this.getJsonAccessor(s.root);
} else {
this.getRoot = (o.QUERY) ? this.getJsonAccessor('QUERY.DATA') : this.getJsonAccessor('DATA');
this.getQueryRoot = function(){
return (o.QUERY) ? o.QUERY : o;
}
}
if(s.totalProperty) {
this.getTotal = this.getJsonAccessor(s.totalProperty);
} else if(o.TOTALROWCOUNT) {
this.getTotal = this.getJsonAccessor('TOTALROWCOUNT');
}
}

var root = this.getRoot(o), c = root.length, totalRecords = c, success = true;
var cols = this.getQueryRoot(o).COLUMNS, d = cols.length;


if(s.totalProperty || o.TOTALROWCOUNT){
var v = parseInt(this.getTotal(o), 10);
if(!isNaN(v)){
totalRecords = v;
}
}

if(s.successProperty){
var v = this.getSuccess(o);
if(v === false || v === 'false'){
success = false;
}
}

// get the array location of each mapping
for(b=0;b < fl; b++){
var fMap = (fi[b].mapping !== undefined && fi[b].mapping !== null) ? fi[b].mapping : fi[b].name;
fi[b].mapArrLoc = cols.indexOf(Ext.util.Format.uppercase(fMap));
}

if (!this.ef) {
if (s.id) {
this.getId = function(rec){
var r = rec[s.id]
return (r === undefined || r === "") ? null : r;
}
} else {
this.getId = function(){return null;};
}
this.ef = [];
for(var i = 0; i < fl; i++){
f = fi[i];
var map = (f.mapping !== undefined && f.mapping !== null) ? f.mapping : f.name;
this.ef[i] = function(rec){
var r = rec[map];
return (r === undefined || r === "") ? null : r;
}
}
}

var records = [];

for(var i = 0; i < c; i++){
var n = root[i];
var values = {};
for(var j = 0, jlen = fl; j < jlen; j++){
var f = fi[j];
var k = f.mapArrLoc !== undefined && f.mapArrLoc !== null ? f.mapArrLoc : j;
var v = n[k] !== undefined ? n[k] : f.defaultValue;
v = f.convert(v, n);
values[f.name] = v;
}
var rec = new this.recordType(values, this.getId(values));
rec.json = values;
records[i] = rec;
}

return {
success: success,
records : records,
totalRecords : totalRecords
};
}
});

RickFaircloth
8 Jul 2009, 10:41 AM
Hi, Cutter...

I'm trying to get my mind around how Ext works and have been bumping into the term "readers", and specifically, in this case, your "CFQueryReader".

I'm working on trying to set up a login using a CFC method for processing, but can't quite figure out how to construct everything.

In jQuery (which I'm attempting to move away from), I would just specify "dataType: 'json'" and in the url, use 'component.cfc?method=login&returnformat=json' and that was all I had to do to have json flowing to and from a component and jQuery.

Is a reader, such as CFQueryReader, necessary for translating data from Ext and CF using a component method?

Thanks for the insight... (and if you know of a complete example of Ext login with CF backend that employs a CFC method that you could point me to I would *really* appreciate it!)

Rick

CutterBl
8 Jul 2009, 10:48 AM
Rick,

No, the CFQueryReader is only necessary for populating a data Store object, and would be used with a Grid component or a ComboBox...

If you specify a returnFormat of 'JSON', then whatever data being returned by the function will automatically be converted to a JSON string when passed back to the browser. At that point, it's up to you how to parse that string into any JavaScript value.

kanntronics
9 Jul 2009, 7:15 PM
@CutterBl

it seems CFQueryReader doesn't work with latest revision of Ext 3.0.. changes in rev 4714 broke it.. maybe there is a problem with the extractor

CutterBl
10 Jul 2009, 2:35 AM
No, CFQueryReader works with the latest and greatest. Are you using CFQueryReader v1.1? If not, check out RIAForge (http://cfqueryreader.riaforge.org) for the latest.

kanntronics
10 Jul 2009, 2:57 AM
yes, i'm using the latest CFQueryReader 1.1.. it work well with Ext 3.0 from the download section but fail with head rev from svn..

if I update to rev 4713, it work fine..

CutterBl
10 Jul 2009, 6:16 AM
I haven't downloaded the latest in a day or two. I'll give it a look first chance.

CutterBl
29 Jul 2009, 2:03 PM
CFQueryReader has been updated for compatibility w/ ExtJs 2.x & 3.x

New Thread: http://extjs.com/forum/showthread.php?p=365992#post365992

arunshocky
19 Aug 2009, 5:49 AM
I tried to use a combo box and tried to retrieve data from the database and display it in the combo...I retrieve data based on some filter conditions in the other combo boxes.I have set the mode of the combo box to be remote.
when I use it for the first time I could see the loading indicator with the loading text.when I try to view from the combo again I give some filter conditions to retrieve but I could not see the loading indicator for the subsequent retrievals.Please help:-|

CutterBl
19 Aug 2009, 5:57 AM
@arunshocky,

Two questions:


Are you using the latest version of CFQueryReader (http://extjs.com/forum/showthread.ph...992#post365992)?
Are you tracking progress in Firebug? Do you see the additional server requests being made?

realjax
22 Oct 2009, 12:08 AM
In CFQueryReader.js there is this line (at 161):

fi[b].mapArrLoc = cols.indexOf(Ext.util.Format.uppercase(fMap));

This means, that cfqueryreader *always* searches for capitalised column names in the receiving JSON string, even when they are not..

so a Json string like :

{"COLUMNS":["ID","afield"],"DATA":[[1,"1"]]}

will (silently!) fail to load data because CFQueryReader will look for 'AFIELD' instead of 'afield'...

CutterBl
22 Oct 2009, 6:23 AM
You are right, that would fail. I've never seen a ColdFusion return that didn't uppercase the column names, even in manually built queries using QueryNew. However, I can see the point.

The issue at hand is that JavaScript is case sensitive, whereas ColdFusion is not. We have to convert the return, in some fashion, to standardize the content prior to processing. For this, I would uppercase the values of the COLUMNS array (around line 140) prior to processing:



var cols = this.getQueryRoot(o).COLUMNS;

for (var i = 0;i < cols.length;i++){
cols[i] = cols[i].toUpperCase();
}


Give that a try, and see if it resolves the issue. Let me know if that works out...

realjax
23 Oct 2009, 1:40 AM
Thanks for the reply Cutter!

Your solution is of course one that would work. However, it still results in changing the column names from what was received through the json string. This may result in unexpected behaviour when, for instance, one defines the column model for a grid only to find that a dataIndex name has been uppercased...

I just changed this line:


fi[b].mapArrLoc = cols.indexOf(Ext.util.Format.uppercase(fMap));into:


fi[b].mapArrLoc = cols.indexOf(fMap);Which works just fine, with both upper,lower and mixed case columns names..

Sidenote: I would never even have run into this 'problem' where it not for the following situation: I was already working with CFQueryReader when a coworker started using Coldbox. Coldbox can create Json output which it lowercases by default..

jergarmar
23 Nov 2009, 4:48 PM
Ha! Looks like someone else got here before me. I, too, have been fiddling around with creating a dynamic CfQueryStore in which the data comes from a JSON-returned query. However, I want all of the fields and datatypes configurations to be sent from the server as well. I ran into enough limitations and annoyances with passing metaData that I created my own, Coldfusion-specific way to do this.

Eventually I want to be able to pass a Coldfusion query object + data config object + column config object and have it populate a CfGrid without any javascript-coded settings. One nice result of this would be that I could have a generic CFC method get the datatype information from the database and then pass that along to Ext in the browser.

So far I've got the CfQueryStore working so that I can do this:



var myCfStore = new Ext.ux.CfQueryStore({
url: 'coldfusionTemplate.cfc'
,root: 'MYQUERY' // this is where the entire JSON query object is, NOT just the data
})


The store (mostly the reader, actually) automatically loops through the "COLUMNS" array and configure it with the passed-in "CONFIGS" object. So the only thing I need to figure out is how to extend the GridPanel to make it get column information from the JSON. I suppose even a standard FormPanel wouldn't be too tough after that.

Anyway, the whole point to this was that it was nice to see others combining Coldfusion with Ext. I might have started with your solution if I had found it earlier, since it looks like you've got something a bit more solid than what I have.

CutterBl
23 Nov 2009, 5:25 PM
@jergarmar,

Actually, this thread is out of date. I wrote the CFQueryReader (http://blog.cutterscrossing.com/index.cfm/CFQueryReader) right after I finished "Learning Ext JS", and have been enhancing it ever since. I do like your idea, though it might only work in the simplest of situations, as I rarely want to show all the columns returned in a query, or even name the columns that way. But, any implementation has value, if for nothing else then as a learning experience.

http://www.extjs.com/forum/showthread.php?t=76071
http://cfqueryreader.riaforge.org

jergarmar
23 Nov 2009, 6:55 PM
@jergarmar,

Actually, this thread is out of date. I wrote the CFQueryReader (http://blog.cutterscrossing.com/index.cfm/CFQueryReader) right after I finished "Learning Ext JS", and have been enhancing it ever since. I do like your idea, though it might only work in the simplest of situations, as I rarely want to show all the columns returned in a query, or even name the columns that way. But, any implementation has value, if for nothing else then as a learning experience.


I admit that what I am trying to do might be rather specific, but I can control both of the scenarios you mention. If I don't want all the columns returned I can either do a server-side query of queries for my Ext page, or also pass back a column array that specifies the number and order of the columns I want (I'm don't HAVE to use the "COLUMNS" array -- it's just convenient when possible). If I want to rename the columns, I'll either do it server-side with sql aliasing or pass back "dataIndex" values in my config object. That way I can still call things whatever I want.

That being said, I recognize that most people don't have a need to show many different sets of grid columns (like for each kind of security group).

CutterBl
23 Nov 2009, 8:11 PM
@jergarmar,

Yes, I have used Direct. As a matter of fact, the example in the CFQueryReader download on RIAForge includes one example with a DirectStore accessed through an API and Router configured with the DirectCFM (http://www.extjs.com/forum/showthread.php?t=67983) stack that Aaron Conran wrote. You don't have to use DirectCFM though, you can write your own stack, according to the remoting specifications (http://www.extjs.com/products/extjs/direct.php).

Direct is a way of marshalling all of your data access services inside their own area. It's actually very similar to exposing remoting methods of a CFC as a JavaScript object, like we do with cfajaxproxy. You should go to the blog post (http://www.extjs.com/blog/2009/05/13/introducing-ext-direct/) (few months back) and read it more in depth. It's not really telling you to do anything different with your server-side stack, per se, but rather helps one organize their front and back end code more efficiently, especially in CRUD operations.

davidsanderson
30 Dec 2009, 2:07 PM
Can someone post an example of paging within an Ext GridPanel using CFQueryReader in the store that reads a query that has been converted using QueryConvertForGrid? Thanks!

pbaylis
28 Mar 2010, 9:36 PM
This is a pair of butt-cheeks for me to install and get running. Could we get some clearer install docs? Why should all pathing be as if site root? What does that mean actually? I rename my wwwroot folder to "cfqueryreader" or vice versa?

CutterBl
31 Mar 2010, 5:33 PM
Your site root is dependent upon your web server configuration. In a typical IIS configuration, if only a single site is defined, then the webroot of your domain for the Default Web Site is C:\Inetpub\wwwroot. If you are defining multiple domains, each site can have a different site root, which is defined within the Properties editor of that site. The site root is the primary root level directory of the site you are setting up, so the sample code would be copied into that directory. Ultimately it doesn't matter, as you can change the pathing of the script and css files to match whatever directory structure you are using, should you place the code in a different directory.

Note in above posts that there is a new thread for the CFQueryReader, as well as updates within the SVN repository on RIAForge. I am also working on an updated reader that will also provide full CRUD support for usage with Ext.Direct, though it will probably be about a month before I release it, as I'm buried in writing the 2nd Edition of Learning Ext JS, and still have to finalize the example code for my CF + ExtJs preso for cf.Objective().

ospillane
11 Jun 2011, 10:08 AM
I needed a version of CFQueryReader to work with ExtJS version 4 and couldn't find one so made a few modifications myself (couldn't upload the file so pasting the source here incase anyone needs it).
FYI, this version only works when 'queryFormat=row' is used, I haven't yet attempted to make changes for 'queryFormat=column'.


Ext.define('Ext.data.reader.CFQueryReader', {
extend: 'Ext.data.reader.Array',
alternateClassName: 'Ext.data.reader.CFQueryReader',
alias : 'reader.CFQueryReader',

totalProperty: 'TOTALROWCOUNT',

read : function(response){
var json = response.responseText;
var o = eval("("+json+")");
if(!o) {
throw {message: "CFQueryReader.read: Json object not found"};
}
return this.readRecords(o);
},

readRecords : function(o){
//START: Added this block of code to resolve IE 'indexOf' compatability issue:
//http://www.tutorialspoint.com/javascript/array_indexof.htm
if (!Array.prototype.indexOf)
{
Array.prototype.indexOf = function(elt /*, from*/)
{
var len = this.length;

var from = Number(arguments[1]) || 0;
from = (from < 0)
? Math.ceil(from)
: Math.floor(from);
if (from < 0)
from += len;

for (; from < len; from++)
{
if (from in this &&
this[from] === elt)
return from;
}
return -1;
};
}
//END: Added this block of code to resolve IE 'indexOf' compatability issue

this.jsonData = o;
//var s = this.meta,
Record = this.model,
f = Record.prototype.fields, fi = f.items, fl = f.length,reset = false;
if(typeof this.getJsonAccessor != "function"){
this.getJsonAccessor = this.createAccessor;
}
if (!this.ef || !this.getQueryRoot) {
if(this.successProperty) {
this.getSuccess = this.getJsonAccessor(this.successProperty);
}

if(this.root){
this.getRoot = this.getJsonAccessor(this.root + '.DATA');
this.getQueryRoot = this.getJsonAccessor(this.root);
} else {
this.getRoot = (o.QUERY) ? this.getJsonAccessor('QUERY.DATA') : this.getJsonAccessor('DATA');
this.getQueryRoot = function(){
return (o.QUERY) ? o.QUERY : o;
};
}
if(this.totalProperty) {
this.getTotal = this.getJsonAccessor(this.totalProperty);
} else if(o.TOTALROWCOUNT) {
this.getTotal = this.getJsonAccessor('TOTALROWCOUNT');
}
}

var root = this.getRoot(o),
c = root.length,
totalRecords = c,
success = true;
var cols = this.getQueryRoot(o).COLUMNS;

for (var i = 0;i < cols.length;i++){
cols[i] = cols[i].toUpperCase();
}

if(this.totalProperty || o.TOTALROWCOUNT){
var v = parseInt(this.getTotal(o), 10);
if(!isNaN(v)){
totalRecords = v;
}
}

if(this.successProperty){
var v = this.getSuccess(o);
if(v === false || v === 'false'){
success = false;
}
}

for(b=0;b < fl; b++){
var fMap = (fi[b].mapping !== undefined && fi[b].mapping !== null) ? fi[b].mapping : fi[b].name;
fi[b].mapArrLoc = cols.indexOf(fMap.toUpperCase());
}
if (!this.ef || reset === true) {
if (this.id) {
this.getId = function(rec){
var r = rec[this.id];
return (r === undefined || r === "") ? null : r;
};
} else {
this.getId = function(){return null;};
}
this.ef = [];
for(var i = 0; i < fl; i++){
f = fi[i];
var map = (f.mapping !== undefined && f.mapping !== null) ? f.mapping : f.name;
this.ef[i] = function(rec){
var r = rec[map];
return (r === undefined || r === "") ? null : r;
};
}
}

var records = [];
for(var i = 0; i < c; i++){
var n = root[i];
var values = {};
for(var j = 0, jlen = fl; j < jlen; j++){
f = fi[j];
var k = f.mapArrLoc !== undefined && f.mapArrLoc !== null ? f.mapArrLoc : j;
var v = n[k] !== undefined ? n[k] : f.defaultValue;
v = f.convert(v, n);
values[f.name] = v;
}
var rec = new this.model(values, this.getId(values));
rec.json = values;
records[i] = rec;
}

return Ext.create('Ext.data.ResultSet', {
total : totalRecords,
records: records,
success: success
});
}
});

CutterBl
13 Jun 2011, 2:39 AM
From basic testing, CFQueryReader already worked with Ext JS 4 & Sencha Touch. CFQueryReader was designed to use the base configuration that ColdFusion creates without defining queryFormat. In using queryFormat, you would definitely require a different custom reader, but the point was that you didn't need to put that extra process on your ColdFusion server.

I see that you have converted the object to the new class definition setup. This is something I need to add in. In your rewrites, did you write in the ability to use this either/or? (with or without queryFormat being used). Also, were you using the latest version out of RIAForge (http://cfqueryreader.riaforge.org)? Send me a direct message here, so that I can credit you on any updates put into version control.