How to reload table from ajax response when the json comes in varied sizes? INVALID JSON on reload()
How to reload table from ajax response when the json comes in varied sizes? INVALID JSON on reload()
jamcnorc
Posts: 8Questions: 1Answers: 0
Hi,
New here so please let me know if I need to update some things. First a bit of context, I am a front end dev and do not have a lot of control over the back end responses. (It can be done, but I'd like to handle this if possible). I am using the datatables plugin inside of a jsp page.
When I submit my table after a user edits it, an ajax call executes a stored procedure which returns the updated data in a json format. This format comes in a different order, and not all of the fields are consistent.
For example, if a record has an error, the corresponding json record will contain the field "error_desc". If a record does not have an error, then the field "error_desc" is omitted from the response.
I have a fiddle here, but my use case relies on on prem servers and is embedded in a complicated application using both struts 2 and dhtmlx. I'm not quite sure how to emulate this issue without going down a separate rabbit hole. This page at least shows how my original table is constructed, and then below you can see my response json object: https://jsfiddle.net/rzL4sja8/
Currently I populate the table from the database, allow the user to edit, the stored procedure runs and returns a json with the updated values, but when I try to call dt.ajax.reload(); I see the dreaded DataTables warning: table id=staged - Invalid JSON response.
I followed the steps from here: https://datatables.net/manual/tech-notes/1#:~:text=When%20DataTables%20shows%20the%20message,back%20was%20not%20valid%20JSON
My json is valid according to json.parser.online.fr but I like I said above I can have a different number of fields depending on the results of the stored procedure and I believe this is why DataTables rejects the response.
Here is an example of the json format, please note that the first record has 5 fields, while the rest have only 4:
'''
[
{
"comment":"Testing delete functionality on the first line1",
"su_id":"91200001",
"error":true,
"field_disp":"101a",
"error_desc":"invalid field_disp"
},
{
"comment":"CMS records indicate that the Respondent passed away on 10/01/2022. Please discuss this case with your FM if necessary.2",
"su_id":"88100002",
"error":false,
"field_disp":"72"
},
{
"comment":"Testing delete functionality a second time3",
"su_id":"91200003",
"error":false,
"field_disp":"103"
},
{
"comment":"this is the last comment4",
"su_id":"88100004",
"error":false,
"field_disp":"34"
}
]
'''
Here are a few other relevant javascript functions which are located on different pages in the app, but are relevant to this stored procedure. I've added a couple of descriptive comments, and you can see the commented out attempts I have made to get this working as well.
'''
function validateStaging(dt){
showLoading();
var params = dt.$('input, select').serialize() +
"&dispatch=validateRocStaging";
new Ajax.Request("rocLoad.do", {
method: 'post',
parameters: params,
success: function() {
},
onFailure: function (transport) {
onAjaxError();
alert("Validation call failed!");
},
onComplete: function (transport) {
var output = transport.responseText;
var data = JSON.parse(output); //this print to console the data I need nicely,
albeit in the wrong order
console.log(data);
//dt.clear();
//dt.ajax.reload(output).draw();
//console.log('afterajaxoutput');
dt.ajax.reload();
//console.log('afterajaxplain');
//dt.ajax.reload(data);
//console.log('afterajaxdata');
//dt.rows().add(JSON.parse(data[0])).draw();
//dt.columns.adjust().draw();
//dt.rows.add(data).draw();
//dt.rows().remove().draw();
//dt.rows.add(output).draw();
//console.log(output);
//output used to repaint table
//stageTable.columns.adjust().draw();
//stageTable.ajax.reload();
//reload works here, commenting to debug return capture
//location.reload(true);
} });}
public ActionForward validateRocStaging(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
try {
RocACPService service = new RocACPService();
updateStaging(mapping, form, request, response); //save data to db table
List<Map<String,Object>> datalist = service.validateROC(); // capture values from stored procedure
request.setAttribute("dataList", datalist);
String dataString = new String();
Gson gson = new Gson();
dataString = gson.toJson(datalist); //convert stored procedure result to json and write to response object
write(response, dataString);
}catch(Exception e) {
log.error(e);
return null;
}
return null; //
}
'''
Thank you for reading.
Answers
One option would be to either remove that
error_descfield (or add it into all records) within theajax.dataSrc. The final example on that reference page shows how you can manipulate the data sent from the server before loading it into the table,Colin
Your problem description is confusing. Your fiddle doesn't have the
ajax.optionand it looks like you use a loop to populate the HTML table. Its unclear how you would get theInvalid JSON responseerror as that comes from usingajax.It doesn't look like you have
columns.dataconfigured which is used to define the columns when using object structured data when using Ajax or Javascript loaded data. If you are usingcolumns.datayou can usecolumns.defaultContentto set the column value for theerror_desccolumn when that object is missing from the data.Usually you will get the Requested unknown parameter error when data is missing from the rows and
columns.defaultContentis not configured. I don't think this would trigger the Invalid JSON error. Are you checking the JSON of XHR response that caused the error?Can you post a link to your page or a running test case replicating the issue so we can help debug?
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case
Kevin
It looks like you are using
ajax.relaod()in yourAjax.Request(). You need to useajaxforajax.relaod()to work. Since it looks like you aren't usingajaxthen this might be resulting in the Invalid JSON error.Maybe you need to use
clear()followed byrows.add()instead. Usecolumns.defautlContentincolumnsDefsfor theerror_desccolumn to handle the case when this is missing.Kevin
@colin @kthorngren
Thank you both for the replies. I read the docs more and see what you are saying about using the ajax option in order for ajax.reload to work.
I guess I'm a little confused about whether using the ajax api is appropriate or not. I can't publish a running test case without escalating this, so I'd like to explain how the application is set up and see if we can talk through this more before going there.
Here is how the app is setup.
A menu click to open the page triggers a Js method (showLoad) that has an ajax call which uses a:
(java Action) that calls a service to get table data and attaches the data to the request using
(java service) returns query result from database
Jsp page has loop which uses data in request object to populate an html table
A DataTables constructor is called on that html table.
Later the datatable buttons trigger js ajax methods in the same class as the showLoad method. These ajax methods call the java actions, which use the java services.
'''
'''
These methods are all passed dt as a parameter, and the serialized data is sent to the backend like this:
(in the js ajax method in separate class)
'''
var params = dt.$('input, select').serialize() + "&dispatch=validatePreLoadStaging";
'''
All of that is working well, but I'm trying to avoid calling showLoad() again after any stored procedure is run and just capture the return data instead. Currently calling showLoad over and over works, but it is clunky and not following best practices.
I'm not clear if I should be using ajax here since it seems like the way this app is set up the ajax calls are a step removed so to speak, and since everything works well with the html loop method.
Here is the java action to perform this 'validation' from the above serialization, and how I am currently capturing the response.
The aim here is to capture the return from the stored procedure as dataString like this:
'''
List<Map<String,Object>> datalist = service.validateROC(user);
request.setAttribute("dataList", datalist);
String dataString = new String();
Gson gson = new Gson();
dataString = gson.toJson(datalist);
write(response, dataString);
'''
And then in onComplete for the ajax method I'd like to use the dataString in the response to redraw the table:
(This is my latest attempt, but using ajax.reload() would be ok if that is better)
'''
var output = transport.responseText;
var data = JSON.parse(output);
dt.clear();
dt.rows.add(data).draw().nodes();
'''
I'd like to know your thoughts here, I'm hoping to find a way to do this without having to reinvent the wheel on this one.
Thanks!
Not sure I totally follow. if the first initialization use the DOM table as the data source and you want to change to using JSON data for subsequent loads then you will need to destroy and reinitialize Datatables.
See this example:
http://live.datatables.net/rexokaju/1/edit
It starts with an HTML sourced table. Reloading the data will use
destroy()to allow for reconfiguring the Datatables options. Since theerror_descdoesn't exist in each row we usecolumns.defaultContentto display something when the object is not there. In this case an empty string.HTH,
Kevin
Thanks Kevin.
I talked through the issue more and it looks like my team wants me to use .clear() and rows.add() as you mentioned above. I am successfully clearing the table and passing the new data to rows.add(), however the api is interpreting the data one character per cell, inserting the entire set into the first column only.
I replaced my data and headers with dummy data from the documents and am having the same issue.
dt.rows.add(testJSON).draw();
It throws the same invalid json error and looks like this:
These other methods result in a blank data table or an error:
dt.rows.add(JSON.parse(testJSON)).draw();
dt.rows.add($.parseJSON(testJSON)).draw()
using a loop to access each record at a time like this
dt.row.add(JSON.parse(testJSON)[i]).
Here is how I initialize the table:
'''
'''
Any idea why this might not be able to parse json data correctly?
rows.add()expects a Javascript array of rows. The first step is to determine what data typetestJSONis. If its a string then useJSON.parse(testJSON)to turn it into a Javascript object. Then check again to see what it is. If its a string then you are double encapsulating it as JSON in your server. But it should be a Javascript array of rows. See the Data docs for details.If you still need help then please post the
testJSONvalue or the XHR response using the browser's network inspector.Kevin
Thanks again Kevin
Here are the two versions of testJSON I tried the above with:
'''
var testJson = '[{"su_id":"88100004", "field_disp":"34", "error_desc": "bla", "comment":"blahthis is the last comment4" }, {"su_id":"91200003", "field_disp":"103", "error_desc":" false", "comment":"Testing delete functionality a second time3"}]';
var testJSON= '[{"su_id":"0.00","field_disp":"15.00","error_desc":"0.00","comment":"12"},{"su_id":"0.00","field_disp":"15.00","error_desc":"0.00","comment":"12"}]';
'''
You have single quotes around the array, ie
'[...]'. This is a string not an array. Remove the single quotes, something like this:BTW, use triple backticks (```) for the markdown code formatting. Not single quotes.
Kevin
Hmmm I thought that would be it. Unfortunately,
When I try this I am getting this error and then a blank table
"DataTables warning: table id=staged - Requested unknown parameter '0' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4"
Thanks for the tip on the backticks
Did you follow the troubleshooting steps at the link provided?
http://datatables.net/tn/4
Indicates that Datatables is configured for each row to be an array of data. But you have objects. You need to use
columns.datato define your columns. Please see the Data Docs for details.Kevin
Yes I had followed those steps, you are correct about the array of objects vs array of arrays. I was able to convert directly and load the table like this:
Thanks for the help again!
Progress but still issues, three things I am still stuck on.
When I use the output object directly, the table fails to reload. However, if I copy paste output exactly as printed to console, (that is what testJson is) the same data populates the table just fine.
When it does populate the table, the data is in a different order after serialization so the data is not going into the correct columns, and the table loses it's editable text boxes upon reload. Here is the code as well as a screenshot of the changes to the table:
Thanks for all the help with this, I really appreciate it!
Where does the output object come from? What is the data type, ie, string or Javascript array?
Use the browser's debugger or
console.log(typeof myVariable);to find out the data type. Can you post an example of the output object?Either use
columns.datato define the columns for the object structured data or make sure the array you build is in the proper order. Using objects is much better as you access the data using the object's key instead of an array index.Looks like your initial table is directly placed into the DOM with the
inputelements, etc. You will need to usecolumns.renderto build the same HTML when you userows.add()to populate the table. My suggestion would be to use Datatables to populate the initial table. See this example showing how to render HTML elements.Kevin
output is created in the java action, the return from a stored procedure call:
typeof output comes back as a string, so I tried JSON.parse(output); which creates an object (testJson after copying from the console also shows as object when using typeof) But even using JSON.parse, the table does not reload.
I appreciate you posting the example, I will add .data and .render to fix those issues.