There is a lesser known JDBC integration interface to ServiceNow than is generally published to the outside world, but it can make JDBC integrations go much smoother that traditionally supported in the ServiceNow product.

A typical JDBC integration in ServiceNow is usually a data pull from a third party into ServiceNow through a one-time or scheduled JDBC pull using a Data Source record. This works great for bringing bulk data from an external source into ServiceNow.

What if, however, you want a more transactional data pull…or even to set data in the external database using JDBC? There is a way to do this by creating XML payloads on the ECC queue. There is some pretty good ECC Queue – JDBC Probe documentation on the wiki. This can be cumbersome, however to build ECC Queue records with XML documents for the queries you wish to make. There is a much easier way!

Unfortunately, what I am about to demonstrate does not come with a vanilla instance, but requires the “Integration – JDBC” plugin. Just request the plugin for your instance via HI, or through your account representative. It is a small plugin that can be enabled in a matter of seconds by ServiceNow.

The plugin will create, among other things, a Script Include called “JDBCProbe”. This script include creates an API for generating the XML payloads on the ECC Queue so that you don’t have to worry about how to represent SQL statements in XML.

JDBCProbe API Basics

Every JDBCProbe API call will require some initial parameters and values.

MID Server: The JDBCProbe requires a MID Server for execution. Specify the name of the MID Server as it it shown in the MID Server list.

Here is what my MID Server list looks like:

I am going to use my MID Server, “DEB1” for these examples.

Driver: You need to specific the JDBC Driver that will be used to make the JDBC call. This is specific to the type of Database you are calling into. For this example, I am using a MySQL database. Thus, the driver string is: “com.mysql.jdbc.Driver”. Other valid OOB driver classes are:

Oracle: oracle.jdbc.OracleDriver
Microsoft SQL Server: com.microsoft.sqlserver.jdbc.SQLServerDriver
MySQL: com.mysql.jdbc.Driver
Sybase: com.sybase.jdbc3.jdbc.SybDriver
IBM DB2 Universal: com.ibm.db2.jcc.DB2Driver

Connection String: The JDBC protocol uses a connection string to establish the authentication and other parameters to establish a connection between the client and the Database. Each Database has its own connection string format. Here is a list taken from the ServiceNow JDBCProbe wiki for setting up your connection string:

Oracle: [jdbc:oracle:thin:@]
Microsoft SQL Server: [jdbc:sqlserver://localhost;user=MyUserName;password=*****;]
MySQL: [jdbc:mysql://localhost/database?user=username%26password=passwd]
Sybase: [jdbc:sybase:Tds:10.10.11.162:4100/NCOMS?user=yyy%26password=xxx]
DB2 Universal: [jdbc:db2://sysmvs1.stl.ibm.com:5021/san_jose:user=db2adm;password=db2adm]

Function: This tells the JDBCProbe what type of query you intend to do with the database. Supported functions are: “insert”, “select”, “update”, “delete”.

JDBCProbe API Functions

The following is a list of the functions available by the JDBCProbe Script Include:

JDBCProbe( string MIDServer ) //Constructor
Constructor for the JDBCProbe class. The MIDServer parameter is the name of the MID Server which will be issuing the database commands.

1
var j = new JDBCProbe("DEB1");

setDriver( string Driver )
Sets the driver class corresponding to the brand of database with which you are connecting.

1
2
var j = new JDBCProbe("DEB1");
j.setDriver("com.mysql.jdbc.Driver");

setConnectionString( string connectionString )
Sets the connection string appropriate for the brand of database with which you are connecting.

1
2
var j = new JDBCProbe("DEB1");
j.setConnectionString("jdbc:mysql://mysqlserver.org/snctestdb?user=MyUsername&password=MyPassword");

setTable( string tablename )
Indicates which table we will query against.

1
2
var j = new JDBCProbe("DEB1");
j.setTable("incident");

setFunction( string function )
Sets the query function being used. Supported functions are “select”, “insert”, “update”, “delete”.

1
2
var j = new JDBCProbe("DEB1");
j.setFunction("insert");

addField( string fieldName, string fieldValue )
Adds a field on the insert query. You should provide both the fieldName as well as the value.

1
2
3
4
5
6
7
var j = new JDBCProbe("DEB1");
j.setDriver("com.mysql.jdbc.Driver");
j.setConnectionString("jdbc:mysql://mysqlserver.org/snctestdb?user=MyUsername&password=MyPassword");
j.setTable("incident");
j.setFunction("insert");
j.addField("priority", "1");
j.create();

addNumberField( string fieldName, string fieldValue )
Adds a field formatted for numbers on the insert query. You should provide both the fieldName as well as the value.

1
2
3
4
5
6
7
var j = new JDBCProbe("DEB1");
j.setDriver("com.mysql.jdbc.Driver");
j.setConnectionString("jdbc:mysql://mysqlserver.org/snctestdb?user=MyUsername&password=MyPassword");
j.setTable("incident");
j.setFunction("insert");
j.addNumberField("priority", 1);
j.create();

create( )
Creates the JDBCProbe entry on the ECC Queue table for processing.

1
2
3
4
5
6
7
var j = new JDBCProbe("DEB1");
j.setDriver("com.mysql.jdbc.Driver");
j.setConnectionString("jdbc:mysql://mysqlserver.org/snctestdb?user=MyUsername&password=MyPassword");
j.setTable("incident");
j.setFunction("insert");
j.addField("priority", "1");
j.create();

setWhereClause( string whereClause)
Allows you to set a string representing the “Where” clause for a select, update, or delete query.

1
2
3
4
5
6
7
var j = new JDBCProbe("DEB1");
j.setDriver("com.mysql.jdbc.Driver");
j.setConnectionString("jdbc:mysql://mysqlserver.org/snctestdb?user=MyUsername&password=MyPassword");
j.setTable("incident");
j.setFunction("select");
j.setWhereClause("priority='1'");
j.create();

setDataSource( string dataSourceSysId )
This allows you to have all JDBC settings and query information come from a datasource rather than your script. You will need to pass in the sys_id to the data source record containing this information.

1
2
3
var j = new JDBCProbe("DEB1");
j.setDataSource("11e091bf0a258102005ba7a71b145a8a");
j.create();

ECC Queue Records

Once you execute your script and end it with the create() function, an “output” record will be placed on the ECC Queue. This output record will contain all of the data set through the script.

Once the MID Server grabs the output record, it will execute the query and then return the JDBC response message back via an “input” record on the ECC Queue corresponding to the original ECC Queue entry.

INSERT example

The following code will insert a row in a table called “incident” on a MySQL server that I have:

1
2
3
4
5
6
7
8
9
10
var j = new JDBCProbe('DEB1');
j.setDriver('com.mysql.jdbc.Driver');
j.setConnectionString('jdbc:mysql://mySqlServer.org/snctest?user=MyUser&password=MyPassword');
j.setTable("incident");
j.setFunction("insert");
j.addField("summary", "My chair will not roll smoothly");
j.addNumberField("priority", 1);
j.addField("urgency", "2");
j.addField("impact", "1");
j.create();

This code creates an output record on the ECC Queue that has the following Payload:

1
2
3
4
5
6
7
8
9
10
11
12
<parameters>
  <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/>
  <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&password=sncrocks"/>
  <parameter name="work">
    <insert table="incident">
      <summary>My chair will not roll smoothly</summary>
      <impact>1</impact>
      <urgency>2</urgency>
      <priority quoted="false">1</priority>
    </insert>
  </parameter>
</parameters>

The result is created by the MID Server on the ECC Queue. Here is the Payload of the result:

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
<?xml version="1.0" encoding="UTF-8"?>
<results probe_time="6449">
  <result>
    <insertResult>1 rows inserted</insertResult>
  </result>
  <result/>
  <parameters>
    <parameter name="topic" value="JDBCProbe"/>
    <parameter name="work" value="&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;&lt;work&gt;&lt;insert table=&quot;incident&quot;&gt;&lt;summary&gt;My chair will not roll smoothly&lt;/summary&gt;&lt;impact&gt;1&lt;/impact&gt;&lt;urgency&gt;2&lt;/urgency&gt;&lt;priority quoted=&quot;false&quot;&gt;1&lt;/priority&gt;&lt;/insert&gt;&lt;/work&gt;"/>
    <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&amp;password=sncrocks"/>
    <parameter name="queue" value="output"/>
    <parameter name="error" value=""/>
    <parameter name="from_sys_id" value=""/>
    <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/>
    <parameter name="sys_id" value="80d4367b507da0006d49b2e21aafd57a"/>
    <parameter name="state" value="ready"/>
    <parameter name="from_host" value=""/>
    <parameter name="agent" value="mid.server.DEB1"/>
    <parameter name="processed" value=""/>
    <parameter name="ecc_queue" value="80d4367b507da0006d49b2e21aafd57a"/>
    <parameter name="response_to" value=""/>
    <parameter name="source" value=""/>
    <parameter name="sequence" value="1373db366fe0000001"/>
    <parameter name="name" value="JDBCProbe"/>
    <parameter name="table_name" value="ecc_queue"/>
    <parameter name="agent_correlator" value=""/>
  </parameters>
</results>

UPDATE example

The following code will update all incidents in my database that have a priority of 1. It will set the urgency field to 4.

1
2
3
4
5
6
7
8
var j = new JDBCProbe('DEB1');
j.setDriver('com.mysql.jdbc.Driver');
j.setConnectionString('jdbc:mysql://mySqlServer.org/snctest?user=MyUser&password=MyPassword');
j.setTable("incident");
j.setFunction("update");
j.addField("urgency", "4");
j.setWhereClause("priority='1'");
j.create();

It creates an output record in the ECC queue with the following Payload:

1
2
3
4
5
6
7
8
9
10
<?xml version="1.0" encoding="UTF-8"?>
<parameters>
  <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/>
  <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&amp;password=sncrocks"/>
  <parameter name="work">
    <update table="incident" where="priority='1'">
      <urgency>4</urgency>
    </update>
  </parameter>
</parameters>

The result is created by the MID Server on the ECC Queue. Here is the Payload of the result:

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
<?xml version="1.0" encoding="UTF-8"?>
<?xml version="1.0" encoding="UTF-8"?>
<results probe_time="102">
  <result>
    <updateResult>4 rows updated</updateResult>
  </result>
  <result/>
  <parameters>
    <parameter name="topic" value="JDBCProbe"/>
    <parameter name="work" value="&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;&lt;work&gt;&lt;update table=&quot;incident&quot; where=&quot;priority='1'&quot;&gt;&lt;urgency&gt;4&lt;/urgency&gt;&lt;/update&gt;&lt;/work&gt;"/>
    <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&amp;password=sncrocks"/>
    <parameter name="queue" value="output"/>
    <parameter name="error" value=""/>
    <parameter name="from_sys_id" value=""/>
    <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/>
    <parameter name="sys_id" value="d2d6367b507da0006d49b2e21aafd5ad"/>
    <parameter name="state" value="ready"/>
    <parameter name="from_host" value=""/>
    <parameter name="agent" value="mid.server.DEB1"/>
    <parameter name="processed" value=""/>
    <parameter name="ecc_queue" value="d2d6367b507da0006d49b2e21aafd5ad"/>
    <parameter name="response_to" value=""/>
    <parameter name="source" value=""/>
    <parameter name="sequence" value="1373db889dd0000001"/>
    <parameter name="name" value="JDBCProbe"/>
    <parameter name="table_name" value="ecc_queue"/>
    <parameter name="agent_correlator" value=""/>
  </parameters>
</results>

SELECT example

The following code will select all incidents in my database that have a priority of 1.

1
2
3
4
5
6
7
var j = new JDBCProbe('DEB1');
j.setDriver('com.mysql.jdbc.Driver');
j.setConnectionString('jdbc:mysql://mySqlServer.org/snctest?user=MyUser&password=MyPassword');
j.setTable("incident");
j.setFunction("select");
j.setWhereClause("priority='1'");
j.create();

It creates an output record in the ECC queue with the following Payload:

1
2
3
4
5
6
7
8
<?xml version="1.0" encoding="UTF-8"?>
<parameters>
  <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/>
  <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&amp;password=sncrocks"/>
  <parameter name="work">
    <select table="incident" where="priority='1'"/>
  </parameter>
</parameters>

The result is created by the MID Server on the ECC Queue. The “Name” field contains the number of rows returned. Here is the Payload of the result:

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
<?xml version="1.0" encoding="UTF-8"?>
<results probe_time="236">
  <result query="SELECT * FROM incident WHERE priority='1'">
    <row id="1">
      <summary class="java.lang.String" length="255" type="12">My chair will not roll smoothly</summary>
      <id class="java.lang.Long" length="20" type="-5">1</id>
      <external_id class="java.lang.String" length="255" type="12"/>
      <priority class="java.lang.Integer" length="11" type="4">1</priority>
      <external_system class="java.lang.String" length="255" type="12"/>
      <impact class="java.lang.Integer" length="11" type="4">1</impact>
      <urgency class="java.lang.Integer" length="11" type="4">4</urgency>
    </row>
    <row id="2">
      <summary class="java.lang.String" length="255" type="12">My chair will not roll smoothly</summary>
      <id class="java.lang.Long" length="20" type="-5">2</id>
      <external_id class="java.lang.String" length="255" type="12"/>
      <priority class="java.lang.Integer" length="11" type="4">1</priority>
      <external_system class="java.lang.String" length="255" type="12"/>
      <impact class="java.lang.Integer" length="11" type="4">1</impact>
      <urgency class="java.lang.Integer" length="11" type="4">4</urgency>
    </row>
    <row id="3">
      <summary class="java.lang.String" length="255" type="12">My chair will not roll smoothly</summary>
      <id class="java.lang.Long" length="20" type="-5">3</id>
      <external_id class="java.lang.String" length="255" type="12"/>
      <priority class="java.lang.Integer" length="11" type="4">1</priority>
      <external_system class="java.lang.String" length="255" type="12"/>
      <impact class="java.lang.Integer" length="11" type="4">1</impact>
      <urgency class="java.lang.Integer" length="11" type="4">4</urgency>
    </row>
    <row id="4">
      <summary class="java.lang.String" length="255" type="12">My chair will not roll smoothly</summary>
      <id class="java.lang.Long" length="20" type="-5">4</id>
      <external_id class="java.lang.String" length="255" type="12"/>
      <priority class="java.lang.Integer" length="11" type="4">1</priority>
      <external_system class="java.lang.String" length="255" type="12"/>
      <impact class="java.lang.Integer" length="11" type="4">1</impact>
      <urgency class="java.lang.Integer" length="11" type="4">4</urgency>
    </row>
  </result>
  <parameters>
    <parameter name="topic" value="JDBCProbe"/>
    <parameter name="work" value="&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;&lt;work&gt;&lt;select table=&quot;incident&quot; where=&quot;priority='1'&quot;/&gt;&lt;/work&gt;"/>
    <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&amp;password=sncrocks"/>
    <parameter name="queue" value="output"/>
    <parameter name="error" value=""/>
    <parameter name="from_sys_id" value=""/>
    <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/>
    <parameter name="sys_id" value="23d7367b507da0006d49b2e21aafd5ae"/>
    <parameter name="state" value="ready"/>
    <parameter name="from_host" value=""/>
    <parameter name="agent" value="mid.server.DEB1"/>
    <parameter name="processed" value=""/>
    <parameter name="ecc_queue" value="23d7367b507da0006d49b2e21aafd5ae"/>
    <parameter name="response_to" value=""/>
    <parameter name="source" value=""/>
    <parameter name="sequence" value="1373dbca0140000001"/>
    <parameter name="name" value="JDBCProbe"/>
    <parameter name="table_name" value="ecc_queue"/>
    <parameter name="agent_correlator" value=""/>
  </parameters>
</results>

DELETE example

The following code will delete all incidents in my database that have a priority of 1.

1
2
3
4
5
6
7
var j = new JDBCProbe('DEB1');
j.setDriver('com.mysql.jdbc.Driver');
j.setConnectionString('jdbc:mysql://mySqlServer.org/snctest?user=MyUser&password=MyPassword');
j.setTable("incident");
j.setFunction("delete");
j.setWhereClause("priority='1'");
j.create();

It creates an output record in the ECC queue with the following Payload:

1
2
3
4
5
6
7
8
<?xml version="1.0" encoding="UTF-8"?>
<parameters>
  <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/>
  <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&amp;password=sncrocks"/>
  <parameter name="work">
    <delete table="incident" where="priority='1'"/>
  </parameter>
</parameters>

The result is created by the MID Server on the ECC Queue. The “Name” field contains the number of rows returned. Here is the Payload of the result:

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
<?xml version="1.0" encoding="UTF-8"?>
<results probe_time="67">
  <result>
    <deleteResult>4 rows deleted</deleteResult>
  </result>
  <result/>
  <parameters>
    <parameter name="topic" value="JDBCProbe"/>
    <parameter name="work" value="&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;&lt;work&gt;&lt;delete table=&quot;incident&quot; where=&quot;priority='1'&quot;/&gt;&lt;/work&gt;"/>
    <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&amp;password=sncrocks"/>
    <parameter name="queue" value="output"/>
    <parameter name="error" value=""/>
    <parameter name="from_sys_id" value=""/>
    <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/>
    <parameter name="sys_id" value="31e8b6bb507da0006d49b2e21aafd57f"/>
    <parameter name="state" value="ready"/>
    <parameter name="from_host" value=""/>
    <parameter name="agent" value="mid.server.DEB1"/>
    <parameter name="processed" value=""/>
    <parameter name="ecc_queue" value="31e8b6bb507da0006d49b2e21aafd57f"/>
    <parameter name="response_to" value=""/>
    <parameter name="source" value=""/>
    <parameter name="sequence" value="1373dc001800000001"/>
    <parameter name="name" value="JDBCProbe"/>
    <parameter name="table_name" value="ecc_queue"/>
    <parameter name="agent_correlator" value=""/>
  </parameters>
</results>