• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

jqGrid Server side

Engaged ,
Aug 08, 2013 Aug 08, 2013

Copy link to clipboard

Copied

I use jqGrid for my project using following server side code for ColdFusion in my cfc file.

The jqGrid works only for a few pages.

I can scroll for a couple of page using jqGrid pager.

It seems that the page and records information does not send to jqGrid correctly.

Do I need to particular to pass any row, pages and records information from client to make server response correct pages?

Your help and information is great appreciated,

Regards,

Iccsi,

<cffunction name="ccdClaimsLookup" access="remote" returnformat="json">
  
    <cfargument name ="StartDate" required="no" default="">
    <cfargument name ="EndDate" required="no" default="">
    <cfargument name="page" required="no" default="1">
    <cfargument name="rows" required="no" default="10">
    <cfargument name="sidx" required="no" default="">
    <cfargument name="sord" required="no" default="ASC">
   
    <cfset var Mydata = ArrayNew(1)>

     <cfset start = ((arguments.page-1)*arguments.rows)+1>
     <cfset end = (start-1) + arguments.rows>
     <cfset i = 1>

    <cfstoredproc procedure="MySP">
           
      <cfprocparam value = "#StartDate#" CFSQLTYPE = "cf_sql_date">
      <cfprocparam value = "#EndDate#" CFSQLTYPE = "cf_sql_date">
    
      <cfprocresult name="spname" resultset="1">
  </cfstoredproc>
 
     <cfloop query="spname" startrow="#start#" endrow="#end#">

       <cfset Mydata = [#MyID#,#MyName#,#MyDate#,#MyNote#]>

            <cfset i = i + 1>           
  </cfloop>

     <cfset totalPages = Ceiling(spname.recordcount/arguments.rows)>
     <cfset stcReturn = {total=#totalPages#,page=#Arguments.page#,records=#spname.recordcount#,rows=#Mydata#}>
       

<cfreturn #serializeJSON(stcReturn, True)#>
    
</cffunction>

Views

4.2K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Guide , Sep 12, 2013 Sep 12, 2013

You can instantiate an object for the JSONUtil.cfc anywhere in your code that you want.  I usually instantiate it in my Application.cfc onApplicationStart() method and store it as application.JSONUtil (as a variable in the application scope).  I keep all my utility cfc's in a "cfc" folder off the web root, so to instantiate the CFC in my Application.cfc I have this:

application.JSONUtil = createObject("component","cfc.JSONUtil");

Then I can call application.JSONUtil.serializeJSON() or application.

...

Votes

Translate

Translate
Guide ,
Aug 08, 2013 Aug 08, 2013

Copy link to clipboard

Copied

@Isccsi,

I can't comment on the data you are passing to jqGrid, as I haven't used that jQuery plugin myself.  I did notice one thing that could be a problem.  You are using the SerializeJSON() function on your data, but you are also specifying the ReturnFormat on your function as JSON.  Specifying the ReturnFormat causes the value being returned to automatically be serialized to JSON; thus, you are serializing your data twice (once by calling the SerializeJSON function, then ColdFusion does it again automatically when it returns the data).  This may cause JSON to become malformed. 

Since you are not returning your query directly from your function (you are looping through it and building an array of arrays), you shouldn't need the SerializeJSON call at all.  You should be able to just return your stcReturn variable.

HTH,

Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Aug 09, 2013 Aug 09, 2013

Copy link to clipboard

Copied

Thanks a million for the message and help,

jqGrid does not crash any more, but it skips a few pages.

Can the function return query direct not need put in array?

Thanks agin for helping,

Regards,

Iccsi,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Aug 10, 2013 Aug 10, 2013

Copy link to clipboard

Copied

jqGrid only outputs the data. Data retrieval is done in the CFC. So, check if you CFC does its job properly.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Aug 10, 2013 Aug 10, 2013

Copy link to clipboard

Copied

Thanks for the message and help,

I removed SerializeJSON from the cfc cffunction.

like following

<cfreturn stcReturn>

The data returns 60 pages data. It skip around 5 pages.

There are 10 fields data send to jqGrid.

Once I limit to 5 fields then it only skip one page.

jqGrid does not skip any pages when I limit to one primary key field.

It seems that jqGrid does not understand some pages of the data for some reason.

I put the cfc link to Web browser it returns the skip pages data.

Are there any way to check that the cfc get correct datra to jqGrid?

Thanks again for helping,

Regards,

Iccsi,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Aug 11, 2013 Aug 11, 2013

Copy link to clipboard

Copied

"Are there any way to check that the cfc get correct datra to jqGrid?" -- Just call the URL including the parameters. Use Firebugs or Chrome Developer Tools as I mentioned you in your previous discussion about jqGrid.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Aug 12, 2013 Aug 12, 2013

Copy link to clipboard

Copied

Another thing to do, var scope all variables that are local to the ccdClaimsLookup() function.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Aug 12, 2013 Aug 12, 2013

Copy link to clipboard

Copied

Will try to do set variable scope local,

Regards,

Iccsi,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Aug 22, 2013 Aug 22, 2013

Copy link to clipboard

Copied

Thanks a million for helping and information,

I tried many browsers and changed and modified data.

After I delete a few records of data then It works.

It seems that a couple of data have some issue, but I could not figure why those records does not show on the iqgrid.

Thasnks again for helping,

regards,

Iccsi,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Aug 23, 2013 Aug 23, 2013

Copy link to clipboard

Copied

Something to think about, I have found that jsStringFormat misformats strings that contain single quotes and this breaks the json parsing that jquery uses (it's improper json so that is understandable). My trick is that I wrote my own wrapper around jsStringFormat that replaces "\'" back to "'" (backslash-single-quote back to single-quote).

I don't use remote cfc calls so I'm not sure your issue is the same jsStringFormat issue -- and if it is, I don't know how you would fix it!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Aug 23, 2013 Aug 23, 2013

Copy link to clipboard

Copied

Oh, I just remembered, jqGrid (maybe jquery) also has issues with embedded nulls within string data. Here is the actual code my jsStringFormat wrapper uses:

<cfreturn reReplace(replace(jsStringFormat(arguments.str),"\'","'","ALL"),"[^\x01-\x7F]","?","ALL") />

(Note: there is no space between the \x and the 01 in the above code -- the forum editor is putting it there???)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Sep 04, 2013 Sep 04, 2013

Copy link to clipboard

Copied

Thanks for the message,

It seems data issue, because it works after I play and delete data.

is the augument.str the string cffcuntionto send back calling function?

you code should be in the cffunction. please let me know if I am wrong,

the double quotes are from cffunction, if you replace double quotes then the cffunction still return the double code.

Thanks again for helping nd information,

Regards,

Iccsi,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Sep 05, 2013 Sep 05, 2013

Copy link to clipboard

Copied

I write my own raw json output as opposed to using any of the built in cfc json data stuff so I'm not sure how much this will help you. Here is the final code that sends jqGrid data -- the application.utilities.jsonStringFormat(xxx) does the reReplace code I previously mentioned:

<cfoutput>{

"page": #attributes.page#,

"total": #variables.pages#,

"records": #qGrid.recordCount#,

"rows": [

  <cfloop query="qGrid" startrow="#variables.startRow#" endrow="#variables.endRow#">

   {

    "id": #val(documentID)#,

    "cell": [

     "#application.utilities.jsonStringFormat(guid)#",

     "#application.utilities.jsonStringFormat(thumbnail)#",

     "#application.utilities.jsonStringFormat(subject)#",

     "#application.utilities.jsonStringFormat(participants)#",

     "#application.utilities.jsonStringFormat(variables.rs.dateTimeFormat(created))#",

     "#application.utilities.jsonStringFormat(state)#"

    ]

   }<cfif currentRow LT min(variables.endRow,qGrid.recordCount)>,</cfif>

  </cfloop>

}

</cfoutput>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Sep 05, 2013 Sep 05, 2013

Copy link to clipboard

Copied

Thanks for the message and help,

I believe this is cfm file, so in jqGrid url just put cfm file for the server file.

Please let me know if I am wrong,

Thanks again for helping,

Regards,

Iccsi,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Aug 12, 2013 Aug 12, 2013

Copy link to clipboard

Copied

Don't you need to double quotation marks in your JSON variable names and values?

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-79fa.html

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Aug 12, 2013 Aug 12, 2013

Copy link to clipboard

Copied

Thanks for the information and help,

The variable does not have double quotations, but data return has double quotation from JSON function call.

Thanks again,

Iccsi,

{"ROWS":[[1,"July, 24 2013 00:00:00",10.00,10.00,10.00,"Test"],[2,"July, 03 2013 00:00:00",20.00,50.00,100.00,"test"],[3,"July, 15 2013 00:00:00",20.00,50.00,100.00,"test"],[4,"July, 17 2013 00:00:00",101.00,20.00,10.00,"Test10"],[5,"July, 18 2013 00:00:00",101.00,20.00,10.00,"Test11"],[6,"July, 18 2013 00:00:00",101.00,20.00,10.00,"Test11"],[7,"July, 30 2013 00:00:00",101.00,20.00,10.00,"Test17"],[8,"July, 30 2013 00:00:00",101.00,20.00,10.00,"Test17"],[9,"July, 30 2013 00:00:00",101.00,20.00,10.00,"Test19"],[10,"July, 30 2013 00:00:00",101.00,20.00,10.00,"Test19"]],"PAGE":1,"RECORDS":93,"TOTAL":10.0}

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Sep 12, 2013 Sep 12, 2013

Copy link to clipboard

Copied

Thanks a million for helping and information,

I found the issues now,

cffunction returns JSON data type depends on the data.

for example, the code are "0D", "00" and "ZD" in the same field.

cffunction sends out sometimes consider 00 and 0D are numbers without double quote, but ZD consider a string so it includes double quote.

Are there any way to force "0D" and "00" to be a string in JSON data out from cffunction?

Your help and information is great appreciated,

Regards,

Iccsi,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Sep 12, 2013 Sep 12, 2013

Copy link to clipboard

Copied

Unfortunately, this is a common problem with SerializeJSON().

I've mostly stopped using it and switched to an alternative JSON serializer.  The one I'm using is called JSONUtil.cfc by Nathan Mische: http://jsonutil.riaforge.org/.

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Sep 12, 2013 Sep 12, 2013

Copy link to clipboard

Copied

Thanks amillion for the information and help,

Does it consider a bug of ColdFsuion JSON serializer?

I visit the web site, but I could not find the document for this JSONUtil.

Can you please advise where can find user manual document?

Thaanks again for helping,

Regards,

Iccsi,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Sep 12, 2013 Sep 12, 2013

Copy link to clipboard

Copied

I would consider it a bug. But it's been part of CF for so long that it'll probably break a lot of work arounds if they ever fix it.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Sep 12, 2013 Sep 12, 2013

Copy link to clipboard

Copied

Thanks for the message and help,

they are cfc file.

How can I use it, any user document for this or I just have the file in my components folder and calling from my cfc cffunction?

Thanksa gain for helping and information,

regards,

Iccsi,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Sep 12, 2013 Sep 12, 2013

Copy link to clipboard

Copied

You can instantiate an object for the JSONUtil.cfc anywhere in your code that you want.  I usually instantiate it in my Application.cfc onApplicationStart() method and store it as application.JSONUtil (as a variable in the application scope).  I keep all my utility cfc's in a "cfc" folder off the web root, so to instantiate the CFC in my Application.cfc I have this:

application.JSONUtil = createObject("component","cfc.JSONUtil");

Then I can call application.JSONUtil.serializeJSON() or application.JSONUtil.deserializeJSON() wherever I need to in my code.

HTH,

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Sep 12, 2013 Sep 12, 2013

Copy link to clipboard

Copied

Thanks for the information and help,

I have the component created in application.cfc, but should I pass the string or the query to the function

<cfoutput application.JSONUtil.serializeJSON(MyQuery, false, false) or application.JSONUtil.serializeJSON(MyString, False, False)>

</cfoutput>

Thanks again for helping,

Regards,

Iccsi,


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Sep 13, 2013 Sep 13, 2013

Copy link to clipboard

Copied

LATEST

Thanks a million for the information and help,

Regards,

Iccsi,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Sep 12, 2013 Sep 12, 2013

Copy link to clipboard

Copied

The main page has a couple of paragraphs of instructions.  Use CreateObject() to instantiate the CFC into a variable (you might even want to instantiate it in your Application.cfm/.cfc and store it in the application scope for easy access anywhere in your application).

As far as the usage, this paragraph sums it up well:

This component has two public methods, serializeJSON and deserializeJSON, which have method signatures similar to ColdFuison's built in Serialize/DeserializeJSON functions. The JSONUtil deserializeJSON method takes the same parameters as DeserializeJSON, JSONVar and strictMapping, and should produce the same results as that function. The JSONUtil serializeJSON method takes the var and serializeQueryByColumns parameters, just as ColdFusion's built in SerializeJSON function, however this method also takes an optional third parameter, strictMapping. If the strictMapping parameter is not provided or set to false the serialize method should produce the same results as ColdFusion's built in SerializeJSON function. If the Boolean strictMapping parameter is set to true, then the serialize method attempts to convert the ColdFusion data to a JSON string using underlying Java/SQL data types as opposed to using ColdFusion's implicit type conversion conventions.

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation