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

[Bill_Collins]

How about this one? (version 10-21-11)

22:41:10.882: [0:00:00.008] getRealForm
22:41:10.948: Evaluator: org.mozilla.javascript.EcmaError: "java" is not defined. Caused by error in Script Include: 'IncidentSLAHelper' at line 44 41: } 42: } 43: ==> 44: jvar_categories = java.lang.reflect.Array.newInstance(java.lang.String, size); 45: 46: // Put the actual category names into the array 47: for (var cat2 in this.categories) {
22:41:10.949: [0:00:00.009] getRealForm
22:41:10.952: Evaluator: org.mozilla.javascript.EcmaError: "getEditLink" is not defined. Caused by error in at line 14 11: gf.setRenderProperties(renderer.getRenderProperties()); 12: return gf.getRenderedPage(); 13: } ==> 14: getEditLink();

Thanks,

Bill

[Bill_Collins]

How about this one? (version 10-21-11)

22:41:10.882: [0:00:00.008] getRealForm
22:41:10.948: Evaluator: org.mozilla.javascript.EcmaError: "java" is not defined. Caused by error in Script Include: 'IncidentSLAHelper' at line 44 41: } 42: } 43: ==> 44: jvar_categories = java.lang.reflect.Array.newInstance(java.lang.String, size); 45: 46: // Put the actual category names into the array 47: for (var cat2 in this.categories) {
22:41:10.949: [0:00:00.009] getRealForm
22:41:10.952: Evaluator: org.mozilla.javascript.EcmaError: "getEditLink" is not defined. Caused by error in at line 14 11: gf.setRenderProperties(renderer.getRenderProperties()); 12: return gf.getRenderedPage(); 13: } ==> 14: getEditLink();

Thanks,

Bill

[psiek]

Did you tried jvar_categories = Packages.java.lang.reflect.Array.newInstance(java.lang.String, size); ?

[Bill_Collins]

I did, but it was late. Let me just check again to make sure I had the case and the plural correct. -Bill

[psiek]

The solution (thanks anders) :

var pParser = new Packages.java.text.SimpleDateFormat(strFormat);