How do I execute an external SQL query via Script include and return the result to a client script?

[sharepointau]

I'm trying to get a script include to return the value of a SQL query to the client side script. I am able to pass the parameters and even return data back. However, I cannot figure out how to execute the SQL query and return the result.

How do I execute the sql query and return the result?

Server Side Script

I've been trying to modify John J. Anderson's example  to no avail.

var Test_SI = Class.create();
Test_SI.prototype = Object.extendsObject(AbstractAjaxProcessor, {

executeQuery:function(){
  var timeout = 50;

  //Database Information
  var driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  var connectionString = "jdbc:sqlserver://server:1433;instanceName=instance;selectMethod=cursor;databaseName=DBNAME";

  //ECC Queue Information
  var midServer = "midsrv";
  
  var desiredName = this.getParameter('sysparm_desiredName'); //This comes from the catalog item form
  var sqlQuery = "SELECT column FROM db.dbo.AllAlias WHERE column='"+desiredName+"'";
  var xml = this._getQueryXML(driver, connectionString, sqlQuery);
	
  
/****DEBUGGING*****/
  gs.log("******** Script Include - desiredName: " + desiredName);
  gs.log("******** Script Include - sqlQuery: " + sqlQuery);
/****DEBUGGING*****/

	//return xml;
  if(!eccResponse.sys_id){
     eccQueueId = 0;
	  gs.log("********Script Include - eccQueueId = 0: " + eccQueueId);
   } else {
     var eccResultRecord = this._getResultRecordFromQueue(eccResponse, eccQueueId);
     this._insertResultsIntoCacheTable(eccResultRecord, eccQueueId);
   }
	 gs.log("********Script Include - eccQueueId: " + eccQueueId);
   
	return eccQueueId;

},

 _getQueryXML:function(driver, connectionString, sqlQuery){
   var xml = '<?xml version="1.0" encoding="UTF-8"?>';
   xml = '<parameters>';
   xml += '<parameter name="jdbc_driver" value="'+driver+'"/>';
   xml += '<parameter name="connection_string" value="'+connectionString+'"/>';
   xml += '<parameter name="query" value="Specific SQL"/>';
   xml += '<parameter name="count_rows" value="true"/>';
   xml += '<parameter name="sql_statement" value="'+sqlQuery+'"/>';
   xml += '</parameters>';
   gs.log("********Script Include - XML Output: " +xml);
  
   return xml;
    
 },
_postQueryToEccQueue:function(midServer, xml){
  var ecc = new GlideRecord("ecc_queue");
  ecc.initialize();
  ecc.agent="mid.server." + midServer;
  ecc.topic="JDBCProbe";
  ecc.payload=xml;
  ecc.queue="output";
  ecc.state="ready";
  ecc.sequence = Packages.com.glide.util.Counter.next('ecc:sequence');
  ecc.insert();

  gs.log("********Script Include - JDBC Query ECC Queue New Sysid: " + ecc.sys_id);
  
return ecc.sys_id;
},
_waitForQueryResponse:function(eccQueueId, timeout){
  var resp = new GlideRecord("ecc_queue");
  resp.addQuery("response_to", eccQueueId);
  resp.addQuery("queue", "input");
  
  var counter = 0;
  do{
    resp.query();
    resp.next();
    gs.sleep(1000); //wait a second before trying again
    counter ++;
  } while(!resp.sys_id && counter < timeout);
  return resp;
},
_getResultRecordFromQueue:function(eccResponse, eccQueueId){
  gs.log("********Script Include - Resp Sys ID: " + eccResponse.sys_id);
 // gs.log("Response Payload: " + eccResponse.payload.replace(/\</g, "&" + "lt;"));
  
  var eccRes = new GlideRecord("ecc_queue");
  eccRes.addQuery("agent", "JDBCProbeResult");
  eccRes.addQuery("topic", "JDBCProbe");
  eccRes.addQuery("queue", "input");
  eccRes.addQuery("state", "ready");
  eccRes.orderByDesc("sys_created_on");
  eccRes.query();
  while(eccRes.next()){
    var payload = new XMLDocument(eccRes.payload);
   // var eccQuery = payload.getNodeText("//results/parameters/parameter[@name="ecc_queue"]/@value");
    gs.log("********Script Include - eccQuery: " + eccQuery);
    gs.log("********Script Include - "+eccQueueId + "==" + eccQuery);
    if( eccQueueId == eccQuery ){
      break;
    } 
  }
  return eccRes;
},
_insertResultsIntoCacheTable:function(eccResultRecord, eccQueueId){
  gs.log("Inserting " + eccResultRecord.name + " Results into Cache Table");
  var payload = new XMLDocument(eccResultRecord.payload);
  gs.log("Payload to Insert: " + payload);
  for(var i=1; i<=eccResultRecord.name; i++){
    var rec = new GlideRecord("u_table");
    var xpath = "//results/result/row[@id='" + i + "']/";
    rec.initialize();
    rec.u_table = payload.getNodeText(xpath + "desiredName");
    gs.log("********Script Include - desiredName From database: " +  payload.getNodeText(xpath + "desiredName"));
    rec.u_ecc_id = eccQueueId;
        
    rec.insert();
  }
}
});

Client Side Script

This only pulls back the xml from above... If I follow fore mentioned John J. Anderson's example I get a null response for "answer."

function onChange(control, oldValue, newValue, isLoading) {
   if (isLoading || newValue == '') {
      return;
   }

var ga = new GlideAjax("Test_SI");
var dn = g_form.getValue("desired_name");
ga.addParam("sysparm_name", "executeQuery");
	//Send the Desired Name to the query
ga.addParam("sysparm_desiredName", dn);
	alert("Add Parameter (desired name): "+dn);

	//Make an asynchronous Ajax call and the process the response in the callback function
ga.getXML(parseQueryResponse);
 
function parseQueryResponse(response) {
	  var queryAnswer = response.responseXML.documentElement.getAttribute('answer');

	  alert("The Query Answer is: "+ queryAnswer); 
	  console.log(queryAnswer);

	}  	
	
}

[SatheeshKumar]

i can see only a part of a code above. it contains code only for xml building . you need build the xml and pass to ecc queue table... so that it will process your request in midserver and return the response.

[sharepointau]

I had the second half commented out because I wasn't sure it was working. I have now added it to this code above. Any help is greatly appreciated.

[sharepointau]

Right now with the scripts above i'm getting "null" on the client side, and the only thing logging in Logs is this

"gs.log("********Script Include - XML Output: " +xml);"

[sharepointau]

maybe my issue is in the "_insertResultsIntoCacheTable:function(eccResultRecord, eccQueueId)" I may not fully understand caching tables. Is this just any table, or is it created dynamically here in this script?