Connecting to ServiceNow

Establishing a Connection shows how to authenticate to ServiceNow and configure any necessary connection properties in a JDBC URL. You can also configure driver capabilities through the available Connection properties, from data modeling to firewall traversal. The Advanced Settings section shows how to set up more advanced configurations and troubleshoot connection errors.

Connecting to JDBC Data Sources

The JDBC Driver for ServiceNow provides full support for integration into Java applications, including Eclipse, NetBeans, IntelliJ IDEA, and many other Integrated Development Environments, as well as J2EE applications running on a Java server such as Tomcat. You can find JSP, console, and swing demos in the installation folder.

Java Version Support

To deploy the driver JAR file, you must have Java Development Kit (JDK) 1.6 or higher installed on your system.

ServiceNow Version Support

The driver models ServiceNow APIs as bidirectional tables. The SOAP API is used to resolve the metadata from ServiceNow. The REST API is then used to work with data.

See Also

• Create Connection Objects

  See Connecting from Code to create JDBC Connection objects.

• Query Data from Code

  See Executing Statements and Using Prepared Statements to execute SQL statements to ServiceNow tables.

• Connect from Java-Based Tools

  See Using from Tools shows how to connect to ServiceNow and query data from several popular database tools.

Creating a JDBC Data Source

You can create a JDBC data source to connect from your Java application. Creating a JDBC data source based on the JDBC Driver for ServiceNow consists of three basic steps:

• Add the driver JAR file to the classpath. The JAR file is located in the lib subfolder of the installation directory. Note that the .lic file must be located in the same folder as the JAR file.
• Provide the driver class. For example:

  cdata.jdbc.servicenow.ServiceNowDriver

• Provide the JDBC URL. For example:

  jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;
  
  or
  
  jdbc:cdata:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;

  The second format above can be used whenever there is a conflict in your application between drivers using the same URL format to ensure you are using the driver. The URL must start with either "jdbc:servicenow:" or "jdbc:cdata:servicenow:" and can include any of the connection properties in name-value pairs separated with semicolons.

Connecting to ServiceNow

Before you can connect to data, you will need to ensure the authenticating user has the following permissions assigned at minimum, required for listing metadata. Before you can do this, the administrator of the account must elevate their role by navigating to User menu -> Elevate Roles -> check the security_admin box -> OK. For the tables listed below, the user must have both row-level permission, such as sys_db_object, as well as field-level permission, such as sys_db_object.*. For additional tables which the user wishes to access, they must have at least row-level permission.

The connection property URL is a required property on all connections.

READ sys_db_object

Access to sys_db_object is required to connect to data. You can enable access to this as follows:

1. Navigate to the System Security -> Access Controls (ACL). Select New to create an access control object.
2. For Type, select record.
3. For Operation, select read.
4. For Name, select Table [sys_db_object] in the first drop-down and --None-- in the second drop-down.
5. In the Requires role section, double-click the text box that says Insert a new row.... Search for and select your desired role.
6. Click Submit to create the ACL object.
7. Assign the role which has the created ACL to the authenticating user. To do this, navigate to User Administration -> Users -> Select authenticating user -> Roles -> Edit... -> add your role from collection.

READ sys_glide_object

Access to the sys_glide_object is required for certain ServiceNow table metadata. You can enable access to this by repeating the above procedure, but instead selecting Field class [sys_glide_object] for the ACL's name.

READ sys_dictionary

Access to sys_dictionary is required to retrieve schema information from ServiceNow. You can enable access to this by navigating to User Administration -> Users -> Select authenticating user -> Roles -> Edit... -> add "personalize_dictionary" role from collection.

Authenticating to ServiceNow

Authenticate using Basic Authentication

In order to authenticate using Basic Authentication you will need to provide your ServiceNow User and Password.

After setting the following connection properties, you are ready to connect:

1. AuthScheme: Set this to BASIC.
2. User: Set this to your username.
3. Password: Set this to your password.
4. URL: Set this to the base URL of your ServiceNow instance site. For example: https://MyInstance12345.service-now.com/.
5. InitiateOAuth: Set this to OFF to avoid entering the OAuth Authorization process.

Using OAuth

ServiceNow uses the OAuth 2.0 authentication standard. To authenticate using OAuth, you will need to register an OAuth app with ServiceNow to obtain the OAuthClientId and OAuthClientSecret. In addition to the OAuth values, you will need to specify the URL, User, and Password.

For more information about connecting via OAuth authentication, refer to our Using OAuth Authentication guide.

Authenticating with ADFS

Set the AuthScheme to ADFS. The following connection properties need to be set:

• User: Set this to the ADFS user.
• Password: Set this to ADFS password for the user.
• SSOLoginURL: Set this to the login URL used by the SSO provider.

The following SSOProperties are needed to authenticate to ADFS:

• RelyingParty: This attribute is the value of the Relying Party Identifier on the ADFS server for ServiceNow.

Below is an example connection string:

AuthScheme=ADFS;User=username;Password=password;SSOLoginURL='https://sts.company.com';SSOProperties='RelyingParty=https://saml.service-now.com';Url=https://MyInstance12345.service-now.com/;

ADFS Integrated

The ADFS Integrated flow indicates you are connecting with the currently logged in Windows user credentials. To use the ADFS Integrated flow, simply do not specify the User and Password, but otherwise follow the same steps in the ADFS guide above.

Authenticating with Okta

Set the AuthScheme to Okta. The following connection properties are used to connect to Okta:

• User: Set this to the Okta user.
• Password: Set this to Okta password for the user.
• SSOLoginURL: Set this to the login URL used by the SSO provider.

The following SSOProperties are needed to authenticate to Okta:

• APIToken (optional): Set this to the API Token that the customer created from the Okta org. It should be used when authenticating a user via a trusted application or proxy that overrides OKTA client request context.
• MFAType (optional): Set this only in case you have configured MFA flow. Currently we support only the follwoing types: OktaVerify,Email and SMS.
• MFAPassCode (optional): Set this only in case you have configured MFA flow. If this is set to empty/invalid the driver will initially issue a MFA challenge which will trigger the platform to send you an one-time password on your device or email, based on the configured MFA type. You need to re-issue another connection where the retrieved one-time password value is passed to MFAPassCode connection property.

The following is an example connection string:

AuthScheme=Okta;SSOLoginURL='https://example.okta.com/home/appType/0bg4ivz6cJRZgCz5d6/46';User=oktaUserName;Password=oktaPassword;Url=https://MyInstance12345.service-now.com/;

Authenticating with OneLogin

Set the AuthScheme to OneLogin. The following connection properties are used to connect to OneLogin:

• User: Set this to the OneLogin user.
• Password: Set this to OneLogin password for the user.

The following SSOProperties are needed to authenticate to OneLogin:

• OAuthClientId: Set to the OAuthClientId, which can be obtained by selecting Developers > API Credentials > Credential > ClientId.
• OAuthClientSecret: Set to the OAuthClientSecret, which can be obtained by selecting Developers > API Credentials > Credential > ClientSecret.
• Subdomain: Set to the subdomain of the OneLogin user accessing the SSO app. For example, if your OneLogin URL is splinkly.onelogin.com, enter splinkly as the subdomain value.
• AppId: Set to the ID of the SSO app.
• Region (optional): Set to the region your OneLogin account resides in. The OneLogin API operates in multiple regions and this property is used to find the correct domain. It can take one of the following values:
  • US (default)
  • EU

The following is an example connection string: The following connection string uses an API key to connect to OneLogin:

AuthScheme=OneLogin;User=OneLoginUserName;Password=OneLoginPassword;SSOProperties='OAuthClientID=3fc8394584f153ce3b7924d9cd4f686443a52b;OAuthClientSecret=ca9257fd5cc3277abb5818cea28c06fe9b3b285d73d06;Subdomain=OneLoginSubDomain;AppId=1433920';Url=https://MyInstance12345.service-now.com/;

Authenticating with PingFederate

Set the AuthScheme to PingFederate. The following connection properties need to be set:

• User: Set this to the PingFederate user.
• Password: Set this to PingFederate password for the user.
• SSOLoginURL: Set this to the login URL used by the SSO provider.

The following SSOProperties are needed to authenticate to PingFederate:

• AuthScheme (optional): The authorization scheme to be used for the IdP endpoint. The allowed values for this IdP are None or Basic.

Additionally, you can use the following SSOProperties to configure mutual SSL authentication for SSOLoginURL, the WS-Trust STS endpoint:

• SSLClientCert
• SSLClientCertType
• SSLClientCertSubject
• SSLClientCertPassword

Below is an example connection string:

AuthScheme=PingFederate;URL='https://dev103586.service-now.com';SSOLoginUrl='https://mycustomserver.com:9033/idp/sts.wst';User=admin;Password=PassValue123;

Building the JDBC URL

Connection strings provide information about a data source and how to connect to that data source. The driver comes with a connection string builder that makes it easier to create and manage the contents of connection strings.

After downloading and installing the driver, double-click the .jar file in the lib folder. You can also manually run the .jar file, as shown in the following examples.

From Windows:

java -jar 'C:\Program Files\ JDBC Driver for ServiceNow 2021\lib\cdata.jdbc.servicenow.jar'

From macOS:

java -jar cdata.jdbc.servicenow.jar

Running the .jar file opens the Connection Properties dialog box. You can use this dialog box to build and test a connection string. Click Test Connection to test and validate the entered connection properties. Click Copy to Clipboard to copy the connection string for use within the application where the JDBC driver is being used. See the help documentation for more information about the connection string options.

OAuth requires the authenticating user to interact with ServiceNow using the browser. The driver facilitates this in various ways as described below.

Custom Credentials

You need to register an app to obtain the OAuthClientId and OAuthClientSecret.

Web Applications

You need to create an OAuth app to use in the web flow. See Creating a Custom OAuth App for the procedure.

Desktop Applications

You need to create an OAuth app. See Creating a Custom OAuth App for the procedure.

Headless Machines

In the headless OAuth flow, users need to authenticate via a browser on another machine. You need to create a custom OAuth app. See Creating a Custom OAuth App for the procedure.

Creating a Custom OAuth App

See Creating a Custom OAuth App for the procedure.

OAuth requires the authenticating user to interact with ServiceNow using the browser. The driver facilitates this in various ways as described below.

Authenticate to ServiceNow from a Desktop Application or Web Application

Authenticate to ServiceNow using the OAuth PASSWORD grant

After setting the following connection properties, you are ready to connect:

• OAuthGrantType: Set this to PASSWORD.
• AuthScheme: Set this to OAUTH.
• InitiateOAuth: Set this to GETANDREFRESH for a Desktop Application and REFRESH for a Web Application. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
• OAuthClientId: Set this to the Client ID in your app settings.
• OAuthClientSecret: Set this to the Client Secret in your app settings.
• User: Set this to your username.
• Password: Set this to your password.
• URL: Set this to the base URL of your ServiceNow instance site. For example: https://MyInstance12345.service-now.com/.

When you connect the driver then completes the OAuth process:

1. Extracts the access token from the response and authenticates requests.
2. Refreshes the access token when it expires.
3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Authenticate to ServiceNow using the OAuth CODE grant

To obtain the access token and refresh token, when connecting via a web application, or if the driver is not authorized to open a browser window, you need to exchange temporary verification values for the access token. In another machine do the following:

1. OAuthGrantType: Set this to CODE.
2. OAuthClientId: Set this to the Client ID in your app settings.
3. OAuthClientSecret: Set this to the Client Secret in your app settings.
4. URL: Set this to the base URL of your ServiceNow instance site. For example: https://MyInstance12345.service-now.com/.
5. Call GetOAuthAuthorizationURL, for example, EXEC GetOAuthAuthorizationURL CallbackURL = 'http://localhost:33333'. The CallbackURL must match the Redirect URI you have set in your Custom OAuth App. The stored procedure* returns the URL to the OAuth endpoint.
6. Log in and authorize the application. You are redirected back to the callback URL. The callback URL contains the verifier code in the query string parameter named "code". Extract the verifier code and call GetOAuthAccessToken, for example, EXEC GetOAuthAccessToken CallbackURL = 'http://localhost:33333', Authmode = 'WEB', Verifier = 'KG1tAuc13DlyLUk7VcqvfkrICtLEMnWgcNMAv20kj4Qum_X47kx1MGUjaF8qFBTR36vSgZlc1_kjLAYq1D9ixg'.

To connect to data, set the following connection properties:

• OAuthGrantType: Set this to CODE.
• OAuthClientId: Set this to the Client ID in your app settings.
• OAuthClientSecret: Set this to the Client Secret in your app settings.
• URL: Set this to the base URL of your ServiceNow instance site. For example: https://MyInstance12345.service-now.com/.
• OAuthAccessToken: Set this to the value that you got from calling GetOAuthAccessToken.

To make the driver automatically refresh the access token when it expires, set InitiateOAuth to REFRESH and set OAuthRefreshToken. Alternatively, set InitiateOAuth to OFF and call the RefreshOAuthAccessToken stored procedure* when the access token expires. Given a refresh token as input, the procedure returns a valid OAuth access token.

Using OAuth on a Headless Machine

The following sections show how to authenticate a headless server or another machine on which the driver cannot open a browser. You can authenticate with a user account or with a service account.
Note: Authenticating with the OAuth Password grant on a headless machine is the same process as explained in Custom Credentials

Authenticate with a User Account

To authenticate with a user account, you need to authenticate from another machine. Authentication is a two-step process.

1. Instead of installing the driver on another machine, you can follow the steps below to obtain the OAuthVerifier value. Or, you can install the driver on another machine and transfer the OAuth authentication values, after you authenticate through the usual browser-based flow.
2. You can then configure the driver to automatically refresh the access token from the headless machine.

You can follow the headless OAuth authentication flow using the OAuth credentials from Creating a Custom OAuth App.

Obtain a Verifier Code

Set the following properties on the headless machine:

• OAuthGrantType: Set this to CODE.
• InitiateOAuth: Set this to OFF.
• OAuthClientId: Set this to the Client ID in your app settings.
• OAuthClientSecret: Set this to the Client Secret in your app settings.
• URL: Set this to the base URL of your ServiceNow instance site.

You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property.

1. Call the GetOAuthAuthorizationURL stored procedure* with the CallbackURL input parameter set to the exact Redirect URI you specified in your app settings.
2. Open the returned URL in a browser. Log in and grant permissions to the driver. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. You will set this in the OAuthVerifier connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• OAuthClientId: Set this to the consumer key in your app settings.
• OAuthClientSecret: Set this to the consumer secret in your app settings.
• OAuthVerifier: Set this to the verifier code.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified file.
• InitiateOAuth: Set this to REFRESH.
• OAuthGrantType: Set this to CODE.
• URL: Set this to the base URL of your ServiceNow instance site.

After the OAuth settings file is generated, set the following properties to connect to data:

• OAuthClientId: Set this to the consumer key in your app settings.
• OAuthClientSecret: Set this to the consumer secret in your app settings.
• OAuthSettingsLocation: Set this to the file containing the encrypted OAuth authentication values. Make sure this file gives read and write permissions to the provider to enable the automatic refreshing of the access token.
• InitiateOAuth: Set this to REFRESH.
• OAuthGrantType: Set this to CODE.
• URL: Set this to the base URL of your ServiceNow instance site.

Transfer OAuth Settings

Follow the steps below to install the driver on another machine, authenticate, and then transfer the resulting OAuth values.

On a second machine, install the driver and connect with the following properties set:

• OAuthSettingsLocation: Set this to a writable text file.
• InitiateOAuth: Set this to GETANDREFRESH.
• OAuthClientId: Set this to the client ID assigned when you registered your app.
• OAuthClientSecret: Set this to the client secret assigned when you registered your app.
• CallbackURL: Set this to the Redirect URL in your app settings.
• URL: Set this to the base URL of your ServiceNow instance site.
• OAuthGrantType: Set this to CODE.

Test the connection to authenticate. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the consumer key in your app settings.
• OAuthClientSecret: Set this to the consumer secret in your app settings.
• OAuthSettingsLocation: Set this to the path to your OAuth settings file. Make sure this file gives read and write permissions to the driver to enable the automatic refreshing of the access token.
• URL: Set this to the base URL of your ServiceNow instance site.
• OAuthGrantType: Set this to CODE.

You can follow the procedure below to register an app and obtain the OAuth client credentials, the OAuthClientId and OAuthClientSecret. Note that you will need to activate the OAuth plugin for your instance if it is not active already.

1. Log in to your ServiceNow instance.
2. In the Navigator, click System OAuth -> Application Registry.
3. Click New and then click Create an OAuth API Endpoint for External Clients.
4. Select the Active check box, enter a name for the app, and set a Client Secret or leave this blank to have one generated automatically.
5. Enter a value in the Redirect URL box. If you are making a desktop application, set the Redirect URL to http://localhost:33333 or a different port number of your choice. If you are making a web application, set the Redirect URL to a page on your Web app you want the user to be returned to after they have authorized your application.
6. After clicking submit, you may navigate back to the newly created app to take note of the OAuthClientId, OAuthClientSecret, and CallbackURL values.

This section details a selection of advanced features of the driver

User Defined Views

The driver allows you to define virtual tables whose contents are decided by a pre-configured query. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how certificate negotiations are handled by the driver. You can specify a specific certificate for use in SSL.

Firewall and Proxy

Configure the driver for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

JDBC Remoting

You can use JDBC Remoting to connect to the driver from remote machines.

Caching Data

Caching Data enables faster access to data and reduces the number of API calls, improving performance. You can cache automatically or explicitly.

Logging

See Logging for an overview of configuration settings that can be used to refine logging.

The JDBC Driver for ServiceNow allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views and are useful in situations where you cannot directly control the query being issued to the driver e.g. when using the driver from a tool. The User Defined Views can be used to define predicates that are always applied no matter what. If additional predicates are specified in the query to the view, then they are combined with the query already defined as part of the view.

For example, a User Defined View called UserViews.RCustomers that only lists customers in a particular city might look like:

SELECT * FROM Customers WHERE City = 'Raleigh';

The query to the driver could be:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined as appropriate.

Defining Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json in the Location folder. The driver will automatically detect the views if a file called UserDefinedViews.json is found in the Location folder.

It is also possible to have multiple view definitions and control them using the UserDefinedViews connection property. If the UserDefinedViews property is specified, only the views defined in this file are seen by the driver.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM incident WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

Defining Views Using DDL Statements

The driver is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

The view is created in the JSON configuration file and will now be discoverable.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead.

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view will be updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

The view will be removed from the JSON configuration file and can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the name of the view from clashing with an actual entity in the data model. It is possible to change the name of the schema used for UserViews. This is done by setting the UserViewsSchemaName property.

Customizing the SSL Configuration

By default, the driver attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The JDBC remoting feature allows you to connect to the JDBC driver from remote machines. The remoting feature is a daemon process that listens for database queries and responds to them. The driver supports the MySQL protocol for remoting.

JDBC remoting will allow any MySQL or SQL Server client (ODBC drivers, programming languages like PHP, Perl, Python, tools like MySQL workbench, and many other applications) to easily connect to ServiceNow.

Configuring the Driver for Remoting

Remoting can be configured through either a Command Line Interface or an INI-formatted configuration file.

Remoting via CLI

The MySQL daemon can be started directly from the command line. This can be accomplish by invoking the JAR and specifying the necessary CLI Options.

Remoting with a Configuration File

If you prefer not to provide every configuration property directly in the command line, a Configuration File can be defined, which will explicitly list all properties. Once defined, the daemon can be started with a single reference to this file in the CLI.

You can start the MySQL daemon from the command line, as shown below:

java -jar cdata.jdbc.servicenow.jar [ options ]

The following command-line options are available:

	Option	Description
	-h, --help	Display help for available options and exit.
	-f, --config-file	The configuration file for the daemon.
	-u, --user	The user allowed to connect. Use a configuration file to configure multiple users. If a user is specified on the command line, then only that user is given access.
	-p, --password	The password for the user specified with the user option. If both user and password are set on the command line, the users section in the config file is ignored.
	-d, --database	The database that clients will use to connect. If multiple databases are specified in the config file, connections are allowed to only the database specified on the command line.
	-c, --connection	The connection string used to connect to the data source being surfaced. If no connection string is specified on the command line, the connection string is read from the config file.
	-P, --port	The port number to use to listen for TCP/IP connections. The default port is 3306.
	-m, --max-connections	The maximum number of allowed TCP/IP connections. The default value is 25 connections.
	--session-timeout	The session timeout time in seconds. The default timeout is 20 seconds.
	-t, --protocol	The protocol used for remoting. The default value is MySQL.
	-g, --logfile	The full path of the log file.
	-F, --logrotationscheme	The interval at which to truncate the logs. The options are 1 (daily in the format [MyFileName]_2016_3_21.txt), 2 (weekly in the format [MyFileName]_Week_5.txt, where 5 is the fifth week in the year), and 3 (monthly in the format [MyFileName]_2016_3_21.txt).
	-v, --verbosity	The verbosity of the log. 1 is informational. Levels up to 5 add the following subsequent details: (2) HTTP headers, (3) the HTTP body, (4) transport-level communication including SSL, and (5) interface commands and other data source communication.
	--test	The database to test the connection with. If this property is not specified, the default database is used.
	--ssl-cert	The path to the SSL certificate.
	--ssl-subject	The subject of the SSL certificate.
	--ssl-password	The password of the SSL certificate.
	-n, --nodeid	Displays the NodeId of this machine.
	-l, --license	Installs the license on this machine. This option will prompt you for the type of license and other details.

Options specified on the command line take precedence over options specified in the config file. You can pass in command-line options to specify a restricted subset of the options allowed in the Configuration File.

You can save configuration settings for MySQL remoting in a config file. The file must be structured in the INI file format. Specify this file with the -f command-line option. The config file can have the following sections:

[mysqld]

In the mysqld section, use the following properties to configure the MySQL daemon:

	Name	Description
	port	The port number to use to listen for TCP/IP connections. The default port is 3306.
	max-connections	The maximum number of allowed TCP/IP connections. 25 is the default.
	session-timeout	The session timeout time in seconds. The default timeout is 20 seconds.
	logfile	The full path of the log file.
	verbosity	The verbosity of the log. 1 is informational. Levels up to 5 add the following subsequent details: (2) HTTP headers, (3) the HTTP body, (4) transport-level communication including SSL, and (5) interface commands and other data source communication.
	logrotationscheme	The interval at which to truncate the logs. The options are 1 (daily in the format [MyFileName]_2016_3_21.txt), 2 (weekly in the format [MyFileName]_Week_5.txt, where 5 is the fifth week in the year), and 3 (monthly in the format [MyFileName]_2016_3_21.txt). The default is 2.
	ssl-cert	The path to the SSL certificate.
	ssl-subject	The subject of the SSL certificate.
	ssl-password	The password of the SSL certificate.

[databases]

In the databases section, define keys that map the MySQL database or the SQL Server catalog to ServiceNow connection strings. Clients connect to the MySQL database or the SQL Server catalog defined here. To connect to ServiceNow, the driver uses the connection string that corresponds to this key.

[databases]
ServiceNow = "InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;"

[users]

In the users section, define the usernames and passwords of the users of the server. If the acl section is not defined, all users have access to all databases.

[mysql_vars]

In the mysql_vars section, define system variables for the MySQL server. The standard variables are supported. Below are several examples:

	Name	Description
	version_comment	This value is hard-coded as JDBC Driver for ServiceNow (MySQL Remoting).
	character_set_client	The character_set used in statements sent by the client.

[acl]

In the acl section, allow users to access ServiceNow databases. Databases must be defined in the databases section. Users must be defined in the users section. Use commas to separate users authorized to access the specified database.

Example Config File

Below is an example config file. The example includes all properties required to configure the server. It also shows how to configure access control for several users and ServiceNow instances.

[mysqld]
port = 3306
max-connections = 25
session-timeout = 20
logfile = ServiceNowRemotingLog.txt
verbosity = 2
ssl-cert = ".JDBC.ServiceNow.Remoting.pfx"
ssl-subject = <subject>
ssl-password = <password>

[databases]
ServiceNow = "InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;"
ServiceNow_ReadOnly = "InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;ReadOnly=True"

[users]
root = <password>
test = <password>

[mysql_vars]
version_comment =  " JDBC Driver for ServiceNow (MySQL Remoting)"

[acl]
ServiceNow = root
ServiceNow_ReadOnly = root, test

You can further restrict the allowed options by passing in options on the command line. See CLI Options for more information.

Caching Data

Caching data provides several benefits, including faster access to data and reducing the number of API calls, which improve performance. The connector supports a simple caching model where multiple connections can also share the cache over time. You can enable and configure caching features by setting the necessary connection properties.

Contents

The sections in this chapter detail the driver's caching functionality and link to the corresponding connection properties, as well as SQL statements.

Configuring the Cache Connection

Configuring the Cache Connection describes the properties that you can set when configuring the cache database.

Caching Metadata

Caching Metadata describes the CacheMetadata property. This property determines whether or not to cache the table metadata to a file store.

Automatically Caching Data

Automatically Caching Data describes how the driver automatically refreshes the cache when the AutoCache property is set.

Explicitly Caching Data

Explicitly Caching Data describes how you can decide what data is stored in the cache and when it is updated.

Data Type Mapping

Data Type Mapping shows the mappings between the data types configured in the schema and the data types in the database.

Configuring the Caching Database

This section describes the properties for caching data to the persistent store of your choice.

CacheLocation

The CacheLocation property species the path to a file-system-based database. When caching is enabled, a file-system-based database is used by default. If CacheLocation is not specified, this database is stored at the path in Location. If neither of these connection properties are specified, the driver uses a platform-dependent default location.

CacheConnection

The CacheConnection property specifies a database driver and the connection string to the caching database.

CacheDriver

The CacheDriver property specifies a database driver and the connection string to the caching database.

This section describes how to enable caching metadata and how to update the metadata cache.

Before being able to query data, the connector requires relevant metadata to be retrieved. By default, metadata is cached in memory and shared across connections. But if you want to persist across processes, or if metadata requests are expensive, the solution is to cache the metadata to disk.

Enable Caching Metadata

To enable caching of metadata, set CacheMetadata = true and see Configuring the Cache Connection for instructions on how to configure your connection string. The driver caches the metadata the first time it is needed and uses the metadata cache for subsequent requests.

Update the Metadata Cache

Because metadata is cached, changes to metadata on the live source, for example, adding or removing a column or attribute, are not automatically reflected in the metadata cache. To get updates to the live metadata, you need to delete or drop the cached data.

Cache Metadata from Code

The following code can be used to build the metadata cache. This is especially useful when using the Object-Relational Mapping Framework (for example, Hibernate).

Connection conn = DriverManager.getConnection("jdbc:servicenow:Cache Location=C:\cdata.servicenow.db;AutoCache=True;InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;");
DatabaseMetaData table_meta = conn.getMetaData();

ResultSet rs=table_meta.getTables(null, null, "%", null); 

while (rs.next()) {
  String tableName = rs.getString("TABLE_NAME");
  System.out.println("Cached metadata for table: "+tableName);
  ResultSet cols = table_meta.getColumns(null, null, tableName, null);
  while(rs.next()){
  }
}
System.out.println();
System.out.println("All tables cached.");
conn.close();

Automatically caching data is useful when you do not want to rebuild the cache for each query. When you query data for the first time, the driver automatically initializes and builds a cache in the background. When AutoCache = true, the driver uses the cache for subsequent query executions, resulting in faster response times.

If replication is enabled, the data is generated once and then copied to local and cloud data stores. With incremental updates, the driver achieves a performance advantage over dropping the cached tables and retrieving the entire table again on every refresh. With iterative updates, the driver only performs the query from the last time that the date was refreshed. If replication is not enabled, updates to the cache require downloading the entire data set.

Configuring Automatic Caching

To automatically update the cache and return results from the local cache, set the following connection string properties:

• AutoCache: This property automatically updates the cache when the value is set to true.
• CacheTolerance: This property ensures that the data retrieved from the database is the most current version. The default value is 600 seconds (10 minutes). The driver checks with the data source for newer records after the tolerance interval has expired. Otherwise, it returns the data directly from the cache.

Caching the incident Table

The following example caches the incident table in the file specified by the CacheLocation property of the connection string.

String connectionString = "jdbc:servicenow:Cache Location=C:\cache.db;" +
                          "AutoCache=true;" +
                          "InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;";
Connection connection = DriverManager.getConnection(connectionString);
Statement stat = connection.createStatement();
boolean ret = stat.execute("SELECT sys_id, priority FROM incident WHERE category = 'request'");
ResultSet rs=stat.getResultSet();
while(rs.next()){
  System.out.println("Read and cached the row with sys_id "+rs.getString("sys_id"));
}
connection.close();

Common Use Case

A common use for automatically caching data is to improve driver performance when making repeated requests to a live data source, such as building a report or creating a visualization. With auto caching enabled, repeated requests to the same data may be executed in a short period of time, but within an allowable tolerance (CacheTolerance) of what is considered "live" data.

With explicit caching (AutoCache = false), you decide exactly what data is cached and when to query the cache instead of the live data. Explicit caching gives you full control over the cache contents by using CACHE Statements. This section describes some strategies to use the caching features offered by the driver.

Creating the Cache

To load data in the cache, issue the following statement.

CACHE SELECT * FROM tableName WHERE ...

Once the statement is issued, any matching data in tableName is loaded into the corresponding table.

Updating the Cache

This section describes two ways to update the cache.

Updating with the SELECT Statement

The following example shows a statement that can update modified rows and add missing rows in the cached table. However, this statement does not delete extra rows that are already in the cache. This statement only merges the new rows or updates the existing rows.

String cmd = "CACHE SELECT * FROM incident WHERE category = 'request'", connection";
stat.execute(cmd);
connection.close();

Updating with the TRUNCATE Statement

The following example shows a statement that can update modified rows and add missing rows in the cached table. This statement can also delete rows in the cache table that are not present in the live data source.

String cmd = "CACHE WITH TRUNCATE SELECT * FROM incident WHERE category = 'request'";
stat.execute(cmd);
connection.close();

Query the Data in Online or Offline Mode

This section describes how to query the data in online or offline mode.

Online: Select Cached Tables

You can use the tableName#CACHE syntax to explicitly execute queries to the cache while still online, as shown in the following example.

SELECT * FROM incident#CACHE

Offline: Select Cached Tables

With Offline = true, SELECT statements always execute against the local cache database, regardless of whether you explicitly specify the cached table or not. Modification of the cache is disabled in Offline mode to prevent accidentally updating only the cached data. Executing a DELETE/UPDATE/INSERT statement while in Offline mode results in an exception.

The following example selects from the local cache but not the live data source because Offline = true.

Connection connection = DriverManager.getConnection("jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;Offline=true;Cache Location=C:\cache.db;");
Statement stat = connection.createStatement();
String query = "SELECT * FROM incident WHERE category='request' ORDER BY priority ASC";
stat.execute(query);
connection.close();

Delete Data from the Cache

You can delete data from the cache by building a direct connection to the database. Note that the driver does not support manually deleting data from the cache.

Common Use Case

A common use for caching is to have an application always query the cached data and only update the cache at set intervals, such as once every day or every two hours. There are two ways in which this can be implemented:

• AutoCache = false and Offline = false. All queries issued by the application explicitly reference the tableName#CACHE table. When the cache needs to be updated, the application executes a tableName#CACHE ... statement to bring the cached data up to date.
• Offline = true. Caching is transparent to the application. All queries are executed against the table as normal, so most application code does not need to be aware that caching is done. To update the cached data, simply create a separate connection with Offline = false and execute a tableName#CACHE ... statement.

The driver maps types from the data source to the corresponding data type available in the chosen cache database. The following table shows the mappings between the data types configured in the schema and the data types in the database. Some schema types have synonyms which are all listed in the Schema column.

Data Type Mapping

Note: String columns can map to different data types depending on their length.

Schema	.NET	JDBC	SQL Server	Derby	MySQL	Oracle	SQLite	Access
int, integer, int32	Int32	int	int	INTEGER	INT	NUMBER	integer	LONG
smallint, short, int16	Int16	short	smallint	SMALLINT	SMALLINT	NUMBER	integer	SHORT
double, float, real	Double	double	float	DOUBLE	DOUBLE	NUMBER	double	DOUBLE
date	DateTime	java.sql.Date	date	DATE	DATE	DATE	date	DATETIME
datetime, timestamp	DateTime	java.sql.Date	datetime	TIMESTAMP	DATETIME	TIMESTAMP	datetime	DATETIME
time, timespan	TimeSpan	java.sql.Time	time	TIME	TIME	TIMESTAMP	datetime	DATETIME
string, varchar	String	java.lang.String	If length > 4000: nvarchar(max), Otherwise: nvarchar(length)	If length > 32672: LONG VARCHAR, Otherwise VARCHAR(length)	If length > 255: LONGTEXT, Otherwise: VARCHAR(length)	If length > 4000: CLOB, Otherwise: VARCHAR2(length)	nvarchar(length)	If length > 255: LONGTEXT, Otherwise: VARCHAR(length)
long, int64, bigint	Int64	long	bigint	BIGINT	BIGINT	NUMBER	bigint	LONG
boolean, bool	Boolean	boolean	tinyint	SMALLINT	BIT	NUMBER	tinyint	BIT
decimal, numeric	Decimal	java.math.BigDecimal	decimal	DECIMAL	DECIMAL	DECIMAL	decimal	CURRENCY
uuid	Guid	java.util.UUID	nvarchar(length)	VARCHAR(length)	VARCHAR(length)	VARCHAR2(length)	nvarchar(length)	VARCHAR(length)
binary, varbinary, longvarbinary	byte[]	byte[]	binary(1000) or varbinary(max) after SQL Server 2000, image otherwise	BLOB	LONGBLOB	BLOB	BLOB	LONGBINARY

This section provides a walk-through of writing data access code to ServiceNow in JDBC.

See Data Model for more information on the available API objects and how to query them with SQL. See SQL Compliance for the SQL syntax.

Connecting from Code

See Establishing a Connection for the prerequisite information you need to deploy the driver and configure the connection to ServiceNow. Connecting from Code shows how to connect with the DriverManager or ServiceNowDataSource classes.

Executing SQL

Use the Statement and PreparedStatement classes to execute SQL to ServiceNow:

• See Executing Statements to execute Statements and iterate over the returned ResultSets.
• See Using Prepared Statements to execute parameterized statements. The PreparedStatement class provides a means to efficiently execute queries more than once and to mitigate SQL injection attacks.

Executing Stored Procedures

You can execute stored procedures* as parameterized statements (with the CallableStatement class) or SQL statements (with the EXECUTE syntax): see Calling Stored Procedures.

Connection Pooling

Instantiate pooled connections with ServiceNowDataSource objects: see Connection Pooling to create and configure the pool.

The JDBC Driver for ServiceNow ships the following files, located in the lib subfolder of the installation directory:

• cdata.jdbc.servicenow.jar: Pure Java Type 4/5 JDBC Driver, compiled with JDK 1.6.
• cdata.jdbc.servicenow.lic: This is the license file and must be placed in same folder as the JAR.
• cdata.jdbc.servicenow.remoting.ini: This is the configuration file for JDBC Remoting.

This section describes how to connect with the JDBC DriverManager or ServiceNowDataSource interfaces.

Connecting with the DriverManager

When connecting with the DriverManager class, the JDBC Driver for ServiceNow follows the JDBC convention: First, load the ServiceNow driver class. Then, make a connection.

Load the Driver

The following step is optional per the JDBC 4.0 specification.

Class.forName("cdata.jdbc.servicenow.ServiceNowDriver");

Establish a Connection

Provide the connection string with the getConnection method of the static DriverManager class. Start the connection string with "jdbc:servicenow:". A typical connection string is the following:

Connection conn = DriverManager.getConnection("jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;");

Alternatively, you can prepare the connection options using a Properties object. Pass the Properties object to the DriverManager.

Properties prop = new Properties();
prop.setProperty("InitiateOAuth","GETANDREFRESH");
prop.setProperty("OAuthClientId","MyClientId");
prop.setProperty("OAuthClientSecret","MyClientSecret");
prop.setProperty("User","MyUser");
prop.setProperty("Password","MyPassword");
prop.setProperty("Url","https://MyInstance12345.service-now.com/");

Connection conn = DriverManager.getConnection("jdbc:servicenow:",prop);

Connecting with the ServiceNowDataSource Class

You can use the ServiceNowDataSource class to create pooled connections, as shown in the following example. See Connection Pooling for more information.

The following example instantiates a pooled Connection object:

ServiceNowDataSource ds = new ServiceNowDataSource("cdata.jdbc.servicenow.ServiceNowDriver", "jdbc:servicenow:UseConnectionPooling=true;InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;"); 
Connection conn = ds.getConnection();

After Connecting from Code, you can execute SQL statements with the Statement class. See Using Prepared Statements to execute parameterized statements.

Select

To execute SQL statements that return data, use the Statement class' generic execute method or the executeQuery method. To return the results of a query, call the getResultSet method of the Statement.

The following example calls the execute method and iterates over the results returned:

Statement stat = conn.createStatement();
boolean ret = stat.execute("SELECT sys_id, priority FROM incident");
if (ret) {
  ResultSet rs=stat.getResultSet();
  while(rs.next()) {
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnName(i) +"="+rs.getString(i));
    }
  }
}

Insert

To execute an insert, use the generic execute method or the executeUpdate method of the Statement class. For example:

Statement stat = conn.createStatement();
int count = stat.executeUpdate("INSERT INTO incident (sys_id, priority) VALUES ('sys_id','priority')");

Update

To execute an update, use the generic execute method or the executeUpdate method of the Statement class. You can call the getUpdateCount method to obtain the count of affected rows. Or, call the executeUpdate method; this method returns the row count. For example:

Statement stat = conn.createStatement();
stat.execute("UPDATE incident SET sys_id = 'XXX' , priority = 'YYY' WHERE sys_id = 'S'");
int count = stat.getUpdateCount();

Delete

To execute a delete, use the generic execute method or the executeUpdate method of the Statement class. You can call the getUpdateCount method to obtain the count of affected rows. Or, call the executeUpdate method; this method returns the row count. For example:

Statement stat = conn.createStatement();
stat.execute("DELETE FROM incident WHERE sys_id = 'S'");
int count = stat.getUpdateCount();

The PreparedStatement object represents a precompiled SQL statement. A PreparedStatement can be used multiple times and mitigates SQL injection attacks. A PreparedStatement can be a SELECT, INSERT, UPDATE, or DELETE statement.

To execute a prepared statement, you can use the generic execute method of the Statement class. This section describes how to execute a prepared statement.

1. Instantiate a PreparedStatement object with the prepareStatement method of the Connection class.

   See Connecting from Code to create the connection.

2. Declare parameters by calling the PreparedStatement's corresponding setter method. Note that the parameter indices start from one.
3. Call the PreparedStatement's execute method to execute the statement.
4. Call the PreparedStatement's getResultSet method to pull the results into a ResultSet object.
5. Call ResultSet.next to iterate over the result set. Use the ResultSetMetaData class to obtain column information about the result set. To instantiate a ResultSetMetaData object, call the ResultSet's getMetaData method.

Select

The following example shows how to execute a SELECT prepared statement:

String query = "SELECT * FROM incident WHERE sys_id=? AND priority=?";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, "XXX");
pstmt.setString(2, "YYY");
boolean ret = pstmt.execute();
if (ret) {
  ResultSet rs=pstmt.getResultSet();
  while(rs.next()) {
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnName(i) +"="+rs.getString(i));
    }
  }
} 

Insert

To execute an insert, you can use the generic execute method or the executeUpdate method, as shown in the following example.

String query = "INSERT INTO incident (sys_id, priority) VALUES (?,?)";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, "XXX");
pstmt.setString(2, "YYY");
int count = pstmt.executeUpdate();

Update

To execute an update, you can use the generic execute method or the executeUpdate method, as shown in the following example. The executeUpdate method returns the affected rows. Or, call getUpdateCount.

String query = "UPDATE incident SET priority = ? WHERE sys_id=?";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, "XXX");
pstmt.setString(2, "YYY");
int count = pstmt.executeUpdate();
System.out.println("Affected rows: "+count);

Delete

To execute a delete, you can use the generic execute method or the executeUpdate method, as shown in the following example. The executeUpdate method returns the affected rows. Or, call getUpdateCount.

String query = "DELETE FROM incident WHERE sys_id = ?";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, "XXX");
int count = pstmt.executeUpdate();
System.out.println("Affected rows: "+count);

The driver implements a standard JDBC connection pool. Set UseConnectionPooling to enable the pool. The following sections show how to configure and use them.

Working with Pooled Connections

Just as you would interact with a non-pooled connection, you use standard JDBC objects to get and close connections. But, in this case, the Connection object retrieved is a handle for the physical connection owned by the connection pool. When the connection is closed, instead of the connection being destroyed, the handle is returned to the pool, where it is available for the next connection request.

You must explicitly close the connection for it to be returned to the pool.

Configuring the Connection Pool

In addition to UseConnectionPooling, set the following connection properties to control the connection pool:

• PoolMaxSize: Define the maximum number of connections that can be open at any given time.
• PoolIdleTimeout: Set a limit to how long connections can remain open and idle. If this limit is exceeded, the connection is returned to the pool.
• PoolWaitTime: Set a limit to how long new connection requests should wait for a connection to become available. If this limit is exceeded, the request throws an error. By default, connection requests wait forever for a connection to become available.

Connection Pooling with ServiceNowDataSource

To use the default method for pooling connections, instantiate the ServiceNowDataSource with UseConnectionPooling:

ServiceNowDataSource servicenowDataSource = new ServiceNowDataSource();
servicenowDataSource.setURL("jdbc:servicenow:UseConnectionPooling=true;InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;");

JDBC Connection Pooling

If you would like to establish a pooled connection using the the JDBC ConnectionPoolDataSource interface, instantiate the ServiceNowConnectionPoolDataSource with UseConnectionPooling:

ServiceNowConnectionPoolDataSource servicenowPoolDataSource = new ServiceNowConnectionPoolDataSource();
servicenowPoolDataSource.setURL("jdbc:servicenow:UseConnectionPooling=true;InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;");

Closing the Connection Pool

On JRE 1.3 or higher, the connection pool itself automatically closes when the application stops running. You can manually close the connection pool by invoking the close method of the DataSource object.

Connection Pooling with JNDI

The Java Naming and Directory Service (JNDI) is an API which allows distributed application to look up services. JNDI can be used to easily set up connection pools.

To set up a connection pool using JNDI, you will need to initialize the JNDI File System Service Provider, as shown in the example code below. To run the example, you need to add the fscontext.jar and providerutil.jar files to your classpath. You can download these files from the Oracle Java Archive: Under the Java SE section, select Java Platform Technologies > Java Naming and Directory Interface.

Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY,
    "com.sun.jndi.fscontext.RefFSContextFactory");
env.put(Context.PROVIDER_URL, "file:///tmp");

Context ctx = new InitialContext(env); 
DataSource ds = null;
Connection conn = null;

The following code registers the ServiceNowDataSource with the JNDI naming service, gets an instance of the DataSource from the service, and creates pooled connections from that instance.

try {
  ServiceNowConnectionPoolDataSource servicenowDataSource = new ServiceNowConnectionPoolDataSource();
  servicenowDataSource.setURL("jdbc:servicenow:UseConnectionPooling=true;InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;");
  ctx.bind("jdbc/servicenow", servicenowDataSource);
  ds = (DataSource) ctx.lookup("jdbc/servicenow");

  conn = ds.getConnection();
  Statement stat = conn.createStatement();
  boolean ret = stat.execute("SELECT 1");
  ResultSet rs=stat.getResultSet(); 
} catch(Exception ex) { } finally {
  if(conn != null) conn.close();
}

Use CallableStatement objects to execute parameterized stored procedure* calls. Use Statement objects to execute stored procedures* as SQL statements with the EXEC syntax.

Using Callable Statement Objects

You can use the generic execute method of the CallableStatement class to execute any stored procedure* as a parameterized query.

To return the stored procedure's results, call getResultSet. To return a count of updated rows, call getUpdateCount.

The following example shows how to execute the RefreshOAuthAccessToken stored procedure:

CallableStatement cstmt = conn.prepareCall("RefreshOAuthAccessToken");
cstmt.setString("OAuthAccessToken", "OAuthAccessToken");
boolean ret = cstmt.execute();   
if (!ret) {
  int count=cstmt.getUpdateCount();
  if (count!=-1) {
    System.out.println("Affected rows: "+count);
  }
}
else {
  ResultSet rs=cstmt.getResultSet();
  while(rs.next()){
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnName(i) +"="+rs.getString(i));
    }
  }
}

Using Statement Objects

You can use the execute method of the Statement class to execute any stored procedure* as an SQL statement.

To return the stored procedure's results, call getResultSet. To return a count of updated rows, call getUpdateCount.

The following example shows how to execute the RefreshOAuthAccessToken stored procedure: (See EXECUTE Statements for more on the syntax.)

Statement stmt = conn.createStatement();
boolean ret = stmt.execute("EXEC RefreshOAuthAccessToken OAuthAccessToken = 'OAuthAccessToken'");

if (!ret) {
  int count=stmt.getUpdateCount();
  if (count!=-1) {
    System.out.println("Affected rows: "+count);
  }
}
else {
  ResultSet rs=stmt.getResultSet();
  while(rs.next()) {
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnName(i) +"="+rs.getString(i));
    }
  }
}

The JDBC Driver for ServiceNow provides the standard JDBC connection process in analytics tools and other applications.

JDBC Integration Quickstarts

The JDBC Driver for ServiceNow provides the standard JDBC connection process in applications ranging from business intelligence tools to IDEs. The following sections show how to create and start querying ServiceNow JDBC data sources, walking through data access in JDBC from several popular database tools.

• DbVisualizer
• SQuirreL SQL
• DBeaver
• Tableau

Complete List of ServiceNow Integration Quickstarts

See ServiceNow integration guides for information on connecting from other applications.

This section shows how to establish a connection to data in DbVisualizer, use the table editor to edit and save data, and execute SQL.

Add the JDBC Driver for ServiceNow

Complete the following steps to add the driver .jar file in a driver definition:

1. In DbVisualizer, select Tools > Driver Manager and then select Driver > Create Driver.
2. Enter a name for the driver.
3. Enter the following in the URL Format box:

   jdbc:servicenow:

4. In the Driver JAR Files section, click the folder icon and browse to the driver .jar file, cdata.jdbc.servicenow.jar. By default, this is located in the lib subfolder of the installation directory. Note that the .lic file in this folder must be located in the same folder as the .jar file.
5. In the Driver Class menu, select the ServiceNowDriver class, cdata.jdbc.servicenow.ServiceNowDriver.

Create a Database Connection for ServiceNow

Complete the following steps to select the ServiceNow driver and build the JDBC URL to create the JDBC data source:

1. In the main DbVisualizer window, select Tools > Connection Wizard.

2. Enter an alias for the connection.
3. Select the driver definition you created.

4. In the following fields, enter the JDBC URL and provide authentication.

   See Establishing a Connection for a guide.

5. Database URL: Enter the full JDBC URL. The syntax of the JDBC URL is jdbc:servicenow: followed by the connection properties in a semicolon-separated list of name-value pairs. A typical connection string is below:

   jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;

6. Database UserId: Enter the username for authentication to ServiceNow, if needed.
7. Database Password: Enter the password for authentication to ServiceNow, if needed.

Note: After you successfully create a database connection for ServiceNow you should set the "Database Type" property to "Generic".

Discover Schemas and Query ServiceNow Data

In the main DbVisualizer window, click Connect on the Connection tab for the newly created connection. You can then browse data and execute SQL in the following ways:

• To access the available tables, from the Databases tab, expand the nodes for the connection, database, schema, and table or view.
• To browse through table data and metadata, right-click a table and click Open in New Tab.
• To execute SQL queries, select SQL Commander > New SQL Commander. Select the Database Connection, Database, and Schema from the available menus.

See Data Model for information on querying specific tables. See SQL Compliance for more information on the SQL syntax.

This section describes how to connect to ServiceNow following the standard JDBC connection process in DBeaver: Add the driver JAR, provide the driver class name, and provide any ServiceNow-specific parameters in the JDBC URL.

Add the JDBC Driver for ServiceNow

Complete the following steps to add the driver .jar file:

1. Open the DBeaver application and, in the Database menu, select the Driver Manager option. Click New to open the Create New Driver form.
2. In the Driver Name box, enter a user-friendly name for the driver.

3. To add the .jar file, click Add File on the Libraries tab. Select the cdata.jdbc.servicenow.jar file, located in the lib subfolder of the installation directory.

   Note that the.lic file must be located in the same folder as the .jar file.

4. Click Find Class, and in the list select, "cdata.jdbc.servicenow.ServiceNowDriver".
5. In the URL Template field, enter jdbc:servicenow:.

Create the JDBC Data Source

Complete the following steps to select the ServiceNow driver and build the JDBC URL to create the JDBC data source:

1. In the main DBeaver window, click Database > New Connection.
2. Select the driver definition you created in the dialog that is displayed.
3. On the next page of the wizard, click the Driver Properties tab.
4. Enter any connection properties required to connect to ServiceNow.

   Here is a typical connection string:

   jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;

   See Establishing a Connection for a connection and authentication guide.

5. Finish creating the connection with the default settings or configure advanced network settings -- proxy, firewall, and SSH.

Discover Schemas and Query ServiceNow Data

Complete the following steps to query information from the tables exposed by the connection:

1. Expand the node for the connection to access the database metadata.
2. Browse the table metadata and edit the table data by right-clicking a Table and then clicking Edit Table.

To execute an SQL query, select SQL Editor > New SQL Editor and select the ServiceNow connection you created. You can then enter queries using code completion.

This section describes how to create a JDBC data source for ServiceNow data and execute queries.

Add the JDBC Driver for ServiceNow

Complete the following steps to add the driver .jar file:

1. In the Drivers pane, click the plus icon to open the Add Driver wizard.
2. In the Name box, enter a user-friendly name for the driver; for example, JDBC Driver for ServiceNow.
3. In the Example URL box, enter jdbc:servicenow:
4. In the Extra Class Path tab, click Add.
5. In the file explorer that opens, select the .jar file for the driver, located in the lib subfolder of the installation directory.
6. Click List Drivers to populate the Class Name menu with the class name for the driver, cdata.jdbc.servicenow.ServiceNowDriver.

Create the JDBC Data Source

Complete the following steps to select the ServiceNow driver you created and build the JDBC URL to create the JDBC data source:

1. In the Aliases pane, click the plus icon.
2. In the Add Alias wizard that opens, provide values for the following fields:
   • Name: Enter a name for the alias; for example, ServiceNow Source.
   • Driver: Select the driver definition you created.
   • URL: Enter jdbc:servicenow:
   • User Name: If needed, enter a user name for authentication, which is added to the JDBC URL.
   • Password: If needed, enter a password for authentication, which is added to the JDBC URL.
3. If you want to define any additional properties, add them to the JDBC URL in a semicolon-separated list. The following is a typical connection string:

   jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;

   See Establishing a Connection for a connection and authentication guide.

Or, follow these steps to add the properties in the Driver Properties dialog:

1. Select the Use Driver Properties check box.
2. In the Specify column, select the check boxes for the required connection properties and specify the corresponding values.
3. In the dialog that appears after you click OK, click Connect to test the connection.

Discover Schemas and Query ServiceNow Data

To connect to the data source, right-click the alias on the Aliases pane and then click Connect. After the metadata has loaded, a new tab for the ServiceNow data source is displayed. On the Objects subtab, you can discover schema information, such as the available tables and views.

To view table data and metadata, select the table on the Objects tab. Access the table data on the Content tab.

To execute an SQL query, enter the query on the SQL tab and then click Run SQL (the runner icon).

This section describes how to connect and start querying data from Tableau.

Add the JDBC Driver for ServiceNow

Note: Before starting Tableau on Windows, make sure that you have placed the .jar file in the C:\Program Files\Tableau\Drivers folder. Before starting Tableau on macOS, make sure that you have placed the .jar file in the ~/Library/Tableau/Drivers folder.

To add the driver .jar file:

1. Start Tableau.
2. Under To a Server, select More.
3. Select Other Databases (JDBC).
4. Enter the JDBC connection string in the URL field. See Building the JDBC URL for ServiceNow below for more information.
5. Select Sign in.

Building the JDBC URL for ServiceNow

Connection strings provide information about a data source and how to connect to that data source. The driver comes with a connection string builder that makes it easier to create and manage the contents of connection strings.

After downloading and installing the driver, double-click the .jar file in the lib folder. You can also manually run the .jar file, as shown in the following examples.

From Windows:

java -jar 'C:\Program Files\ JDBC Driver for ServiceNow 2021\lib\cdata.jdbc.servicenow.jar'

From macOS:

java -jar cdata.jdbc.servicenow.jar

Running the .jar file opens the Connection Properties dialog box. You can use this dialog box to build and test a connection string. Click Test Connection to test and validate the entered connection properties. Click Copy to Clipboard to copy the connection string for use within the application where the JDBC driver is being used. See the help documentation for more information about the connection string options.

Discover Schemas and Query Data

To query data:

1. Select from the Database pull-down menu.
2. Select Odata from the Schema pull-down menu.
3. Drag the table onto the join area. You can include multiple tables.
4. Select Update Now or Automatically Update. Update Now lets you preview the first 10,000 rows of the data source (or enter the number of rows you want to see in the Rows text box). Automatically Update automatically reflects the changes in the preview area.
5. In the Connection menu, select the Live option, so that you skip loading a copy of the data into Tableau and instead work on real-time data.
6. Click the tab for your worksheet. Columns are listed as Dimensions and Measures, depending on the data type.

The driver supports schema discovery using JDBC classes or using SQL queries to the available system tables. The JDBC classes enable access to schema information, connection property information, and information on the columns returned.

Through SQL queries to the available System Tables, you can access schema and connection property information as well as information on data source functionality and statistics on update operations.

Using JDBC Interfaces

You can use JDBC interfaces to access schema information, connection property metadata, and result set metadata. The driver implements the standard interfaces as defined in the JDBC 4.0 specification.

Retrieving Schema Information

The DatabaseMetaData class provides information on the following:

• Tables
• Columns
• Procedures
• Procedure Parameters
• Primary Keys
• Foreign Keys

Retrieving Connection Property Information

The Driver class, returned by DriverManager, provides information about Connection Properties.

Retrieving Result Set Column Information

The ResultSetMetaData class provides information about the columns returned in Result Sets.

Using SQL

You can query the System Tables to access any metadata surfaced through the driver.

You can use the getTables method of the DatabaseMetaData interface to retrieve a list of tables:

String connectionString = "jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;";

Connection conn = DriverManager.getConnection(connectionString);
DatabaseMetaData table_meta = conn.getMetaData();
ResultSet rs=table_meta.getTables(null, null, "%", null);  
while(rs.next()){
  System.out.println(rs.getString("TABLE_NAME"));
}

The getTables method returns the following columns:

Column Name	Data Type	Description
TABLE_CAT	String	The table catalog.
TABLE_SCHEM	String	The table schema.
TABLE_NAME	String	The table name.
TABLE_TYPE	String	The table type.
REMARKS	String	The table description.

You can use the getColumns method of the DatabaseMetaData interface to retrieve column information. You can restrict the results by the table name. The code example below retrieves the column names for the incident table:

String connectionString = "jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;";

Connection conn = DriverManager.getConnection(connectionString);
DatabaseMetaData table_meta = conn.getMetaData();
ResultSet rs = table_meta.getColumns(null,null,"incident", null);
while(rs.next()){
  System.out.println(rs.getString("COLUMN_NAME")); 
}

The getColumns method returns the following columns:

Column Name	Data Type	Description
TABLE_CAT	String	The database name.
TABLE_SCHEM	String	The table schema.
TABLE_NAME	String	The table name.
COLUMN_NAME	String	The column name.
DATA_TYPE	int	The data type identified by the value of a constant defined in java.sql.Types.
TYPE_NAME	String	The data type name used by the driver.
COLUMN_SIZE	int	The length in characters of the column or the numeric precision.
BUFFER_LENGTH	int	The buffer length.
DECIMAL_DIGITS	int	The column scale or number of digits to the right of the decimal point.
NUM_PREC_RADIX	int	The radix, or base.
NULLABLE	int	Whether the column can contain null as defined by the following JDBC DatabaseMetaData constants: columnNoNulls (0) or columnNullable (1).
REMARKS	String	The column description.
COLUMN_DEF	String	The default value for the column.
SQL_DATA_TYPE	int	Reserved by the specification.
SQL_DATETIME_SUB	int	Reserved by the specification.
CHAR_OCTET_LENGTH	int	The maximum length of binary and character-based columns.
ORDINAL_POSITION	int	The column index, starting at 1.
IS_NULLABLE	String	Whether a null value is allowed: YES or NO.
SCOPE_CATALOG	String	The table catalog that is the scope of a reference attribute.
SCOPE_SCHEMA	String	The table schema that is the scope of a reference attribute.
SCOPE_TABLE	String	The table name that is the scope of a reference attribute.
SOURCE_DATA_TYPE	int	The source type of a distinct type. Or, a user-generated Ref type. If DATA_TYPE is not DISTINCT, this value is null. If a user-generated Ref, this value is null.
IS_AUTOINCREMENT	String	Whether the column value is assigned by ServiceNow in fixed increments.
IS_GENERATEDCOLUMN	String	Whether the column is generated: YES or NO.

You can use the DatabaseMetaData interface to retrieve stored procedure* information. The getProcedures method returns descriptions of the available stored procedures.

The following code retrieves the names of the available stored procedures:

String connectionString = "jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;";

Connection conn = DriverManager.getConnection(connectionString);
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs = meta.getProcedures(null, null, "%");
while(rs.next()){
  System.out.println(rs.getString("PROCEDURE_NAME"));
}

The getProcedures method returns the following columns:

Column Name	Data Type	Description
PROCEDURE_CAT	String	The catalog the procedure belongs to.
PROCEDURE_SCHEM	String	The schema the procedure belongs to.
PROCEDURE_NAME	String	The stored procedure* name.
REMARKS	String	The description of the stored procedure.
PROCEDURE_TYPE	short	Returns 2 if the procedure returns a result. Returns 1 if the procedure does not return a result. Returns 0 if unknown.
SPECIFIC_NAME	String	The name that uniquely identifies the stored procedure* within its schema.

You can use the DatabaseMetaData interface to retrieve stored procedure* information. The getProcedureColumns method returns descriptions of stored procedure* parameters. You can restrict the results by the stored procedure* name.

The following code example outputs information about the parameters of the RefreshOAuthAccessToken stored procedure:

String connectionString = "jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;";

Connection conn = DriverManager.getConnection(connectionString);
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs=meta.getProcedureColumns(null, null, "RefreshOAuthAccessToken", null);  
while(rs.next()) {   
  for(int i=1;i<=rs.getMetaData().getColumnCount();i++)  {
    System.out.println(rs.getMetaData().getColumnName(i) +"="+rs.getString(i));
  }
}

The getProcedureColumns method returns the following columns:

Column Name	Data Type	Description
PROCEDURE_CAT	String	The catalog that the procedure belongs to.
PROCEDURE_SCHEM	String	The schema that the procedure belongs to.
PROCEDURE_NAME	String	The name of the stored procedure.
COLUMN_NAME	String	The name of the procedure column.
COLUMN_TYPE	String	The type of procedure column as defined by the following DatabaseMetaData constants: procedureColumnIn (1), procedureColumnInOut (2), procedureColumnResult (3), procedureColumnOut (4), and procedureColumnReturn (5).
DATA_TYPE	int	The data type name as defined in java.sql.Types.
TYPE_NAME	String	The driver-defined data type name.
PRECISION	int	The number of digits allowed for numeric data.
LENGTH	int	The number of characters allowed for character data. The number of digits allowed for numeric data.
SCALE	short	The number of digits to the right of the decimal point in numeric data.
RADIX	short	The radix, or base.
NULLABLE	short	Whether the parameter can contain null as defined by the following DatabaseMetaData constants: parameterNoNulls (0), parameterNullable (1), and parameterNullableUnknown (2).
REMARKS	String	The description of the parameter.
COLUMN_DEF	String	The default value for the parameter.
SQL_DATA_TYPE	int	Reserved in the specification.
SQL_DATETIME_SUB	int	Reserved in the specification.
CHAR_OCTET_LENGTH	int	The maximum length of binary-based and character-based columns. Null for other data types.
ORDINAL_POSITION	int	The index of the output parameter.
IS_NULLABLE	String	Whether the column can include null: YES or NO.
SPECIFIC_NAME	String	The name that uniquely identifies the stored procedure* within its schema.

You can use the getPrimaryKeys method to return information about the primary keys for ServiceNow tables. You can restrict the results by the table name.

The following code example outputs the column or columns composing the primary key for the incident table:

String connectionString = "jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;";

Connection conn = DriverManager.getConnection(connectionString);
ResultSet rs = conn.getMetaData().getPrimaryKeys(null,null,"incident");
while(rs.next()){
  System.out.println(rs.getString("COLUMN_NAME"));
}

The getPrimaryKeys method returns the following columns:

Column Name	Data Type	Description
TABLE_CAT	String	The table catalog
TABLE_SCHEM	String	The table schema.
TABLE_NAME	String	The table name.
COLUMN_NAME	String	The column name.
KEY_SEQ	short	The sequence number, or column index starting from 1, within the foreign key.
PK_NAME	String	The primary key name.

You can obtain foreign key information by executing the getExportedKeys or getImportedKeys methods:

• Call the getExportedKeys method to retrieve information about all the foreign keys that the specified table exports.

  The following example outputs each foreign key that references the incident primary key, as well as the table that contains the associated foreign key:

  String connectionString = "jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;";
  
  Connection conn = DriverManager.getConnection(connectionString);
  ResultSet rs = conn.getMetaData().getExportedKeys(null,null,"incident");
  while(rs.next()){
    System.out.println(rs.getString("FKCOLUMN_NAME")+": "+rs.getString("FKTABLE_NAME"));
  }

• Call the getImportedKeys method to return information about all the primary keys that the specified table imports.

  The following example outputs each foreign key column that is imported by the incident table, as well as the table that contains the associated primary key:

  String connectionString = "jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;";
  
  Connection conn = DriverManager.getConnection(connectionString);
  ResultSet rs = conn.getMetaData().getImportedKeys(null,null,"incident");
  while(rs.next()){
    System.out.println(rs.getString("FKCOLUMN_NAME")+": "+rs.getString("PKTABLE_NAME"));
  }

Both the getExportedKeys and getImportedKeys methods return the following columns:

Column Name	Data Type	Description
PK_TABLE_CAT	String	The catalog of the table that contains the primary key.
PKTABLE_SCHEM	String	The schema of the table that contains the primary key.
PKTABLE_NAME	String	The name of the table that contains the primary key.
PKCOLUMN_NAME	String	The column name of the primary key.
FKTABLE_CAT	String	The catalog of the table that contains the foreign key.
FKTABLE_SCHEM	String	The schema of the table that contains the foreign key.
FKTABLE_NAME	String	The name of the table that contains the foreign key.
FKCOLUMN_NAME	String	The column name of the foreign key.
KEY_SEQ	short	The sequence number, or column index starting from 1, within the foreign key.
UPDATE_RULE	short	The rule specifying the action when the primary key is updated.
DELETE_RULE	short	The rule specifying the action when the primary key is deleted.
FK_NAME	String	The foreign key name or null.
PK_NAME	String	The primary key name.
DEFERRABILITY	short	Whether the evaluation of foreign key constraints can be deferred until commit.

The following table shows the rules for the UPDATE_RULE and DELETE_RULE columns. The rules are specified with the following constants defined in the DatabaseMetaData class:

Name	Value	Description
importedKeyNoAction	3	Do not update the primary key if imported.
importedKeyCascade	0	Update the imported key to match the primary key.
importedKeySetNull	2	Change the imported key to "NULL" if the primary key has been updated.
importedKeySetDefault	4	Change the imported key to default values if the primary key has been updated.
importedKeyRestrict	1	This rule is the same as importedNoAction (provides backwards compatibility with ODBC 2.x)

The following table shows the possible values for the DEFERRABILITY column. These values are specified with the following constants defined in the DatabaseMetaData class:

Name	Value	Description
importedKeyInitiallyDeferred	5	Indicates whether the foreign key is initially deferred.
importedKeyInitiallyImmediate	6	Indicates whether the foreign key is initially immediate.
importedKeyNotDeferrable.	7	Indicates whether the foreign key is not deferrable.

See SQL-92 for more information on the preceding values.

The available connection properties can be retrieved with the getPropertyInfo method of the Driver class. This method returns an array with elements of type DriverPropertyInfo.

String connectionString = "jdbc:servicenow:";

Driver driver = DriverManager.getDriver(connectionString);
Properties info = new Properties();
DriverPropertyInfo[] attr = driver.getPropertyInfo(connectionString,info);
for(int i=0;i<attr.length;i++){
  System.out.println(attr[i].name);
  System.out.println(attr[i].description);
  System.out.println(attr[i].required);
  System.out.println(attr[i].value);
  String[] c = attr[i].choices;
  if(c != null) {
    for(String s: c)
      System.out.println(s);
  }
}

The DriverPropertyInfo class has the following properties:

Property Name	Data Type	Description
Name	String	The name of the connection property.
Description	String	The description for the connection property.
Required	boolean	Whether the connection property must be set to connect to ServiceNow.
Choices	String[]	An array of the allowed values for the connection property.
Value	String	The current value of the connection property or the default value if one is not set by the user.

You can use ResultSetMetaData to retrieve metadata about the results of a query.

The query can contain any of the following:

• Joins
• Aggregates
• Aliases
• Fully qualified names
• Generated columns

You can instantiate a ResultSetMetaData object by invoking the getMetaData method of the Statement class. A ResultSetMetaData instance is populated with data after the statement has been executed. The following query prints out the columns in the result of the query:

String connectionString = "jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;";

Connection conn = DriverManager.getConnection(connectionString);
PreparedStatement pstmt = conn.prepareStatement("SELECT sys_id, priority AS My_priority, GETDATE() FROM incident WHERE category = 'request'");
pstmt.executeQuery();
ResultSetMetaData rs = pstmt.getMetaData();
for(int i=1;i<=rs.getColumnCount();i++) {
  System.out.println(rs.getColumnName(i));
}

The JDBC Driver for ServiceNow supports several operations on data, including querying, deleting, modifying, and inserting.

SELECT Statements

See SELECT Statements for a syntax reference and examples.

See Data Model for information on the capabilities of the ServiceNow API.

INSERT Statements

See INSERT Statements for a syntax reference and examples.

UPDATE Statements

The primary key sys_id is required to update a record. See UPDATE Statements for a syntax reference and examples.

DELETE Statements

The primary key sys_id is required to delete a record. See DELETE Statements for a syntax reference and examples.

GETDELETED Statements

GETDELETED statements return the Ids of deleted records. See GETDELETED Statements for a syntax reference and examples.

CACHE Statements

CACHE statements allow granular control over the driver's caching functionality. For a syntax reference and examples, see CACHE Statements.

For more information on the caching feature, see Caching Data.

REPLICATE Statements

REPLICATE statements allow high-level control over caching and incremental updates. For a syntax reference and examples, see REPLICATE Statements.

You can also incrementally update a cache automatically; see AutoCache for more information.

EXECUTE Statements

Use EXECUTE or EXEC statements to execute stored procedures. See EXECUTE Statements for a syntax reference and examples.

Names and Quoting

• Table and column names are considered identifier names; as such, they are restricted to the following characters: [A-Z, a-z, 0-9, _:@].
• To use a table or column name with characters not listed above, the name must be quoted using square brackets ([name]) in any SQL statement.
• Parameter names can optionally start with the @ symbol (e.g., @p1 or @CustomerName) and cannot be quoted.
• Strings must be quoted using single quotes (e.g., 'John Doe').

Transactions and Batching

Transactions are not currently supported.

Additionally, the driver does not support batching of SQL statements. To execute multiple commands, you can create multiple instances and execute each separately.

A SELECT statement can consist of the following basic clauses.

• SELECT
• INTO
• FROM
• JOIN
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY
• LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the driver:

SELECT {
  [ TOP <numeric_literal> | DISTINCT ]
  { 
    * 
    | { 
        <expression> [ [ AS ] <column_reference> ] 
        | { <table_name> | <correlation_name> } .* 
      } [ , ... ] 
  }
  [ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
  { 
    FROM <table_reference> [ [ AS ] <identifier> ] 
  } [ , ... ]
  [ [  
      INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } 
    ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ] 
  ] [ ... ] 
  [ WHERE <search_condition> ]
  [ GROUP BY <column_reference> [ , ... ]
  [ HAVING <search_condition> ]
  [ UNION [ ALL ] <select_statement> ]
  [ 
    ORDER BY 
    <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
  ]
  [ 
    LIMIT <expression>
    [ 
      { OFFSET | , }
      <expression> 
    ]
  ] 
} | SCOPE_IDENTITY() 

<expression> ::=
  | <column_reference>
  | @ <parameter> 
  | ?
  | COUNT( * | { [ DISTINCT ] <expression> } )
  | { AVG | MAX | MIN | SUM | COUNT } ( <expression> ) 
  | NULLIF ( <expression> , <expression> ) 
  | COALESCE ( <expression> , ... ) 
  | CASE <expression>
      WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
    [ ELSE { <expression> | NULL } ]
    END 
  | <literal>
  | <sql_function> 

<search_condition> ::= 
  {
    <expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
} [ { AND | OR } ... ] 

Examples

1. Return all columns:

   SELECT * FROM incident

2. Rename a column:

   SELECT [priority] AS MY_priority FROM incident

3. Cast a column's data as a different data type:

   SELECT CAST(priority AS VARCHAR) AS Str_priority FROM incident

4. Search data:

   SELECT * FROM incident WHERE category = 'request';

5. Return the number of items matching the query criteria:

   SELECT COUNT(*) AS MyCount FROM incident 

6. Return the number of unique items matching the query criteria:

   SELECT COUNT(DISTINCT priority) FROM incident 

7. Return the unique items matching the query criteria:

   SELECT DISTINCT priority FROM incident 

8. Summarize data:

   SELECT priority, MAX(priority) FROM incident GROUP BY priority

   See Aggregate Functions for details.
9. Retrieve data from multiple tables.

   SELECT incident.close_notes, system_user.name FROM incident INNER JOIN system_user ON incident.caller_id = system_user.system_id

   See JOIN Queries for details.
10. Sort a result set in ascending order:

    SELECT sys_id, priority FROM incident  ORDER BY priority ASC

11. Restrict a result set to the specified number of rows:

    SELECT sys_id, priority FROM incident LIMIT 10 

12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.

    SELECT * FROM incident WHERE category = @param

See Explicitly Caching Data for information on using the SELECT statement in offline mode.

Pseudo Columns

Some input-only fields are available in SELECT statements. These fields, called pseudo columns, do not appear as regular columns in the results, yet may be specified as part of the WHERE clause. You can use pseudo columns to access additional features from ServiceNow.

SELECT * FROM incident WHERE Pseudo = '@Pseudo'

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM incident WHERE category = 'request'

COUNT(DISTINCT)

Returns the number of distinct, non-null field values matching the query criteria.

SELECT COUNT(DISTINCT sys_id) AS DistinctValues FROM incident WHERE category = 'request'

AVG

Returns the average of the column values.

SELECT priority, AVG(priority) FROM incident WHERE category = 'request'  GROUP BY priority

MIN

Returns the minimum column value.

SELECT MIN(priority), priority FROM incident WHERE category = 'request' GROUP BY priority

MAX

Returns the maximum column value.

SELECT priority, MAX(priority) FROM incident WHERE category = 'request' GROUP BY priority

SUM

Returns the total sum of the column values.

SELECT SUM(priority) FROM incident WHERE category = 'request'

The JDBC Driver for ServiceNow supports standard SQL joins like the following examples.

Inner Join

An inner join selects only rows from both tables that match the join condition:

SELECT incident.close_notes, system_user.name FROM incident INNER JOIN system_user ON incident.caller_id = system_user.system_id

Left Join

A left join selects all rows in the FROM table and only matching rows in the JOIN table:

SELECT incident.close_notes, system_user.name FROM incident LEFT JOIN system_user ON incident.caller_id = system_user.system_id

The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.

L_TODAY()

The current day.

SELECT * FROM MyTable WHERE MyDateField = L_TODAY()

L_YESTERDAY()

The previous day.

SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()

L_TOMORROW()

The following day.

SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()

L_LAST_WEEK()

Every day in the preceding week.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()

L_THIS_WEEK()

Every day in the current week.

SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()

L_NEXT_WEEK()

Every day in the following week.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()

Also available:

• L_LAST/L_THIS/L_NEXT MONTH
• L_LAST/L_THIS/L_NEXT QUARTER
• L_LAST/L_THIS/L_NEXT YEAR

L_LAST_N_DAYS(n)

The previous n days, excluding the current day.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)

L_NEXT_N_DAYS(n)

The following n days, including the current day.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)

Also available:

• L_LAST/L_NEXT_90_DAYS

L_LAST_N_WEEKS(n)

Every day in every week, starting n weeks before current week, and ending in the previous week.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)

L_NEXT_N_WEEKS(n)

Every day in every week, starting the following week, and ending n weeks in the future.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)

Also available:

• L_LAST/L_NEXT_N_MONTHS(n)
• L_LAST/L_NEXT_N_QUARTERS(n)
• L_LAST/L_NEXT_N_YEARS(n)

You can use the SELECT INTO statement to export formatted data to a file.

Data Export with an SQL Query

The following query exports data into a file formatted in comma-separated values (CSV):

boolean ret = stat.execute("SELECT sys_id, priority INTO [csv://c:/incident.txt] FROM [incident] WHERE category = 'request'");
System.out.println(stat.getUpdateCount()+" rows affected");

You can specify other file formats in the URI. The following example exports tab-separated values:

Statement stat = conn.createStatement();
boolean ret = stat.execute("SELECT * INTO [incident] IN 'csv://filename=c:/incident.csv;delimiter=tab' FROM [incident] WHERE category = 'request'");
System.out.println(stat.getUpdateCount()+" rows affected");

The driver provides functions that are similar to those that are available with most standard databases. These functions are implemented in the provider engine and thus are available across all data sources with the same consistent API. Three categories of functions are available: string, date, and math.

The driver interprets all SQL function inputs as either strings or column identifiers, so you need to escape all literals as strings, with single quotes. For example, contrast the SQL Server syntax and driver syntax for the DATENAME function:

• SQL Server:

  SELECT DATENAME(yy,GETDATE())

• driver:

  SELECT DATENAME('yy',GETDATE())

String Functions

These functions perform string manipulations and return a string value. See STRING Functions for more details.

SELECT CONCAT(firstname, space(4), lastname) FROM incident WHERE category = 'request'

Date Functions

These functions perform date and date time manipulations. See DATE Functions for more details.

SELECT CURRENT_TIMESTAMP() FROM incident

Math Functions

These functions provide mathematical operations. See MATH Functions for more details.

SELECT RAND() FROM incident

Function Parameters and Nesting SQL Functions

The driver supports column names, constants, and results of other functions as parameters to functions. The following are all valid uses of SQL functions:

SELECT CONCAT('Mr.', SPACE(2), firstname, SPACE(4), lastname) FROM incident

ASCII(character_expression)

Returns the ASCII code value of the left-most character of the character expression.

• character_expression: The character expression.

SELECT ASCII('0');
                      --  Result: 48

CHAR(integer_expression)

Converts the integer ASCII code to the corresponding character.

• integer_expression: The integer from 0 through 255.

SELECT CHAR(48);
                      -- Result: '0'

CHARINDEX(expressionToFind ,expressionToSearch [,start_location ])

Returns the starting position of the specified expression in the character string.

• expressionToFind: The character expression to find.
• expressionToSearch: The character expression, typically a column, to search.
• start_location: The optional character position to start searching for expressionToFind in expressionToSearch.

SELECT CHARINDEX('456', '0123456');
                      -- Result: 4

                      SELECT CHARINDEX('456', '0123456', 5);
                      -- Result: -1

CHAR_LENGTH(character_expression),

Returns the number of UTF-8 characters present in the expression.

• character_expression: The set of characters to be be evaluated for length.

SELECT CHAR_LENGTH('sample text') FROM Account LIMIT 1
				 -- Result: 11			

CONCAT(string_value1, string_value2 [, string_valueN])

Returns the string that is the concatenation of two or more string values.

• string_value1: The first string to be concatenated.
• string_value2: The second string to be concatenated.
• *: The optional additional strings to be concatenated.

SELECT CONCAT('Hello, ', 'world!');
                      -- Result: 'Hello, world!'

CONTAINS(expressionToSearch, expressionToFind)

Returns 1 if expressionToFind is found within expressionToSearch; otherwise, 0.

• expressionToSearch: The character expression, typically a column, to search.
• expressionToFind: The character expression to find.

SELECT CONTAINS('0123456', '456');
                      -- Result: 1

                      SELECT CONTAINS('0123456', 'Not a number');
                      -- Result: 0

ENDSWITH(character_expression, character_suffix)

Returns 1 if character_expression ends with character_suffix; otherwise, 0.

• character_expression: The character expression.
• character_suffix: The character suffix to search for.

SELECT ENDSWITH('0123456', '456');
                      -- Result: 1

                      SELECT ENDSWITH('0123456', '012');
                      -- Result: 0

FILESIZE(uri)

Returns the number of bytes present in the file at the specified file path.

• uri: The path of the file to read the size from.

SELECT FILESIZE('C:/Users/User1/Desktop/myfile.txt');
				-- Result: 23684

FORMAT(value [, parseFormat], format )

Returns the value formatted with the specified format.

• value: The string to format.
• format: The string specifying the output syntax of the date or numeric format.
• parseFormat: The string specifying the input syntax of the date value. Not applicable to numeric types.

SELECT FORMAT(12.34, '#');
                      -- Result: 12

                      SELECT FORMAT(12.34, '#.###');
                      -- Result: 12.34

                      SELECT FORMAT(1234, '0.000E0');
                      -- Result: 1.234E3
                      
                      SELECT FORMAT('2019/01/01', 'yyyy-MM-dd');
                      -- Result: 2019-01-01
                      
                      SELECT FORMAT('20190101', 'yyyyMMdd', 'yyyy-MM-dd');
                      -- Result: '2019-01-01'

FROM_UNIXTIME(time, issecond)

Returns a representation of the unix_timestamp argument as a value in YYYY-MM-DD HH:MM:SS expressed in the current time zone.

• time: The time stamp value from epoch time. Milliseconds are accepted.
• issecond: Indicates the time stamp value is milliseconds to epoch time.

SELECT FROM_UNIXTIME(1540495231, 1);
                      -- Result: 2018-10-25 19:20:31

                      SELECT FROM_UNIXTIME(1540495357385, 0);
                      -- Result: 2018-10-25 19:22:37

HASHBYTES(algorithm, value)

Returns the hash of the input value as a byte array using the given algorithm. The supported algorithms are MD5, SHA1, SHA2_256, SHA2_512, SHA3_224, SHA3_256, SHA3_384, and SHA3_512.

• algorithm: The algorithm to use for hashing. Must be one of MD5, SHA1, SHA2_256, SHA2_512, SHA3_224, SHA3_256, SHA3_384, or SHA3_512.
• value: The value to hash. Must be either a string or byte array.

SELECT HASHBYTES('MD5', 'Test');
                      -- Result (byte array): 0x0CBC6611F5540BD0809A388DC95A615B

INDEXOF(expressionToSearch, expressionToFind [,start_location ])

Returns the starting position of the specified expression in the character string.

• expressionToSearch: The character expression, typically a column, to search.
• expressionToFind: The character expression to find.
• start_location: The optional character position to start searching for expressionToFind in expressionToSearch.

SELECT INDEXOF('0123456', '456');
                      -- Result: 4

                      SELECT INDEXOF('0123456', '456', 5);
                      -- Result: -1

ISNULL ( check_expression , replacement_value )

Replaces null with the specified replacement value.

• check_expression: The expression to be checked for null.
• replacement_value: The expression to be returned if check_expression is null.

SELECT ISNULL(42, 'Was NULL');
                      -- Result: 42

                      SELECT ISNULL(NULL, 'Was NULL');
                      -- Result: 'Was NULL'

JSON_AVG(json, jsonpath)

Computes the average value of a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

SELECT JSON_AVG('[1,2,3,4,5]', '$[x]');
                      -- Result: 3

                      SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 3

                      SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
                      -- Result: 4.5

JSON_COUNT(json, jsonpath)

Returns the number of elements in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

SELECT JSON_COUNT('[1,2,3,4,5]', '$[x]');
                      -- Result: 5

                      SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 5

                      SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
                      -- Result: 2

JSON_EXTRACT(json, jsonpath)

Selects any value in a JSON array or object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to extract.
• jsonpath: The XPath used to select the nodes. The JSONPath must be a string constant. The values of the nodes selected will be returned in a token-separated list.

SELECT JSON_EXTRACT('{"test": {"data": 1}}', '$.test');
                      -- Result: '{"data":1}'

                      SELECT JSON_EXTRACT('{"test": {"data": 1}}', '$.test.data');
                      -- Result: 1

                      SELECT JSON_EXTRACT('{"test": {"data": [1, 2, 3]}}', '$.test.data[1]');
                      -- Result: 2

JSON_MAX(json, jsonpath)

Gets the maximum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

SELECT JSON_MAX('[1,2,3,4,5]', '$[x]');
                      -- Result: 5

                      SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 5

                      SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[..3]');
                      -- Result: 4

JSON_MIN(json, jsonpath)

Gets the minimum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

SELECT JSON_MIN('[1,2,3,4,5]', '$[x]');
                      -- Result: 1

                      SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 1

                      SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
                      -- Result: 4

JSON_SUM(json, jsonpath)

Computes the summary value in JSON according to the JSONPath expression. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

SELECT JSON_SUM('[1,2,3,4,5]', '$[x]');
                      -- Result: 15

                      SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
                      -- Result: 15

                      SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
                      -- Result: 9

LEFT ( character_expression , integer_expression )

Returns the specified number of characters counting from the left of the specified string.

• character_expression: The character expression.
• integer_expression: The positive integer that specifies how many characters will be returned counting from the left of character_expression.

SELECT LEFT('1234567890', 3);
                      -- Result: '123'

LEN(string_expression)

Returns the number of characters of the specified string expression.

• string_expression: The string expression.

SELECT LEN('12345');
                      -- Result: 5

LOCATE(substring,string)

Returns an integer representing how many characters into the string the substring appears.

• substring: The substring to find inside larger string.
• string: The larger string that will be searched for the substring.

SELECT LOCATE('sample','XXXXXsampleXXXXX');
				-- Result: 6

LOWER ( character_expression )

Returns the character expression with the uppercase character data converted to lowercase.

• character_expression: The character expression.

SELECT LOWER('MIXED case');
                      -- Result: 'mixed case'

LTRIM(character_expression)

Returns the character expression with leading blanks removed.

• character_expression: The character expression.

SELECT LTRIM('     trimmed');
                      -- Result: 'trimmed'

MASK(string_expression, mask_character [, start_index [, end_index ]])

Replaces the characters between start_index and end_index with the mask_character within the string.

• string_expression: The string expression to be searched.
• mask_character: The character to mask with.
• start_index: The optional number of characters to leave unmasked at beginning of string. Defaults to 0.
• end_index: The optional number of characters to leave unmasked at end of string. Defaults to 0.

SELECT MASK('1234567890','*',);
                        -- Result: '**********'
                        SELECT MASK('1234567890','*', 4);
                        -- Result: '1234******'
                        SELECT MASK('1234567890','*', 4, 2);
                        -- Result: '1234****90'  

NCHAR(integer_expression)

Returns the Unicode character with the specified integer code as defined by the Unicode standard.

• integer_expression: The integer from 0 through 255.

OCTET_LENGTH(character_expression),

Returns the number of bytes present in the expression.

• character_expression: The set of characters to be be evaluated.

SELECT OCTET_LENGTH('text') FROM Account LIMIT 1
				 -- Result: 4

PATINDEX(pattern, expression)

Returns the starting position of the first occurrence of the pattern in the expression. Returns 0 if the pattern is not found.

• pattern: The character expression that contains the sequence to be found. The wild-card character % can be used only at the start or end of the expression.
• expression: The expression, typically a column, to search for the pattern.

SELECT PATINDEX('123%', '1234567890');
                      -- Result: 1

                      SELECT PATINDEX('%890', '1234567890');
                      -- Result: 8

                      SELECT PATINDEX('%456%', '1234567890');
                      -- Result: 4

POSITION(expressionToFind IN expressionToSearch)

Returns the starting position of the specified expression in the character string.

• expressionToFind: The character expression to find.
• expressionToSearch: The character expression, typically a column, to search.

SELECT POSITION('456' IN '123456');
                      -- Result: 4

                      SELECT POSITION('x' IN '123456');
                      -- Result: 0

QUOTENAME(character_string [, quote_character])

Returns a valid SQL Server-delimited identifier by adding the necessary delimiters to the specified Unicode string.

• character_string: The string of Unicode character data. The string is limited to 128 characters. Inputs greater than 128 characters return null.
• quote_character: The optional single character to be used as the delimiter. Can be a single quotation mark, a left or right bracket, or a double quotation mark. If quote_character is not specified brackets are used.

SELECT QUOTENAME('table_name');
                      -- Result: '[table_name]'

                      SELECT QUOTENAME('table_name', '"');
                      -- Result: '"table_name"'

                      SELECT QUOTENAME('table_name', '[');
                      -- Result: '[table_name]'

REPLACE(string_expression, string_pattern, string_replacement)

Replaces all occurrences of a string with another string.

• string_expression: The string expression to be searched. Can be a character or binary data type.
• string_pattern: The substring to be found. Cannot be an empty string.
• string_replacement: The replacement string.

SELECT REPLACE('1234567890', '456', '|');
                      -- Result: '123|7890'

                      SELECT REPLACE('123123123', '123', '.');
                      -- Result: '...'

                      SELECT REPLACE('1234567890', 'a', 'b');
                      -- Result: '1234567890'

REPLICATE ( string_expression ,integer_expression )

Repeats the string value the specified number of times.

• string_expression: The string to replicate.
• integer_expression: The repeat count.

SELECT REPLACE('x', 5);
                      -- Result: 'xxxxx'

REVERSE ( string_expression )

Returns the reverse order of the string expression.

• string_expression: The string.

SELECT REVERSE('1234567890');
                      -- Result: '0987654321'

RIGHT ( character_expression , integer_expression )

Returns the right part of the string with the specified number of characters.

• character_expression: The character expression.
• integer_expression: The positive integer that specifies how many characters of the character expression will be returned.

SELECT RIGHT('1234567890', 3);
                      -- Result: '890'

RTRIM(character_expression)

Returns the character expression after it removes trailing blanks.

• character_expression: The character expression.

SELECT RTRIM('trimmed     ');
                      -- Result: 'trimmed'

SOUNDEX(character_expression)

Returns the four-character Soundex code, based on how the string sounds when spoken.

• character_expression: The alphanumeric expression of character data.

SELECT SOUNDEX('smith');
                      -- Result: 'S530'

SPACE(repeatcount)

Returns the string that consists of repeated spaces.

• repeatcount: The number of spaces.

SELECT SPACE(5);
                      -- Result: '     '

SPLIT(string, delimiter, offset)

Returns a section of the string between to delimiters.

• string: The string to split.
• delimiter: The character to split the string with.
• offset: The number of the split to return. Positive numbers are treated as offsets from the left, and negative numbers are treated as offsets from the right.

SELECT SPLIT('a/b/c/d', '/', 1);
                      -- Result: 'a'
                      SELECT SPLIT('a/b/c/d', '/', -2);
                      -- Result: 'c'

STARTSWITH(character_expression, character_prefix)

Returns 1 if character_expression starts with character_prefix; otherwise, 0.

• character_expression: The character expression.
• character_prefix: The character prefix to search for.

SELECT STARTSWITH('0123456', '012');
                      -- Result: 1

                      SELECT STARTSWITH('0123456', '456');
                      -- Result: 0

STR ( float_expression [ , integer_length [ , integer_decimal ] ] )

Returns the character data converted from the numeric data. For example, STR(123.45, 6, 1) returns 123.5.

• float_expression: The float expression.
• length: The optional total length to return. This includes decimal point, sign, digits, and spaces. The default is 10.
• decimal: The optional number of places to the right of the decimal point. The decimal must be less than or equal to 16.

SELECT STR('123.456');
                      -- Result: '123'

                      SELECT STR('123.456', 2);
                      -- Result: '**'

                      SELECT STR('123.456', 10, 2);
                      -- Result: '123.46'

STUFF(character_expression , integer_start , integer_length , replaceWith_expression)

Inserts a string into another string. It deletes the specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

• character_expression: The string expression.
• start: The integer value that specifies the location to start deletion and insertion. If start or length is negative, null is returned. If start is longer than the string to be modified, character_expression, null is returned.
• length: The integer that specifies the number of characters to delete. If length is longer than character_expression, deletion occurs up to the last character in replaceWith_expression.
• replaceWith_expression: The expression of character data that will replace length characters of character_expression beginning at the start value.

SELECT STUFF('1234567890', 3, 2, 'xx');
                      -- Result: '12xx567890'

SUBSTRING(string_value FROM start FOR length)

Returns the part of the string with the specified length; starts at the specified index.

• string_value: The character string.
• start: The positive integer that specifies the start index of characters to return.
• length: Optional. The positive integer that specifies how many characters will be returned.

SELECT SUBSTRING('1234567890' FROM 3 FOR 2);
                      -- Result: '34'

                      SELECT SUBSTRING('1234567890' FROM 3);
                      -- Result: '34567890'

TOSTRING(string_value1)

Converts the value of this instance to its equivalent string representation.

• string_value1: The string to be converted.

SELECT TOSTRING(123);
                      -- Result: '123'

                      SELECT TOSTRING(123.456);
                      -- Result: '123.456'

                      SELECT TOSTRING(null);
                      -- Result: ''

TRIM(trimspec trimchar FROM string_value)

Returns the character expression with leading and/or trailing blanks removed.

• trimspec: Optional. If included must be one of the keywords BOTH, LEADING or TRAILING.
• trimchar: Optional. If included should be a one-character string value.
• string_value: The string value to trim.

SELECT TRIM('     trimmed     ');
                      -- Result: 'trimmed'

                      SELECT TRIM(LEADING FROM '     trimmed     ');
                      -- Result: 'trimmed     '

                      SELECT TRIM('-' FROM '-----trimmed-----');
                      -- Result: 'trimmed'

                      SELECT TRIM(BOTH '-' FROM '-----trimmed-----');
                      -- Result: 'trimmed'

                      SELECT TRIM(TRAILING '-' FROM '-----trimmed-----');
                      -- Result: '-----trimmed'

UNICODE(ncharacter_expression)

Returns the integer value defined by the Unicode standard of the first character of the input expression.

• ncharacter_expression: The Unicode character expression.

UPPER ( character_expression )

Returns the character expression with lowercase character data converted to uppercase.

• character_expression: The character expression.

SELECT UPPER('MIXED case');
                      -- Result: 'MIXED CASE'

XML_EXTRACT(xml, xpath [, separator])

Extracts an XML document using the specified XPath to flatten the XML. A comma is used to separate the outputs by default, but this can be changed by specifying the third parameter.

• xml: The XML document to extract.
• xpath: The XPath used to select the nodes. The nodes selected will be returned in a token-separated list.
• separator: The optional token used to separate the items in the flattened response. If this is not specified, the separator will be a comma.

SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch');
                      -- Result: 'a,e,i,o,u'

                      SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch', ';');
                      -- Result: 'a;e;i;o;u'

CURRENT_DATE()

Returns the current date value.

SELECT CURRENT_DATE();
                  -- Result: 2018-02-01

CURRENT_TIMESTAMP()

Returns the current time stamp of the database system as a datetime value. This value is equal to GETDATE and SYSDATETIME, and is always in the local timezone.

SELECT CURRENT_TIMESTAMP();
                  -- Result: 2018-02-01 03:04:05

DATEADD (datepart , integer_number , date [, dateformat])

Returns the datetime value that results from adding the specified number (a signed integer) to the specified date part of the date.

• datepart: The part of the date to add the specified number to. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), and millisecond (ms).
• number: The number to be added.
• date: The expression of the datetime data type.
• dateformat: The optional output date format.

SELECT DATEADD('d', 5, '2018-02-01');
                  -- Result: 2018-02-06

                  SELECT DATEADD('hh', 5, '2018-02-01 00:00:00');
                  -- Result: 2018-02-01 05:00:00

DATEDIFF ( datepart , startdate , enddate )

Returns the difference (a signed integer) of the specified time interval between the specified start date and end date.

• datepart: The part of the date that is the time interval of the difference between the start date and end date. The valid values and abbreviations are day (dd, d), hour (hh), minute (mi, n), second (ss, s), and millisecond (ms).
• startdate: The datetime expression of the start date.
• enddate: The datetime expression of the end date.

SELECT DATEDIFF('d', '2018-02-01', '2018-02-10');
                  -- Result: 9

                  SELECT DATEDIFF('hh', '2018-02-01 00:00:00', '2018-02-01 12:00:00');
                  -- Result: 12

DATEFROMPARTS(integer_year, integer_month, integer_day)

Returns the datetime value for the specified year, month, and day.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.

SELECT DATEFROMPARTS(2018, 2, 1);
                    -- Result: 2018-02-01

DATENAME(datepart , date)

Returns the character string that represents the specified date part of the specified date.

• datepart: The part of the date to return. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), millisecond (ms), microsecond (mcs), nanosecond (ns), and TZoffset (tz).
• date: The datetime expression.

SELECT DATENAME('yy', '2018-02-01');
                     -- Result: '2018'

                     SELECT DATENAME('dw', '2018-02-01');
                     -- Result: 'Thursday'

DATEPART(datepart, date [,integer_datefirst])

Returns a character string that represents the specified date part of the specified date.

• datepart: The part of the date to return. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), millisecond (ms), microsecond (mcs), nanosecond (ns), TZoffset (tz), ISODOW, ISO_WEEK (isoweek, isowk,isoww), and ISOYEAR.
• date: The datetime string.
• datefirst: The optional integer representing the first day of the week. The default is 7, Sunday.

SELECT DATEPART('yy', '2018-02-01');
                    -- Result: 2018

                    SELECT DATEPART('dw', '2018-02-01');
                    -- Result: 5

DATETIME2FROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute, integer_seconds, integer_fractions, integer_precision)

Returns the datetime value for the specified date parts.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.
• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.
• seconds: The integer expression specifying the seconds.
• fractions: The integer expression specifying the fractions of the second.
• precision: The integer expression specifying the precision of the fraction.

SELECT DATETIME2FROMPARTS(2018, 2, 1, 1, 2, 3, 456, 3);
                    -- Result: 2018-02-01 01:02:03.456

DATETIMEFROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute, integer_seconds, integer_milliseconds)

Returns the datetime value for the specified date parts.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.
• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.
• seconds: The integer expression specifying the seconds.
• milliseconds: The integer expression specifying the milliseconds.

SELECT DATETIMEFROMPARTS(2018, 2, 1, 1, 2, 3, 456);
                    -- Result: 2018-02-01 01:02:03.456

DATE_TRUNC(date, datepart)

Truncates the date to the precision of the given date part. Modeled after the Oracle TRUNC function.

• date: The datetime string that specifies the date.
• datepart: Refer to the Oracle documentation for valid datepart syntax.

SELECT DATE_TRUNC('05-04-2005', 'YY');
                    -- Result: '1/1/2005'
					
                    SELECT DATE_TRUNC('05-04-2005', 'MM');
                    -- Result: '5/1/2005'                    

DATE_TRUNC2(datepart, date, [weekday])

Truncates the date to the precision of the given date part. Modeled after the PostgreSQL date_trunc function.

• datepart: One of 'millennium', 'century', 'decade', 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute' or 'second'.
• date: The datetime string that specifies the date.
• weekday: The optional day of the week to use as the first day for 'week'. One of 'sunday', 'monday', etc.

SELECT DATE_TRUNC2('year', '2020-02-04');
                    -- Result: '2020-01-01'

                    SELECT DATE_TRUNC2('week', '2020-02-04', 'monday');
                    -- Result: '2020-02-02', which is the previous Monday

DAY(date)

Returns the integer that specifies the day component of the specified date.

• date: The datetime string that specifies the date.

SELECT DAY('2018-02-01');
                    -- Result: 1

DAYOFMONTH(date)

Returns the day of the month of the given date part.

• date: The datetime string that specifies the date.

SELECT DAYOFMONTH('04/15/2000');
				  -- Result: 15

DAYOFWEEK(date)

Returns the day of the week of the given date part.

• date: The datetime string that specifies the date.

SELECT DAYOFWEEK('04/15/2000');
				  -- Result: 7

DAYOFYEAR(date)

Returns the day of the year of the given date part.

• date: The datetime string that specifies the date.

SELECT DAYOFYEAR('04/15/2000');
				  -- Result: 106

EOMONTH(date [, integer_month_to_add ]) or LAST_DAY(date)

Returns the last day of the month that contains the specified date with an optional offset.

• date: The datetime expression specifying the date for which to return the last day of the month.
• integer_month_to_add: The optional integer expression specifying the number of months to add to the date before calculating the end of the month.

SELECT EOMONTH('2018-02-01');
                  -- Result: 2018-02-28
                  
                  SELECT LAST_DAY('2018-02-01');
                  -- Result: 2018-02-28

                  SELECT EOMONTH('2018-02-01', 2);
                  -- Result: 2018-04-30

FDWEEK(date)

Returns the first day of the week of the given date part.

• date: The datetime string that specifies the date.

SELECT FDWEEK('02-08-2018');
				  -- Result: 2/4/2018

FDMONTH(date)

Returns the first day of the month of the given date part.

• date: The datetime string that specifies the date.

SELECT FDMONTH('02-08-2018');
				  -- Result: 2/1/2018

FDQUARTER(date)

Returns the first day of the quarter of the given date part.

• date: The datetime string that specifies the date.

SELECT FDQUARTER('05-08-2018');
				  -- Result: 4/1/2018

FILEMODIFIEDTIME(uri)

Returns the time stamp associated with the Date Modified of the relevant file.

• uri: An absolute path pointing to a file on the local file system.

SELECT FILEMODIFIEDTIME('C:/Documents/myfile.txt');
				 -- Result: 6/25/2019 10:06:58 AM

FROM_DAYS(datevalue)

Returns a date derived from the number of days after 1582-10-15 (based upon the Gregorian calendar). This will be equivalent to the MYSQL FROM_DAYS function.

• datevalue: A integer value representing the number of days since 1582-10-15.

SELECT FROM_DAYS(736000);
				-- Result: 2/6/2015

GETDATE()

Returns the current time stamp of the database system as a datetime value. This value is equal to CURRENT_TIMESTAMP and SYSDATETIME, and is always in the local timezone.

SELECT GETDATE();
                  -- Result: 2018-02-01 03:04:05

GETUTCDATE()

Returns the current time stamp of the database system formatted as a UTC datetime value. This value is equal to SYSUTCDATETIME.

SELECT GETUTCDATE();
                  -- For example, if the local timezone is Eastern European Time (GMT+2)
                  -- Result: 2018-02-01 05:04:05

HOUR(date)

Returns the hour component from the provided datetime.

• date: The datetime string that specifies the date.

SELECT HOUR('02-02-2020 11:30:00');
				-- Result: 11

ISDATE(date, [date_format])

Returns 1 if the value is a valid date, time, or datetime value; otherwise, 0.

• date: The datetime string.
• date_format: The optional datetime format.

SELECT ISDATE('2018-02-01', 'yyyy-MM-dd');
                      -- Result: 1

                      SELECT ISDATE('Not a date');
                      -- Result: 0

LAST_WEEK()

Returns a time stamp equivalent to exactly one week before the current date.

SELECT LAST_WEEK();	//Assume the date is 3/17/2020	
				-- Result: 3/10/2020

LAST_MONTH()

Returns a time stamp equivalent to exactly one month before the current date.

SELECT LAST_MONTH(); //Assume the date is 3/17/2020	
				-- Result: 2/17/2020

LAST_YEAR()

Returns a time stamp equivalent to exactly one year before the current date.

SELECT LAST_YEAR();	//Assume the date is 3/17/2020	
				-- Result: 3/10/2019

LDWEEK(date)

Returns the last day of the provided week.

• date: The datetime string.

SELECT LDWEEK('02-02-2020');
				-- Result: 2/8/2020

LDMONTH(date)

Returns the last day of the provided month.

• date: The datetime string.

SELECT LDMONTH('02-02-2020');
				-- Result: 2/29/2020

LDQUARTER(date)

Returns the last day of the provided quarter.

• date: The datetime string.

SELECT LDQUARTER('02-02-2020');
				-- Result: 3/31/2020

MAKEDATE(year, days)

Returns a date value from a year and a number of days.

• year: The year
• days: The number of days into the year. Value must be greater than 0.

SELECT MAKEDATE(2020, 1);
          -- Result: 2020-01-01

MINUTE(date)

Returns the minute component from the provided datetime.

• date: The datetime string that specifies the date.

SELECT MINUTE('02-02-2020 11:15:00');
				-- Result: 15

MONTH(date)

Returns the month component from the provided datetime.

• date: The datetime string that specifies the date.

SELECT MONTH('02-02-2020');
				-- Result: 2

QUARTER(date)

Returns the quarter associated with the provided datetime.

• date: The datetime string that specifies the date.

SELECT QUARTER('02-02-2020');
				-- Result: 1

SECOND(date)

Returns the second component from the provided datetime.

• date: The datetime string that specifies the date.

SELECT SECOND('02-02-2020 11:15:23');
				-- Result: 23

SMALLDATETIMEFROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute)

Returns the datetime value for the specified date and time.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.
• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.

SELECT SMALLDATETIMEFROMPARTS(2018, 2, 1, 1, 2);
                      -- Result: 2018-02-01 01:02:00

STRTODATE(string,format)

Parses the provided string value and returns the corresponding datetime.

• string: The string value to be converted to datetime format.
• format: A format string which describes how to interpret the first string input. Follows standard Oracle date format syntax. A few special formats are available as well, including UNIX, UNIXMILIS, TICKS, and FILETICKS.

SELECT STRTODATE('03*04*2020','dd*MM*yyyy');
				-- Result: 4/3/2020

SYSDATETIME()

Returns the current time stamp as a datetime value of the database system. It is equal to GETDATE and CURRENT_TIMESTAMP, and is always in the local timezone.

SELECT SYSDATETIME();
                  -- Result: 2018-02-01 03:04:05

SYSUTCDATETIME()

Returns the current system date and time as a UTC datetime value. It is equal to GETUTCDATE.

SELECT SYSUTCDATETIME();
                  -- For example, if the local timezone is Eastern European Time (GMT+2)
                  -- Result: 2018-02-01 05:04:05

TIMEFROMPARTS(integer_hour, integer_minute, integer_seconds, integer_fractions, integer_precision)

Returns the time value for the specified time and with the specified precision.

• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.
• seconds: The integer expression specifying the seconds.
• fractions: The integer expression specifying the fractions of the second.
• precision : The integer expression specifying the precision of the fraction.

SELECT TIMEFROMPARTS(1, 2, 3, 456, 3);
                      -- Result: 01:02:03.456

TO_DAYS(date)

Returns the number of days since 0000-00-01. This will only return a value for dates on or after 1582-10-15 (based upon the Gregorian calendar). This will be equivalent to the MYSQL TO_DAYS function.

• date: The datetime string that specifies the date.

SELECT TO_DAYS('02-06-2015');
				-- Result: 736000

WEEK(date)

Returns the week (of the year) associated with the provided datetime.

• date: The datetime string that specifies the date.

SELECT WEEK('02-17-2020 11:15:23');
				-- Result: 8

YEAR(date)

Returns the integer that specifies the year of the specified date.

• date: The datetime string.

SELECT YEAR('2018-02-01');
                      -- Result: 2018

ABS ( numeric_expression )

Returns the absolute (positive) value of the specified numeric expression.

• numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

SELECT ABS(15);
                      -- Result: 15

                      SELECT ABS(-15);
                      -- Result: 15

ACOS ( float_expression )

Returns the arc cosine, the angle in radians whose cosine is the specified float expression.

• float_expression: The float expression that specifies the cosine of the angle to be returned. Values outside the range from -1 to 1 return null.

SELECT ACOS(0.5);
                      -- Result: 1.0471975511966

ASIN ( float_expression )

Returns the arc sine, the angle in radians whose sine is the specified float expression.

• float_expression: The float expression that specifies the sine of the angle to be returned. Values outside the range from -1 to 1 return null.

SELECT ASIN(0.5);
                      -- Result: 0.523598775598299

ATAN ( float_expression )

Returns the arc tangent, the angle in radians whose tangent is the specified float expression.

• float_expression: The float expression that specifies the tangent of the angle to be returned.

SELECT ATAN(10);
                      -- Result: 1.47112767430373

ATN2 ( float_expression1 , float_expression2 )

Returns the angle in radians between the positive x-axis and the ray from the origin to the point (y, x) where x and y are the values of the two specified float expressions.

• float_expression1: The float expression that is the y-coordinate.
• float_expression2: The float expression that is the x-coordinate.

SELECT ATN2(1, 1);
                      -- Result: 0.785398163397448

CEILING ( numeric_expression ) or CEIL( numeric_expression )

Returns the smallest integer greater than or equal to the specified numeric expression.

• numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

SELECT CEILING(1.3);
                      -- Result: 2

                      SELECT CEILING(1.5);
                      -- Result: 2

                      SELECT CEILING(1.7);
                      -- Result: 2

COS ( float_expression )

Returns the trigonometric cosine of the specified angle in radians in the specified expression.

• float_expression: The float expression of the specified angle in radians.

SELECT COS(1);
                      -- Result: 0.54030230586814

COT ( float_expression )

Returns the trigonometric cotangent of the angle in radians specified by float_expression.

• float_expression: The float expression of the angle in radians.

SELECT COT(1);
                      -- Result: 0.642092615934331

DEGREES ( numeric_expression )

Returns the angle in degrees for the angle specified in radians.

• numeric_expression: The angle in radians, an expression of an indeterminate numeric data type except for the bit data type.

SELECT DEGREES(3.1415926);
                      -- Result: 179.999996929531

EXP ( float_expression )

Returns the exponential value of the specified float expression. For example, EXP(LOG(20)) is 20.

• float_expression: The float expression.

SELECT EXP(2);
                      -- Result: 7.38905609893065

EXPR ( expression )

Evaluates the expression.

• expression: The expression. Operators allowed are +, -, *, /, ==, !=, >, <, >=, and <=.

SELECT EXPR('1 + 2 * 3');
                      -- Result: 7

                      SELECT EXPR('1 + 2 * 3 == 7');
                      -- Result: true

FLOOR ( numeric_expression )

Returns the largest integer less than or equal to the numeric expression.

• numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

SELECT FLOOR(1.3);
                      -- Result: 1

                      SELECT FLOOR(1.5);
                      -- Result: 1

                      SELECT FLOOR(1.7);
                      -- Result: 1

GREATEST(int1,int2,....)

Returns the greatest of the supplied integers.

SELECT GREATEST(3,5,8,10,1)
				-- Result: 10			

HEX(value)

Returns a the equivalent hex for the input value.

• value: A string or numerical value to be converted into hex.

SELECT HEX(866849198);
				-- Result: 33AB11AE
				
				SELECT HEX('Sample Text');
				-- Result: 53616D706C652054657874

LEAST(int1,int2,....)

Returns the least of the supplied integers.

SELECT LEAST(3,5,8,10,1)
				-- Result: 1			

LOG ( float_expression [, base ] )

Returns the natural logarithm of the specified float expression.

• float_expression: The float expression.
• base: The optional integer argument that sets the base for the logarithm.

SELECT LOG(7.3890560);
                      -- Result: 1.99999998661119

LOG10 ( float_expression )

Returns the base-10 logarithm of the specified float expression.

• float_expression: The expression of type float.

SELECT LOG10(10000);
                      -- Result: 4

MOD(dividend,divisor)

Returns the integer value associated with the remainder when dividing the dividend by the divisor.

• dividend: The number to take the modulus of.
• divisor: The number to divide the dividend by when determining the modulus.

SELECT MOD(10,3);
				-- Result: 1

NEGATE(real_number)

Returns the opposite to the real number input.

• real_number: The real number to find the opposite of.

SELECT NEGATE(10);
				-- Result: -10
				
				SELECT NEGATE(-12.4)
				--Result: 12.4

PI ( )

Returns the constant value of pi.

SELECT PI()
                  -- Result: 3.14159265358979 

POWER ( float_expression , y )

Returns the value of the specified expression raised to the specified power.

• float_expression: The float expression.
• y: The power to raise float_expression to.

SELECT POWER(2, 10);
                      -- Result: 1024

                      SELECT POWER(2, -2);
                      -- Result: 0.25

RADIANS ( float_expression )

Returns the angle in radians of the angle in degrees.

• float_expression: The degrees of the angle as a float expression.

SELECT RADIANS(180);
                      -- Result: 3.14159265358979

RAND ( [ integer_seed ] )

Returns a pseudorandom float value from 0 through 1, exclusive.

• seed: The optional integer expression that specifies the seed value. If seed is not specified, a seed value at random will be assigned.

SELECT RAND();
                      -- This result may be different, since the seed is randomized
                      -- Result: 0.873159630165044

                      SELECT RAND(1);
                      -- This result will always be the same, since the seed is constant
                      -- Result: 0.248668584157093

ROUND ( numeric_expression [ ,integer_length] [ ,function ] )

Returns the numeric value rounded to the specified length or precision.

• numeric_expression: The expression of a numeric data type.
• length: The optional precision to round the numeric expression to. When this is ommitted, the default behavior will be to round to the nearest whole number.
• function: The optional type of operation to perform. When the function parameter is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

SELECT ROUND(1.3, 0);
                      -- Result: 1

                      SELECT ROUND(1.55, 1);
                      -- Result: 1.6

                      SELECT ROUND(1.7, 0, 0);
                      -- Result: 2

                      SELECT ROUND(1.7, 0, 1);
                      -- Result: 1
                      
                      SELECT ROUND (1.24);
                      -- Result: 1.0

SIGN ( numeric_expression )

Returns the positive sign (1), 0, or negative sign (-1) of the specified expression.

• numeric_expression: The expression of an indeterminate data type except for the bit data type.

SELECT SIGN(0);
                      -- Result: 0

                      SELECT SIGN(10);
                      -- Result: 1

                      SELECT SIGN(-10);
                      -- Result: -1

SIN ( float_expression )

Returns the trigonometric sine of the angle in radians.

• float_expression: The float expression specifying the angle in radians.

SELECT SIN(1);
                     -- Result: 0.841470984807897

SQRT ( float_expression )

Returns the square root of the specified float value.

• float_expression: The expression of type float.

SELECT SQRT(100);
                      -- Result: 10

SQUARE ( float_expression )

Returns the square of the specified float value.

• float_expression: The expression of type float.

SELECT SQUARE(10);
                      -- Result: 100

                      SELECT SQUARE(-10);
                      -- Result: 100

TAN ( float_expression )

Returns the tangent of the input expression.

• float_expression: The expression of type float.

SELECT TAN(1);
                      -- Result: 1.5574077246549

TRUNC(decimal_number,precision)

Returns the supplied decimal number truncated to have the supplied decimal precision.

• decimal_number: The decimal value to truncate.
• precision: The number of decimal places to truncate the decimal number to.

SELECT TRUNC(10.3423,2);
				-- Result: 10.34

To create new records, use INSERT statements.

INSERT Syntax

The INSERT statement specifies the columns to be inserted and the new column values. You can specify the column values in a comma-separated list in the VALUES clause, as shown in the following example:

INSERT INTO <table_name> 
( <column_reference> [ , ... ] )
VALUES 
( { <expression> | NULL } [ , ... ] ) 
  

<expression> ::=
  | @ <parameter> 
  | ?
| <literal>

You can use the executeUpdate method of the Statement and PreparedStatement classes to execute data manipulation commands and retrieve the rows affected.

String cmd = "INSERT INTO incident (priority) VALUES (?)";
PreparedStatement pstmt = connection.prepareStatement(cmd);
pstmt.setString(1, "4");
int count = pstmt.executeUpdate();
System.out.println(count+" rows were affected");
connection.close();	

To modify existing records, use UPDATE statements.

Update Syntax

The UPDATE statement takes as input a comma-separated list of columns and new column values as name-value pairs in the SET clause, as shown in the following example:

UPDATE <table_name> SET { <column_reference> = <expression> } [ , ... ] WHERE { sys_id = <expression>  } [ { AND | OR } ... ] 

<expression> ::=
  | @ <parameter> 
  | ?
| <literal>

You can use the executeUpdate method of the Statement or PreparedStatement classes to execute data manipulation commands and retrieve the rows affected, as shown in the following example:

String cmd = "UPDATE incident SET priority='4' WHERE sys_id = ?";
PreparedStatement pstmt = connection.prepareStatement(cmd);
pstmt.setString(1, "S");
int count = pstmt.executeUpdate();
System.out.println(count + " rows were affected");
connection.close();

To delete information from a table, use DELETE statements.

DELETE Syntax

The DELETE statement requires the table name in the FROM clause and the row's primary key in the WHERE clause, as shown in the following example:

<delete_statement> ::= DELETE FROM <table_name> WHERE { sys_id = <expression> } [ { AND | OR } ... ]

<expression> ::=
  | @ <parameter> 
  | ?
| <literal>

You can use the executeUpdate method of the Statement or PreparedStatement classes to execute data manipulation commands and retrieve the number of affected rows, as shown in the following example:

Connection connection = DriverManager.getConnection("jdbc:servicenow:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;User=MyUser;Password=MyPassword;Url=https://MyInstance12345.service-now.com/;",);
String cmd = "DELETE FROM incident WHERE sys_id = ?";
PreparedStatement pstmt = connection.prepareStatement(cmd);
pstmt.setString(1, "S");
int count=pstmt.executeUpdate();
connection.close();

You can issue the GETDELETED query to retrieve all records deleted from the live data for the time range specified. This query accepts a datetime value as a filter, as shown in the following example:

GETDELETED FROM <table_name> WHERE <search_condition>

<search_condition> ::= 
  {
    <expression> { = | < | <= | > | >= } [ <expression> ] 
  } [ { AND | OR } ... ]

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

String cmd = "GETDELETED FROM incident WHERE sys_updated_on >='2013-01-01' AND sys_updated_on <='2013-02-01'";
PreparedStatement pstmt = connection.prepareStatement(cmd);
ResultSet rs = pstmt.getResultSet();
while(rs.next()){	
  System.out.println(rs.getString("sys_id"));
}
connection.close();

Note: By putting a CACHE command in front of the query, you can update the cache to remove all values that have been deleted from the data source, as shown in the following example:

CACHE GETDELETED FROM [TableName] WHERE sys_updated_on >='2013-01-01' AND sys_updated_on <='2013-02-01'

When caching is enabled, CACHE statements provide complete control over the data that is cached and the table to which it is cached. The CACHE statement executes the SELECT statement specified and caches its results to a table with the same name in the cache database or to table specified in <cached_table_name>. The driver updates or inserts rows to the cache depending on whether or not they already exist in the cache, so the primary key, which is used to identify existing rows, must be included in the selected columns.

See Caching Data for more information on different caching strategies.

CACHE Statement Syntax

The cache statement may include the following options that alter its behavior:

CACHE [ <cached_table_name> ] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] <select_statement> 

WITH TRUNCATE

If this option is set, the driver removes existing rows in the cache table before adding the selected rows. Use this option if you want to refresh the entire cache table but keep its existing schema.

AUTOCOMMIT

If this option is set, the driver commits each row individually. Use this option if you want to ignore the rows that could not be cached due to some reason. By default, the entire result set is cached as a single transaction.

DROP EXISTING

If this option is set, the driver drops the existing cache table before caching the new results. Use this option if you want to refresh the entire cache table, including its schema.

SCHEMA ONLY

If this option is set, the driver creates the cache table based on the SELECT statement without executing the query.

ALTER SCHEMA

If this option is set, the driver alters the schema of the existing table in the cache if it does not match the schema of the SELECT statement. This option results in new columns or dropped columns, if the schema of the SELECT statement does not match the cached table.

Common Queries

Use the following cache statement to cache all rows of a table:

CACHE SELECT * FROM incident

Use the following cache statement to cache all rows of a table into the cache table Cachedincident:

CACHE Cachedincident SELECT * FROM incident

Use the following cache statement for incremental caching. The DateModified column may not exist in all tables. The cache statement shows how incremental caching would work if there were such a column. Also, notice that, in this case, the WITH TRUNCATE and DROP EXISTING options are specifically omitted, which would have deleted all existing rows.

CACHE Cachedincident SELECT * FROM incident WHERE DateModified > '2013-04-04'

Use the following cache statements to create a table with all available columns that will then cache only a few of them. The sequence of statements cache only sys_id and priority even though the cache table Cachedincident has all the columns in incident.

CACHE Cachedincident SCHEMA ONLY SELECT * FROM incident
CACHE Cachedincident SELECT sys_id, priority FROM incident

The REPLICATE statement makes it easy to maintain copies of one or many tables in a cache database. Each time the REPLICATE statement is invoked, the driver requests incremental changes that have occurred since the last time the cache database was updated.

The REPLICATE statement adds new rows, updates existing rows that have changed, and deletes rows that have been deleted. The REPLICATE statement can execute multiple underlying operations based on the API available from the data source. All operations are executed as a single transaction to maintain the consistency of the cached data.

REPLICATE Syntax

You can use REPLICATE statements to perform the following actions:

• Replicate a single table: REPLICATE <table_name>
• Replicate all tables:REPLICATE ALL
• Replicate only the specified tables: REPLICATE TABLES

• Replicate a query's results: REPLICATE <select_statement>. Use a SELECT statement to control the columns that are cached, as well their names, or to apply any SQL formulas before replicating data.

The EXCLUDE COLUMNS clause can be specified in all of these statements. The REPLICATE ALL statement additionally supports an EXCLUDE TABLES clause.

Here is the complete syntax :

REPLICATE 
{ 
  [ <cached_table_name> ] 
  [ EXCLUDE COLUMNS ( <column_name> [ , ... ] ) ] 
  { <select_statement> | <table_reference> } 
  (<column_definition> [ , ... ] [<table_constraint>])
  [WITH {<option_name>=<option_value>|<option_name>} , ... ]
  [AS] { <select_statement> | <table_reference> } 
}
| TABLES 
  ( 
    {
      <table_name> 
      [ EXCLUDE COLUMNS ( <column_name [ , ... ] ) ]
    } [ , ... ] 
  ) 
| ALL 
  [
    { 
      EXCLUDE COLUMNS ( { <table_name>.<column_name> } [ , ... ] )
      | EXCLUDE TABLES ( <table_name> [ , ... ] ) 
    }
  ]
  
<table_constraint> := PRIMARY KEY(<column_name>,...)
<option_name> := DropTable | TruncateTable | AlterSchema ...
<option_value> := <literal> | <identifier>

Common Queries

Use the following statement to maintain a copy of a table. This command creates a table incident in the cache database if it does not already exist. If the table exists, the REPLICATE statement updates recent changes (newly updated, deleted, and inserted records) since the last cache update.

REPLICATE incident

Use the following statement to replicate incident to REP_incident.

REPLICATE REP_incident SELECT sys_id, priority FROM incident

Use the following statement to select specific columns and perform operations on data before it is replicated. This command creates the table REP_incident with the columns DateModified and FullName. The FullName column is a concatenation of FirstName and LastName from the incident table.

REPLICATE REP_incident SELECT  DateModified, CONCAT(firstname," ",lastname) AS FullName FROM incident

Note: Selecting fewer columns than what are already in the cache leads to stale data in some columns. The SELECT statement with a custom projection (changed column names/values, or specific columns) should remain the same throughout the life of the cache.

To execute stored procedures, you can use EXECUTE or EXEC statements.

EXEC and EXECUTE assign stored procedure* inputs, referenced by name, to values or parameter names.

Stored Procedure* Syntax

To execute a stored procedure* as an SQL statement, use the following syntax:

{ EXECUTE | EXEC } <stored_proc_name> 
{
  [ @ ] <input_name> = <expression>
} [ , ... ]

<expression> ::=
  | @ <parameter> 
  | ?
| <literal>

Example Statements

Reference stored procedure* inputs by name:

EXECUTE my_proc @second = 2, @first = 1, @third = 3;

Execute a parameterized stored procedure* statement:

EXECUTE my_proc second = @p1, first = @p2, third = @p3; 

You can use the driver to work with all the tables in your account, including custom tables. The table definitions are dynamically retrieved. The driver connects to ServiceNow and gets the list of tables and the metadata for the tables by calling the appropriate Web services. Any changes you make to your ServiceNow account, such as activating a plugin, adding new columns to a table, or changing the data type of a column, will immediately be reflected when you connect.

Tables

The metadata for the tables in ServiceNow are obtained during run time based on a number of predefined schemas from ServiceNow and your unique ServiceNow account information. In Tables you will find a small sample of the tables available in the ServiceNow development environment, which models the core ServiceNow system.

You can use data dictionary tables to obtain details about the tables available in your ServiceNow environment. Data dictionary tables and other system tables are prefixed with "system_".

The following data dictionary tables define the structure of the database tables:

• system_db_object: This contains information about each table, one row per table.
• system_dictionary: This defines every table and field in the system.
• system_documentation: This contains the human-readable labels and language information for every field of every table in the database.

Views

The driver will expose the ServiceNow Database Views as Views only if the IncludeDatabaseViews connection property is set to 'True'.

The metadata for the Views in ServiceNow are obtained during run time based on a number of predefined schemas from ServiceNow and your unique ServiceNow account information. In Views you will find a small sample of the views available in the ServiceNow development environment, which models the core ServiceNow system.

The following data dictionary tables define the structure of the database views:

• system_db_view: This contains information about each view, one row per view.
• sys_db_view_table: This defines all source tables that each Database View contains.
• sys_db_view_table_field: This defines all source table fields exposed for each Database View.
• system_dictionary: This defines every table field in the system.
• system_documentation: This contains the human-readable labels and language information for every field of every view in the database.

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Stored Procedures are function-like interfaces to ServiceNow. They can be used to create materialized views -- tables that contain the results of a query. To use the driver to work with a materialized view, specify the query in the stored procedure* call. The resulting schema is then saved in an XML file that can be easily modified without calling the stored procedure* again. Schemas are saved in .rsd files in the folder you specify as the Location.

The driver models the data in ServiceNow into a list of tables that can be queried using standard SQL statements.

Generally, querying ServiceNow tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

JDBC Driver for ServiceNow Tables

Name	Description
ast_contract	The ServiceNow table ast_contract.
ast_license_base	The ServiceNow table ast_license_base.
change_request	The ServiceNow table change_request.
cmdb_ci	The ServiceNow table cmdb_ci.
cmdb_metric	The ServiceNow table cmdb_metric.
cmn_building	The ServiceNow table cmn_building.
cmn_context_help	The ServiceNow table cmn_context_help.
cmn_cost_center	The ServiceNow table cmn_cost_center.
cmn_department	The ServiceNow table cmn_department.
cmn_location	The ServiceNow table cmn_location.
cmn_map_page	The ServiceNow table cmn_map_page.
cmn_notif_device	The ServiceNow table cmn_notif_device.
cmn_notif_device_variable	The ServiceNow table cmn_notif_device_variable.
cmn_notif_grmember	The ServiceNow table cmn_notif_grmember.
cmn_notif_group	The ServiceNow table cmn_notif_group.
cmn_notif_message	The ServiceNow table cmn_notif_message.
cmn_notif_service_provider	The ServiceNow table cmn_notif_service_provider.
cmn_other_schedule	The ServiceNow table cmn_other_schedule.
cmn_relative_duration	The ServiceNow table cmn_relative_duration.
cmn_schedule	The ServiceNow table cmn_schedule.
cmn_schedule_blackout	The ServiceNow table cmn_schedule_blackout.
cmn_schedule_condition	The ServiceNow table cmn_schedule_condition.
cmn_schedule_maintenance	The ServiceNow table cmn_schedule_maintenance.
cmn_schedule_page	The ServiceNow table cmn_schedule_page.
cmn_schedule_span	The ServiceNow table cmn_schedule_span.
cmn_timeline_page	The ServiceNow table cmn_timeline_page.
cmn_timeline_page_style	The ServiceNow table cmn_timeline_page_style.
cmn_timeline_sub_item	The ServiceNow table cmn_timeline_sub_item.
diagrammer_action	The ServiceNow table diagrammer_action.
expert_panel	The ServiceNow table expert_panel.
item_option_new	The ServiceNow table item_option_new.
question	The ServiceNow table question.
sc_category	The ServiceNow table sc_category.
sc_cat_item	The ServiceNow table sc_cat_item.
sla	The ServiceNow table sla.
sysauto	The ServiceNow table sysauto.
sysauto_script	The ServiceNow table sysauto_script.
syslog	The ServiceNow table syslog.
sysrule	The ServiceNow table sysrule.
system_db_object	The ServiceNow table system_db_object.
system_dictionary	The ServiceNow table system_dictionary.
system_documentation	The ServiceNow table system_documentation.
system_import_set_row	The ServiceNow table system_import_set_row.
system_script_client	The ServiceNow table system_script_client.
system_ui_policy	The ServiceNow table system_ui_policy.
system_ui_policy_action	The ServiceNow table system_ui_policy_action.
task	The ServiceNow table task.
v_field_creator	The ServiceNow table v_field_creator.

The ServiceNow table ast_contract.

Columns

Name	Type	ReadOnly	References	Description
vendor	String	False		The vendor column for the table ast_contract.
renewable	String	False		The renewable column for the table ast_contract.
tax_rate	String	False		The tax_rate column for the table ast_contract.
contract_model	String	False		The contract_model column for the table ast_contract.
state	String	False		The state column for the table ast_contract.
sys_class_name	String	False		The sys_class_name column for the table ast_contract.
ends	Datetime	False		The ends column for the table ast_contract.
total_cost	String	False		The total_cost column for the table ast_contract.
contract_administrator	String	False		The contract_administrator column for the table ast_contract.
cost_center	String	False		The cost_center column for the table ast_contract.
expiration	String	False		The expiration column for the table ast_contract.
description	String	False		The description column for the table ast_contract.
cost_adjustment_type	String	False		The cost_adjustment_type column for the table ast_contract.
payment_schedule	String	False		The payment_schedule column for the table ast_contract.
renewal_options	String	False		The renewal_options column for the table ast_contract.
license_type	String	False		The license_type column for the table ast_contract.
vendor_account	String	False		The vendor_account column for the table ast_contract.
cost_adjustment_percentage	String	False		The cost_adjustment_percentage column for the table ast_contract.
payment_amount	String	False		The payment_amount column for the table ast_contract.
renewal_end_date	Datetime	False		The renewal_end_date column for the table ast_contract.
process	String	False		The process column for the table ast_contract.
ratecard	String	False		The ratecard column for the table ast_contract.
cost_per_unit	String	False		The cost_per_unit column for the table ast_contract.
monthly_cost	String	False		The monthly_cost column for the table ast_contract.
sys_mod_count	String	False		The sys_mod_count column for the table ast_contract.
commitment	String	False		The commitment column for the table ast_contract.
sys_created_by	String	False		The sys_created_by column for the table ast_contract.
approval_history	String	False		The approval_history column for the table ast_contract.
sys_updated_by	String	False		The sys_updated_by column for the table ast_contract.
number	String	False		The number column for the table ast_contract.
active	String	False		The active column for the table ast_contract.
sales_tax	String	False		The sales_tax column for the table ast_contract.
renewal_contact	String	False		The renewal_contact column for the table ast_contract.
license_quantity_entitled	String	False		The license_quantity_entitled column for the table ast_contract.
starts	Datetime	False		The starts column for the table ast_contract.
sub_total_cost	String	False		The sub_total_cost column for the table ast_contract.
location	String	False		The location column for the table ast_contract.
po_number	String	False		The po_number column for the table ast_contract.
sys_domain	String	False		The sys_domain column for the table ast_contract.
short_description	String	False		The short_description column for the table ast_contract.
cost_adjustment_reason	String	False		The cost_adjustment_reason column for the table ast_contract.
tax_cost	String	False		The tax_cost column for the table ast_contract.
invoice_payment_terms	String	False		The invoice_payment_terms column for the table ast_contract.
terms_and_conditions	String	False		The terms_and_conditions column for the table ast_contract.
vendor_contract	String	False		The vendor_contract column for the table ast_contract.
cost_adjustment	String	False		The cost_adjustment column for the table ast_contract.
tax_exempt	String	False		The tax_exempt column for the table ast_contract.
renewal_date	Datetime	False		The renewal_date column for the table ast_contract.
substate	String	False		The substate column for the table ast_contract.
approver	String	False		The approver column for the table ast_contract.
lifetime_cost	String	False		The lifetime_cost column for the table ast_contract.
yearly_cost	String	False		The yearly_cost column for the table ast_contract.
sys_created_on	Datetime	False		The sys_created_on column for the table ast_contract.
discount	String	False		The discount column for the table ast_contract.
sys_updated_on	Datetime	False		The sys_updated_on column for the table ast_contract.
application_model	String	False		The application_model column for the table ast_contract.
sys_id [KEY]	String	False		The sys_id column for the table ast_contract.
sys_domain_path	String	False		The sys_domain_path column for the table ast_contract.

The ServiceNow table ast_license_base.

Columns

Name	Type	ReadOnly	References	Description
number	String	False		The number column for the table ast_license_base.
vendor_account	String	False		The vendor_account column for the table ast_license_base.
license_count	String	False		The license_count column for the table ast_license_base.
option	String	False		The option column for the table ast_license_base.
sys_created_on	Datetime	False		The sys_created_on column for the table ast_license_base.
vendor	String	False		The vendor column for the table ast_license_base.
license_key	String	False		The license_key column for the table ast_license_base.
upgraded_from	String	False		The upgraded_from column for the table ast_license_base.
sys_updated_on	Datetime	False		The sys_updated_on column for the table ast_license_base.
sys_class_name	String	False		The sys_class_name column for the table ast_license_base.
short_description	String	False		The short_description column for the table ast_license_base.
license_cost	String	False		The license_cost column for the table ast_license_base.
sys_id [KEY]	String	False		The sys_id column for the table ast_license_base.
invoice_number	String	False		The invoice_number column for the table ast_license_base.
location	String	False		The location column for the table ast_license_base.
percent_used	String	False		The percent_used column for the table ast_license_base.
vendor_contract	String	False		The vendor_contract column for the table ast_license_base.
install_count	String	False		The install_count column for the table ast_license_base.
manual	String	False		The manual column for the table ast_license_base.
sys_created_by	String	False		The sys_created_by column for the table ast_license_base.
department	String	False		The department column for the table ast_license_base.
description	String	False		The description column for the table ast_license_base.
maintenance_cost	String	False		The maintenance_cost column for the table ast_license_base.
sys_updated_by	String	False		The sys_updated_by column for the table ast_license_base.
parent	String	False		The parent column for the table ast_license_base.
purchased	Datetime	False		The purchased column for the table ast_license_base.
total_cost	String	False		The total_cost column for the table ast_license_base.
compliance_level	String	False		The compliance_level column for the table ast_license_base.
po_number	String	False		The po_number column for the table ast_license_base.
license_available	String	False		The license_available column for the table ast_license_base.
discoverable_key	String	False		The discoverable_key column for the table ast_license_base.
sys_mod_count	String	False		The sys_mod_count column for the table ast_license_base.

The ServiceNow table change_request.

Columns

Name	Type	ReadOnly	References	Description
outside_maintenance_schedule	String	False		The outside_maintenance_schedule column for the table change_request.
on_hold_reason	String	False		The on_hold_reason column for the table change_request.
start_date	Datetime	False		The start_date column for the table change_request.
cab_date	Datetime	False		The cab_date column for the table change_request.
close_code	String	False		The close_code column for the table change_request.
requested_by	String	False		The requested_by column for the table change_request.
backout_plan	String	False		The backout_plan column for the table change_request.
change_plan	String	False		The change_plan column for the table change_request.
implementation_plan	String	False		The implementation_plan column for the table change_request.
sys_id [KEY]	String	False		The sys_id column for the table change_request.
phase	String	False		The phase column for the table change_request.
review_status	String	False		The review_status column for the table change_request.
conflict_status	String	False		The conflict_status column for the table change_request.
phase_state	String	False		The phase_state column for the table change_request.
production_system	String	False		The production_system column for the table change_request.
type	String	False		The type column for the table change_request.
on_hold	String	False		The on_hold column for the table change_request.
end_date	Datetime	False		The end_date column for the table change_request.
requested_by_date	Datetime	False		The requested_by_date column for the table change_request.
risk_impact_analysis	String	False		The risk_impact_analysis column for the table change_request.
test_plan	String	False		The test_plan column for the table change_request.
justification	String	False		The justification column for the table change_request.
scope	String	False		The scope column for the table change_request.
review_comments	String	False		The review_comments column for the table change_request.
std_change_producer_version	String	False		The std_change_producer_version column for the table change_request.
category	String	False		The category column for the table change_request.
review_date	Datetime	False		The review_date column for the table change_request.
conflict_last_run	Datetime	False		The conflict_last_run column for the table change_request.
risk	String	False		The risk column for the table change_request.
reason	String	False		The reason column for the table change_request.
cab_recommendation	String	False		The cab_recommendation column for the table change_request.
delivery_task	String	False		The delivery_task column for the table change_request.
upon_approval	String	False		The upon_approval column for the table change_request.
work_start	Datetime	False		The work_start column for the table change_request.
delivery_plan	String	False		The delivery_plan column for the table change_request.
rejection_goto	String	False		The rejection_goto column for the table change_request.
work_end	Datetime	False		The work_end column for the table change_request.
upon_reject	String	False		The upon_reject column for the table change_request.
business_service	String	False		The business_service column for the table change_request.
sys_domain	String	False		The sys_domain column for the table change_request.
sys_created_on	Datetime	False		The sys_created_on column for the table change_request.
sys_updated_on	Datetime	False		The sys_updated_on column for the table change_request.
expected_start	Datetime	False		The expected_start column for the table change_request.
variables	String	False		The variables column for the table change_request.
number	String	False		The number column for the table change_request.
comments	String	False		The comments column for the table change_request.
urgency	String	False		The urgency column for the table change_request.
opened_at	Datetime	False		The opened_at column for the table change_request.
watch_list	String	False		The watch_list column for the table change_request.
sla_due	Datetime	False		The sla_due column for the table change_request.
active	String	False		The active column for the table change_request.
state	String	False		The state column for the table change_request.
work_notes	String	False		The work_notes column for the table change_request.
closed_by	String	False		The closed_by column for the table change_request.
follow_up	Datetime	False		The follow_up column for the table change_request.
work_notes_list	String	False		The work_notes_list column for the table change_request.
cmdb_ci	String	False		The cmdb_ci column for the table change_request.
approval_history	String	False		The approval_history column for the table change_request.
business_duration	Datetime	False		The business_duration column for the table change_request.
location	String	False		The location column for the table change_request.
user_input	String	False		The user_input column for the table change_request.
assigned_to	String	False		The assigned_to column for the table change_request.
escalation	String	False		The escalation column for the table change_request.
time_worked	String	False		The time_worked column for the table change_request.
additional_assignee_list	String	False		The additional_assignee_list column for the table change_request.
correlation_id	String	False		The correlation_id column for the table change_request.
comments_and_work_notes	String	False		The comments_and_work_notes column for the table change_request.
order	String	False		The order column for the table change_request.
made_sla	String	False		The made_sla column for the table change_request.
sys_mod_count	String	False		The sys_mod_count column for the table change_request.
sys_created_by	String	False		The sys_created_by column for the table change_request.
sys_updated_by	String	False		The sys_updated_by column for the table change_request.
sys_domain_path	String	False		The sys_domain_path column for the table change_request.
sys_class_name	String	False		The sys_class_name column for the table change_request.
parent	String	False		The parent column for the table change_request.
priority	String	False		The priority column for the table change_request.
close_notes	String	False		The close_notes column for the table change_request.
reassignment_count	String	False		The reassignment_count column for the table change_request.
due_date	Datetime	False		The due_date column for the table change_request.
short_description	String	False		The short_description column for the table change_request.
company	String	False		The company column for the table change_request.
approval_set	Datetime	False		The approval_set column for the table change_request.
opened_by	String	False		The opened_by column for the table change_request.
contact_type	String	False		The contact_type column for the table change_request.
assignment_group	String	False		The assignment_group column for the table change_request.
approval	String	False		The approval column for the table change_request.
calendar_duration	Datetime	False		The calendar_duration column for the table change_request.
knowledge	String	False		The knowledge column for the table change_request.
correlation_display	String	False		The correlation_display column for the table change_request.
wf_activity	String	False		The wf_activity column for the table change_request.
description	String	False		The description column for the table change_request.
impact	String	False		The impact column for the table change_request.
closed_at	Datetime	False		The closed_at column for the table change_request.
group_list	String	False		The group_list column for the table change_request.
activity_due	Datetime	False		The activity_due column for the table change_request.

The ServiceNow table cmdb_ci.

Columns

Name	Type	ReadOnly	References	Description
name	String	False		The name column for the table cmdb_ci.
serial_number	String	False		The serial_number column for the table cmdb_ci.
delivery_date	Datetime	False		The delivery_date column for the table cmdb_ci.
lease_id	String	False		The lease_id column for the table cmdb_ci.
checked_out	Datetime	False		The checked_out column for the table cmdb_ci.
sys_domain_path	String	False		The sys_domain_path column for the table cmdb_ci.
sys_created_by	String	False		The sys_created_by column for the table cmdb_ci.
purchase_date	Datetime	False		The purchase_date column for the table cmdb_ci.
location	String	False		The location column for the table cmdb_ci.
cost_cc	String	False		The cost_cc column for the table cmdb_ci.
managed_by	String	False		The managed_by column for the table cmdb_ci.
sys_updated_by	String	False		The sys_updated_by column for the table cmdb_ci.
assignment_group	String	False		The assignment_group column for the table cmdb_ci.
model_id	String	False		The model_id column for the table cmdb_ci.
gl_account	String	False		The gl_account column for the table cmdb_ci.
supported_by	String	False		The supported_by column for the table cmdb_ci.
unverified	String	False		The unverified column for the table cmdb_ci.
company	String	False		The company column for the table cmdb_ci.
manufacturer	String	False		The manufacturer column for the table cmdb_ci.
po_number	String	False		The po_number column for the table cmdb_ci.
due_in	String	False		The due_in column for the table cmdb_ci.
asset	String	False		The asset column for the table cmdb_ci.
sys_mod_count	String	False		The sys_mod_count column for the table cmdb_ci.
asset_tag	String	False		The asset_tag column for the table cmdb_ci.
sys_class_name	String	False		The sys_class_name column for the table cmdb_ci.
order_date	Datetime	False		The order_date column for the table cmdb_ci.
department	String	False		The department column for the table cmdb_ci.
checked_in	Datetime	False		The checked_in column for the table cmdb_ci.
sys_domain	String	False		The sys_domain column for the table cmdb_ci.
sys_updated_on	Datetime	False		The sys_updated_on column for the table cmdb_ci.
install_status	String	False		The install_status column for the table cmdb_ci.
justification	String	False		The justification column for the table cmdb_ci.
cost	String	False		The cost column for the table cmdb_ci.
owned_by	String	False		The owned_by column for the table cmdb_ci.
assigned	Datetime	False		The assigned column for the table cmdb_ci.
vendor	String	False		The vendor column for the table cmdb_ci.
invoice_number	String	False		The invoice_number column for the table cmdb_ci.
support_group	String	False		The support_group column for the table cmdb_ci.
skip_sync	String	False		The skip_sync column for the table cmdb_ci.
assigned_to	String	False		The assigned_to column for the table cmdb_ci.
install_date	Datetime	False		The install_date column for the table cmdb_ci.
warranty_expiration	Datetime	False		The warranty_expiration column for the table cmdb_ci.
due	Datetime	False		The due column for the table cmdb_ci.
cost_center	String	False		The cost_center column for the table cmdb_ci.
sys_created_on	Datetime	False		The sys_created_on column for the table cmdb_ci.
short_description	String	False		The short_description column for the table cmdb_ci.
attributes	String	False		The attributes column for the table cmdb_ci.
fault_count	String	False		The fault_count column for the table cmdb_ci.
change_control	String	False		The change_control column for the table cmdb_ci.
fqdn	String	False		The fqdn column for the table cmdb_ci.
subcategory	String	False		The subcategory column for the table cmdb_ci.
last_discovered	Datetime	False		The last_discovered column for the table cmdb_ci.
correlation_id	String	False		The correlation_id column for the table cmdb_ci.
discovery_source	String	False		The discovery_source column for the table cmdb_ci.
mac_address	String	False		The mac_address column for the table cmdb_ci.
maintenance_schedule	String	False		The maintenance_schedule column for the table cmdb_ci.
model_number	String	False		The model_number column for the table cmdb_ci.
monitor	String	False		The monitor column for the table cmdb_ci.
sys_id [KEY]	String	False		The sys_id column for the table cmdb_ci.
operational_status	String	False		The operational_status column for the table cmdb_ci.
start_date	Datetime	False		The start_date column for the table cmdb_ci.
dns_domain	String	False		The dns_domain column for the table cmdb_ci.
category	String	False		The category column for the table cmdb_ci.
first_discovered	Datetime	False		The first_discovered column for the table cmdb_ci.
can_print	String	False		The can_print column for the table cmdb_ci.
comments	String	False		The comments column for the table cmdb_ci.
ip_address	String	False		The ip_address column for the table cmdb_ci.
schedule	String	False		The schedule column for the table cmdb_ci.

The ServiceNow table cmdb_metric.

Columns

Name	Type	ReadOnly	References	Description
sys_updated_by	String	False		The sys_updated_by column for the table cmdb_metric.
snc_mi	String	False		The snc_mi column for the table cmdb_metric.
sys_mod_count	String	False		The sys_mod_count column for the table cmdb_metric.
sys_created_by	String	False		The sys_created_by column for the table cmdb_metric.
sys_id [KEY]	String	False		The sys_id column for the table cmdb_metric.
sys_class_name	String	False		The sys_class_name column for the table cmdb_metric.
sys_created_on	Datetime	False		The sys_created_on column for the table cmdb_metric.
sys_updated_on	Datetime	False		The sys_updated_on column for the table cmdb_metric.

The ServiceNow table cmn_building.

Columns

Name	Type	ReadOnly	References	Description
sys_mod_count	String	False		The sys_mod_count column for the table cmn_building.
location	String	False		The location column for the table cmn_building.
sys_updated_by	String	False		The sys_updated_by column for the table cmn_building.
sys_updated_on	Datetime	False		The sys_updated_on column for the table cmn_building.
floors	String	False		The floors column for the table cmn_building.
notes	String	False		The notes column for the table cmn_building.
sys_created_by	String	False		The sys_created_by column for the table cmn_building.
name	String	False		The name column for the table cmn_building.
contact	String	False		The contact column for the table cmn_building.
sys_id [KEY]	String	False		The sys_id column for the table cmn_building.
sys_created_on	Datetime	False		The sys_created_on column for the table cmn_building.

The ServiceNow table cmn_context_help.

Columns

Name	Type	ReadOnly	References	Description
document	String	False		The document column for the table cmn_context_help.
plugin_id	String	False		The plugin_id column for the table cmn_context_help.
language	String	False		The language column for the table cmn_context_help.
type	String	False		The type column for the table cmn_context_help.
servicenow	String	False		The servicenow column for the table cmn_context_help.
url	String	False		The URL column for the table cmn_context_help.
active	String	False		The active column for the table cmn_context_help.
sys_id [KEY]	String	False		The sys_id column for the table cmn_context_help.
use_plugin_base_url	String	False		The use_plugin_base_url column for the table cmn_context_help.
sys_policy	String	False		The sys_policy column for the table cmn_context_help.
sys_replace_on_upgrade	String	False		The sys_replace_on_upgrade column for the table cmn_context_help.
sys_customer_update	String	False		The sys_customer_update column for the table cmn_context_help.
sys_scope	String	False		The sys_scope column for the table cmn_context_help.
sys_package	String	False		The sys_package column for the table cmn_context_help.
sys_updated_on	Datetime	False		The sys_updated_on column for the table cmn_context_help.
sys_created_on	Datetime	False		The sys_created_on column for the table cmn_context_help.
sys_name	String	False		The sys_name column for the table cmn_context_help.
sys_updated_by	String	False		The sys_updated_by column for the table cmn_context_help.
sys_created_by	String	False		The sys_created_by column for the table cmn_context_help.
sys_class_name	String	False		The sys_class_name column for the table cmn_context_help.
sys_mod_count	String	False		The sys_mod_count column for the table cmn_context_help.
sys_update_name	String	False		The sys_update_name column for the table cmn_context_help.

The ServiceNow table cmn_cost_center.

Columns

Name	Type	ReadOnly	References	Description
sys_created_by	String	False		The sys_created_by column for the table cmn_cost_center.
parent	String	False		The parent column for the table cmn_cost_center.
valid_to	Datetime	False		The valid_to column for the table cmn_cost_center.
sys_id [KEY]	String	False		The sys_id column for the table cmn_cost_center.
sys_created_on	Datetime	False		The sys_created_on column for the table cmn_cost_center.
name	String	False		The name column for the table cmn_cost_center.
manager	String	False		The manager column for the table cmn_cost_center.
location	String	False		The location column for the table cmn_cost_center.
sys_updated_by	String	False		The sys_updated_by column for the table cmn_cost_center.
sys_mod_count	String	False		The sys_mod_count column for the table cmn_cost_center.
code	String	False		The code column for the table cmn_cost_center.
account_number	String	False		The account_number column for the table cmn_cost_center.
valid_from	Datetime	False		The valid_from column for the table cmn_cost_center.
sys_updated_on	Datetime	False		The sys_updated_on column for the table cmn_cost_center.

The ServiceNow table cmn_department.

Columns

Name	Type	ReadOnly	References	Description
cost_center	String	False		The cost_center column for the table cmn_department.
sys_updated_on	Datetime	False		The sys_updated_on column for the table cmn_department.
dept_head	String	False		The dept_head column for the table cmn_department.
primary_contact	String	False		The primary_contact column for the table cmn_department.
head_count	String	False		The head_count column for the table cmn_department.
sys_created_by	String	False		The sys_created_by column for the table cmn_department.
name	String	False		The name column for the table cmn_department.
id	String	False		The ID column for the table cmn_department.
description	String	False		The description column for the table cmn_department.
sys_id [KEY]	String	False		The sys_id column for the table cmn_department.
sys_created_on	Datetime	False		The sys_created_on column for the table cmn_department.
sys_mod_count	String	False		The sys_mod_count column for the table cmn_department.
company	String	False		The company column for the table cmn_department.
parent	String	False		The parent column for the table cmn_department.
sys_updated_by	String	False		The sys_updated_by column for the table cmn_department.

The ServiceNow table cmn_location.

Columns

Name	Type	ReadOnly	References	Description
parent	String	False		The parent column for the table cmn_location.
lat_long_error	String	False		The lat_long_error column for the table cmn_location.
state	String	False		The state column for the table cmn_location.
sys_created_by	String	False		The sys_created_by column for the table cmn_location.
fax_phone	String	False		The fax_phone column for the table cmn_location.
phone_territory	String	False		The phone_territory column for the table cmn_location.
contact	String	False		The contact column for the table cmn_location.
full_name	String	False		The full_name column for the table cmn_location.
name	String	False		The name column for the table cmn_location.
sys_id [KEY]	String	False		The sys_id column for the table cmn_location.
zip	String	False		The zip column for the table cmn_location.
sys_created_on	Datetime	False		The sys_created_on column for the table cmn_location.
country	String	False		The country column for the table cmn_location.
sys_mod_count	String	False		The sys_mod_count column for the table cmn_location.
stock_room	String	False		The stock_room column for the table cmn_location.
latitude	String	False		The latitude column for the table cmn_location.
street	String	False		The street column for the table cmn_location.
sys_updated_by	String	False		The sys_updated_by column for the table cmn_location.
time_zone	String	False		The time_zone column for the table cmn_location.
city	String	False		The city column for the table cmn_location.
sys_updated_on	Datetime	False		The sys_updated_on column for the table cmn_location.
phone	String	False		The phone column for the table cmn_location.
company	String	False		The company column for the table cmn_location.
longitude	String	False		The longitude column for the table cmn_location.

The ServiceNow table cmn_map_page.

Columns

Name	Type	ReadOnly	References	Description
name	String	False		The name column for the table cmn_map_page.
center_latitude	String	False		The center_latitude column for the table cmn_map_page.
script	String	False		The script column for the table cmn_map_page.
type_selection	String	False		The type_selection column for the table cmn_map_page.
show_device_location	String	False		The show_device_location column for the table cmn_map_page.
controls_size	String	False		The controls_size column for the table cmn_map_page.
initial_zoom	String	False		The initial_zoom column for the table cmn_map_page.
center_address	String	False		The center_address column for the table cmn_map_page.
overview	String	False		The overview column for the table cmn_map_page.
sys_id [KEY]	String	False		The sys_id column for the table cmn_map_page.
type	String	False		The type column for the table cmn_map_page.
refresh_on_zoom	String	False		The refresh_on_zoom column for the table cmn_map_page.
suffix	String	False		The suffix column for the table cmn_map_page.
center_longitude	String	False		The center_longitude column for the table cmn_map_page.
sys_policy	String	False		The sys_policy column for the table cmn_map_page.
sys_replace_on_upgrade	String	False		The sys_replace_on_upgrade column for the table cmn_map_page.
sys_customer_update	String	False		The sys_customer_update column for the table cmn_map_page.
sys_scope	String	False		The sys_scope column for the table cmn_map_page.
sys_package	String	False		The sys_package column for the table cmn_map_page.
sys_updated_on	Datetime	False		The sys_updated_on column for the table cmn_map_page.
sys_created_on	Datetime	False		The sys_created_on column for the table cmn_map_page.
sys_name	String	False		The sys_name column for the table cmn_map_page.
sys_updated_by	String	False		The sys_updated_by column for the table cmn_map_page.
sys_created_by	String	False		The sys_created_by column for the table cmn_map_page.
sys_class_name	String	False		The sys_class_name column for the table cmn_map_page.
sys_mod_count	String	False		The sys_mod_count column for the table cmn_map_page.
sys_update_name	String	False		The sys_update_name column for the table cmn_map_page.