Integration via JDBC from a UI Action

[craigclarke]

From a ServiceNow Newbie.....

I have managed to get a JDBC connection into a SQL Server database and tested the SQL query I need to execute. I would like to add a button to a ServiceNow form that basically passes and executes the SQL (referencing values from the form the button sits on) and returns the results (many columns, sometimes many rows too) into a ServiceNow table (a bespoke one).

From looking on the wiki I think the solution may be involving a mid server, data source, import set and transform map — can anyone tell me if I am I going down the right route?
•How can this be executed from a UI Action? Via JAVA script I assume? Does anyone know of an example I can use?
•From what I can see I can specify specific SQL in the SQL Statement of a data source — can this SQL reference columns/parameters in it? Or do I have to pass the whole SQL as a parameter from the UI Action? or use a Probe?....so confusing!
•The Mid server sits on the SQL Server I assume (prob an obv question)?

Thanks for your time

[john_andersen]

Here is a blog post that may be helpful. In it I had a button, that, when pushed, would cause a JDBC call to be issued and results to populate a temporary table in ServiceNow:

http://www.john-james-andersen.com/blog/service-now/live-jdbc-queries-displayed-in-servicenow.html

The above blog post did not use the JDBCProbe Script Include, but it could have and would have saved some scripting. You can check out my blog on the JDBCProbe here:

http://www.john-james-andersen.com/blog/service-now/using-the-servicenow-jdbcprobe.html

Good luck!

[craigclarke]

Thanks John, very helpful indeed.

Out of interest - when you call the 'script includes' from a button (UI Action) on a form - did you give the user a 'Loading' screen or something similar? I am anticipating a delay and want the user to know something is happening. This is the function I call from the Onclick field...
function updatecmdb() {
//Call a script to get the data from another source
var ga = new GlideAjax('NHBC_sgv_cmdb');
ga.addParam('sysparm_name','executeQuery');
ga.addParam('sysparm_obj_name', g_form.getValue('name'));
ga.getXMLWait();
var retval = ga.getAnswer();
if (retval == 0) {
alert('CMDB Not Updated');
} else {
alert('CMDB Updated');
}
}

Thanks again

[Michael Domke]

I'd like to learn more about this "caching table". I'm assuming the caching (temporary) table you discussed in your blog, the u_related_alert_cache table, is a physical table you had to create using the Tables & Columns feature?

If so, what do you do to manage the table or more specifically, the data within the table? Do you flush (delete) the records when you're done with them or do you simply leave them there and since there's no other interface to view the table, no one will see any data except when they see the dialog?

My interests in this involve a requirement to build a report and include additional data (as columns) yet the additional data doesn't exist as a field in the table I'm reporting on. So the question is, how do I display those columns? My idea was to create a "dummy/temporary" table to use specifically for the reporting purpose but was concerned about managing any data in that table.

Any info you can provide should be very helpful to me.

Many thanks,
Michael

[john_andersen]

I can't remember if I did or not...it was a proof of concept piece of work.

Here is a good blog on putting "loading" messages on the screen:

http://www.servicenowguru.com/system-ui/show-hide-loading-dialog-frames/