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]

I haven't really used this solution a lot, but yes, it is a table that I created within ServiceNow. You can set table cleanup rules on tables such that they clean up on any schedule that you require.

This wiki link might be useful:
http://wiki.servicenow.com/index.php?title=Table_Rotation

Of course, if there is a way to just display third party data live without storing it into a table, that is probably the preferred method. But, if there is no other way, and the reporting will be used only occasionally (not every few seconds), then the temporary table route should be ok to use.

[Michael Domke]

Thanks for the quick reply John:

Your response is basically what I expected. Many thanks again.

This would be a great feature I'd love to see in ServiceNow. That is, the ability to create a data structure "on the fly" and be able to feed that data to the familiar ServiceNow list structure for presentation.

Michael