Can we run SQL update queries in data sources

[Developer3]

We have a requirement of bi-directional integration between SQL database and Servicenow CMDB. Can we run update queries in servicenow data source so that the scheduled load can update the data in SQL DB?

If not, what would be the preferable way to run update queries in this integration?

[Developer3]

Yes i need to  update data in SQL db from ServiceNow. Could you please provide more details on how i can implement this.

[nataliya_b · Accepted solution]

high level:

- create data source that points to your sql db, put generic part of sql 

dynamic part will be added in script includes

- create script includes (see code sample below)

- create ui action (we using ui action) OR you could create scheduled job to run code from script includes

i did not test this code, but it could be starting point for you

var Asset_Util = Class.create();
Asset_Util.prototype = {
initialize : function() {

this.dtSource= YOUR_DATA_SOURCE_NAME; 
this.timeout = 20;


},

executeRemoteQuery:function(assetId){

var queryResults='';
var ds = new GlideRecord("sys_data_source");
ds.addQuery('name', this.dtSource);
ds.query();
if (ds.next()) {
this.midServer = ds.mid_server.getDisplayValue();
this.SQL = ds.sql_statement.toString();

var mid = new GlideRecord("ecc_agent");

mid.addQuery('name', this.midServer);
mid.query();

if (mid.next()) {


var eccQueueId=0;



var sqlSelect = new JDBCProbe(this.midServer);
sqlSelect.setDataSource(ds.sys_id);




var mySQL = this.SQL + " WHERE assetId='"+assetId+"'"; //HERE DYNAMIC PART OF SQL


sqlSelect.setFunction("");
sqlSelect.addParameter("query", "Specific SQL");
sqlSelect.addParameter("sql_statement", mySQL);
eccQueueId= sqlSelect.create();
gs.print(eccQueueId);

}

}






},







type: 'Asset_Util'
};

[Developer3]

Thank you for the steps. I will try this.

[Malathi Vinoth]

how to query data from database through SNOW workflow