JDBCProbe via Script

[Patrick Quinlan]

Ok Guys and Gals....I feel like I've exhausted all possible avenues of trying to find answers. 

I am attempting to import data from an external Oracle database. I have successfully built a Data Source and passed a SQL statement which works. This only proves that the datasource can be reached by my mid server and that my SQL query is correct. 

What I am attempting to do now, however, is make that same call to the external database, via a script that will send a specific SQL query to target specific criteria. The reason I need to do this is so that user input on a form can be used in the SQL query and then I want to be able to parse through the XML response in order to populate additional fields on the form. 

I feel like I'm getting close, as I can create an output JDBCProbe entry on the ECC Queue table that does get processed, however the cooresponding input record does not and I cannot for the life of me figure out what I'm missing. 

This is the code that I'm using:

var SQLstatement = "select wo.work_order_id, wo.description, woi.work_item_id, woi.description, woi.* from work_order wo, work_order_item woi where wo.client_id = 1 and wo.status = 'Open' and (wo.expiration_date is null or wo.expiration_date > sysdate) and wo.client_id = woi.client_id and wo.work_order_id = woi.work_order_id and woi.status = 'Open' and (woi.end_date is null or woi.end_date > sysdate) and woi.work_order_id = '2267'";


//build up payload xml
var payloadXML = '<?xml version="1.0" encoding="UTF-8"?>';
 payloadXML += '<parameters>';
 payloadXML += '<parameter name="jdbc_user_name" value="myUsername">'
 payloadXML += '<parameter name="jdbc_password" value="myPassword">'
 payloadXML += '<parameter name="jdbc_driver" value=oracle.jdbc.OracleDriver"/>';
 payloadXML += '<parameter name="connection_string" value="jdbc:oracle:thin:myServer:myPort:myDatabase"/>';
 payloadXML +='<parameter name= "query" value= "Specific SQL" />';
 payloadXML +='<parameter name= "sql_statement" value = "' + SQLstatement + '"/>';
 payloadXML += '</parameters>';


//create output ECC record
var eccQ = new GlideRecord("ecc_queue");
eccQ.initialize();
eccQ.agent = 'myMidServer';
eccQ.queue = 'output';
eccQ.topic = 'JDBCProbe';
eccQ.state = 'ready';
eccQ.payload = payloadXML;
eccQ.insert();

In the Input Record I'm getting the following:

<results error="Failed to init the JDBC connection. Check configuration."

Any thoughts from the gurus??

[rahulpandey]

Hi Patrick, I did this in a different way for MySQL I had a use case to export incidents when they get created to a MySQL db. I fired a shell script and query inside it. However, servicenow Integration hub has already jdbc activity, which very intuitive. I hope, this will help you.

[ahammoud]

Have you activated the PlugIn Integration Probe (com.snc.integration.jdbc) ?