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

[Brian_Scott]

Been working with JDBC to export SN data to a MSSQL backend, and I'm trying to figure out how to get the data out of a select statement. When I query ecc_queue and look at the payload in a script I get a concated string of all values I requested, but when I look at ECC Queue in list form, Payload is in XML format. Why is there a discrepency here?

Also, for those who might be interested, I came up with a good way to serialize JDBC calls

//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");
var j1_ecc_sid = j1.create();

var grEcc = new GlideRecord('ecc_queue');
grEcc.get(j1_ecc_sid);
while (grEcc.state != 'error' && grEcc.state != 'processed'){
    gs.sleep(1000);
    grEcc.get(j1_ecc_sid);
}
// Code has waited for j1 to finish
// If there is a response to your data, wait another 1-2 seconds for the ECC Queue to update

[achyuthram]

Thanks for the useful information. I have a question that when we are checking the condition, can we compare the sys_id or incident number from the mysql database directly and update the databases

[AJ5]

Hello SN Gurus,

I am following this thread and my goal is to select records from the SN table and insert to external SQL database. I have a working JDBC probe connected to the external database. I am also able to do an import to SN table from external database.

My question is to export SN data where do i write these scripts? I want to select record and insert to the external database.

The other question i have for insert statement, the example on this thread.

Field values are keyed in like

j1.addField("name","First");
j1.addField("status","row");

Can't i just query the field like 'Incident_id' 'Status' and 'Name' without specifying the values for each field and dynamically fetching and inserting the records to external source. 

Please provide your inputs and share your experience. 

Thanks in advance

AJ

[Service Manager]

AJ,

Is it working for you?

were you able to insert the new record and update the existing records?

Thanks

[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.