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);

	}  	
	
}

[sharepointau]

Should the midServer variable take the mid server name ServiceNow knows it as "midsrv" or should it be the FQDN "midsrv.domain.com"

[kente]

Your "executeQuery" function doesnt have a return?'

****DEBUGGING*****/

	//return xml;
},

[sharepointau]

yes, you are right on returning something, but if I uncomment that "return xml;" it only returns the xml that makes up the query and not the query results.

[sharepointau · Accepted solution]

As it turns out John Anderson's example is a great one to follow. There may be something to say for simply using the JDBC probes, but this is another way to accomplish the task.

PROBLEM ---

- Before request submission we need to verify a field is unique by querying an external SQL Database

--------------------------------------------------
SOLUTION --- 

- Use a client side script to call a script include that queries the SQL db. Then parse the response to take action based on decisions in the data returned. 

-----------------------------------------------------
SCRIPT INCLUDE ---

var YOURSCRIPT_SI = Class.create();
YOURSCRIPT_SI.prototype = Object.extendsObject(AbstractAjaxProcessor, {
	
	executeRemoteQuery:function(){
		var timeout = 20;
		var queryResults='';
		var driver    = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		var midServer = "mid1";
		var desiredName = this.getParameter('sysparm_desiredname');
		var dbServer  = "sql1.domain.com";
		var dbPort    = "1433";
		var dbInstance = "sql01";
		var dbUser = "servicenow_user";
		var dbPassword = "password01";
		var database  = "database1";
		var sqlQuery  = "SELECT column1 FROM database1.dbo.table WHERE column1='"+desiredName+"'";
		
		var connectionString = "jdbc:sqlserver://"+dbServer+":"+dbPort+";instanceName="+dbInstance+";databaseName="+database+";user="+dbUser+";password="+dbPassword;
		
		var xml = this._getQueryXML(driver, connectionString, sqlQuery);
		var eccQueueId = this._postQueryToEccQueue(midServer, xml);
		var eccResponse = this._waitForQueryResponse(eccQueueId, timeout);	
		
		
		 		if(!eccResponse.sys_id){
		 			eccQueueId = 0;
		 		} else {
		 			var eccRes = new GlideRecord("ecc_queue");
		 					gs.log("Script Include - beforeIF eccRes.get('sys_id',eccResponse.sys_id): " + eccRes.getValue('sys_id'));
		 			if(eccRes.get('sys_id',eccResponse.sys_id)){//
		 					gs.log("Script Include - afterIF eccRes.get('sys_id',eccResponse.sys_id): " + eccRes.get('sys_id',eccResponse.sys_id));
 									queryResults = eccRes.payload.toString();
								}
// 				var eccResultRecord = this._getResultRecordFromQueue(eccResponse, eccQueueId);
// 					queryResults = eccResultRecord.payload.toString();
					 		}
					
					gs.log("Script Include - queryResults: " + queryResults);
					return queryResults;
				},
				_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="sql_statement" value="'+sqlQuery+'"/>';
					xml += '</parameters>';
					//GOOD
					gs.log("Script Include - xml: " + 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.insert();
					
					//GOOD
					gs.log("Script Include - ecc.sysid: " + ecc.sys_id);
					return ecc.sys_id;
				},
				_waitForQueryResponse:function(eccQueueId, timeout){
					var resp = new GlideRecord("ecc_queue");
					
					gs.log("Script Include - eccQueueId _waitForQuery: " + eccQueueId);
					gs.log("Script Include - timeout _waitForQuery: " + timeout);
					
					resp.addQuery("response_to", eccQueueId);				
					resp.addQuery("agent","JDBCProbeResult");
					resp.addQuery("queue", "input");
					
					gs.log("Script Include - respEccQueueID in _waitForQuery: " + resp.sys_id);
					
					var counter = 0;
					do{
						resp.query();
						resp.next();
						gs.sleep(200); //wait before trying again
						counter ++;
					} while(!resp.sys_id && counter < timeout);
					gs.log("Script Include - resp in _waitForQuery: " + resp);
					return resp;
				},
				_getResultRecordFromQueue:function(eccResponse, eccQueueId){
					gs.log("Script Include - Resp Sys ID: " + eccResponse.sys_id);
					gs.log("Script Include - Response Payload: " + eccResponse.payload.toString());
					
					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- "+eccQueueId + "==" + eccQuery);
						if( eccQueueId == eccQuery ){
							break;
						}
					}
					return eccRes;
				},

				type: 'YOURSCRIPT_SI'
			});

CLIENT SIDE SCRIPT --- 

function onChange(control, oldValue, newValue, isLoading) {
 if (isLoading || newValue == '') {
   return;
   }
   var desiredName = g_form.getValue('desired_name');
   	
   //clear the message
   g_form.hideFieldMsg(control);
   //clears all messages
   g_form.clearMessages();


//Show alert while waiting for response from script include
g_form.addInfoMessage('<div style="padding:15px;"> We are determining if your desired name <strong>, "' + desiredName + '"</strong>, is unique. This should not take long. Please wait for a response below. <br/><br/> *******************************************</div>');
	
   //Determines if the desiredname is unique
   var ga = new GlideAjax("YOURSCRIPT_SI");
	   ga.addParam("sysparm_name", "executeRemoteQuery");
	   ga.addParam("sysparm_desiredname", desiredName);
	   ga.getXML(parseQueryResponse);

	function parseQueryResponse(response) {
	 var answer = response.responseXML.documentElement.getAttribute("answer");	
	 console.log(answer);		
	if(!answer){
		g_form.addInfoMessage('<div style="padding:15px;"> The name, <strong>' + desiredName + '</strong>, you requested is unique. You may proceed with your request.</div> ');		
	} else {
		g_form.addErrorMessage(' The name, <strong>' + desiredName + '</strong>, is already taken. Please try again. ');
		g_form.setValue('desired_name','');
	  }
	}

return;
}

[Dheeraj16]

Could you please help me with oracle connection string.