Alpha Video Training
Results 1 to 13 of 13

Thread: JSTree - getting database table into JSON format

  1. #1
    Member pettechservices's Avatar
    Real Name
    Chris Sutton
    Join Date
    Apr 2012
    Location
    Southern New Jersey
    Posts
    388

    Default JSTree - getting database table into JSON format

    Hi,

    DavidK was helping me out and had recommended JSTree, which is a jQuery tree control.

    I can perform an AJAX callback to get JSON data from the database. I'm really lost on how to properly format the JSON data from the table.

    I have basically done the following in my XBasic Function for the callback:

    Function cbGetCats as c (e as p)
    dim cn as sql::connection
    cn.Open("::Name::dbConn")
    cn.Execute("select cfgcat_id as id,cfgcat_parentnode as parent, cfgcat_desc as text from cfg_categories")
    rs=cn.ResultSet
    txt=rs.ToJSONObjectSyntax()
    cbGetCats = txt

    end Function

    I thought this would be enough in my callback but nothing is appearing in JStree, and I cannot figure out how to use FireBug to see what is actually returning, if anything.


    I have JSTree being called as:

    function doJSTree() {
    jQuery('#jstree_div').jstree({
    'core' : {
    'data' : {
    'url' : {dialog.object}.runAction('getTD')
    }
    }
    });
    }


    getTD is my JavaScript Action, which performs the callback. The callback runs, and I have tested that the database is returning the data properly, because I'll output it to a label.... but it doesn't appear in the totally proper JSON format (it has come back in a variety of ways..sometimes with everything delimited such as \"ID\" etc.. I can't even recall all the ways at this point.

    If I manually add the data in the doJSTree function, without using an Ajax callback, the tree populates correctly. I have even taken my database output from the interactive window before (that appeared to be formatted properly) and placed in manually into the doJSTree function, and it worked...

    Any hints on how to get the data from the table into JSON format????? THANK YOU

  2. #2
    Member pettechservices's Avatar
    Real Name
    Chris Sutton
    Join Date
    Apr 2012
    Location
    Southern New Jersey
    Posts
    388

    Default Re: JSTree - getting database table into JSON format

    Ok, maybe I can get some further help...

    I have figured out how to get JStree on the page, and figured out (I think?) how to return JSON data..

    My issue is this:

    JSTree is asking for a URL for the Ajax callback.... what would that be???? Do I need to publish the whole application first?

    My XBasic function is called: cbGetCats and is in the code section of the project (I moved it from within the component).

    Do I need to somehow create a a5w page to receive the callback, and if so, what do I put in the page??

    Thank you

  3. #3
    "Certified" Alphaholic TheSmitchell's Avatar
    Real Name
    Sarah
    Join Date
    Apr 2012
    Posts
    1,326

    Default Re: JSTree - getting database table into JSON format

    Quote Originally Posted by pettechservices View Post
    Ok, maybe I can get some further help...

    I have figured out how to get JStree on the page, and figured out (I think?) how to return JSON data..

    My issue is this:

    JSTree is asking for a URL for the Ajax callback.... what would that be???? Do I need to publish the whole application first?

    My XBasic function is called: cbGetCats and is in the code section of the project (I moved it from within the component).

    Do I need to somehow create a a5w page to receive the callback, and if so, what do I put in the page??

    Thank you
    I don't know how to make a callback to the current page. I think it's possible, but not knowing how, you'll have to put your code in a separate file.

    The return from your callback needs to be JSON data. Use ? to return data. EG:

    Code:
    'some file called mycallback.ajax.a5w
    
    ? "['Root1','Root2']"

    It looks like you can also use a function to populate the data instead (which might be easier than a separate .ajax file. User preference, I suppose.) If you don't want to have to put your code in separate files, you could use a function. I encountered something similar in Full Calendar. The function takes two parameters. The second is a function you need to call after you've finished doing whatever you need to do. The first is the node that's being loaded.
    Code:
    $('#tree').jstree({
        'core' : {
            'data' : function (obj, cb) {
                {dialog.object}._functions.jstreecb = function (t) { return function (data) { cb.call(t,data); }; }(this);
                {dialog.object}.runAction('getTD');
            }
        }});
    This line: {dialog.object}._functions.jstreecb = function (t) { return function (data) { cb.call(t,data); }; }(this);

    If you're not familiar with this pattern, it's a closure. Since you want to make a callback to the server to get your data, "this" is going to be something completely when it comes time to call it. To ensure you have "this", you can put it in a closure to generate a new function. "this" gets stored in t so it can be referenced in the cb call. Data is still unknown, so the function that's returned by function (t) still requires a parameter, data.

    If you store the function in {dialog.object}._functions.jstreecb (or whatever you want to call it), you can call the function later:

    Code:
    var someOtherFunction = function () {
      if ({dialog.object}._functions.jstreecb) {
        {dialog.object}._functions.jstreecb(['Root1','Root2']);
      }
    }
    Or call it from an ajax callback:
    Code:
    function getData as c (e as p)
      getData = "if ({dialog.object}._functions.jstreecb) { {dialog.object}._functions.jstreecb(['Root1','Root2']); }"
    end function
    Using the function instead of the callback changes what you're xbasic function will have to return. It'll need to call the callback function, {dialog.object}._functions.jstreecb, with the JSON data you generated.

    Hope this gives you options?
    ---
    Sarah
    Last edited by TheSmitchell; 03-11-2015 at 02:51 PM. Reason: spacing issue?
    Alpha Anywhere latest pre-release

  4. #4
    Member pettechservices's Avatar
    Real Name
    Chris Sutton
    Join Date
    Apr 2012
    Location
    Southern New Jersey
    Posts
    388

    Default Re: JSTree - getting database table into JSON format

    Sarah, thank you so much! Can't wait to try this!!!! Thank you again.

  5. #5
    "Certified" Alphaholic TheSmitchell's Avatar
    Real Name
    Sarah
    Join Date
    Apr 2012
    Posts
    1,326

    Default Re: JSTree - getting database table into JSON format

    Quote Originally Posted by pettechservices View Post
    Sarah, thank you so much! Can't wait to try this!!!! Thank you again.
    You're welcome! With any luck, I didn't write bugs into my example.
    Alpha Anywhere latest pre-release

  6. #6
    Member pettechservices's Avatar
    Real Name
    Chris Sutton
    Join Date
    Apr 2012
    Location
    Southern New Jersey
    Posts
    388

    Default Re: JSTree - getting database table into JSON format

    Hi Sarah,

    Thank you! I'm still lost here.... getting into all this is just very new for me - jQuery and JavaScript and Ajax and everything else just seems to be such a large learning curve for me for some reason...

    Here is my Xbasic Function (callback?) defined in my UX:

    Code:
    function cbGetCats as c (e as p)
    
    dim txt as C
    dim ntxt as C
    	
    dim cn as sql::connection
    cn.Open("::Name::dbConn")
    cn.Execute("select cfgcat_id as id,cfgcat_parentnode as parent, cfgcat_desc as text from cfg_categories")
    rs=cn.ResultSet
    txt=rs.ToJSONObjectSyntax()
    ntxt = json_reformat_safe(txt,.t.)
    
    ' for now, skip the database because I can't get that to format properly and force manual JSON 
    ' just to test 
    
    dim abc as C
    
    abc =  <<%json% 
    [
    	{"id":1,"text":"Root Node 1"},
    	{"id":2,"text":"Root Node 2"}
        ]
    %json%
    
    cbGetCats = abc
    End function
    Here is my code to load JSTree initially (I'm find with loading all the children also, which in my mind is better... but I can probably figure that part out once I understand the order of how things go...

    Code:
    function doJSTree() {
    	jQuery('#jstree_div').jstree(
    	{
    	    'core' : {
           	 	'data' : function (obj, cb) {
           	 		{dialog.object}._functions.jstreecb = function (t) { return function (data) {cb.call(t,data); }; }(this);
           	 		{dialog.object}.runAction('getTD');
    	 	 	},
           	 	 'error' : function (jqXHR, textStatus, errorThrown) { 
          			alert('error: ' + jqXHR.responseText); 
          			}
          	      	 }
         });
    
    }
    I'm not really sure where I'm supposed to define: {dialog.object}._functions.jstreecb....? I was using cbGetCats to get the tree information... do I need 2 functions (in xbasic?) for that? Do I still need my runaction of "getTD", which is simply my ajax callback Action JavaScript? I'm assuming that would be redundant.

    sorry for being so confused. I did read the link about closures but I'm still lost.

  7. #7
    "Certified" Alphaholic TheSmitchell's Avatar
    Real Name
    Sarah
    Join Date
    Apr 2012
    Posts
    1,326

    Default Re: JSTree - getting database table into JSON format

    Quote Originally Posted by pettechservices View Post
    Hi Sarah,

    Thank you! I'm still lost here.... getting into all this is just very new for me - jQuery and JavaScript and Ajax and everything else just seems to be such a large learning curve for me for some reason...

    Here is my Xbasic Function (callback?) defined in my UX:

    Code:
    function cbGetCats as c (e as p)
    
    dim txt as C
    dim ntxt as C
    	
    dim cn as sql::connection
    cn.Open("::Name::dbConn")
    cn.Execute("select cfgcat_id as id,cfgcat_parentnode as parent, cfgcat_desc as text from cfg_categories")
    rs=cn.ResultSet
    txt=rs.ToJSONObjectSyntax()
    ntxt = json_reformat_safe(txt,.t.)
    
    ' for now, skip the database because I can't get that to format properly and force manual JSON 
    ' just to test 
    
    dim abc as C
    
    abc =  <<%json% 
    [
    	{"id":1,"text":"Root Node 1"},
    	{"id":2,"text":"Root Node 2"}
        ]
    %json%
    
    cbGetCats = abc
    End function
    Here is my code to load JSTree initially (I'm find with loading all the children also, which in my mind is better... but I can probably figure that part out once I understand the order of how things go...

    Code:
    function doJSTree() {
    	jQuery('#jstree_div').jstree(
    	{
    	    'core' : {
           	 	'data' : function (obj, cb) {
           	 		{dialog.object}._functions.jstreecb = function (t) { return function (data) {cb.call(t,data); }; }(this);
           	 		{dialog.object}.runAction('getTD');
    	 	 	},
           	 	 'error' : function (jqXHR, textStatus, errorThrown) { 
          			alert('error: ' + jqXHR.responseText); 
          			}
          	      	 }
         });
    
    }
    I'm not really sure where I'm supposed to define: {dialog.object}._functions.jstreecb....? I was using cbGetCats to get the tree information... do I need 2 functions (in xbasic?) for that? Do I still need my runaction of "getTD", which is simply my ajax callback Action JavaScript? I'm assuming that would be redundant.

    sorry for being so confused. I did read the link about closures but I'm still lost.
    Confusion is totally expected. I still go cross-eyed trying to understand how ajax callbacks work.

    Basically, with the function method, what I'm proposing is doing a basic ajax callback to calculate your data and then calling a javascript function to update your JSTree when the callback completes.

    **Alpha's ajax callbacks return javascript to execute in the browser.**

    SO

    What you need to do to utilize the function method of populating the data with an ajax callback to the server IS call the callback function at the end of your ajax callback that JSTree wants you to call.

    Because you can't really pass all of the javascript that implements the function callback JSTree wants you to use, you need to save it somewhere. This is where {dialog.object}._functions comes in very handy.

    You squirrel away your callback in a function stored in {dialog.object}._functions. You save the callback function in your 'data' function. 'data' is where jsTree gives you the callback you need to call. This is where you need to save it. (BTW: I did some testing and found that the closure is not needed - huzzah!):
    Code:
    function doJSTree() {
    	jQuery('#jstree_div').jstree(
    	{
    	    'core' : {
           	 	'data' : function (node, nut) {
                            // Save this 'nut' for later:
           	 		{dialog.object}._functions.jstreecb = nut;
      	 	}
                 }
         });
    }
    NEXT, you need to perform the ajax callback to get the JSON data from database:
    Code:
    function doJSTree() {
    	jQuery('#jstree_div').jstree(
    	{
    	    'core' : {
           	 	'data' : function (node, nut) {
                            // Save this 'nut' for later:
           	 		{dialog.object}._functions.jstreecb = nut;
    
                            {dialog.object}.ajaxCallback('','','cbGetCats');
      	 	}
                 }
         });
    }
    In your xbasic function, you need to (1) get the data from the database (I leave this as a challenge for you to solve) and (2) return javascript to call the callback function you saved.
    Code:
    function cbGetCats as c (e as p)
      dim abc as c =<<%json% 
    [
    	{"id":1,"text":"Root Node 1"},
    	{"id":2,"text":"Root Node 2"}
        ]
    %json%
    
      dim js as c =<<%js%
    if ({dialog.object}._functions.jstreecb) {
      {dialog.object}._functions.jstreecb(abc);
    }
    %js%
      js = "var abc = " + abc + ";" + js;
      cbGetCats = js
    
    end function
    And that should get you half way there.


    I worked out a working example with a simple xbasic function and a call in a Client-Side event. It includes handling a child.

    Here's my xbasic:
    Code:
    function cbGetCats as c (e as p)
    
      dim node as c = e.node
      
      dim a1 as c =<<%json% 
    [
    	{"id":"p1","parent":"#","text":"Root Node 1"},
    	{"id":"p2","parent":"#","text":"Root Node 2","children":true}
    ]
    %json%
      dim a2 as c=<<%json%
    [
    	{"id":"p3","parent":"p2","text":"Child Node 1"}
    ]
    %json%
      dim js as c =<<%js%
    if ({dialog.object}._functions.jstreecb) {
      {dialog.object}._functions.jstreecb(node);
    }
    %js%
      dim node_json as c = if(node="parent",a1,a2);
      js = "var node = " + node_json + ";" + js;
      cbGetCats = js
    
    end function
    And here is the code I placed in onInitializeComplete (client-side event):
    Code:
    A5.loadJavascriptFile('javascript/dist/jstree.min.js',function () {jQuery('#jstree_demo_div').jstree( {
    	    'core' : {
           	 	'data' : function (node, cb) {
                    	// Save this 'nut' for later:
           	 		{dialog.object}._functions.jstreecb = nut;
           	 		{dialog.object}.ajaxCallback('','','cbGetCats','','node='+((node.id==="#")?'parent':'child'));
           	 	}
      	 	}
         });});
    I created a <div> element inside a Static text control with the id "jstree_demo_div" and included the .css file in the UX properties. I did not include the jQuery file that ships with jsTree.

    In testing all this, I could only get it to really work after publishing locally. Live Preview & Working Preview didn't want to play ball. "fun"
    Last edited by TheSmitchell; 03-11-2015 at 10:04 PM. Reason: badness
    Alpha Anywhere latest pre-release

  8. #8
    "Certified" Alphaholic TheSmitchell's Avatar
    Real Name
    Sarah
    Join Date
    Apr 2012
    Posts
    1,326

    Default Re: JSTree - getting database table into JSON format

    PS: I read somewhere that your node id cannot start with a number.

    UPDATE: And then I tested that theory and it's total bunk. Number away.
    Alpha Anywhere latest pre-release

  9. #9
    "Certified" Alphaholic TheSmitchell's Avatar
    Real Name
    Sarah
    Join Date
    Apr 2012
    Posts
    1,326

    Default Re: JSTree - getting database table into JSON format

    Also, if you're still confused, ask more questions and I'll try to answer them.
    Alpha Anywhere latest pre-release

  10. #10
    Member pettechservices's Avatar
    Real Name
    Chris Sutton
    Join Date
    Apr 2012
    Location
    Southern New Jersey
    Posts
    388

    Default Re: JSTree - getting database table into JSON format

    Hahaha thank you! :) Trying to figure it out now!!

  11. #11
    Member pettechservices's Avatar
    Real Name
    Chris Sutton
    Join Date
    Apr 2012
    Location
    Southern New Jersey
    Posts
    388

    Default Re: JSTree - getting database table into JSON format

    OH MY GOSH! THIS TOTALLY WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Thank you!! Thank you!! Thank you!! Thank you!! Thank you!! Thank you!! Thank you!! Thank you!! Thank you!! Thank you!! Thank you!! Thank you!! Thank you!!

    I am sending you a PM...

  12. #12
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,723

    Default Re: JSTree - getting database table into JSON format

    jsTree is a really nice jQuery plugin... http://www.youtube.com/watch?v=oV4NwFD86Ws

  13. #13
    Member pettechservices's Avatar
    Real Name
    Chris Sutton
    Join Date
    Apr 2012
    Location
    Southern New Jersey
    Posts
    388

    Default Re: JSTree - getting database table into JSON format

    Yes, I found it to be really easy to figure out once I figured out the Javascript and getting the data into the tree... Sarah was right on with her help and I was easily able to connect the database table to it, return which checkboxes are checked, etc. I'd love to make it into a learning example for others to understand how to actually incorporate it into their own projects.

Similar Threads

  1. Using JSON table to populate UX Component
    By PevishaJ in forum Mobile & Browser Applications
    Replies: 9
    Last Post: 12-16-2014, 12:36 AM
  2. covert text to database format
    By Dick Anderson in forum Alpha Five Version 11 - Desktop Applications
    Replies: 4
    Last Post: 04-25-2013, 10:37 PM
  3. new database using a previous format
    By chorwitz in forum Alpha Five Version 5
    Replies: 1
    Last Post: 08-18-2006, 09:29 PM
  4. Date Format stored in Database
    By Sharon Goh in forum Alpha Five Version 6
    Replies: 1
    Last Post: 09-01-2004, 08:58 AM
  5. A5 Database Format
    By Kurt Virnig in forum Upgrading to Alpha Five from Alpha Four
    Replies: 2
    Last Post: 05-19-2004, 08:14 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •