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

[lawrence_eng]

Thank you for sharing, Anders!

[#3]

Hi,
Is there a use a query such as "select ci.name, ci.serialnumber, ci.xxx... from cmdb_ci where created >= "Date" or updated >= "Date".

Nofil

[anders9]

Finally got round to looking at this one. Note that this is for querying a "external" relational database using a defined data source.

Adding to the examples above - here is an example of a SELECT query:

var j1 = new JDBCProbe(mid.name);
j1.setDataSource(ds.sys_id);
j1.setFunction("SELECT");
j1.addField("NAME");
j1.setTable("MYTABLE");
j1.setWhereClause("ID < 200");

Regards,
Anders

[blaine2]

For the benefit of anybody who would use Anders' SELECT example here, know that the given addField call has no effect.   It looks like it specifies the output columns but it does not.   The addField function takes two parameters not one, and is for specifying field values (which purpose does not apply to SELECTs).