Using the JDBC Probe from scripts

[anders9]

I recently did some investigation for a colleague regarding some functionality around the JDBCProbe. I couldn't find any examples of this on the Wiki nor the community so I though I add my findings here.

The initial requirements was to insert a row in a external database using a JDBC connection. I also added the examples to update and delete rows for reference. The examples below are using a specific MID server and data source.

// Get a specific MID Server (=MID01 in my case)
var mid = new GlideRecord("ecc_agent");
mid.addQuery('name','MID01');
mid.query();
mid.next();

// Get a specific Data Source (predefined to connect to a specific database instance)
var ds = new GlideRecord("sys_data_source");
ds.addQuery('name','Oracle AHDB');
ds.query();
ds.next();


//Create a JDBC Probe and insert three rows (one will be updated and one deleted later on...)
var j1 = new JDBCProbe(mid.name);
j1.setDataSource(ds.sys_id);
j1.setFunction("INSERT");
j1.setTable("MYTABLE");
j1.addParameter("skip_sensor","true");
j1.addField("name","First");
j1.addField("status","row");
j1.addField("id","111");
j1.create();

var j2 = new JDBCProbe(mid.name);
j2.setDataSource(ds.sys_id);
j2.setFunction("INSERT");
j2.setTable("MYTABLE");
j2.addParameter("skip_sensor","true");
j2.addField("name","Second");
j2.addField("status","row");
j2.addField("id","222");
j2.create();

var j3 = new JDBCProbe(mid.name);
j3.setDataSource(ds.sys_id);
j3.setFunction("INSERT");
j3.setTable("MYTABLE");
j3.addParameter("skip_sensor","true");
j3.addField("name","Third");
j3.addField("status","row");
j3.addField("id","333");
j3.create();


//Create a JDBC Probe and update a row
var u = new JDBCProbe(mid.name);
u.setDataSource(ds.sys_id);
u.setFunction("UPDATE");
u.setTable("MYTABLE");
u.setWhereClause("name='Second'");
u.addParameter("skip_sensor","true");
u.addField("name","Updated");
u.create();


//Create a JDBC Probe and delete a row
var d = new JDBCProbe(mid.name);
d.setDataSource(ds.sys_id);
d.setFunction("DELETE");
d.setTable("MYTABLE");
d.setWhereClause("name='Third'");
d.addParameter("skip_sensor","true");
d.create();

It's worth mentioning that there is no guarantee that the items are picked up from the ECC queue in the order that the script runs - so the update and/or delete above may be running before the inserts. But just wanted to show some examples so hope this helps.

Would be interesting to know how people capture the error handling here. e.g. what if my table don't exist, insert wasn't allowed or update didn't match anything...


Regards,
Anders

[Liuduan]

【 “I have a working JDBC probe connected to the external database. ”】

Could you share your code, and how did you connect to the external database? I need to query an external database from a script, but I have some difficulties to do that. If you can share your code, it would be very helpful.

[Liuduan]

【 “I have a working JDBC probe connected to the external database. ”】

Could you share your code, and how did you connect to the external database? I need to query an external database from a script, but I have some difficulties to do that. If you can share your code, it would be very helpful.

[psjajda]

Hi,

I also have similar requirement to update database table fields based on catalog item input variables. I created Run script in workflow of cat item, as below :

var acc = "ABC~"+current.<variable_Name>;
var gr = new JDBCProbe("midserver_name");
gr.setDataSource("<Datasource sysID>");
gr.setFunction("UPDATE");
gr.setTable("<TableName>");
gr.setWhereClause(<wherecondition>);
gr.addField("field0 ", "A");
gr.addField("field1","updatetime");
gr.create();

but it is not working, I am receiving error as:

Run Script(sysID of script): org.mozilla.javascript.WrappedException: Wrapped org.mozilla.javascript.JavaScriptException: java.lang.NullPointerException (<refname>; line 17)

Is anyone faced this kind of issue? Please let me know how to fix it. 

Thanks,

Pankaj

[Liuduan]

// Get a specific Data Source (predefined to connect to a specific database instance)
var ds = new GlideRecord("sys_data_source");
ds.addQuery('name','Oracle AHDB');
ds.query();
ds.next();

When I use a Data Source sys_id to replace above “sys_data_source”, I got an error “GlideRecord.query() - invalid table name”

Do you have any suggestions?

[bhupendra paree]

is there any possibility to get the confirmation whether data write to the SQL was successful or not from Servicenow??(using logging or response )
If Yes, then How. Kindly let us know.