The other day I needed to have ServiceNow make a JDBC call on the fly, depending on a given event. I knew that ServiceNow could made scheduled JDBC queries, but I needed something that would work on an event driven schedule rather than just a set time schedule.

Since the scheduled JDBC engine requires the use of a MID Server, I knew that to understand how the JDBC calls worked, I would need to go to the ECC Queue. In combination with the ServiceNow wiki, I found that the best way to get results through JDBC calls through the MID Server was to format the SQL query in XML format, as specified in the “Work” element method within the JDBC Probe.

In this example, I am going to be using a MySQL database. The connection strings and driver names for the various database platforms can be found on the Direct JDBC Probe section in the wiki.

In order to make an on-the-fly JDBC query to a database, you need to place an Output record on the ECC Queue. Here is a sample ECC Queue Message:

The settings are as follows:
Agent: mid.server.<YourMidServerNameGoesHere>
Topic: JDBCProbe
Queue: output
State: ready
Payload:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<parameters>
  <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/>
  <parameter name="connection_string" value="jdbc:mysql://YOUR-URL-GOES-HERE:3306/snctest?user=snctest&amp;password=YOUR-PASSWORD"/>
  <parameter name="work">
    <select table="maint_window">
      <assetid/>
      <se_name/>
      <ci_name/>
      <mx_name/>
      <se_start_date_time/>
      <se_end_date_time/>
    </select>
  </parameter>
</parameters>

When this is submitted on the queue, you should get two response messages from the MID Server. The first one is an acknowledgement (or error). The topic on that message will be: JDBCProbeCompleted.

The next message will have the topic of JDBCProbe, but the Agent field will have the value: JDBCProbeResult. Inside of that message the payload will contact information on the original ECC Queue record that posted the query, and also will post the result in XML format.

Now, if you want to do this all via Javascript, it becomes just as trivial (term used very loosely) as creating a record on the ECC Queue table and then monitoring that table for a response, and processing that response.

Here is a sample script that can do this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
//JDBC query in this example:
//"SELECT se_name,ci_name,mx_name,se_start_date_time,se_end_date_time FROM maint_window";


//Set up the connection details
var driver = "com.mysql.jdbc.Driver";
var serverAddr = "mysql.wishtips.com";
var serverPort = "3306";
var tableName = "snctest";
var user = "snctest";
var password = "sncrocks";

//Build up the Payload XML
var xml = '<?xml version="1.0" encoding="UTF-8"?>';
xml = '<parameters>';
xml += '<parameter name="jdbc_driver" value="' + driver + '"/>';
xml += '<parameter name="connection_string" value="jdbc:mysql://' + serverAddr + ':' + serverPort + '/' + tableName + '?user=' + user + '&' + 'amp;password=' + password + '"/>';
xml += '<parameter name="work">';
xml += '<select table="maint_window">';
xml += '<assetid/><se_name/><ci_name/><mx_name/><se_start_date_time/><se_end_date_time/>';
xml += '</select>';
xml += '</parameter>';
xml += '</parameters>';

//set up the ECC Queue record
var ecc = new GlideRecord("ecc_queue");
ecc.initialize();
ecc.agent="mid.server.RadRoadTrips.com";
ecc.topic="JDBCProbe";
ecc.payload=xml;
ecc.queue="output";
ecc.state="ready";
ecc.sequence = Packages.com.glide.util.Counter.next('ecc:sequence');

//Add the record to the queue
ecc.insert();

//You'll want to keep a hold of these to compare result records
gs.print("New Sysid: " + ecc.sys_id);
gs.print("Sequence: " + ecc.sequence);

//Loop waiting for a record that is created in response to your query
var resp = new GlideRecord("ecc_queue");
resp.addQuery("response_to", ecc.sys_id);
resp.addQuery("queue", "input");
var counter = 0;
do{
 gs.print("Counter: " + counter);
 resp.query();
 resp.next();
 gs.print("Find a record??: " + resp.sys_id);
 gs.sleep(2000);
 counter ++;
} while(!resp.sys_id && counter < 23);

//Found a response
gs.print("Response Payload: " + resp.payload.replace(/\</g, "&" + "lt;"));

//Now look for the corresponding JDBCProbeResult
var eccRes = new GlideRecord("ecc_queue");
eccRes.addQuery("agent", "JDBCProbeResult");
eccRes.addQuery("topic", "JDBCProbe");
eccRes.addQuery("queue", "input");
eccRes.addQuery("state", "ready");
eccRes.orderByDesc("sys_created_on");
eccRes.query();
while(eccRes.next()){
  var payload = new XMLDocument(eccRes.payload);
  var eccQuery = payload.getNodeText("//results/parameters/parameter[@name="ecc_queue"]/@value");
  if( ecc.sys_id == eccQuery ){
    break;
  }
}


//Handle the result of the SELECT query
for(var i=1; i<=eccRes.name; i++){
  //perform in something in SN on this row of data
}

gs.print("Payload: " + eccRes.payload);