JDBCProbe on demand SELECT scripts

[arabeau]

Hi All,

I'm trying to create an on demand script that can do a JDBCProbe but I think I am missing some lines of code... (currently in Istanbul)

Script Method 1) Using the JDBCProbe Script include to make a SELECT statement - issue = ECC Queue record just sits in ready state and never processes, cant specify which fields to return

var j = new JDBCProbe('myMidServer');
j.setDriver('oracle.jdbc.OracleDriver');
j.setConnectionString(myConnectionString);
j.setFunction("select");
j.setTable("VOUCHERS");
j.setWhereClause("VOUCHERS.VOUCHER_STAT_CODE='U')AND (VOUCHERS.CREATE_DATE>{ts '2018-01-02 00:00:00'})ORDER BY VOUCHERS.CREATE_DATE DESC");
j.create();

I cant find any documentation related to using this script include to specify the fields I want returned (ie, get it to add <VOUCHERS.VOUCHER_ID/> or <VOUCHERS.CREATE_DATE/> as child elements to the XML payload under the

<parameter name='work'>

<select table = 'VOUCHERS'>here</select>

</parameter> element

- does this script include need to be modified to do this? if so, would anyone know how to do that? I also don't see a method for adding user credentials

Script Method 2: creating the ECC Queue record and XML payload manually issue = running the below script returns an input record with <results error="java.lang.NullPointerException&#13;&#10;" probe_time="203" result_code="900000"><result error="java.lang.NullPointerException&#13;&#10;"/>

var SQLstatement = "SELECT VOUCHERS.VOUCHER_ID,VOUCHERS.VOUCHER_NUM, VOUCHERS.VOUCHER_TYPE_CODE, VOUCHERS.VOUCHER_STAT_CODE, VOUCHERS.CREATE_USER FROM VOUCHERS WHERE (VOUCHERS.VOUCHER_TYPE_CODE='S-PAMA') AND (VOUCHERS.VOUCHER_STAT_CODE='U')AND (VOUCHERS.CREATE_DATE>{ts '2018-01-02 00:00:00'})ORDER BY VOUCHERS.CREATE_DATE DESC";

//build up payload xml
var payloadXML = '<?xml version="1.0" encoding="UTF-8"?>';
 payloadXML += '<parameters>';
 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();

anyone know what I'm missing?? any help would be greatly appreciated!!

[arabeau]

went with option 2 as I can at least specify a select statement -

I added <parameter name="jdbc_user_name" value="XXX"> and <parameter name="jdbc_password" value="YYY"> to the XML payload -

I still get the null pointer exception error in the JDBCProbe Completed ECC Input but its returning a JDBCPROBE Results record that I can parse for values

leaving this open in the event that someone knows how to get rid of nullpointer exceptions in the ECC Input records AND/OR if anyone has other relevant pointers (or even how to do select statements with JDBCProbe script include)

[Patrick Quinlan]

I'm attempting a similar task in that I need to perform a JDBCProbe to an external Oracle database to retrieve information based on a set of criteria. I have built a Data Source that can perform the action, but I can't seem to find a way to call that Data Source and update the SQL on the fly based on user input on a form. 

I'm attempting to use Option 2 that you've specified and I am able to successfully add an entry to the ECC Queue that get's processed, but it's not giving me a JDBCProbe Result. It does process the 'Output' record that gets created, but the Input record never seems to get processed. 

Any help you can provide is greatly appreciated.