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]

Thank you for sharing, and your message is very helpful.

Could you post more complete code for SELECT query, including how to read out the query results?

Where can I find a good manual for JDBCProbe(), including the methods?

[Liuduan]

Thank you for sharing, and your message is very helpful.

Could you post more complete code for SELECT query, including how to read out the query results?

Where can I find a good manual for JDBCProbe(), including the methods?

[Sashi K1]

Hi

why am I getting java script error which says Object not found, JDBCProbe

error exactly in the script line

var j1 = new JDBCProbe(mid.name);

[haldroid]

Great stuff. I have been reviewing the Wiki page on JDBCProbe and the above sample code and one question emerges. Suppose I am trying to populate one or more tables in Service Now using a custom JDBCProbe. I can see how to put together SELECTs to create ECC input queue data payloads. But what about SNC back-end processing? The JDBCProbe Wiki page specifically says to specify skip_sensor. Can you point us at an example of scripting that handles the ECC input after a JDBCProbe SELECT has run?

Thanks again!

[Sashi K1]

Hi just checking one more time...

When I follow above example, why am I getting a java script error at this line

var j1 = new JDBCProbe(mid.name);

org.mozilla.javascript.EcmaError: "JDBCProbe" is not defined.
Caused by error in at line 34

==> 34: var j1 = new JDBCProbe(mid.name);

am I missing any libraries?

I have found another way to insert records using ECC Queue as explained in wiki but curious why above example doesn't work for me?