How does Ajax sourced data work in Lotus Notes/Domino environment?

How does Ajax sourced data work in Lotus Notes/Domino environment?

gh2mgh2m Posts: 63Questions: 16Answers: 0

I am trying simple example of https://datatables.net/examples/data_sources/ajax.html on a Domino server. I created an arrays.txt file and attached to a Lotus Notes document and I am able to pull the data up with '../ajax/data/arrays.txt' ("ajax": 'https://servername/dbname.nsf/view/key/$FILE/arrays.txt' to be exact). But nothing shows on my page. No error either on the browser or server console. Is there anything special in order to use ajax sourced data in Domino? I have successfully used DataTable data: dataSet(array populated with lotusscript agent) without any issue.

Answers

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    Datatables doesn't care nor know what the server type or data source is. The response needs to be a valid JSON response as described in the Datatables Ajax docs. Use the browser's network inspector tool to see what the actual response is.

    Kevin

  • gh2mgh2m Posts: 63Questions: 16Answers: 0

    I was able to resolve the issue by adding sServerMethod: 'POST' to the Datatable definition. Thanks.

    A different question. I have a large data set, over 100k rows, that takes time to load even with "deferRender": true, setting. User doesn't need this data till clicking a button for modal popup, in other words the data is initially hidden. Is there a setting I can use to have page show the page while table is continuously loaded with ajax data in the background. Basically I don't want user have to wait for page loading.

  • allanallan Posts: 61,438Questions: 1Answers: 10,050 Site admin

    Server-side processing can be used to load just the data needed for the current DataTables page. However, you will need to write a server-side script that interfaces with your data set, since we don't provide one that will work with Domino.

    Allan

  • gh2mgh2m Posts: 63Questions: 16Answers: 0

    I got server-side processing somewhat working. My problem is it took about a minute to load 6300 rows (some columns are heavy) and all the rows are in the same page although Show 10 entries is on. Following is my table configuration. I must missed something. Any advise?

    $('#example').DataTable( {
    "processing": true,
    "serverSide": true,
    "ajax": {
    "url": "https://server/dtserversidetest?openagent",
    "type": "POST"
    },
    "deferRender": true
    } )

  • gh2mgh2m Posts: 63Questions: 16Answers: 0

    Also, clicking on a page number, 2, 3, etc., or Next button, or search will cause Invalid JSON response error.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    With serverSide, it should always be fast, as you're not loading 6300 rows at a time, only the number of records displayed on that page, so it sounds like you're not complying with the protocol. The protocol is discussed here. Also see examples here.

    Cheers,

    Colin

  • gh2mgh2m Posts: 63Questions: 16Answers: 0

    I almost followed the example exactly and I can see (from F12 Developer Tools, Network->Response body) my server program produced the following JSON string:
    {
    "draw": 1,
    "recordsTotal": 6000,
    "recordsFiltered": 6000,
    "data": [
    {
    "first_name": "Airi",
    "last_name": "Satou",
    "position": "Accountant",
    "office": "Tokyo",
    "start_date": "28th Nov 08",
    "salary": "$162,700"
    },
    ......
    {
    "first_name": "Cedric",
    "last_name": "Kelly",
    "position": "Senior Javascript Developer",
    "office": "Edinburgh",
    "start_date": "29th Mar 12",
    "salary": "$433,060"
    }
    ]
    }

    Am I missing something?

  • gh2mgh2m Posts: 63Questions: 16Answers: 0

    Actually I was using array as data source. So my response from server is like following:
    {
    "draw": 1,
    "recordsTotal": 57,
    "recordsFiltered": 57,
    "data": [
    [
    "Angelica",
    "Ramos",
    "System Architect",
    "London",
    "9th Oct 09",
    "$2,875"
    ],
    [
    "Ashton",
    "Cox",
    "Technical Author",
    "San Francisco",
    "12th Jan 09",
    "$4,800"
    ],
    ...
    ]
    }

  • gh2mgh2m Posts: 63Questions: 16Answers: 0

    It actually only takes 2.24 seconds (from Taken column of Network -> Summary) to finish the server program. This tells me my Datatable on client side configuration may be the problem. But I can't tell because there are only 4 settings/parameter as you can see above from my 9/21 response.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Can you link to your page so we can take a look, please? I'm still not clear whether all 6000 records are being returned in that message.

    Colin

  • gh2mgh2m Posts: 63Questions: 16Answers: 0

    How can I add test case with server processing code, especially in Lotus Notes/Domino agent? Is that possible?

  • gh2mgh2m Posts: 63Questions: 16Answers: 0

    Here is my test case: http://live.datatables.net/qedaxexe/1/

    My server-side program, Lotus Notes agent, generate and return following response to the datatable:

    '''
    {
    "draw": 1,
    "recordsTotal": 21,
    "recordsFiltered": 20,
    "data": [
    {"Vendor_ID":"1","Vendor_Name":"Company 1","LOB_NM":"CAO","PSN_NO":"101","BUS_ENT_ID":"1"},
    {"Vendor_ID":"2","Vendor_Name":"Company 2","LOB_NM":"GMCA","PSN_NO":"102","BUS_ENT_ID":"2"},
    {"Vendor_ID":"3","Vendor_Name":"Company 3","LOB_NM":"RETAIL","PSN_NO":"103","BUS_ENT_ID":"3"},
    {"Vendor_ID":"4","Vendor_Name":"Company 4","LOB_NM":"RETAIL","PSN_NO":"104","BUS_ENT_ID":"4"},
    {"Vendor_ID":"5","Vendor_Name":"Company 5","LOB_NM":"GT&O","PSN_NO":"105","BUS_ENT_ID":"5"},
    {"Vendor_ID":"6","Vendor_Name":"Company 6","LOB_NM":"CAO","PSN_NO":"106","BUS_ENT_ID":"6"},
    {"Vendor_ID":"7","Vendor_Name":"Company 7","LOB_NM":"RETAIL","PSN_NO":"107","BUS_ENT_ID":"7"},
    {"Vendor_ID":"8","Vendor_Name":"Company 8","LOB_NM":"CAO","PSN_NO":"108","BUS_ENT_ID":"8"},
    {"Vendor_ID":"9","Vendor_Name":"Company 9","LOB_NM":"GT&O","PSN_NO":"109","BUS_ENT_ID":"9"},
    {"Vendor_ID":"10","Vendor_Name":"Company 10","LOB_NM":"GHR","PSN_NO":"110","BUS_ENT_ID":"10"},
    {"Vendor_ID":"11","Vendor_Name":"Company 11","LOB_NM":"LEGAL","PSN_NO":"111","BUS_ENT_ID":"11"},
    {"Vendor_ID":"12","Vendor_Name":"Company 12":"GT&O","PSN_NO":"112","BUS_ENT_ID":"12"},
    {"Vendor_ID":"13","Vendor_Name":"Company 13","LOB_NM":"GT&O","PSN_NO":"113","BUS_ENT_ID":"13"},
    {"Vendor_ID":"14","Vendor_Name":"Company 14","LOB_NM":"CAO","PSN_NO":"114","BUS_ENT_ID":"14"},
    {"Vendor_ID":"15","Vendor_Name":"Company 15","LOB_NM":"AUDIT","PSN_NO":"115","BUS_ENT_ID":"15"},
    {"Vendor_ID":"16","Vendor_Name":"Company 16","LOB_NM":"CAO","PSN_NO":"116","BUS_ENT_ID":"16"},
    {"Vendor_ID":"17","Vendor_Name":"Company 17","LOB_NM":"GHR","PSN_NO":"117","BUS_ENT_ID":"17"},
    {"Vendor_ID":"18","Vendor_Name":"Company 18","LOB_NM":"GT&O","PSN_NO":"118","BUS_ENT_ID":"18"},
    {"Vendor_ID":"19","Vendor_Name":"Company 19","LOB_NM":"GHR","PSN_NO":"119","BUS_ENT_ID":"19"},
    {"Vendor_ID":"20","Vendor_Name":"Company 20","LOB_NM":"GWIM","PSN_NO":"120","BUS_ENT_ID":"20"},
    {"Vendor_ID":"21","Vendor_Name":"Company 21","LOB_NM":"GWIM","PSN_NO":"121","BUS_ENT_ID":"21"}
    ]
    }
    '''

    My issue: it returns all 21 rows and shows on one page although it sets to only show 10 rows. How do I control to only load and show 10 rows, the first page?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    That will be your server-side script returning too many records - it's the responsibility of that script to do the ordering/searching/paging and to return the correct number of records. The protocol is discussed here. Also see examples here.

    Cheers,

    Colin

  • gh2mgh2m Posts: 63Questions: 16Answers: 0

    Thanks Colin. Are you saying I need to add ordering/searching/paging parameters in my server script and only return 10 records from SQL table like following:

    {
    "draw": 1,
    "recordsTotal": 21,
    "recordsFiltered": 20,
    "ordering":??
    "search":??
    "paging":??
    "data": [
    {"Vendor_ID":"1","Vendor_Name":"Company 1","LOB_NM":"CAO","PSN_NO":"101","BUS_ENT_ID":"1"},
    ......
    10 records only
    }

    Do you have any pseudo code in java I can follow? I am not familiar with php

  • allanallan Posts: 61,438Questions: 1Answers: 10,050 Site admin

    If you are using server-side processing, then yes, you need to limit the number of records the server returns. DataTables will send information about the data it is requesting (such as the sorting, paging and filtering information that should be applied by the server-side script) then it needs to return the data that would match based on that.

    We don’t publish a Java server-side processing script, but there are a few third party ones out there - for example this one.

    Do you actually need server-side processing? How many records do you have? If less than 50’000 then you should be fine to just load the data from the server and use client-side processing.

    See this manual page for information about the two processing modes and the difference between them.

    Allan

  • gh2mgh2m Posts: 63Questions: 16Answers: 0

    Thank Allan. I didn't think I need server-side process based on datatable documentation. My data is less than 15,000. The problem is SQL select cost me more than 10 seconds due to large amount of column data. Several columns has data near 8000 chars. On top of other process we have to run, initial loading/re-loading page takes 15 to 30 seconds. That's the reason I am thinking to use server-side, hoping I can reduce 10 seconds.

This discussion has been closed.