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

[anders9]

Hello Sashikanth,

Where/How are you running the script?
Do you know what version of ServiceNow you're using?
If you look in your Data Sources in ServiceNow - do you have a JDBC option?

Regards,
Anders

[Sashi K1]

We are usng October '11 Preview 3 release version of ServiceNow.

I'm creating a Scheduled Job to create JDBCProbe to export data from SN to MSSQL Server. While following this approach I'm getting API errors in Javascript, not in the case of creating an XML Playload and creating a JDBCProbe topic in ECCQueue.

Appreciate your help much

[anders9]

Hmm... ok - can't see the reason why it wouldn't work in your environment. To avoid a long trail of troubleshooting please send me an email - or log a case with support and we should be able to work this one out.

Regards,
Anders

Email: anders.henriksson@service-now.com

[b_rdhugorynning]

I got the same error as Sashikanth because i had simply not installed the "Integration - JDBC" plugin.

[psiek]

Hi,

I got exactly the same kind off problem. I'm trying to release a working code from a fall 2010 to a june11 and all my script includes that are using java objects are broken :

Evaluator: org.mozilla.javascript.EcmaError: "java" is not defined.
Caused by error in script at line 2

1: var strFormat = gs.getProperty('glide.sys.date_format') + " " + gs.getProperty('glide.sys.time_format');
==> 2: var pParser = new java.text.SimpleDateFormat(strFormat);
3: var datNew = new Date(pParser.parse(strDate));


Can someone give a solution ?