24 Replies Latest reply: Sep 13, 2013 10:59 AM by iccsi RSS

    jqGrid Server side

    iccsi Community Member

      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[i] = [#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>

        • 1. Re: jqGrid Server side
          Carl Von Stetten Community Member

          @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.

          • 2. Re: jqGrid Server side
            iccsi Community Member

            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,

            • 3. Re: jqGrid Server side
              p.sim Community Member

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

              • 4. Re: jqGrid Server side
                iccsi Community Member

                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,

                • 5. Re: jqGrid Server side
                  p.sim Community Member

                  "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.

                  • 6. Re: jqGrid Server side
                    p.sim Community Member

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

                    • 7. Re: jqGrid Server side
                      p.sim Community Member

                      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-79 fa.html

                      • 8. Re: jqGrid Server side
                        iccsi Community Member

                        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}

                        • 9. Re: jqGrid Server side
                          iccsi Community Member

                          Will try to do set variable scope local,

                           

                          Regards,

                           

                          Iccsi,

                          • 10. Re: jqGrid Server side
                            iccsi Community Member

                            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,

                            • 11. Re: jqGrid Server side
                              Steve Sommers Community Member

                              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!

                              • 12. Re: jqGrid Server side
                                Steve Sommers Community Member

                                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???)

                                • 13. Re: jqGrid Server side
                                  iccsi Community Member

                                  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,

                                  • 14. Re: jqGrid Server side
                                    Steve Sommers Community Member

                                    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>

                                    • 15. Re: jqGrid Server side
                                      iccsi Community Member

                                      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,

                                      • 16. Re: jqGrid Server side
                                        iccsi Community Member

                                        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,

                                        • 17. Re: jqGrid Server side
                                          Carl Von Stetten Community Member

                                          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.

                                          • 18. Re: jqGrid Server side
                                            iccsi Community Member

                                            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,

                                            • 19. Re: jqGrid Server side
                                              Steve Sommers Community Member

                                              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.

                                              • 20. Re: jqGrid Server side
                                                Carl Von Stetten Community Member

                                                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.

                                                • 21. Re: jqGrid Server side
                                                  iccsi Community Member

                                                  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,

                                                  • 22. Re: jqGrid Server side
                                                    Carl Von Stetten Community Member

                                                    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.

                                                    • 23. Re: jqGrid Server side
                                                      iccsi Community Member

                                                      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,


                                                      • 24. Re: jqGrid Server side
                                                        iccsi Community Member

                                                        Thanks a million for the information and help,

                                                         

                                                        Regards,

                                                         

                                                        Iccsi,