Can we run SQL update queries in data sources

Developer3 · ‎03-04-2020

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?

nataliya_b · ‎03-05-2020

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'
};

View solution in original post

Priyanka Vasant · ‎03-04-2020

Hi,

update SQL field in 'sys_data_source' table, then execute your data source.

let me know if you found it this working.

Regards,

Priyanka A.

Developer3 · ‎03-04-2020

I have a doubt, how can I use the CMDB field names in the update query in Date Source -> "SQL statement" field.

For ex,

UPDATE SQL_Table SET Asset_number = <<CMDB Asset number>> WHERE Asset_name = 'xyz';

In the above sql statement, how can i use the CMDB Asset number field name of cmdb_ci_computer table.

nataliya_b · ‎03-04-2020

just to clarify requirement: you need to update data in SQL db from ServiceNow. is it right?

nataliya_b · ‎03-04-2020

yes, it is feasible

you could use JDBCProbe for this purpose. this post my be helpful.

In our solution we used datasource (instead of connection string). Let me know if you want to use it

I could provide more details

http://www.john-james-andersen.com/blog/service-now/live-jdbc-queries-displayed-in-servicenow.html