For the most part, all the integration you need to manage within the Service Portal can be accomplished using REST web services, but occasionally you run into a dinosaur like an MS SQL database acting as a data warehouse. When the DBAs won't run up a web service, you can always use JDBC by calling a Script Includes from the server component of your portal widget.
(function() {
/* Widget server code */
if(input.hasOwnProperty('searchingFor')){
var driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
var dbServer = "xxxxx01";
var dbPort = "1433";
var database = "XXXX";
var midServer = "xxxxx03";
var sqlQuery = "SELECT * FROM XYZ WHERE employee_number='"+ input.searchingFor;
var dw = new dataWarehouse()
data.raw = dw.executeRemoteQuery(driver, dbServer, dbPort, database, midServer, sqlQuery)
}
})();This will return an XML document you'll need to parse or scrap (either server side or on the client) in c.data.raw.
You will need to tweak the connection string if you're querying a different type of database, but this approach produces sub-second response times. Not quite as smooth as a web service, but still workable.
Special thanks to John James Anderson for the original code for this global script includes code.
var dataWarehouse = Class.create();
dataWarehouse.prototype = Object.extendsObject(AbstractAjaxProcessor, {
//see http://www.john-james-andersen.com/blog/service-now/using-the-servicenow-jdbcprobe.html
executeRemoteQuery:function(driver, dbServer, dbPort, database, midServer, sqlQuery){
var timeout = 30, queryResults='';
var connectionString = "jdbc:sqlserver://"+dbServer+":"+dbPort+";databaseName="+database+";integratedSecurity=true;";
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");
if(eccRes.get('sys_id',eccResponse.sys_id)){
queryResults = eccRes.payload.toString();
}
}
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>';
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();
return ecc.sys_id;
},
_waitForQueryResponse:function(eccQueueId, timeout){
var resp = new GlideRecord("ecc_queue");
resp.addQuery("response_to", eccQueueId);
resp.addQuery("agent","JDBCProbeResult");
resp.addQuery("queue", "input");
var counter = 0;
do{
resp.query();
resp.next();
gs.sleep(200); //wait before trying again
counter ++;
} while(!resp.sys_id && counter < timeout);
return resp;
},
type: 'dataWarehouse'
});When debugging results, it is extremely useful to look at the ECC Queue
Look at ecc_queue.list immediately after attempting your integration call. You should see several entries. The initial outbound call and a bunch of subsequent entries in the queue. If there's lots of noise and it's difficult to determine what's happening, filter based on the response to sys_id matching the sys_id of your original request. But your results should be in a record that looks something like this...
Also, double check that your query works when run independently AND that your MID Server has the appropriate credentials (trust) to allow it to run that query against your database. You may need to pass credentials in the connection string, but these records should allow you to see what the MID server is getting as a response from the database and hopefully steer you in the right direction.
Kind regards,
Peter
This is what your ECC Queue should look like
