PDA

View Full Version : [2.0] ColdFusion JsonReader



daemach
20 Dec 2007, 3:51 PM
I was having a hard time dealing with Adobe's JSON format when dealing with grids so I created a new reader class to deal with it. Extract the attachment into your extjs\source\data folder then use it with something similar to the code below.



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

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


This will consume an Adobe json recordset string similar to the following:



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


If you don't provide an id column as the second parameter, Ext will auto-generate ID's for the records.

Enjoy!

nluneau
27 Dec 2007, 8:44 AM
I don't see the attachment ;)

daemach
27 Dec 2007, 9:05 AM
Hmm - I don't know why it didn't stick the first time.

graveyardfashions
22 Feb 2008, 7:15 AM
Hello!

I'm trying to use your reader. I've modified it slightly, as my CF JSON data is a slightly different format (are you using CF 8?):



{"COLUMNS":["USER_ID","FIRST_NAME","LAST_NAME","PASSWORD","EMAIL","TS","B_ACTIVE","TITLE","PHONE","COMPANY","B_ASSESSOR"],
"DATA":[[272,"Marilyn","Abney","test","Marilyn.Abney@TestCompany.com","January, 28 2008 00:00:00",true,null,null,"TestCompany",false],
[6,"Cathy","Adair","test","Cathy.Adair@ca.com","January, 15 2008 00:00:00",true,null,null,"TestCompany",false],
[7,"Stephanie","Adams","test","Stephanie.Adams@ca.com","January, 15 2008 00:00:00",true,null,null,"TestCompany",false]]}


So I updated your code by removing the reference to the QUERY array, and removed the TOTALCOUNT return:



readRecords: function(json){
var aRecords = [];
var cList = json.COLUMNS;
var cData = json.DATA;
var idField = this.meta.id;

for (var i = 0; i < cData.length; i++) {
var oRecord = {};

for (var j = 0; j < cList.length; j++) {
oRecord[cList[j]] = cData[i][j];
}
if (idField) {
var id = cData[i][cList.indexOf(idField)];
aRecords.push(new Ext.data.Record(oRecord, id));
} else {
aRecords.push(new Ext.data.Record(oRecord));
}
}
return {
success: true,
records: aRecords
//,totalRecords: json.TOTALROWCOUNT
};
}


I'm still not successfully getting a response. So:
1. is there something else I need to do with your code to allow it to work with the slightly different CFJSON output?
2. How do I debug this?

Thanks for any help you can give! I've got high hopes for this.

Best wishes,
Cat

daemach
22 Feb 2008, 5:53 PM
Use queryConvertForGrid(). (http://livedocs.adobe.com/coldfusion/8/htmldocs/functions_m-r_18.html#292503)

graveyardfashions
25 Feb 2008, 7:05 AM
Ha! That certainly got me a lot closer, thank you. For anyone else who is curious, the cfc should look something like:



<cfcomponent displayName="User" hint="Handles all user/security issues for the application.">

<cffunction name="getUserList" access="public" returnType="query" output="false"
hint="Returns all the users.">
<cfset var qGetUsers = "">

<cfquery name="qGetUsers" datasource="#application.dbsource#">
SELECT u.user_id, u.first_name, u.last_name, u.password, u.email, u.ts,
u.b_active, u.title, u.phone, c.title as company, u.b_assessor
FROM tblUser u
LEFT JOIN tblCompany c on u.company_id = c.company_id
ORDER BY company, last_name
</cfquery>
<cfreturn qGetUsers>
</cffunction>

<cffunction name="getUserListForGrid" access="remote" output="false"
hint="Returns all the users in the format for the JSON userList grid.">
<cfset var qGetUsersForGrid = "">
<cfset var qUserList = getUserList()>

<cfquery name="qGetUsersForGrid" dbtype="query">
SELECT user_Id, first_name + ' ' + last_name as name, email, company
FROM qUserList
</cfquery>

<cfreturn QueryConvertForGrid(qGetUsersForGrid, 1, qGetUsersForGrid.recordcount)>
</cffunction>
</cfcomponent>
This gets the query data halfway there - but you still need to access it with the returnFormat=JSON, like this:



(...normal includes for ext, then...)

<script language="JavaScript" src="<cfoutput>#APPLICATION.root#</cfoutput>includes/js/CFJsonReader.js"></script>

<script language="javascript">
Ext.onReady(function(){

var user_colModel = new Ext.grid.ColumnModel([
{header: "ID", width: 20, sortable: true, dataIndex: 'USER_ID'},
{header: "Company", width: 150, sortable: true, dataIndex: 'COMPANY'},
{header: "Email", width: 150, sortable: true, dataIndex: 'EMAIL'},
{header: "Name", width: 200, sortable: true, dataIndex: 'NAME'}
]);

var myDataModel = [
{name: 'USER_ID', mapping: 'USER_ID', type:'int'},
{name: 'NAME', mapping: 'NAME', type:'string'},
{name: 'EMAIL', mapping: 'EMAIL', type:'string'},
{name: 'COMPANY', mapping: 'COMPANY', type:'string'}
];

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

var user_DS = new Ext.data.Store({
url: '<cfoutput>#APPLICATION.root#</cfoutput>cfc/user.cfc?method=getUserListForGrid&returnFormat=json',
reader: myCFReader,
listeners: {
loadexception: function(proxy, store, response, e) {
console.log("Response Text?"+response.responseText);
console.log("dgStore Message \n"+proxy+"\n"+store+"\n"+response+"\n"+e.message);
},
load: function(){
console.log('load Worked!');
}
}
});
user_DS.load();

// ****
// Bringing it all together in the Grid
// ****
var user_grid = new Ext.grid.GridPanel({
ds: user_DS,
cm: user_colModel,
anchor:'100% 100%',
width:950,
height:500,
//autoheight: true,
title:'Current System Users',
stripeRows: 'true'
});
user_grid.render('user_grid');
});

bcaufield
29 Feb 2008, 2:39 PM
Hey great job with the CF Reader! I finally got it to work but I have one problem: the grid will not sort. I have an XML data reader that works with the same data and grid configuration and that grid sorts fine, so I think my setup is OK. The only thing different between the two approaches is the data reader. Have you seen anything like this?

graveyardfashions
3 Mar 2008, 9:48 AM
That's really interesting - mine is sorting fine (albeit slowly, because there's a lot of data in there).

Can you post your code? Maybe I can help. :)

bcaufield
5 Mar 2008, 12:14 PM
OK, here's what I'm doing:
The CFC looks like this:

<cfcomponent displayName="User" hint="Handles all user/security issues for the application.">

<cffunction name="getUserListForGrid" access="remote" output="false"
hint="Returns all the users in the format for the JSON userList grid.">
<cfset var qGetUsersForGrid = "">

<cfquery name="qGetUsersForGrid" datasource="theftofservice">
SELECT u.userid, u.userfullname, u.email, d.name as deptname
FROM User u
LEFT JOIN department d on u.accesslevel = d.accesslevel
ORDER BY u.userid
</cfquery>

<!--- Don't serialize for CFJsonReader --->
<cfreturn QueryConvertForGrid(qGetUsersForGrid, 1, qGetUsersForGrid.recordcount)>
</cffunction>
</cfcomponent>This will return JSON that looks like this:


{"TOTALROWCOUNT":9,"QUERY":{"COLUMNS":["USERID","USERFULLNAME","EMAIL","DEPTNAME"],"DATA":[["bbrown","Bill Brown","user@test.com","IT"],["cstrahm","Chris Strahm","user@test.com","Operations"],["guest1","Test User","user@test.com","Test"],["guest2","Test User 2","user@test.com","Operations"],["guest3","Test User 3","user@test.com","IT"],["guest4","Test User 4","user@test.com","Operations"],["lsampson","Linda Sampson","user@test.com","Operations"],["rhansen","Rober Hansen","user@test.com","Operations"],["rwilliams","Bob Williams","user@test.com","Operations"]]}}
My test script looks like this:


Ext.onReady(function() {

// create the data model
var myDataModel = [
// set up the fields mapping into the json
{name: 'ID', mapping: 'USERID', type:'string'},
{name: 'Name', mapping: 'USERFULLNAME', type:'string'},
{name: 'Email', mapping: 'EMAIL', type:'string'},
{name: 'Dept', mapping: 'DEPTNAME', type:'string'}
]

// create the reader
var myReader = new Ext.data.CFJsonReader(myDataModel,{id:'USERID'})

// get the data from coldfusion as json
var user_DS = new Ext.data.Store({
url: 'cfc/user.cfc?method=getUserListForGrid&returnFormat=json',
reader: myReader,
listeners: {
loadexception: function(proxy, store, response, e) {
console.log("Response Text?"+response.responseText);
console.log("dgStore Message \n"+proxy+"\n"+store+"\n"+response+"\n"+e.message);
},
load: function(){
console.log('load Worked!');
}
}
});

// create the column model
var userCols = new Ext.grid.ColumnModel([
{header: 'ID', width: 50, sortable: true, dataIndex: 'USERID'},
{header: 'Name', width: 90, sortable: true, dataIndex: 'USERFULLNAME'},
{header: 'Email', width: 90, sortable: true, dataIndex: 'EMAIL'},
{header: 'Dept', width: 90, sortable: true, dataIndex: 'DEPTNAME'}
]);

// create the grid
var grid = new Ext.grid.GridPanel({
ds: user_DS,
cm: userCols,
viewConfig: {
forceFit: true
},
renderTo: 'content',
title: 'Json Grid Example',
width: 500,
frame: true,
autoHeight: true,
stripRows: true
});

// Show the grid and load it!
grid.render();
user_DS.load();
});
Pretty basic, huh?

BTW, I noticed that the "id:" argument of theCFJsonReader method acts like a "Select Distinct" SQL command; is that by design?

Thanks in advance for your help!

tfrugia
5 Mar 2008, 1:56 PM
bcaufield,

Try this:



var myDataModel = [
// set up the fields mapping into the json
{name: 'USERID', type:'string'},
{name: 'USERFULLNAME', type:'string'},
{name: 'EMAIL', type:'string'},
{name: 'DEPTNAME', type:'string'}
]

tfrugia
5 Mar 2008, 5:05 PM
Sorry, I was too quick to reply - ignore above post, that should not matter.

I am using basically using the exact same code as you and mine is sorting just fine. The only difference is I am not using the mapping and just using the NAME field in the model (which should be sufficient if it matches the mappings). I also provide the same NAME to my column model, but that shouldn't matter either.

Are you getting any errors in firebug when you sort?

bcaufield
6 Mar 2008, 5:46 AM
It works - thanks a lot!

bcaufield
6 Mar 2008, 5:59 AM
tfrugia:

Well I too was too quick to answer; I didn't notice your last post. I don't receive any errors when the sort fails and I am using fire bug with "break on all errors" turned on. While the DataModel changed fix the problem, I am not sure what I was doing wrong; isn't the DataModel supposed to translate a the database field names to the columns's header name? Or is that what the dataIndex is doing and the mapping in the dataModel is redundant?

jonnycattt
19 Mar 2008, 9:15 AM
hey all, i just stumbled across this and wanted to give it a shot. However, I'm getting this error:

this.onMetaChange has no properties

this is the entirety of my js code so far:



Ext.onReady(function(){
var dm = [
{name: 'MASTERID', mapping: 'MASTERID', type: 'int'},
{name: 'MASTERIP', mapping: 'MASTERIP', type: 'string'}
];

var myReader = new Ext.data.CFJsonReader(dm,{id:'MASTERID'});

var serversDS = Ext.data.Store({
url: '/argus/dope/ajax/cfc/DopeAirService.cfc?method=getQueryForExt&functionToRun=getMasterServers&returnformat=json',
reader: myReader,
listeners: {
loadexception: function(proxy, store, response, e) {
console.log("Response Text?"+response.responseText);
console.log("dgStore Message \n"+proxy+"\n"+store+"\n"+response+"\n"+e.message);
},
load: function(){
console.log('load Worked!');
}
}
});
});


The thing is that I don't even see the url call being made in firebug, so something seems to be happening prior to that.

Any advice?

thanks all!

Marc

graveyardfashions
27 Mar 2008, 11:01 AM
What does your cffunction look like?

tfrugia
2 Apr 2008, 1:26 PM
You need to call the load method on the store:
serversDS.load();

jonnycattt
2 Apr 2008, 3:07 PM
THanks a lot for your response all.

my cffunction looks like this, although it's not getting called. my serversDSload(); never gets called, either.


<cffunction name="getQueryForEXT" access="remote">
<cfargument name="functionToRun" type="string" required="true">
<cfset var q_return = "">
<cfinvoke component="#this#" method="#functionToRun#" returnvariable="q_return">
<cfreturn QueryConvertForGrid(q_return,1,q_return.recordcount)>
</cffunction>

the error's happening in ext-all at line 10443 (in the ext-all-debug) file:


Ext.data.Store = function(config){
10413 this.data = new Ext.util.MixedCollection(false);
10414 this.data.getKey = function(o){
10415 return o.id;
10416 };
10417
10418 this.baseParams = {};
10419
10420 this.paramNames = {
10421 "start" : "start",
10422 "limit" : "limit",
10423 "sort" : "sort",
10424 "dir" : "dir"
10425 };
10426
10427 if(config && config.data){
10428 this.inlineData = config.data;
10429 delete config.data;
10430 }
10431
10432 Ext.apply(this, config);
10433
10434 if(this.url && !this.proxy){
10435 this.proxy = new Ext.data.HttpProxy({url: this.url});
10436 }
10437
10438 if(this.reader){
10439 if(!this.recordType){
10440 this.recordType = this.reader.recordType;
10441 }
10442 if(this.reader.onMetaChange){
10443 ------ >>>>> HERE'S THE ERROR: this.reader.onMetaChange = this.onMetaChange.createDelegate(this);
10444 }
10445 }


Is it possible I have an old version of the cfjsonreader?

here's what i have, for what it's worth:


/*
* Ext JS Library 2.0
* Copyright(c) 2006-2007, Ext JS, LLC.
* licensing@extjs.com
*
* http://extjs.com/license
*/
/*
* John Wilson (Daemach) daemach@gmail.com
* http://ideamill.synaptrixgroup.com
*
*/
Ext.data.CFJsonReader = function(recordType, meta){
this.meta = meta || {};
this.recordType = Ext.data.Record.create(recordType);

};

Ext.extend(Ext.data.CFJsonReader, Ext.data.DataReader, {
read: function(response){
var json = response.responseText;
var o = eval("(" + json + ")");

if (!o) {
throw {
message: "CFJsonReader.read: Json object not found"
};
}
if (o.metaData) {

delete this.ef;
this.meta = o.metaData;
this.recordType = Ext.data.Record.create(o.metaData.fields);
this.onMetaChange(this.meta, this.recordType, o);
}
return this.readRecords(o);
},

// private function a store will implement
onMetaChange: function(meta, recordType, o){

},
readRecords: function(json){
var aRecords = [];
var cList = json.QUERY.COLUMNS;
var cData = json.QUERY.DATA;
var idField = this.meta.id;




for (var i = 0; i < cData.length; i++) {
var oRecord = {};

for (var j = 0; j < cList.length; j++) {
oRecord[cList[j]] = cData[i][j];
}
if (idField) {
var id = cData[i][cList.indexOf(idField)];
aRecords.push(new Ext.data.Record(oRecord, id));
} else {
aRecords.push(new Ext.data.Record(oRecord));
}
}
return {
success: true,
records: aRecords,
totalRecords: json.TOTALROWCOUNT
};
}
});


thoughts?

MorbertMacErp
7 Apr 2008, 6:45 AM
I was having a hard time dealing with Adobe's JSON format when dealing with grids so I created a new reader class to deal with it. Extract the attachment into your extjs\source\data folder then use it with something similar to the code below.

Is there any other action required (beyond ensuring extjs\source\data\CFJsonReader.js is in the directory) to use this?

I keep getting the following error from firebug:

Ext.data.CFJsonReader is not a constructor
(no name)()
fire()
fireDocReady()ext-all-debug.js (line 1542)
var myCFReader = new Ext.data.CFJsonReader(myDataModel,{id:'MYIDFIELD'});
ext-all-debug.js (line 1505)

anujg
22 Apr 2008, 2:23 PM
i am using cfjsonreader successfully but cant get the sorting to work, i have tried both remoteSort as true and false. It just doesnt do anything when i try to sort. here is the code



var myDataModel = [


{name: 'CustomerID',type:'string'},

{name: 'FirstName',type:'string'},

{name: 'LastName',type:'string'},

{name: 'Address',type:'string'},

{name: 'Address2',type:'string'},

{name: 'City',type:'string'},

{name: 'State',type:'string'},

{name: 'Zip',type:'string'},

{name: 'Country',type:'string'},

{name: 'Company',type:'string'}


];

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

var user_DS = new Ext.data.GroupingStore({
url: 'gridmanager.cfc?method=getContacts&returnFormat=json',
reader: myCFReader,
listeners: {
loadexception: function(proxy, store, response, e) {
console.log("Response Text?"+response.responseText);
console.log("dgStore Message \n"+proxy+"\n"+store+"\n"+response+"\n"+e.message);
},
load: function(){
console.log('load Worked!');
}
}
,remoteSort:true


});
user_DS.setDefaultSort('LastName','ASC');
user_DS.load({params:{start:0, limit:25}});

graveyardfashions
25 Apr 2008, 7:33 AM
Has anyone else experienced errors using this extension with extJS 2.1? I'm getting an error that I suspect is due to CFJsonReader. ( http://www.extjs.com/forum/showthread.php?t=33644 )

Best wishes,
Cat

wom75
2 May 2008, 7:57 AM
Is there any other action required (beyond ensuring extjs\source\data\CFJsonReader.js is in the directory) to use this?

I keep getting the following error from firebug:

Ext.data.CFJsonReader is not a constructor
(no name)()
fire()
fireDocReady()ext-all-debug.js (line 1542)
var myCFReader = new Ext.data.CFJsonReader(myDataModel,{id:'MYIDFIELD'});
ext-all-debug.js (line 1505)

MorbertMacErp, I'm getting the same error message. Did you find out where it needs to be included?

Thanks

graveyardfashions
8 May 2008, 6:06 AM
You need to have the following line prior to your extJS code:

<script language="JavaScript" src="path/to/includes/js/CFJsonReader.js"></script>

Otherwise, the CFJsonReader will not be available to extJS.

wom75
5 Jun 2008, 11:30 AM
After finding out about CFJSON, I decided to go with that. Sending it the argument, queryFormat="array", it returns data in a format suitable for Ext.

CutterBl
20 Jul 2008, 11:15 AM
I didn't really want to use QueryConvertForPaging() in my CF functions, as I wanted to be able to reuse these methods in other areas of my server-side code. For similar reasons, I didn't want to use the CFJSON.cfc that's out there either, as well as the fact that I didn't want to put the conversion overhead on CF when the client browser can easily handle the transitions for me. So, I adjusted the reader to take in CF's JSON format, even if it wasn't first run through QueryConvertForPaging().



/*
* Ext JS Library 2.0
* Copyright(c) 2006-2007, Ext JS, LLC.
* licensing@extjs.com
*
* http://extjs.com/license
*/
/*
* John Wilson (Daemach) daemach@gmail.com
* http://ideamill.synaptrixgroup.com
*
* MODIFIED:
* [05.05.2008 Steve 'Cutter' Blades]
* Added the ability to read the returned JSON, even if
* you don't use the ColdFusion QueryConvertForGrid() function
*/
Ext.data.CFJsonReader = function(meta, recordType){
this.meta = meta || {};
this.recordType = Ext.data.Record.create(recordType);
};

Ext.extend(Ext.data.CFJsonReader, Ext.data.DataReader, {
read: function(response){
var json = response.responseText;
if(json.indexOf('"QUERY":') < 0){
json = '{"QUERY":' + json + '}';
}
var o = eval("(" + json + ")");
if (!o) {
throw {
message: "CFJsonReader.read: Json object not found"
};
}
if (o.metaData) {
delete this.ef;
this.meta = o.metaData;
this.recordType = Ext.data.Record.create(o.metaData.fields);
this.onMetaChange(this.meta, this.recordType, o);
}
return this.readRecords(o);
},

// private function a store will implement
onMetaChange: function(meta, recordType, o){},

readRecords: function(json){
var aRecords = [];
var cList = json.QUERY.COLUMNS;
var cData = json.QUERY.DATA;
var idField = this.meta.id;

for (var i = 0; i < cData.length; i++) {
var oRecord = {};

for (var j = 0; j < cList.length; j++) {
oRecord[cList[j]] = cData[i][j];
}
if (idField) {
var id = cData[i][cList.indexOf(idField)];
aRecords.push(new Ext.data.Record(oRecord, id));
} else {
aRecords.push(new Ext.data.Record(oRecord));
}
}
if(!json.TOTALROWCOUNT){
json.TOTALROWCOUNT = aRecords.length;
}
//console.log(aRecords)
return {
success: true,
records: aRecords,
totalRecords: json.TOTALROWCOUNT
};
}
});

eric00000007
10 Sep 2008, 5:33 AM
Thanks for this great extension. That's very usefull.

I have the same sorting trouble as mention before. With your last code from 07-20-2008 posted on this thread sorting doesn't work with a grid.

It Works with the code posted at http://extjs.com/forum/showthread.php?t=21408&page=3, but there is no more support of other Coldfusion Json objects.

Let us know if you plan something or if we should correct your last CFJsonReader.

eric00000007
10 Sep 2008, 5:45 AM
Damn, I spent maybe 2 Hours on trying to sort the grid !!!! There is a tiny mixon the last code you sent:


Ext.data.CFJsonReader = function(meta, recordType)
You just invert the 2 arguments !!! from you previous code.

Ext.data.CFJsonReader = function(recordType,meta)Thanks again

Regarding the documentation of the DataReader it is better to keep your last code as reference

eric00000007
10 Sep 2008, 8:40 AM
Damn ! The sorting doesn't work with my second grid !!!!! Even if server retrun JSON object with QueryConvertForGrid() .... Maybe there is a scope problem or I miss something ...
I am tired for today.

CutterBl
1 Dec 2008, 7:35 PM
Inspired by John's Reader, I've written a modified Reader (http://extjs.com/forum/showthread.php?t=53271) that extends the ArrayReader instead of the base DataReader. The modifications now allow for proper field aliasing, type casting, etc. that you typically find with other data readers.