JDBC Driver for ServiceNow

The JDBC driver for ServiceNow runs SQL queries against ServiceNow for any Java/JDBC application. The driver supports allows for easy data extraction using Tableau, Informatica, SAP BusinessObjects, Talend, or other BI/ETL software. SQL support includes functions, expressions, aggregation, and joins.

Simple Licensing

Terms and Agreement

Download

ServiceNow JDBC driver

Getting Started

Get started in 5 minutes

Features

  • Access data in ServiceNow from any Java/JDBC program.
  • Manipulate and control data using standard SQL functions not natively supported by ServiceNow.
  • Perform SQL joins across ServiceNow tables. Join and merge data with other relational and NoSQL systems.
  • Handles extraction of large data sets into other databases. Compatible with Tableau, Oracle, SAP, Talend, Informatica, and major BI/ETL software.
  • Simplifies querying and extracting data compared to using the ServiceNow SOAP API. Increases your development efficiency and simplifies maintenance.

Customer Testimonial


"The driver works perfectly for our needs to extract data from ServiceNow to our corporate data warehouse and is easy to integrate with our Oracle ELT tool ODI (Oracle Data Integrator 11g). Your support team is incredible with deep knowledge on JDBC drivers and the way ServiceNow works. I would definitely recommend this tool for folks who want to extract data from ServiceNow using JDBC."– Laxman Malladi, Manager - Enterprise Applications, Herbalife

How it works

  • The SQL query is validated and translated into a ServiceNow SOAP request.
  • Metadata is exposed to relational systems through JDBC by building a schema by parsing WSDL table descriptions provided by ServiceNow.
  • SQL features not natively supported by ServiceNow are executed using the UnityJDBC virtualization engine.

Getting Started

Start using the JDBC Driver for ServiceNow in 5 minutes:

  1. Download the UnityJDBC Driver distribution which contains the ServiceNow JDBC Driver and sample JDBC code for querying ServiceNow. The sample programs can be run directly against a live ServiceNow instance. There is also code to show how the ServiceNow JDBC Driver supports ETL load/refresh/update by extracting large data tables from ServiceNow which can then be inserted into other database systems.
  2. Configure the connection. The JDBC Driver for ServiceNow can be used with any query or reporting software that supports JDBC. The connection information is: 
    	JDBC Driver class name:  snow.jdbc.SNowDriver
	URL format:              jdbc:snow://<serverName>
    As part of the connection string, connection arguments can be passed into the driver that will affect its behavior.
    Connector Argument Details
    AttributeValuesDescription
    debug true, false The debug property will cause the driver to print out debug information to the console during its operation.
    user <username> User name for connection.
    password <password> Password for connection.
    encoding utf-8 Character encoding used.
    schema <file_location> File location of schema. Default schema location is snow_schema.xml in the current directory.
    rebuildschema true, false If true, rebuilds schema for connection. If false, uses existing cached schema if available. Uses location provided in schema property.
    tables comma-separated list of tables Instead of using a fixed schema, dynamically build a schema using only the tables specified (comma-separated). Example: tables=alm_asset,incident
    batchsize 100 to 500000 Limits requests to ServiceNow API to the given size (default 1000). Used to handle issues with timeouts in ServiceNow SOAP API when dealing with large tables. A large table request is automatically divided into smaller requests of maximum size equal to batchsize.
    threads 5 Performs parallel requests to ServiceNow API to retrieve large tables in batches. Provides higher performance for retrieving large tables in parallel.
  3. Connect to a database and run your queries.

    If the submitted query cannot be natively handled by the JDBC Driver for ServiceNow (such as for queries containing JOIN, GROUP BY or HAVING clauses), the query will be promoted to UnityJDBC for processing. UnityJDBC will parse the query into subqueries that will then be run on the specific ServiceNow tables and process the intermediate results to produce the final result.

    The trial version has no expiration date and is fully functioning except that it is limited to returning up to 100 results. If your query produces more than 100 results, purchase a ServiceNow JDBC license here for access to the complete result set.

Example Code Walkthrough

  1. Create a new instance of the JDBC Driver for ServiceNow (optional).
    2. 	Class.forName("snow.jdbc.SNowDriver");
  2. Connect to the URL. A schema will be created and cached. Control the schema location using the schema URL parameter. Note that caching schemas is recommended for good performance. Note that it is also possible to only access a selected list of tables using the tables URL parameter.
    3. 	String url="jdbc:snow://demoodbc.service-now.com?schema=snow_schema.xml";
	con = DriverManager.getConnection(url, "admin", "admin");
  3. Create a statement and submit a query.
    4. 	stmt = con.createStatement();
	String sql = "SELECT number, caller_id, priority, active FROM incident";
	              " WHERE active = 1 ORDER BY number DESC LIMIT 100 OFFSET 5;";
	rst = stmt.executeQuery(sql);
  4. Print out your results.
    5. 	ResultSetMetaData meta = rst.getMetaData();
	int numColumns = meta.getColumnCount();
	System.out.print(meta.getcolumnName(1));
	for (int j = 2; j <= meta.getColumnCount(); j++)
		System.out.print(", " + meta.getColumnName(j));
	System.out.println();
	while (rst.next())
	{
		System.out.print(rst.getObject(1));
		for (int j = 2; j <= numColumns; j++)
			System.out.print(", " + rst.getObjects(j));
		System.out.println();
	}
  5. Close the statement and connection.
    6. 	rst.close();
	stmt.close();
	con.close();

Features and SQL Support for the JDBC Driver for ServiceNow

The following table shows which SQL queries can be handled directly by the JDBC driver for ServiceNow. Queries that cannot be run natively through the JDBC driver for ServiceNow will be automatically promoted to the UnityJDBC virtualization engine. Note: The trial version will return up to 100 results. If your query produces more than 100 results, purchase a license here. Note that the ODBC Driver released by ServiceNow does not support the JDBC-ODBC bridge or access from any Java programs.

Important Notes:

  • Case-sensitive identifiers: SQL identifiers (table and field names) are not case-sensitive. Use delimited-identifiers (with double-quotes) to force case-sensitivity in SQL and always use double-quotes for SQL reserved words (e.g. "select").

  • SQL functions: Common SQL functions including string functions (concat, substr, instr), date functions (e.g. dateadd, datediff, day), trigonometry functions, mathematical functions (e.g. abs), bit functions, and others are supported. Complete list of SQL functions supported.

  • JDBC DatabaseMetaData and ResultSetMetaData Support: The JDBC Driver for ServiceNow supports the JDBC API allowing interoperability with database software using SQL and JDBC. This includes support for DatabaseMetaData and ResultSetMetaData. A schema is built on connection and can be cached (see Connection Parameters for more details). For DatabaseMetaData, common API methods used include getTables, getColumns, getPrimaryKeys, getIndexInfo, getDatabaseMajorVersion, among others.

  • Long-running requests and ServiceNow SOAP API Timeouts - For long-running SOAP API requests, timeouts are possible. To increase the timeout limit, see the reference ServiceNow SOAP API long-running request timeouts.

  • Permissions - The permissions required to execute queries is the ServiceNow user account must have soap_query or soap permissions. See the reference ServiceNow web service user roles.
Feature SQL Example JDBC Driver for ServiceNow UnityJDBC Virtualization Support
SELECT Clause
Select all fields from one table 
SELECT *
FROM   incident
Select a list of fields from one table 
SELECT NUMBER, caller_id
FROM   incident
Select with field aliases 
SELECT NUMBER n, caller_id AS cid
FROM   incident
Select expressions 
SELECT NUMBER+1 n, 'Caller: '+caller_id AS cid
FROM   incident
Select distinct 
SELECT DISTINCT caller_id
FROM   incident
FROM Clause
One table query 
SELECT *
FROM   incident
Multiple table query 
SELECT I.number, I.caller_id, U.sys_id
FROM   incident I, sys_user U
WHERE  I.caller_id = U.sys_id
Inner join clause 
SELECT I.number, I.caller_id, U.sys_id
FROM   incident I INNER JOIN sys_user U
       ON I.caller_id = U.sys_id
Left outer join clause 
SELECT I.number, I.caller_id, U.sys_id
FROM   incident I LEFT OUTER JOIN sys_user U
       ON I.caller_id = U.sys_id
Right outer join clause 
SELECT I.number, I.caller_id, U.sys_id
FROM   incident I RIGHT OUTER JOIN sys_user U
       ON I.caller_id = U.sys_id
Full outer join clause 
SELECT I.number, I.caller_id, U.sys_id
FROM   incident I FULL OUTER JOIN sys_user U
       ON I.caller_id = U.sys_id
WHERE Clause
Equality (=) comparison 
SELECT NUMBER, caller_id
FROM   incident
WHERE  NUMBER = 'INC0000006'
Greater than equal (>=) comparison 
SELECT NUMBER, caller_id
FROM   incident
WHERE  NUMBER >= 'INC0000006'
Greater than (>) comparison 
SELECT NUMBER, caller_id
FROM   incident
WHERE  NUMBER > 'INC0000006'
Less than equal (<=) comparison 
SELECT NUMBER, caller_id
FROM   incident
WHERE  NUMBER <= 'INC0000006'
Less than (<) comparison 
SELECT NUMBER, caller_id
FROM   incident
WHERE  NUMBER < 'INC0000006'
Not equal (!= or <>) comparison 
SELECT NUMBER, caller_id
FROM   incident
WHERE  NUMBER != 'INC0000006'
Like comparison 
SELECT NUMBER, caller_id
FROM   incident
WHERE  NUMBER LIKE 'INC00000%'
Two attribute comparison 
SELECT NUMBER, opened_at, sys_created_on
FROM   incident
WHERE  opened_at = sys_created_on
Negation 
SELECT NUMBER, caller_id
FROM   incident
WHERE  NOT (NUMBER = 'INC00000%')
AND and OR 
SELECT NUMBER, caller_id
FROM   incident
WHERE  priority = 1 AND (severity = 1 OR severity=3)
NOT LIKE comparison 
SELECT NUMBER, caller_id
FROM   incident
WHERE  NUMBER NOT LIKE 'INC%'
Date and Timestamp Support
DateTime comparison with Current Time 
SELECT NUMBER, caller_id, opened_at
FROM   incident
WHERE  opened_at < CURRENT_TIMESTAMP()
DateTime comparison with Date Constant 
SELECT NUMBER, caller_id, opened_at
FROM   incident
WHERE  opened_at > '2014-05-28 13:35:00'
DateTime comparison for use with ETL/Data Load 
SELECT NUMBER, caller_id, sys_updated_on
FROM   incident
WHERE  sys_updated_on > '2014-05-28 13:35:00'
Details
Details
group by Clause
Group by one attribute 
SELECT   caller_id, COUNT(*) AS cnt
FROM     incident
GROUP BY caller_id
Group by multiple attributes 
SELECT   caller_id, priority, COUNT(*) AS cnt
FROM     incident
GROUP BY caller_id, priority
Aggregate Functions
Count(*) 
SELECT COUNT(*)
FROM   incident
Count(attribute) 
SELECT COUNT(caller_id)
FROM   incident
Maximum 
SELECT MAX(NUMBER)
FROM   incident
Minimum 
SELECT MIN(NUMBER)
FROM   incident
Summation 
SELECT SUM(reopen_count)
FROM   incident
Average 
SELECT avg(reopen_count)
FROM   incident
Having Clause
Having clause 
SELECT   caller_id, COUNT(*) AS cnt
FROM     incident
GROUP BY caller_id
HAVING   COUNT(*) > 1
Order by Clause
Order by one attribute 
SELECT   NUMBER, caller_id
FROM     incident
ORDER BY NUMBER ASC
Order by multiple attributes 
SELECT   NUMBER, caller_id
FROM     incident
ORDER BY priority ASC, NUMBER ASC
Order by expression 
SELECT   NUMBER, caller_id
FROM     incident
ORDER BY priority+1 DESC
Limit/Offset Clause
Limit clause 
SELECT NUMBER, caller_id
FROM   incident
LIMIT  10
Offset clause 
SELECT NUMBER, caller_id
FROM   incident
LIMIT  10
offset 5
Union Clause
Two query union 
SELECT caller_id
FROM   incident
WHERE  severity = 3
UNION
SELECT caller_id
FROM   incident
WHERE  priority = 1

Setup Instructions