We’ve developed some resources to help you work effectively from home during COVID-19 Click to learn more

executeJSON function and DataTable

Hey there...

I'm having an issue with using SearchEngine and populating datatable by jQuery using executeJSON as result. 

It looks like DataTable expects something special (which I have tried) but it still says the json returned is invalid.

  String ticketId = getCgiVariable("ticketId");

  String prefix = "{ \"data\": \"";
  String suffix = "}";


  SearchEngine se; se.addFields("y_cat_req_junction","id,x_category.x_main_category.x_main_task,x_category.x_task_name,x_due_date,x_request,x_done");
  se.addCriteria("y_cat_req_junction.x_request","equals", ticketId);
  se.addOrder("y_cat_req_junction.x_category.x_main_category",true);
  
  printLine(se.executeJSON());

I've tried a number of things on the frontend side but the error persists....

Any suggestions ?

RE: executeJSON function and DataTable

Hi,

Yeah the first one is the hardest, we do about ~5 of these each day :)
Below is a working ejscrpt that accepts the call from the datatable and return the data. take from it what you want.

%EJSCRIPT_START%
<%

String header = "Content-Type: application/json;charset=utf-8\n";

setParserVariable("ej.headers", header);
JSONBuilder jb;
jb.pushObject("");

jb.addInteger("draw", 1);
jb.pushArray("data");

Map data;
#setLanguageLevel 3;
String valueFromEmail = getCgiVariable("email");
String ComapanyId =getCgiVariable("CustIdCompanyId");

Integer CountTickets = 0;
SearchEngine se;
se.addField("sale.sale_id");
se.addField("sale.heading");
se.addField("sale.amount");
se.addField("sale.status");
se.addField("sale.saledate");
se.addField("sale.contact_id");
se.addCriteria("sale.contact_id","OperatorEquals",ComapanyId,"OperatorAnd",0);
se.addCriteria("sale.saleType_id","OperatorEquals", "7","OperatorAnd",0);
se.addCriteria("sale.saledate","OperatorGT", "2016-01-01 00:51:51.000","OperatorAnd",0);
se.addOrder("sale.sale_id", false);
for(se.execute(); !se.eof(); se.next())
{

String FakturaTyp = "";
String FakturaText = "";
if(se.getField(3) == "2")
{
FakturaTyp = "background-color:#5cb85c;color:white;";
FakturaText = "Invoiced";
}
if(se.getField(3) == "1")
{
FakturaTyp = "background-color:#f1b157;color:white;";
FakturaText = "Not invoiced";
}

CountTickets++;
jb.pushArray("");
jb.addString("", se.getField(0));
jb.addString("", se.getField(1));
jb.addString("", "<p class=Convert>" + se.getField(2) + "</p>");
jb.addString("", "<div class='ui tiny tag labels CurrentSale' data-saleid='" + se.getField(0) + "'><a class='ui label' style='" + FakturaTyp + "'>" + FakturaText + "</a></div>");
jb.popLevel();
}
jb.popLevel();
jb.addInteger("recordsTotal", CountTickets);
jb.addInteger("recordsFiltered", CountTickets);
jb.popLevel(); // jb.finalize() could be used to pop all levels
print(jb.getString());
%>
%EJSCRIPT_END%

Remember, in the frontend, you need to have the exact number of columns (and headers) as the json returns.

Let me know if you need the front-end part as well


Regards
Pär Pettersson

Av: Pär Pettersson 25. jul 2020

RE: executeJSON function and DataTable

Hi Ummair,

I think what you are searching for is executeToJSONBuilder.

#setLanguageLevel 3;

SearchEngine se;
se.addFields("sale", "sale_id,heading,amount");
se.setLimit(5);

JSONBuilder jb;
jb.setPrettyPrint(4);
jb.pushObject("");
jb.addString("foo", "bar");

se.executeToJSONBuilder(jb, "id:Integer,heading:String,amount:Float", "sales");
jb.popLevel();

printLine(jb.getString());

This give me results I would expect:

{
  "foo": "bar",
  "sales": [
    {
      "id": 1,
      "heading": "Example sale",
      "amount": 300
    },
    {
      "id": 2,
      "heading": "Sale Number Two",
      "amount": 1250
    },
    {
      "id": 3,
      "heading": "Sale to new customer",
      "amount": 1223
    },
    {
      "id": 4,
      "heading": "Test New Sale sdsd",
      "amount": 0
    },
    {
      "id": 5,
      "heading": "Sale of the Century",
      "amount": 1000110
    }
  ]
}

Hope this helps!

Av: Tony Yates 27. jul 2020