Connecting to GraphQL

For information on the available WHL files for supported environments, and how to install the appropriate file for your Python distribution, see Package Installation.

For information on the module to import, and how to configure the necessary connection properties in a connection string, see Establishing a Connection.

Other available connection properties can be used to configure other aspects of the connector capabilities.

Python Version Support

The CData Python Connector for GraphQL can be installed and used in Python 3.10 or newer.

GraphQL Version Support

The connector leverages the GraphQL API to enable bidirectional access to GraphQL.

See Also

• Using the Connector: Establish connections and query GraphQL through Python code.
• From SQLAlchemy: Use SQLAlchemy to establish a connection with dialect URL, and interact with GraphQL data using mapped classes and Sessions.

Dependencies

The Python connectors require that Python 3.10 or newer be installed.

Installation

The CData Python Connector for GraphQL is available as a WHL file for Windows, Linux, and Mac. Each connector is built using the Python 3.10 Stable ABI (indicated by the abi3 tag in the filename), so a single wheel supports any Python 3.10 or newer installation — there is no need to match your exact Python minor version. Use the "pip install" command with the appropriate WHL file for your platform.

Windows:

pip install cdata_graphql_connector-26.0.9655-cp310-abi3-win_amd64.whl

Linux:

pip install cdata_graphql_connector-26.0.9655-cp310-abi3-linux_x86_64.whl

macOS:

pip install cdata_graphql_connector-26.0.9655-cp310-abi3-macosx_12_0_arm64.whl

The macOS wheel supports arm64 (Apple Silicon) architectures only on macOS 12 and newer.

Regardless of the environment, certain distributions might require that the "pip3 install" command be used instead, to differentiate from a Python 2 distribution that might exist already. After installation, confirm whether the connector is successfully installed by running the "pip list" command. If "cdata_graphql_connector" is present in the list output by the command, then the installation was successful.

Upgrading

When upgrading, "pip install" does not automatically clean up old JRE files. To avoid leftover files that could cause JVM errors, uninstall the previous version before installing the new one.

Licensing

After the installation is complete, a separate step is needed to activate a license for the connector. Among the CData assets in the distribution's site packages, there is an install-license tool that activates this license. From within the distribution's site-packages folder, after navigating to the "cdata/installlic_graphql" folder, simply use a command like the below to activate the license. Omitting the <key> argument activates a trial license:

• Windows:
  

  ./install-license.exe <key>

• Linux / Mac:
  

  ./install-license.sh <key>

Sometimes, file access issues may cause pip to install the connector in a fallback file path that is not the python distribution's main or primary site-packages location. This can make it difficult to find where the connector was installed, and from there, the license activator. In that event, this python script below will print out the full file path of the connector's native file. This file will be stored in the mentioned cdata folder, from which the installlic_graphql folder is trivial to find:

import os
import cdata.graphql
path = os.path.abspath(cdata.graphql.__file__)
print(path)

Uninstallation

If the connector needs to be uninstalled for any reason, do so by running the pip uninstall command, as in the example below:

pip uninstall cdata-graphql-connector

The objects available within our connector are accessible from the "cdata.graphql" module. To use the module's objects directly:

1. Import the module as follows:
   

   import cdata.graphql as mod

2. To establish a connection string, call the connect() method from the connector object using an appropriate connection string, such as:
   

   mod.connect("AuthScheme=OAuth;OAuthVersion=2.0;CallbackURL=http://localhost:33333;OauthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessTokenURL=https://mysite.com/login/oauth/access_token;")

Connecting to GraphQL

Set the following to connect:

• URL: Specify the URL of the GraphQL service, for example https://api.example.com/graphql.
• Location: Set this to the file path containing any custom defined schemas for the GraphQL service.

Authenticating to GraphQL

The driver supports the following types of authentication:

• Basic
• OAuth 1.0 & 2.0
• OAuthPKCE
• AWS Cognito Credentials:
  • AwsCognitoSrp
  • AwsCognitoBasic

Basic

Set AuthScheme to Basic. You must specify the User and Password of the GraphQL service.

OAuth

In all OAuth flows, you must set AuthScheme to OAuth and OAuthVersion to 1.0 or 2.0. The following sections assume you have done so.

Desktop Applications

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

• OAuthRequestTokenURL: Required for OAuth 1.0. This is the URL where the application makes a request for the request token.
• OAuthAuthorizationURL: Required for OAuth 1.0 and 2.0. This is the URL where the user logs into the service and grants permissions to the application. In OAuth 1.0 if permissions are granted the request token is authorized.
• OAuthAccessTokenURL: Required for OAuth 1.0 and 2.0. This is the URL where the request for the access token is made. In OAuth 1.0 the authorized request token is exchanged for the access token.
• OAuthRefreshTokenURL: Required for OAuth 2.0. In OAuth 2.0 this is the URL where the refresh token is exchanged for a new access token when the old one expires. Note that for your data source this may be the same as the access token URL.
• OAuthClientId: Set this to the client Id in your application settings. This is also called the consumer key.
• OAuthClientSecret: Set this to the client secret in your application settings. This is also called the consumer secret.
• CallbackURL: Set this to http://localhost:33333. If you specified a redirect URL in your application settings, this must match.
• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the access token in the connection string.

When you connect, the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

1. Extracts the access token from the callback URL and authenticates requests.
2. Refreshes the access token when it expires.
3. Saves OAuth values in OAuthSettingsLocation. These values persist across connections.

Web Applications

When connecting via a Web application, or if the connector is not authorized to open a browser window, use the provided stored procedures to get and manage the OAuth token values.

Note: You can extend the stored procedure schemas to set defaults for the OAuth URLs or other connection string properties. See Operations for a guide.

Set Up the OAuth Flow

Provide the OAuth URLs to authenticate in the Web flow.

• OAuthRequestTokenURL: Required for OAuth 1.0. This is the URL where the application makes a request for the request token.
• OAuthAuthorizationURL: Required for OAuth 1.0 and 2.0. This is the URL where the user logs into the service and grants permissions to the application. In OAuth 1.0 if permissions are granted the request token is authorized.
• OAuthAccessTokenURL: Required for OAuth 1.0 and 2.0. This is the URL where the request for the access token is made. In OAuth 1.0 the authorized request token is exchanged for the access token.
• OAuthRefreshTokenURL: Required for OAuth 2.0. In OAuth 2.0 this is the URL where the refresh token is exchanged for a new access token when the old one expires. Note that for your data source this may be the same as the access token URL.

Get an Access Token

In addition to the OAuth URLs, set the following additional connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the client Id in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings.

You can then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the AuthMode input to WEB and set the CallbackURL input to the Redirect URI you specified in your application settings. The stored procedure returns the URL to the OAuth endpoint.
2. Log in and authorize the application. You are redirected back to the callback URL.

3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB.

   In OAuth 1.0, set the Verifier input to the "oauth_verifier" parameter. Extract the verifier code from the callback URL. Additionally, set the AuthToken and AuthSecret to the values returned by GetOAuthAccessToken.

   In OAuth 2.0, set the Verifier input to the "code" parameter in the query string of the callback URL.

Connect to Data and Refresh the Token

The OAuthAccessToken returned by GetOAuthAccessToken has a limited lifetime. To automatically refresh the token, set the following on the first data connection.

OAuth Endpoints

• OAuthRequestTokenURL
• OAuthAuthorizationURL
• OAuthAccessTokenURL
• OAuthRefreshTokenURL

OAuth Tokens and Keys

• OAuthClientId
• OAuthClientSecret
• OAuthRefreshToken
• OAuthAccessToken

Alternatively, use the RefreshOAuthAccessToken stored procedure to manually refresh the token.

Initiate OAuth

• InitiateOAuth: Set this to REFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where the connector saves the OAuth values. These values persist across connections.

On subsequent data connections, set the following:

• InitiateOAuth
• OAuthSettingsLocation
• OAuthRequestTokenURL
• OAuthAuthorizationURL
• OAuthAccessTokenURL
• OAuthRefreshTokenURL

Headless Machines

To create GraphQL data sources on headless servers or other machines on which the connector cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the connector on a machine with an internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow, as described in "Transfer OAuth Settings" below.
2. Then configure the connector to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

Set the following properties on the headless machine:

• InitiateOAuth: Set this to OFF.
• OAuthClientId: Set this to the application Id in your application settings.
• OAuthClientSecret: Set this to the application secret in your application settings.

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 application settings.
2. Save the value of the returned AuthToken and AuthKey if OAuthVersion is set to 1.0. They are used in the next step.
3. Open the returned URL in a browser. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
4. Save the value of the verifier code. Later, you must set this in the OAuthVerifier connection property.

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

• OAuthRequestTokenURL: Required for OAuth 1.0. In OAuth 1.0 this is the URL where the application makes a request for the request token.
• OAuthAuthorizationURL: Required for OAuth 1.0 and 2.0. This is the URL where the user logs into the service and grants permissions to the application. In OAuth 1.0 if permissions are granted the request token is authorized.
• OAuthAccessTokenURL: Required for OAuth 1.0 and 2.0. This is the URL where the request for the access token is made. In OAuth 1.0 the authorized request token is exchanged for the access token.
• OAuthRefreshTokenURL: Required for OAuth 2.0. In OAuth 2.0 this is the URL where the refresh token is exchanged for a new access token when the old one expires. Note that for your data source this may be the same as the access token URL.
• OAuthClientId: Set this to the client Id in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings.
• CallbackURL: Set this to http://localhost:33333. If you specified a redirect URL in your application settings, this must match.
• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the access token in the connection string.

Connect to Data

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

• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the provider to enable the automatic refreshing of the access token.
• InitiateOAuth: Set this to REFRESH.

Option 2: Transfer OAuth Settings

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

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

• OAuthSettingsLocation: Set this to a writable location.
• InitiateOAuth: Set this to GETANDREFRESH.
• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.
• CallbackURL: Set this to the Callback URL in your application settings.

Test the connection to authenticate. The resulting authentication values are written, encrypted, to the location specified by OAuthSettingsLocation. After 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.
• OAuthSettingsLocation: Set this to the location of your OAuth settings file. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

OAuthPKCE

NOTE:OAuth Proof Key for Code Exchange (PKCE) is an extension to the OAuth 2.0 Authorization Code flow.

Desktop Applications

After setting the following, you are ready to connect:

• AuthScheme: Set this to OAuthPKCE.
• InitiateOAuth: Set this to GETANDREFRESH to avoid making the OAuth exchange manually and manually setting the access token in the connection string.
• OAuthClientId: Set this to the client Id generated when creating your OAuth application on the GraphQL service.
• OAuthAuthorizationURL: Set this to the authorization URL for the GraphQL service. This is the URL where the user logs into the service and grants permissions to the OAuth application, for example https://api.example.com/authorize.
• OAuthAccessTokenURL: Set this to the access token URL for the GraphQL service. This is the URL where the request for the access token is made, for example https://api.example.com/token.
• OAuthRefreshTokenURL: Set this to the refresh token URL for the GraphQL service. This is the URL where the refresh token is exchanged for a new access token when the old one expires. Note that for your data source this may be the same as the OAuthAccessTokenURL.

When you connect, the connector opens the OAuth authorization endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

1. Extracts the authorization code from the callback URL.
2. Exchanges the authorization code for an access and refresh token.
3. Refreshes the access token when it expires.
4. Saves OAuth values in OAuthSettingsLocation. These values persist across connections.

Web Applications

When connecting via a Web application, or if the connector is not authorized to open a browser window, use the provided stored procedures to get and manage the OAuth token values.

Note: You can extend the stored procedure schemas to set defaults for the OAuth URLs or other connection string properties. See Operations for a guide.

Set Up the OAuth Flow

Provide the OAuth URLs to authenticate in the Web flow:

• OAuthAuthorizationURL: This is the URL where the user logs into the service and grants permissions to the OAuth application.
• OAuthAccessTokenURL:This is the URL where the request for the access token is made.
• OAuthRefreshTokenURL: This is the URL where the refresh token is exchanged for a new access token when the old one expires. Note that for your data source this may be the same as the access token URL.

Get an Access Token

In addition to the OAuth URLs, set OAuthClientId to the client Id in your application settings to obtain the OAuthAccessToken.

You can then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Redirect URI you specified in your application settings. The stored procedure returns the URL to the GraphQL service's authorization URL and the PKCEVerifier. The PKCEVerifier is a randomly generated value used for security reasons with OAuthPKCE. Save the PKCEVerifier; you need it as an input when executing the GetOAuthAccessToken stored procedure.
2. Log in and authorize the application. You are redirected back to the callback URL.

3. Call the GetOAuthAccessToken stored procedure.

   Set the AuthMode input to WEB.

   Set the Verifier input to the value of the "code" parameter in the query string of the callback URL.

   Set the PKCEVerifier input to the value generated after calling the GetOAuthAuthorizationURL stored procedure.

Connect to Data and Refresh the Token

The OAuthAccessToken returned by GetOAuthAccessToken has a limited lifetime. To automatically refresh the token, set the following on the first data connection.

OAuth Endpoints

• OAuthAuthorizationURL
• OAuthAccessTokenURL
• OAuthRefreshTokenURL

OAuth Tokens and Keys

• OAuthClientId
• OAuthRefreshToken
• OAuthAccessToken

Alternatively, use the RefreshOAuthAccessToken stored procedure to manually refresh the token.

Initiate OAuth

• InitiateOAuth: Set this to REFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where the connector saves the OAuth values. These values persist across connections.

On subsequent data connections, set the following:

• InitiateOAuth
• OAuthSettingsLocation
• OAuthAuthorizationURL
• OAuthAccessTokenURL
• OAuthRefreshTokenURL

AWS Cognito Credentials

If you want to use the connector with a user registered in a User Pool in AWS Cognito, set the following properties to authenticate:

• AuthScheme: Set this to AwsCognitoSrp (recommended). You can also use AwsCognitoBasic.
• AWSCognitoRegion: Set this to the region of the User Pool.
• AWSUserPoolId: Set this to the User Pool Id.
• AWSUserPoolClientAppId: Set this to the User Pool Client App Id.
• AWSUserPoolClientAppSecret: Set this to the User Pool Client Secret.
• AWSIdentityPoolId: Set this to the Identity Pool Id of the Identity Pool that is linked with the User Pool.
• User: Set this to the username of the user registered in the User Pool.
• Password: Set this to the password of the user registered in the User Pool.

Java Native Interface (JNI) is a standard programming interface for writing Java native methods and embedding the Java virtual machine into native applications.

The connector leverages the JNI for improved performance on Mac and Linux.

Configure the Config INI File

The Linux and Mac editions of the GraphQL python connector are configured with an ini file. This file is used to set several parameters, including JNI behavior. This file is to be located in:

{path_to_distribution_site-packages}/cdata/config.ini

Ensure that any configuration properties you set in the ini file fall under the following section name (adjust the 311 number if you are using an different python version from 3.11):

• For Linux:
  

  [graphql.cpython-311-x86_64-linux-gnu.so]

• For Mac:
  

  [graphql.cpython-311-darwin.so]

Configure the JNI connector's behavior by editing the properties in the connector's config.ini file. The connector can be configured as follows:

• LOGFILE: Set this the same way as the CDATA_LOGFILE envrionment variable below.
• JAVA_HOME: Configure the path to the JVM library location used to launch the JVM.
• CLASS_PATH: Use a colon-separated list to configure the paths to the third-party jar libraries.

Configure Environment Variables

Additionally, set the following environment variables:

• CDATA_JAVA_HOME: Configure the path to the JVM library location used to launch the JVM.
• CDATA_JVM_OPTIONS: Place JVM options here.
• CDATA_LOGFILE: Set this in the following scheme: <SCHEME>://<TAG>[|<LEVEL>]
  • SCHEME: The options are STDOUT, FILE.
    • STDOUT: Both the native wrapper and odbc core log into stdout. The Logfile and Verbosity properties can override the behavior of ODBC core.
    • FILE: The native wrapper logs into <FILENAME> while the odbc core logs into <FILENAME>.driver.log. The Logfile and Verbosity properties can override the behavior of ODBC core.
  • TAG
    • For STDOUT, set this to 1. For FILE, set this to the filename.
  • LEVEL
    • Set to one of: FATAL | ERROR | WARNING | INFO | DEBUG

The following are some examples of this syntax:

• STDOUT://1|DEBUG
• FILE:///tmp/my_py.log|DEBUG

Custom Logger

The Python connector supports a custom logging mechanism for redirecting log output to any destination, such as a cloud storage service or logging framework. Use setCustomLoggerFactory() to register a factory function that creates a logger instance for each connection.

The factory function receives the context string from the Logfile connection property (the portion after CUSTOM://) and must return an object with a writeLog(verbosity, message) method.

To enable custom logging:

1. Call setCustomLoggerFactory() with your factory function before opening connections.
2. Set Logfile to CUSTOM:// followed by a context string to identify the connection.
3. Set Verbosity to the desired log level.

The following example demonstrates a custom logger factory that creates a separate logger instance per connection:

import cdata.graphql as mod
import time

class MyLogger:
    def __init__(self, loggerId):
        self.loggerId = loggerId
    def writeLog(self, verbosity, message):
        print("[MyLogger " + self.loggerId + "] " + message)

def createLogger(context):
    return MyLogger(context[len("MyLoggerId="):])

mod.setCustomLoggerFactory(createLogger)

conn1 = mod.connect("...;Logfile=CUSTOM://MyLoggerId=1;Verbosity=2;")
# do something with conn1
time.sleep(1)  # Wait for logs to flush from conn1

conn2 = mod.connect("...;Logfile=CUSTOM://MyLoggerId=2;Verbosity=2;")
# do something with conn2
time.sleep(1)  # Wait for logs to flush from conn2

General Changes

Date	Version	Source	Category	Type	Description
2026-05-27	26.0.9643	General	Connection	Removed	Removed the deprecated ReplaceInvalidTypesWithNull connection property. Use the ReplaceInvalidValuesWithNull property instead.
2026-05-22	26.0.9638	Python		Removed	Remove support for Intel x64 architecture on macOS
2026-05-07	26.0.9623	General	Data Model	Added	Added the ColumnCapabilities column to the sys_tablecolumns system table. This column is a bit mask denoting the column's write capabilities.
2026-05-07	26.0.9623	Python		Changed	Updated embedded JRE to jre-17.0.19+10 (Linux x64 / MacOs x64).
2026-04-23	26.0.9609	GraphQL	Metadata	Changed	Changed all instances of `other:relativePath` to `other:inputPath` for stored procedure input parameters.
2026-04-15	26.0.9601	General	Query Exec	Changed	String comparisons using GREATER, LESS, and CONTAINS operators are now case-insensitive by default.
2026-04-01	26.0.9587	GraphQL		Removed	Removed the BatchSize connection property.
2026-03-25	25.0.9580	GraphQL	Connection	Added	Added support for handling recursive (self-referencing) types during schema exploration by introducing the ExpandTypeRecursion connection property. This property includes three modes: NEVER (default, stops on previously visited types), DIRECT (allows a single level of direct self-reference and then stops exploration), and ALWAYS (fully expands recursion within depth limits).
2026-01-19	25.0.9515	GraphQL		Changed	Changed the GraphQL schema generation to include complete filter-to-path (possiblePaths) metadata for all supported filters, enabling more accurate server-side query optimization. Changed the possible path selection logic to consider all supported filters instead of only filters marked with isPathFilter.
2026-01-13	25.0.9509	General		Added	Added support for the REGEXP_REPLACE() string function.
2025-12-21	25.0.9486	Python		Added	Added support for custom loggers in Python connectors on Linux and macOS.
2025-12-05	25.0.9470	General		Added	Added support for the INSERT INTO SELECT statement, with driver-side execution for providers that do not support the operation natively.
2025-10-30	25.0.9434	Python		Changed	Updated embedded JRE to jre-17.0.17+10 (Linux x64 / MacOs x64).
2025-10-06	25.0.9410	General		Added	Support for parsing datetime formats using ".S" and ",S" for milliseconds and nanoseconds.
2025-09-12	25.0.9386	General		Added	Added the IsInsertable, IsUpdateable, and IsDeleteable columns to the sys_tables table.
2025-09-10	25.0.9384	General		Changed	All columns in statically defined Views are now reported as read-only.
2025-09-03	25.0.9377	General		Changed	Corrected the behavior when IN criteria with NULL values are used in the projection part. It now returns NULL instead of 0. For example, "NULL IN (1,2)" returns "NULL".
2025-09-01	25.0.9375	General		Added	Added support for using the CAST function with infinity values. This function can cast "inf" and "-inf" to DOUBLE, FLOAT, or REAL.
2025-08-21	25.0.9364	General		Changed	Report behavior change: Fixed inconsistent string value comparisons in non-table queries. For example, "SELECT 'A' = 'a'" previously returned false, but it now returns true.
2025-08-13	25.0.9356	General		Changed	Changed the maximum number of pages held in memory from 15 to 5 for the page providers to decrease heap usage.
2025-07-23	25.0.9335	GraphQL		Changed	Changed the default value of the OAuthVersion connection property from 'Disabled' to '2.0'.
2025-07-23	25.0.9335	GraphQL		Removed	Removed 'Disable' as a valid value from the OAuthVersion connection property.
2025-07-18	25.0.9330	GraphQL		Removed	Removed the OAuthGrantType property. The grant type is now set implicitly through the 'AuthScheme' property. For example, you can use the 'OAuthPassword' AuthScheme instead of AuthScheme=OAuth with OAuthGrantType=Password.
2025-07-10	25.0.9322	GraphQL		Added	Added the Scope connection property.
2025-07-07	25.0.9319	Python		Removed	Removed the 32-bit version of Windows Python.
2025-07-02	25.0.9314	Python		Removed	Removed support for Python 3.9.
2025-06-27	25.0.9309	GraphQL		Added	Added support for GraphQL interfaces and unions in generated schema files. Added ExposeAbstractTypes connection property.
2025-06-25	25.0.9307	General		Removed	Removed the "ADLS Gen 1" value from the ConnectionType property.
2025-06-25	25.0.9307	Python		Added	Added support for Python 3.13 in Windows, Linux, and Mac editions.
2025-06-25	25.0.9307	Python		Removed	Removed support for Python 3.8 as it is no longer supported.
2025-06-20	25.0.9302	General		Added	Created the following functions: TEXT_ENCODE: encodes a string into a different charset (UTF8 → UTF7 and returns a binary array as the result). TEXT_DECODE: takes a binary array and decodes it back into a string when provided the charset. BASE64_ENCODE: takes a binary array and encodes it as a base64 string (varchar). BASE64_DECODE: takes a base 64-encoded string and decodes it into a binary array.
2025-06-18	25.0.9300	General		Changed	The internal code for exception handling has been refactored. Exception messages returned during certain error conditions may now have different wording or formatting.
2025-05-27	25.0.9278	General		Removed	Removed the "Proprietary" enum option from ProxyAuthscheme.
2025-05-12	25.0.9263	Python		Changed	Updated embedded JRE to jre-17.0.15+6 (Linux x64 / MacOS x64) and jre-17.0.15+6 (MacOS aarch64).
2025-02-15	24.0.9177	General		Added	Added support for converting unsigned integer types to the nearest signed data type that has enough precision to hold the unsigned value.This is done for JDBC only because it does not have support for unsigned data types.
2024-11-27	24.0.9097	General		Added	Added ThreadId to LogModule output. Logfile lines now include the Thread ID associated with the action being performed.
2024-08-28	24.0.9006	GraphQL		Removed	Removed the ExposeRootObjects connection property.
2024-08-28	24.0.9006	GraphQL		Added	Added the ExposeObjectTables connection property. This property exposes GraphQL objects that meet certain conditions as tables. See its connection property page for more information. Added support for pagination for arguments nested in input objects.
2024-08-28	24.0.9006	GraphQL		Changed	Changed the names of arguments that the driver expects when customizing the other:Pagination attribute of the rsb:info element in schema files (.rsd). "cursorName" is now "cursorPath", "offsetArgumentName" is now "offsetArgumentPath", and "pageSizeArrgumentName" is now "pageSizeArgumentPath".
2024-06-05	24.0.8922	Python		Added	Added support for Python 3.12.
2024-05-09	24.0.8895	General		Changed	The ROUND function previously did not accept negative precision values. That feature has now been restored.
2024-03-15	23.0.8840	General		Added	Created a new SQL function called STRING_COMPARE that provides java's String.compare() ability to SQL queries. Returns a number representative of the compared value of two strings
2023-11-29	23.0.8733	General		Changed	The ROUND function doesn't accept the negative precision values anymore.
2023-11-29	23.0.8733	General		Changed	The returning types of the FDMonth, FDQuarter, FDWeek, LDMonth, LDQuarter, LDWeek functions are changed from Timestamp to Date. The return type of the ABS function will be consistent with the parameter value type.
2023-11-28	23.0.8732	General		Added	Added the HMACSHA256 formatter to allow for secrets to be decoded if it is in base64 format
2023-08-29	23.0.8641	Python		Added	Added support for SQLAlchemy 2.0.
2023-06-20	23.0.8571	General		Added	Added the new sys_lastresultinfo system table.
2023-05-19	23.0.8539	Python		Added	Added support for Python 3.11 on Windows, Linux and Mac.
2023-05-16	23.0.8536	Python		Removed	Removed support for Python 3.7 on Windows and Linux
2023-04-27	23.0.8517	GraphQL		Changed	Columns with a GraphQL type of "Float" - defined as a "signed double-precision finite value" - will be pushed having a data type of "double".
2023-04-25	23.0.8515	General		Removed	Removed support for the SELECT INTO CSV statement. The core code doesn't support it anymore.
2023-01-24	22.0.8424	GraphQL		Added	Added support for using PKCE with the authorization code OAuth grant type.
2022-12-14	22.0.8383	General		Changed	Added the Default column to the sys_procedureparameters table.
2022-11-16	22.0.8355	GraphQL		Added	Added support for flattening arguments of type "INPUT_OBJECT". The depth can be controlled by the ExpandArgumentsDepth connection property.
2022-11-16	22.0.8355	GraphQL		Changed	Optimized metadata discovery. If a specific argument is the plural form ("subregions") of a column ("subregion") it will not be pushed anymore as a separate column but it will be used as a server-side filter for the column ("subregion").
2022-11-15	22.0.8354	Python		Changed	Updated embedded JRE to jre8u345-b01(Linux x64 / MacOS x64) and jre-17.0.5+8(MacOS aarch64).
2022-11-03	22.0.8342	GraphQL		Added	Added support for custom scalar types as pagination arguments. Added "cursorType", "offsetArgumentType", and "pageSizeArgumentType" as extra customizable table info in generated schema files.
2022-09-30	22.0.8308	General		Changed	Added the IsPath column to the sys_procedureparameters table.
2022-05-18	22.0.8173	Python		Added	Added support for Python 3.10 on Windows, Linux, and Mac Added support for Python 3.9 on Mac Added support for Mac M1
2022-05-18	22.0.8173	Python		Removed	Removed support for Python 3.6 on Windows and Linux
2022-04-05	21.0.8130	GraphQL		Added	Added the ExposeDynamicProcedures connection property. Added the ExpandTemporaryTablesDepth connection property.
2022-03-01	21.0.8095	GraphQL		Added	Added support for the AwsCognitoBasic and AwsCognitoSrp authentication methods. They can be used when connecting to the AWS AppSync service.
2022-02-24	21.0.8090	GraphQL		Added	Added support for executing the IN operation server side when the argument type is of type LIST. This will be detected automatically based on the introspection schema.To add support for the IN operator in previously generated scripts you must modify the "other:filter" extra info from "other:filter=id:=" to "other:filter=id:=,IN".
2022-02-09	21.0.8075	GraphQL		Added	Added support for Offset pagination on GraphQL objects of type LIST.
2022-02-09	21.0.8075	GraphQL		Changed	Renamed the "other:pageInfoObjects" table extra info to "other:paginationObjects".
2022-02-09	21.0.8075	GraphQL		Removed	Removed the "other:paginationType" table extra info.
2022-01-25	21.0.8060	GraphQL		Added	Added support for exposing tables and columns dynamically based on the INTROSPECTION mechanism of the GraphQL service. The metadata output can be controlled with the following properties:1) ExpandTablesDepth2) ExpandColumnsDepth3) IncludeDeprecatedMetadata Added support for exposing GraphQL mutations as stored procedures. If a mutation has fields of type LIST, we will push a temporary table foreach of them. These tables will be of type "TEMPORARY TABLE" and are listed when selecting from "sys_tables". Example of a complex mutation: ```statement.execute("INSERT INTO productCreate_metafields#TEMP(namespace,key,value,type) VALUES('MRproductInfo','ALU','449788022','string')");statement.execute("INSERT INTO productCreate_variants#TEMP(RowId,price,sku,inventoryManagement,weightUnit,weight,options,metafields,inventoryQuantities) VALUES(1,'39.99','38536314-0acb-4d3f-b8ff-a0f2014d2c75','SHOPIFY','POUNDS',1,'L,XL,XXL','productCreate_variants_metafields#TEMP','productCreate_variants_inventoryQuantities#TEMP')");statement.execute("INSERT INTO productCreate_variants_metafields#TEMP(ParentId,namespace,key,value,type) VALUES('1','MRproductInfo','ALU','449788022-M-','string')");statement.execute("INSERT INTO productCreate_variants_metafields#TEMP(ParentId,namespace,key,value,type) VALUES('1','MRproductInfo','ItemNumber','400000881201','string')");statement.execute("INSERT INTO productCreate_variants_inventoryQuantities#TEMP(ParentId,locationId,availableQuantity) VALUES('1','gid://shopify/Location/1448223412312389678762340087',5)");statement.execute("INSERT INTO productCreate_media#TEMP(originalSource,alt,mediaContentType) VALUES('https://sampleUrl.com','Magic Shoes','IMAGE')");statement.execute("EXECUTE productCreate title='Shoes - 449788022', descriptionHtml=' - MEN\\'S SHOES 42-MENS L/S TEES'," + " productType='Staging', vendor='Shoes Company', published='false', options='size,width'," + " metafields='productCreate_metafields#TEMP', variants='productCreate_variants#TEMP', media='productCreate_media#TEMP'");```
2021-10-25	21.0.7968	GraphQL		Added	Added support for aggregate columns. The "other:fragment" extra info is required to specify the desired group of fields. Ex: `attr name="ColumnValues" xs:type="string" other:relativePath="column_values" desc="Column values." other:fragment="fragment ItemColumnValues on ColumnValue { id \\r\\n value }"`
2021-09-02	21.0.7915	General		Added	Added support for the STRING_SPLIT table-valued function in the CROSS APPLY clause.
2021-08-07	21.0.7889	General		Changed	Added the KeySeq column to the sys_foreignkeys table.
2021-08-06	21.0.7888	General		Changed	Added the new sys_primarykeys system table.
2021-07-23	21.0.7874	General		Changed	Updated the Literal Function Names for relative date/datetime functions. Previously, relative date/datetime functions resolved to a different value when used in the projection as opposed to the predicate. For example: SELECT LAST_MONTH() AS lm, Col FROM Table WHERE Col > LAST_MONTH(). Formerly, the two LAST_MONTH() methods would resolve to different datetimes. Now, they will match. As a replacement for the previous behavior, the relative date/datetime functions in the criteria may have an 'L' appended to them. For example: WHERE col > L_LAST_MONTH(). This will continue to resolve to the same values that were previously calculated in the criteria. Note that the "L_" prefix will only work in the predicate - it not available for the projection.
2021-04-23	21.0.7783	General		Changed	Updated how display sizes are determined for varchar primary key and foreign key columns so they will match the reported length of the column.
2021-04-16	21.0.7776	General		Changed	Reduced the length to 255 for varchar primary key and foreign key columns.
2021-04-16	21.0.7776	General		Changed	Updated implicit and metadata caching to improve performance and support for multiple connections. Old metadata caches are not compatible - you need to generate new metadata caches if you are currently using CacheMetadata.
2021-04-16	21.0.7776	General		Changed	Updated index naming convention to avoid duplicates.

This section shows how to control the various schemas that the connector offers to bridge the gap between relational SQL and GraphQL services.

Schema Introspection

GraphQL services offer a introspection query service which the connector can use to obtain view and column names.

All SCALAR mutation fields are exposed directly, and all object fields are expanded.

Mutations

The connector will automatically scan for available Using Mutations. Given that there is no method provided by GraphQL for determining which mutations can be used for each table, each mutation is exposed as a stored procedure.

LIST fields are exposed as temporary tables (GraphQL tables of type TEMPORARY_TABLE). The discovered temporary tables can be obtained by querying the sys_tables and sys_tablecolumns system tables.

Customizing Schemas

Operations details the process for configuring custom schema files. Setting up these custom schema files is a required step in establishing a connection to GraphQL data.

System Tables

See System Tables to query the current table metadata.

Stored Procedures

The connector makes use of Stored Procedures to perform various functions, such as obtaining and refreshing OAuth tokens.

By default, the connector will automatically read metadata from GraphQL.

Schema Introspection

GraphQL services offer a introspection query service which the connector can use to obtain view and column names.

A GraphQL introspection query service has a query object at its root. Other objects are nested into the root query object, which can in turn have their own nested objects.

The connector reads LIST or Relay Connection type objects as views. If a field is SCALAR, it's read as a column, and if a field is a simple OBJECT, it is expanded.

Set the metadata introspection depth as follows:

• ExpandTablesDepth: Setting to 0 will read only from the root query object. In scenarios where lists are nested in other lists, set the ExpandTablesDepth to the number of nested layers deep to be scanned.
• ExpandColumnsDepth: This determines how many more layers deep (starting from the ExpandTablesDepth) to expand objects to include fields from their nested child objects.

The connector will automatically scan for available mutations. Given that there is no method provided by GraphQL for determining which mutations can be used for each table, each mutation is exposed as a stored procedure. This replaces the traditional use of INSERT, UPDATE, and DELETE SQL statements when working with GraphQL.

All SCALAR mutation fields are exposed directly, and all object fields are expanded.

LIST fields are exposed as temporary tables (GraphQL tables of type TEMPORARY_TABLE). The discovered temporary tables can be obtained by querying the sys_tables and sys_tablecolumns system tables. These tables contain a RowId and ParentId field to denote the row and housing (parent) table of a given child table.

An example of a mutation is productCreate. Invoke mutations as a stored procedure after first loading the relevant child tables needed for the operation:

INSERT INTO productCreate_metafields#TEMP (namespace,key,value,type) VALUES ('MRproductInfo','ALU','449788022','string')

INSERT INTO productCreate_variants#TEMP (RowId,price,sku,inventoryManagement,weightUnit,weight,options,metafields,inventoryQuantities) VALUES (1,'39.99','38536314-0acb-4d3f-b8ff-a0f2014d2c75','SHOPIFY','POUNDS',1,'L,XL,XXL','productCreate_variants_metafields','productCreate_variants_inventoryQuantities')

INSERT INTO productCreate_variants_metafields#TEMP (ParentId,namespace,key,value,type) VALUES ('1','MRproductInfo','ALU','449788022-M-','string')

INSERT INTO productCreate_variants_metafields#TEMP (ParentId,namespace,key,value,type) VALUES ('1','MRproductInfo','ItemNumber','400000881201','string')

INSERT INTO productCreate_variants_inventoryQuantities#TEMP (ParentId,locationId,availableQuantity) VALUES ('1','gid://shopify/Location/1448280087',5)

INSERT INTO productCreate_media#TEMP (originalSource,alt,mediaContentType) VALUES ('https://static.nike.com/a/images/t_PDP_1280_v1/f_auto,q_auto:eco/qwqfyddzikcgc4ozwigp/revolution-5-road-running-shoes-szF7CS.png','Magic Shoes','IMAGE')

EXECUTE productCreate title='NIKE  - 449788022', descriptionHtml='MEN''S SHOES 42-MENS L/S TEES',productType='Staging', vendor='NIKE', published='false', options='size,width',metafields='productCreate_metafields#TEMP', variants='productCreate_variants#TEMP', media='productCreate_media#TEMP'

Custom schemas are defined in configuration files. This chapter outlines the structure of these files.

Note: The GenerateSchemaFiles property enables you to persist table metadata in static schema files that are easy to customize (to persist your changes to column data types, for example). Because this connector retrieves full metadata at once, setting this property to either "OnStart" or "OnUse" will generate schema files for all tables in your database as soon as metadata is triggered (either upon connection or during your first SELECT query). It is also possible to create a specific schema file for a table using the CreateSchema stored procedure.

Editing Schema Files

Tables and views are defined by authoring schema files in APIScript. APIScript is a simple configuration language that allows you to define the columns and the behavior of the table. It also has built-in Operations that enable you to process GraphQL. In addition to these data processing primitives, APIScript is a full-featured language with constructs for conditionals, looping, etc. However, as shown by the example schema, for most table definitions you will not need to use these features.

Example Schema

Below is a fully functional table schema that models the Labels table and contains all the components you will need to execute SQL to GraphQL data sources.

You can find more information on each of the components of a schema in Column Definitions, SELECT Execution.

 <rsb:script xmlns:rsb="http://apiscript.com/ns?v1" xmlns:xs="http://www.cdata.com/ns/rsbscript/2" xmlns:other="http://apiscript.com/ns?v1"> 
   <rsb:info title="Labels" desc="Lists information about the different labels you can apply on an issue." other:possiblePaths="{'UserLogin':{'RepositoryName':{'path':'/repository/labels/edges/node','Name':{'path':'/repository/label'}}}}" other:paginationObjects="{'labels':{'cursorPath':'after','cursorType':'String','pageSizeArgumentPath':'first','pageSizeArgumentType':'Int','depth':'2','paginationType':'Cursor','isConnection':'True','pageInfo':['endCursor','hasNextPage','hasPreviousPage','startCursor']}}">
     <attr   name="Id"               xs:type="string"     key="true"   other:relativePath="id"             desc="The ID of the label."                                                                                                                                                                                                                                                                                                                     /> 
     <attr   name="RepositoryName"   xs:type="string"                            other:relativePath="name"           desc="The name of the repository."                                     other:filter="name:="    other:argumenttype="String!"    other:depth="1"                                           references="Repositories.Name"                                                                                /> 
     <attr   name="UserLogin"        xs:type="string"                                                                          desc="The login name of the user."                                     other:filter="owner:="   other:argumenttype="String!"    other:depth="1"                                           references="Users.Login"            other:mirror="true"         other:canBeSliced="true"  /> 
     <attr   name="Color"            xs:type="string"                            other:relativePath="color"          desc="Identifies the label color."                                                                                                                                                                                                                                                                                                              /> 
     <attr   name="CreatedAt"        xs:type="datetime"                          other:relativePath="createdAt"      desc="Identifies the date and time when the label was created."                                                                                                                  other:orderby="CREATED_AT"                                                                                                                           /> 
     <attr   name="Description"      xs:type="string"                            other:relativePath="description"    desc="A brief description of this label."                                                                                                                                                                                                                                                                                                       /> 
     <attr   name="IsDefault"        xs:type="boolean"                           other:relativePath="isDefault"      desc="Indicates whether or not this is a default label."                                                                                                                                                                                                                                                                                        /> 
     <attr   name="Name"             xs:type="string"                            other:relativePath="name"           desc="Identifies the label name."                                      other:filter="name:="    other:argumenttype="String!"                                 other:orderby="NAME"                                                                                                                                 />
     <attr   name="ResourcePath"     xs:type="string"                            other:relativePath="resourcePath"   desc="The HTTP path for this label."                                                                                                                                                                                                                                                                                                            /> 
     <attr   name="UpdatedAt"        xs:type="datetime"                          other:relativePath="updatedAt"      desc="Identifies the date and time when the label was last updated."                                                                                                                                                                                                                                                                            /> 
     <attr   name="Url"              xs:type="string"                            other:relativePath="url"            desc="The HTTP URL for this label."                                                                                                                                                                                                                                                                                                             /> 
   </rsb:info> 

   <rsb:script method="GET"> 
     <rsb:push op="graphqladoSelect" /> 
   </rsb:script> 

 </rsb:script>

Example Custom Headers

Static Headers

The following example shows how to add static headers in the schema file. These headers are added to the request every time the schema file is called.

  <rsb:script xmlns:rsb="http://apiscript.com/ns?v1" xmlns:xs="http://www.cdata.com/ns/rsbscript/2" xmlns:other="http://apiscript.com/ns?v1"> 
    ...
    <input name="Ship1" other:headerName="DynamicValuedHeader" />
    <input name="Ship2" other:headerName="DynamicValuedHeader" />
  </rsb:info> 

  <api:set attr="Header:Name#1"  value="StaticValuedHeader" />
  <api:set attr="Header:Value#1" value="StaticValuedHeader__Value" />

Dynamic Headers

The following example shows how to add dynamic headers in the schema file. These headers are added to the request every time the schema file is called.

  <rsb:script xmlns:rsb="http://apiscript.com/ns?v1" xmlns:xs="http://www.cdata.com/ns/rsbscript/2" xmlns:other="http://apiscript.com/ns?v1"> 
    ...
    <input name="Ship1" other:headerName="DynamicValuedHeader" />
    <input name="Ship2" other:headerName="DynamicValuedHeader" />
    <input name="Ship3" other:headerName="DynamicValuedHeader2" />
  </rsb:info> 

  <api:set attr="Header:Name#1"  value="DynamicValuedHeader" />
  <api:set attr="Header:Value#1" value="[_input.Ship1] - [_input.Ship2]" />

  SELECT * FROM [Table] WHERE [Ship1] = "Value1" AND [Ship2] = "Value2" AND [DynamicValuedHeader2] = "custom value"

In the above example, the value format of DynamicValuedHeader is parsed by the driver, but for DynamicValuedHeader2, it is the same as the value specified in the query.

The basic attributes of a column are the name of the column, the data type, whether the column is a primary key, the relative path and the depth. The connector uses the depth attribute to extract nodes from hierarchical data.

Mark up column attributes in the block of the schema file. You can also provide a description of each attribute using the desc property.

 <rsb:script xmlns:rsb="http://apiscript.com/ns?v1" xmlns:xs="http://www.cdata.com/ns/rsbscript/2" xmlns:other="http://apiscript.com/ns?v1"> 
   <rsb:info title="Labels" desc="Lists information about the different labels you can apply on an issue." other:possiblePaths="{'UserLogin':{'RepositoryName':{'path':'/repository/labels/edges/node','Name':{'path':'/repository/label'}}}}" other:paginationObjects="{'labels':{'cursorPath':'after','cursorType':'String','pageSizeArgumentPath':'first','pageSizeArgumentType':'Int','depth':'1','paginationType':'Cursor','isConnection':'True','pageInfo':['endCursor','hasNextPage','hasPreviousPage','startCursor']}}"> 
     <attr   name="Id"               xs:type="string"     key="true"   other:relativePath="id"             desc="The ID of the label."                                                                                                                                                                                                                                                                                                                  /> 
     <attr   name="RepositoryName"   xs:type="string"                            other:relativePath="name"           desc="The name of the repository."                                     other:filter="name:="    other:argumenttype="String!"    other:depth="1"                                           references="Repositories.Name"                                                                             /> 
     <attr   name="UserLogin"        xs:type="string"                                                                          desc="The login name of the user."                                     other:filter="owner:="   other:argumenttype="String!"    other:depth="1"                                           references="Users.Login"         other:mirror="true"         other:canBeSliced="true"  /> 
     <attr   name="Color"            xs:type="string"                            other:relativePath="color"          desc="Identifies the label color."                                                                                                                                                                                                                                                                                                           /> 
     <attr   name="CreatedAt"        xs:type="datetime"                          other:relativePath="createdAt"      desc="Identifies the date and time when the label was created."                                                                                                                  other:orderby="CREATED_AT"                                                                                                                        /> 
     <attr   name="Description"      xs:type="string"                            other:relativePath="description"    desc="A brief description of this label."                                                                                                                                                                                                                                                                                                    /> 
     <attr   name="IsDefault"        xs:type="boolean"                           other:relativePath="isDefault"      desc="Indicates whether or not this is a default label."                                                                                                                                                                                                                                                                                     /> 
     <attr   name="Name"             xs:type="string"                            other:relativePath="name"           desc="Identifies the label name."                                      other:filter="name:="    other:argumenttype="String!"                                 other:orderby="NAME"                                                                                                                              />
     <attr   name="ResourcePath"     xs:type="string"                            other:relativePath="resourcePath"   desc="The HTTP path for this label."                                                                                                                                                                                                                                                                                                         /> 
     <attr   name="UpdatedAt"        xs:type="datetime"                          other:relativePath="updatedAt"      desc="Identifies the date and time when the label was last updated."                                                                                                                                                                                                                                                                         /> 
     <attr   name="Url"              xs:type="string"                            other:relativePath="url"            desc="The HTTP URL for this label."                                                                                                                                                                                                                                                                                                          /> 
   </rsb:info> 

   <rsb:script method="GET"> 
     <rsb:push op="graphqladoSelect" /> 
   </rsb:script> 

 </rsb:script>

The following sections provide more detail on using paths to extract columns and rows. To see the column definitions in a complete schema, refer to Customizing Schemas.

Mapping SELECT Projection to GraphQL Fields

Control the building process of a GraphQL field path with the properties listed below:

1. The other:possiblePaths property is used to specify the base paths that select the column's value, along with any required and/or optional filters that may apply.

   Base paths start with a '/' and contain the full path to the last GraphQL nested object.
   

     <rsb:info title="Labels" desc="Lists information about the different labels you can apply to an issue." other:possiblePaths="{'UserLogin':{'RepositoryName':{'path':'/repository/labels/edges/node','Name':{'path':'/repository/label'}}}}" other:paginationObjects="{'labels':{'cursorPath':'after','cursorType':'String','pageSizeArgumentPath':'first','pageSizeArgumentType':'Int','depth':'2','paginationType':'Cursor','isConnection':'True','pageInfo':['endCursor','hasNextPage','hasPreviousPage','startCursor']}}">

   The following GraphQL query is based on the above script example:
   

   { # base path=/repository/labels/edges/node
     repository {
       labels {
         edges {
           node {
             ...
           }
         }
       }
     }
   }

2. The other:relativePath property must be specified for each column. This property is used in conjuction with the other:possiblePaths property to build the GraphQL field path.
   

     <attr   name="Name"  xs:type="string"  other:relativePath="name"  desc="Identifies the label name."    />

   Based on the above script example the connector will build the following GraphQL query:
   

   { # base path=/repository/labels/edges/node
     repository { # depth=1 
       labels { # depth=2
         edges {
           node {
             name # path=base path + relative path.
           }
         }
       }
     }
   }

3. Use the other:depth property to specify an element inside a specific GraphQL object. The indexes are 1-based. If this attribute is not specified then the default value will be equal to the last nested GraphQL object.
   

     <attr   name="RepositoryName"   xs:type="string"  other:relativePath="name"   desc="The name of the repository."  other:depth="1" />

   The following GraphQL query is built from the above script example:
   

   { # base path=/repository/labels/edges/node
     repository { # depth=1 
       name # This is mapped to the RepositoryName column
       labels { # depth=2
         edges {
           node {
             ...
           }
         }
       }
     }
   }

4. Use the other:fragment property to specify a group of fields. This property can be used when the GraphQL server returns an array of objects and the connector may need to push this info as an aggregate.
   

     <attr   name="ColumnValues"   xs:type="string"  other:relativePath="column_values"   desc="Column values."  other:fragment="fragment ItemColumnValues on ColumnValue {  id \\r\\n value }" />

   Based on the above script example, the connector will build the following GraphQL query:
   

   query {
       items {
           column_values {
               ...ItemColumnValues
           }
       }
   }
   fragment ItemColumnValues on ColumnValue {
       id
       value
   }

5. Use the other:canbesliced property enable slicing behavior in the connector

   For example,
   

   SELECT * FROM Table WHERE Col IN ('1','2','3')

   becomes
   

   SELECT * FROM Table WHERE Col=1
   SELECT * FROM Table WHERE Col=2
   SELECT * FROM Table WHERE Col=3

6. Use the other:mirror property to reflect the value specified in the criteria. Use on columns that are not specified in the server response.

   For example:

   
   

   SELECT * FROM Table WHERE Col=X (If other:mirror=true the connector will artificially set the value of Col to X for every row.)

7. Use references to reference the key column of the parent table. Example: If there are two tables Orders and OrderLineItems and the OrderLineItems has a column OrderId, the references field for this column will be "Orders.Id".

Notes:

• Paths and column names (when used to generate the path) are case sensitive.
• At least one possible path should be specified.
• The other:relativePath property must be specified for every column. Otherwise, the connector cannot map the SELECT column to a GraphQL field.

When a SELECT query is issued, the connector translates the SQL statement into a GraphQL query. The connector automatically generates the necessary schema when connecting to the API. The sections below explain how to customize filtering, ordering, and pagination when manual configuration is required.

Filtering

Mapping Filters to GraphQL Arguments

Use other:filter and other:argumentType on a column definition to map a SQL WHERE condition to a GraphQL argument:

• other:filter is a semicolon-separated list of <argumentName>:<operators>, where <operators> is a comma-separated list of SQL operators the argument accepts. Valid operators are =, <, <=, >, and >=.
• other:argumentType specifies the GraphQL type of the argument as defined in the GraphQL schema type language.

For example, to push the following query condition server-side:

SELECT * FROM customers WHERE modifiedAt < '2019-10-30T04:00:00.000Z'

Configure other:filter on the column to specify how filter operators map to GraphQL arguments:

<attr name="modifiedAt" xs:type="datetime" other:relativePath="modifiedAt" other:argumentType="DateTime" other:filter="modifiedAtAfter:>;modifiedAtBefore:<" />

Then include the filter in other:possiblePaths so the connector knows which path the filter applies to:

other:possiblePaths="{'path':'/customers','modifiedAt':{'path':'/customers'}}"

The following request body is generated:

{
    "variables": {
        "modifiedAt_modifiedAtBefore": "2019-10-30T04:00:00.000Z"
    },
    "query": "query($modifiedAt_modifiedAtBefore:DateTime) { customers(modifiedAtBefore:$modifiedAt_modifiedAtBefore) { id modifiedAt } }"
}

Note: Filters defined with other:filter must also appear as keys in other:possiblePaths to be pushed to the server. Filters not listed in other:possiblePaths are evaluated client-side after all data is fetched.

Possible Paths

Different GraphQL objects may be accessible through different paths and filter combinations. The other:possiblePaths table extra info defines all valid paths, filters, and combinations of filter requirements. The connector intelligently selects the optimal path based on the filters provided in the query.

The JSON structure uses nesting to define filter requirements:

• A path key defines a path that can be queried at that level of nesting.
• A filter key with a sibling path key is optional for that path.
• A filter key with no sibling path key is required to access the paths nested within it.
• Filters nested inside other filters form a chain: all filters in the chain must be provided together to access the nested path.

No Filters Required

When the only key is path, the data is accessible without any filters:

other:possiblePaths="{'path':'/categories'}"

Any query on this table will work without filters:

SELECT * FROM categories

Optional Filters

When filter keys appear at the same level as a path key, those filters are optional:

other:possiblePaths="{'path':'/articles','status':{'path':'/articles'},'featured':{'path':'/articles'}}"

The data is accessible with or without filters. When status or featured are provided, they are sent as arguments to filter results on the server:

SELECT * FROM articles
SELECT * FROM articles WHERE status = 'PUBLISHED'
SELECT * FROM articles WHERE featured = true
SELECT * FROM articles WHERE status = 'PUBLISHED' AND featured = true

Required Filter

When a filter key has no sibling path key, it must be provided to access the paths nested within it:

other:possiblePaths="{'departmentId':{'path':'/users','role':{'path':'/users'}}}"

The departmentId filter is required. The role filter is optional because it has a sibling path key at the same level:

SELECT * FROM users WHERE departmentId = 'eng-001'
SELECT * FROM users WHERE departmentId = 'eng-001' AND role = 'ADMIN'

Chained Filter Requirements

When a GraphQL argument uses an input object type, its fields are exposed as individual filter columns. If the fields must be specified together, they are represented as a chain in other:possiblePaths. For example, priceRange_min and priceRange_max represent the min and max fields of a priceRange input argument:

other:possiblePaths="{'categoryId':{'path':'/products','inStock':{'path':'/products'},'priceRange_min':{'priceRange_max':{'path':'/products'}}}}"

The categoryId filter is required. The inStock filter is optional. The priceRange_min and priceRange_max filters are optional but must both be provided together:

SELECT * FROM products WHERE categoryId = 'electronics'
SELECT * FROM products WHERE categoryId = 'electronics' AND inStock = true
SELECT * FROM products WHERE categoryId = 'electronics' AND priceRange_min = 100 AND priceRange_max = 500

Multiple Possible Paths

When multiple paths are defined, the connector selects the optimal path based on the filters provided. Each path can have different filter requirements and may point to different endpoints:

other:possiblePaths="{'path':'/businesses','id':{'path':'/business'}}"

Without filters, the connector queries /businesses. When id is provided, the connector switches to the more specific /business endpoint:

SELECT * FROM businesses
SELECT * FROM businesses WHERE id = 'QnVzaW5lc3M6ZTY4...'

The second query is converted to:

{
    "variables": {
        "id_id": "QnVzaW5lc3M6ZTY4..."
    },
    "query": "query($id_id:ID) { business(id:$id_id) { id name createdAt } }"
}

Ordering

To enable server-side sorting, configure the following on the column definition:

• other:orderByFormat defines the shape of the sort argument, using the placeholders {orderByArgumentValue} for the sort field and {sortOrder} for the direction. Can be set on the table or column.
  

  <attr name="createdAt" xs:type="datetime" other:relativePath="createdAt" other:orderByFormat="{field: {orderByArgumentValue}, direction: {sortOrder}}" other:orderBy="sort:CREATED_AT" />

• other:orderBy maps the column to a sort argument value. Format: <argumentName>:<value>. Set on the column only.
  

  <attr name="createdAt" xs:type="datetime" other:relativePath="createdAt" other:orderBy="sort:CREATED_AT" />

The query:

SELECT id FROM labels ORDER BY createdAt ASC

is converted to:

{
    "variables": {
        "first": <Pagesize>
    },
    "query": "query($first:Int) { repository { labels(sort:{field: CREATED_AT, direction: ASC}, first:$first) { edges { node { id } } pageInfo { endCursor hasNextPage } } } }"
}

Pagination

The connector supports two pagination modes, configured via the other:paginationObjects table extra info.

Cursor-Based Pagination

other:paginationObjects = "{
    'labels': {
        'cursorPath': 'after',
        'cursorType': 'String',
        'pageSizeArgumentPath': 'first',
        'pageSizeArgumentType': 'Int',
        'depth': '2',
        'paginationType': 'Cursor',
        'isConnection': 'True',
        'pageInfo': ['endCursor', 'hasNextPage']
    }
}"

The following request body is generated:

{
    "variables": {
        "UserLogin_owner": "user",
        "RepositoryName_name": "test",
        "first": <Pagesize>
    },
    "query": "query($UserLogin_owner:String!, $RepositoryName_name:String!, $first:Int) { repository(owner:$UserLogin_owner, name:$RepositoryName_name) { labels(first:$first) { edges { node { id color createdAt description isDefault name resourcePath updatedAt url } } pageInfo { endCursor hasNextPage } } name } }"
}

Offset-Based Pagination

other:paginationObjects = "{
    'businesses': {
        'offsetArgumentPath': 'page',
        'offsetArgumentType': 'Int',
        'offsetArgumentDefaultValue': '1',
        'pageSizeArgumentPath': 'pageSize',
        'pageSizeArgumentType': 'Int',
        'depth': '1',
        'paginationType': 'Offset',
        'isConnectionObject': 'True',
        'pageInfo': ['currentPage', 'totalPages', 'totalCount']
    }
}"

The following request body is generated:

{
    "variables": {
        "pageSize_1": <Pagesize>
    },
    "query": "query($pageSize_1:Int) { businesses(pageSize:$pageSize_1) { edges { node { id } } pageInfo { totalPages currentPage } } }"
}

Note: Input objects are supported as pagination arguments. Use a slash-separated path for offsetArgumentPath and pageSizeArgumentPath to target nested fields:

other:paginationObjects = "{
    'businesses': {
        'offsetArgumentPath': 'query/pagination/page',
        'offsetArgumentType': 'custom_query',
        'pageSizeArgumentPath': 'query/pagination/pageSize',
        'pageSizeArgumentType': 'custom_query',
        'depth': '1',
        'paginationType': 'Offset',
        'isConnectionObject': 'True',
        'pageInfo': ['currentPage', 'totalPages', 'totalCount']
    }
}"

The following request body is generated:

{
    "variables": {
        "query": {
            "pagination": {
                "pageSize": <Pagesize>
            }
        }
    },
    "query": "query($query:custom_query) { businesses(query:$query) { edges { node { id } } pageInfo { totalPages currentPage } } }"
}

The connector has high-performance operations for processing GraphQL data sources. These operations are platform neutral: Schema files that invoke these operations can be used in both .NET and Java. You can also extend the connector with your own operations written in .NET or Java.

The connector has the following operations:

Operation Name	Description
OAuthGetAccessToken	For OAuth 1.0, exchange a request token for an access token. For OAuth 2.0, get an access token or get a new access token with the refresh token.
OAuthGetUserAuthorizationURL	Generates the user authorization URL. OAuth 2.0 will not access the network in this operation.

The OAuthGetAccessToken operation is an APIScript operation that is used to facilitate the OAuth authentication and refresh flows.

The connector includes stored procedures that invoke this operation to complete the OAuth exchange. The following example schema briefly lists some of the typically required inputs before the following sections explain them in more detail.

Creating a GetOAuthAccessToken Stored Procedure

Invoke the OAuthGetAccessToken with the GetOAuthAccessToken stored procedure. The following inputs are required for most data sources and will provide default values for the connection properties of the same name.

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">

  <api:info title="GetOAuthAccessToken"   description="Obtains the OAuth access token to be used for authentication with various APIs."                                                         >
    <input  name="AuthMode"               desc="The OAuth flow. APP or WEB."                                                                                                                    />
    <input  name="CallbackURL"            desc="The URL to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access. " />
    <input  name="OAuthAccessToken"       desc="The request token. OAuth 1.0 only."                                                                                                             />
    <input  name="OAuthAccessTokenSecret" desc="The request token secret. OAuth 1.0 only."                                                                                                      />
    <input  name="Verifier"               desc="The verifier code obtained when the user grants permissions to your app."                                                                       />

    <output name="OAuthAccessToken"       desc="The access token."                                                                                                                              />
    <output name="OAuthTokenSecret"       desc="The access token secret."                                                                                                                       />
    <output name="OAuthRefreshToken"      desc="A token that may be used to obtain a new access token."                                                                                         />
 </api:info>

  <!-- Set OAuthVersion to 1.0 or 2.0. -->
  <api:set attr="OAuthVersion"                                                    value="MyOAuthVersion"                 />
  <!-- Set RequestTokenURL to the URL where the request for the request token is made. OAuth 1.0 only.-->
  <api:set attr="OAuthRequestTokenURL"                                            value="http://MyOAuthRequestTokenURL" />
  <!-- Set OAuthAuthorizationURL to the URL where the user logs into the service and grants permissions to the application. -->
  <api:set attr="OAuthAuthorizationURL"                                           value="http://MyOAuthAuthorizationURL" />
  <!-- Set OAuthAccessTokenURL to the URL where the request for the access token is made. -->
  <api:set attr="OAuthAccessTokenURL"                                             value="http://MyOAuthAccessTokenURL"   />
  <!-- Set GrantType to the authorization grant type. OAuth 2.0 only. -->
  <api:set attr="GrantType"                                                       value="CODE"                           />
  <!-- Set SignMethod to the signature method used to calculate the signature of the request. OAuth 1.0 only.-->
  <api:set attr="SignMethod"                                                      value="HMAC-SHA1"                      />
  <api:call op="oauthGetAccessToken">
    <api:push/>
  </api:call>
  
</api:script>

Writing the RefreshOAuthAccessToken Stored Procedure

You can also use OAuthGetAccessToken to refresh the access token by providing the following inputs:

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">

  <api:info title="RefreshOAuthAccessToken" description="Refreshes the OAuth access token used for authentication." >
    <input  name="OAuthRefreshToken"        desc="A token that may be used to obtain a new access token."           />

    <output name="OAuthAccessToken"         desc="The authentication token returned."                               />
    <output name="OAuthTokenSecret"         desc="The authentication token secret returned. OAuth 1.0 only."        />
    <output name="OAuthRefreshToken"        desc="A token that may be used to obtain a new access token."           />
    <output name="ExpiresIn"                desc="The remaining lifetime on the access token."                      />

  </api:info>

  <!-- Set OAuthVersion to 1.0 or 2.0. -->
  <api:set attr="OAuthVersion"                                                    value="MyOAuthVersion"                 />
    <!-- Set GrantType to REFRESH. OAuth 2.0 only. -->
    <api:set attr="GrantType"            value="REFRESH" />
    <!-- Set SignMethod to the signature method used to calculate the signature of the request. OAuth 1.0 only.-->
    <api:set attr="SignMethod"           value="HMAC-SHA1" />
    <!-- Set OAuthAccessTokenURL to the URL where the request for the access token is made. -->
    <api:set attr="OAuthAccessTokenURL"  value="http://MyOAuthAccessTokenURL" />
    <!-- Set AuthMode to 'WEB' when calling RefreshOAuthAccessToken -->
    <api:set attr="AuthMode" value="WEB"/>
  <api:call op="oauthGetAccessToken">
    <api:push/>
  </api:call>
  
</api:script>

Input Parameters

• OAuthVersion: The OAuth version. The allowed values are 1.0, 2.0. The default value is 1.0.
• AuthMode: The OAuth flow. OAuth 2.0 only. If you choose the App mode, this operation will launch your browser and prompt you to authenticate with your account credentials. Set this parameter to WEB to authenticate a Web app or if the connector is not allowed to open a Web browser. The default value is APP.
• OAuthRequestTokenURL: The URL where the connector makes a request for the request token. OAuth 1.0 only. Required for OAuth 1.0.
• OAuthAuthorizationURL: The URL where the user logs into the service and grants permissions to the application. In OAuth 1.0, if permissions are granted the request token is authorized.
• OAuthAccessTokenURL: The URL where the request for the access token is made. In OAuth 1.0, the authorized request token is exchanged for the access token.
• CallbackURL: The URL to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access. This value must match the callback URL you specify when you register an app. Note that your data source may additionally require the port.
• OAuthClientId: The client Id obtained when you register an app. Also called a consumer key.
• OAuthClientSecret: The client secret obtained when you register an app. Also called a consumer secret.
• OAuthAccessToken: The request token. OAuth 1.0 only.
• OAuthAccessTokenSecret: The request token secret. OAuth 1.0 only.
• OAuthRefreshToken: A token that may be used to obtain a new access token.
• GrantType: Authorization grant type. OAuth 2.0 only. The allowed values are CODE, PASSWORD, CLIENT, REFRESH. The default value is CODE.
• Verifier: The verifier code obtained when the user grants permissions to the connector. In the OAuth 2.0 code grant type, the verifier code is located in the code query string parameter of the callback URL. In OAuth 1.0, the verifier is located in the oauth_verifier query string parameter of the callback URL.
• SignMethod: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
• Cert: Path for the PFX personal certificate file. OAuth 1.0 only.
• CertPassword: Personal certificate password. OAuth 1.0 only.
• OtherOptions: Other options to control the behavior of OAuth.
• OAuthParam:*: Other parameters.
• PostData: The HTTP POST data.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• LogFile: Specifies a file where the request and response are logged.
• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request.
• Proxy_Port: The port number of the proxy server.
• Proxy_User: The user Id used to authenticate with the proxy server.
• Proxy_Password: The password used to authenticate with the proxy server.
• Proxy_AuthScheme: The authentication scheme of the proxy server. The allowed values are BASIC, DIGEST, NONE, NTLM. The default value is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.
• Firewall_Type: The type of the firewall. The allowed values are NONE, TUNNEL, SOCKS4, SOCKS5. The default value is NONE.
• Firewall_Server: The IP address or host name of the firewall.
• Firewall_Port: The port number of the firewall.
• Firewall_User: The user Id used to authenticate with the firewall.
• Firewall_Password: The password used to authenticate with the firewall.

Output Parameters

• OAuthAccessToken: The access token.
• OAuthTokenSecret: The access token secret.
• OAuthRefreshToken: A token that may be used to obtain a new access token.
• ExpiresIn: The remaining lifetime on the access token.
• OAuthParam:*: Other parameters sent from the server.

The OAuthGetUserAuthorizationURL is an APIScript operation that is used to facilitate the OAuth authentication flow for Web apps, for offline apps, and in situations where the connector is not allowed to open a Web browser. To pass the needed inputs to this operation, define the GetOAuthAuthorizationURL stored procedure. The connector can call this internally.

Define stored procedures in .rsb files with the same file name as the schema's title. The example schema briefly lists some of the typically required inputs before the following sections explain them in more detail.

Writing the GetOAuthAuthorizationURL Stored Procedure

Call OAuthGetUserAuthorizationURL in the GetOAuthAuthorizationURL stored procedure.

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">

  <api:info title="Get OAuth Authorization URL" description="Obtains the OAuth authorization URL used for authentication with various APIs."                                                          >
    <input  name="CallbackURL"                  desc="The URL to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access. " />

    <output name="URL"                          desc="The URL where the user logs in and is prompted to grant permissions to the app. "                                                               />
    <output name="OAuthAccessToken"             desc="The request token. OAuth 1.0 only."                                                                                                             />
    <output name="OAuthTokenSecret"             desc="The request token secret. OAuth 1.0 only."                                                                                                      />
  </api:info>

  <!-- Set OAuthVersion to 1.0 or 2.0. -->
  <api:set attr="OAuthVersion"          value="MyOAuthVersion"                 />
  <!-- Set ResponseType to the desired authorization grant type. OAuth 2.0 only.-->
  <api:set attr="ResponseType"           value="code"                           />
  <!-- Set SignMethod to the signature method used to calculate the signature. OAuth 1.0 only.-->
  <api:set attr="SignMethod"            value="HMAC-SHA1"                      />
  <!-- Set OAuthAuthorizationURL to the URL where the user logs into the service and grants permissions to the application. -->
  <api:set attr="OAuthAuthorizationURL"  value="http://MyOAuthAuthorizationURL" />
  <!-- Set OAuthAccessTokenURL to the URL where the request for the access token is made. -->
  <api:set attr="OAuthAccessTokenURL"   value="http://MyOAuthAccessTokenURL"/>
  <!-- Set RequestTokenURL to the URL where the request for the request token is made. OAuth 1.0 only.-->
  <api:set attr="OAuthRequestTokenURL"   value="http://MyOAuthRequestTokenURL"       />
  <api:call op="oauthGetUserAuthorizationUrl">
    <api:push/>
  </api:call>
  
</api:script>

<p>

Input Parameters

• OAuthVersion: The OAuth version. The allowed values are 1.0, 2.0. The default value is 1.0.
• OAuthAuthorizationURL: The URL where the user logs into the service and grants permissions to the application. In OAuth 1.0, if permissions are granted the request token is authorized.
• OAuthRequestTokenURL: The URL where the connector makes a request for the request token. OAuth 1.0 only. Required for OAuth 1.0.
• CallbackURL: The URL to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access. This value must match the callback URL you specify when you register an app. Note that your data source may additionally require the port. The default value is http://127.0.0.1/.
• OAuthClientId: The client Id. Also called a consumer key.
• OAuthClientSecret: The client secret. Also called a consumer secret.
• ResponseType: The desired authorization grant type. OAuth 2.0 only. The allowed values are CODE, IMPLICIT. The default value is CODE.
• SignMethod: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, RSA-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
• Cert: Path for the personal certificate PFX file. OAuth 1.0 only.
• CertPassword: Personal certificate password. OAuth 1.0 only.
• OtherOptions: Other options to control the behavior of OAuth.
• OAuthParam:*: Other parameters. OAuth 1.0 only.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request.
• Proxy_Port: The port number of the proxy server.
• Proxy_User: The user Id used to authenticate with the proxy server.
• Proxy_Password: The password used to authenticate with the proxy server.
• Proxy_AuthScheme: The authentication scheme of the proxy server. The allowed values are BASIC, DIGEST, NONE, NTLM. The default value is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.
• Firewall_Type: The type of the firewall. The allowed values are NONE, TUNNEL, SOCKS4, SOCKS5. The default value is NONE.
• Firewall_Server: The IP address or host name of the firewall.
• Firewall_Port: The port number of the firewall.
• Firewall_User: The user Id used to authenticate with the firewall.
• Firewall_Password: The password used to authenticate with the firewall.

Output Parameters

• URL: The URL where the user logs in and is prompted to grant permissions to the app. In OAuth 1.0, if permissions are granted the request token is authorized.
• OAuthAccessToken: The request token. OAuth 1.0 only.
• OAuthTokenSecret: The request token secret. OAuth 1.0 only.
• OAuthParam:*: Other parameters sent from the server. OAuth 1.0 only.

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for GraphQL:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries

• sys_identity: Returns information about batch operations or single updates.

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

Name	Type	Description
CatalogName	String	The database name.

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas
          

Columns

Name	Type	Description
CatalogName	String	The database name.
SchemaName	String	The schema name.

Lists the available tables.

The following query retrieves the available tables and views:

          SELECT * FROM sys_tables
          

Columns

Name	Type	Description
CatalogName	String	The database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view.
TableType	String	The table type (table or view).
Description	String	A description of the table or view.
IsUpdateable	Boolean	Whether the table can be updated.
IsInsertable	Boolean	Whether the table can be inserted into.
IsDeleteable	Boolean	Whether rows can be deleted from the table.

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Users table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName = 'Users' 

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view containing the column.
ColumnName	String	The column name.
DataTypeName	String	The data type name.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The storage size of the column.
DisplaySize	Int32	The designated column's normal maximum width in characters.
NumericPrecision	Int32	The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The column scale or number of digits to the right of the decimal point.
IsNullable	Boolean	Whether the column can contain null.
Description	String	A brief description of the column.
Ordinal	Int32	The sequence number of the column.
IsAutoIncrement	String	Whether the column value is assigned in fixed increments.
IsGeneratedColumn	String	Whether the column is generated.
IsHidden	Boolean	Whether the column is hidden.
IsArray	Boolean	Whether the column is an array.
IsReadOnly	Boolean	Whether the column is read-only.
IsKey	Boolean	Indicates whether a field returned from sys_tablecolumns is the primary key of the table.
ColumnType	String	The role or classification of the column in the schema. Possible values include SYSTEM, LINKEDCOLUMN, NAVIGATIONKEY, REFERENCECOLUMN, and NAVIGATIONPARENTCOLUMN.
ColumnCapabilities	Int32	A bit mask denoting the column's write capabilities. The value is the sum of the following: 1 if the column is required for INSERTs, 2 if the column is allowed for INSERTs, and 4 if the column is allowed for UPDATEs. A value of 0 indicates that the write capabilities of the column are unknown or that the column is read-only.

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures
          

Columns

Name	Type	Description
CatalogName	String	The database containing the stored procedure.
SchemaName	String	The schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure.
Description	String	A description of the stored procedure.
ProcedureType	String	The type of the procedure, such as PROCEDURE or FUNCTION.

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the SelectEntries stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'SelectEntries' AND Direction = 1 OR Direction = 2

To include result set columns in addition to the parameters, set the IncludeResultColumns pseudo column to True:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'SelectEntries' AND IncludeResultColumns='True'

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the stored procedure.
SchemaName	String	The name of the schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure containing the parameter.
ColumnName	String	The name of the stored procedure parameter.
Direction	Int32	An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
DataTypeName	String	The name of the data type.
NumericPrecision	Int32	The maximum precision for numeric data. The column length in characters for character and date-time data.
Length	Int32	The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericScale	Int32	The number of digits to the right of the decimal point in numeric data.
IsNullable	Boolean	Whether the parameter can contain null.
IsRequired	Boolean	Whether the parameter is required for execution of the procedure.
IsArray	Boolean	Whether the parameter is an array.
Description	String	The description of the parameter.
Ordinal	Int32	The index of the parameter.
Values	String	The values you can set in this parameter are limited to those shown in this column. Possible values are comma-separated.
SupportsStreams	Boolean	Whether the parameter represents a file that you can pass as either a file path or a stream.
IsPath	Boolean	Whether the parameter is a target path for a schema creation operation.
Default	String	The value used for this parameter when no value is specified.
SpecificName	String	A label that, when multiple stored procedures have the same name, uniquely identifies each identically-named stored procedure. If there's only one procedure with a given name, its name is simply reflected here.
IsCDataProvided	Boolean	Whether the procedure is added/implemented by CData, as opposed to being a native GraphQL procedure.

Pseudo-Columns

Name	Type	Description
IncludeResultColumns	Boolean	Whether the output should include columns from the result set in addition to parameters. Defaults to False.

Describes the primary and foreign keys.

The following query retrieves the primary key for the Users table:

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Users' 
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
IsKey	Boolean	Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey	Boolean	Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.
ForeignKeyType	String	Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

         SELECT * FROM sys_primarykeys
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
KeySeq	String	The sequence number of the primary key.
KeyName	String	The name of the primary key.

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the index.
SchemaName	String	The name of the schema containing the index.
TableName	String	The name of the table containing the index.
IndexName	String	The index name.
ColumnName	String	The name of the column associated with the index.
IsUnique	Boolean	True if the index is unique. False otherwise.
IsPrimary	Boolean	True if the index is a primary key. False otherwise.
Type	Int16	An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder	String	The sort order: A for ascending or D for descending.
OrdinalPosition	Int16	The sequence number of the column in the index.

Returns information on the available connection properties and those set in the connection string.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Name	Type	Description
Name	String	The name of the connection property.
ShortDescription	String	A brief description.
Type	String	The data type of the connection property.
Default	String	The default value if one is not explicitly set.
Values	String	A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value	String	The value you set or a preconfigured default.
Required	Boolean	Whether the property is required to connect.
Category	String	The category of the connection property.
IsSessionProperty	String	Whether the property is a session property, used to save information about the current connection.
Sensitivity	String	The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName	String	A camel-cased truncated form of the connection property name.
Ordinal	Int32	The index of the parameter.
CatOrdinal	Int32	The index of the parameter category.
Hierarchy	String	Shows dependent properties associated that need to be set alongside this one.
Visible	Boolean	Informs whether the property is visible in the connection UI.
ETC	String	Various miscellaneous information about the property.

Describes the SELECT query processing that the connector can offload to the data source.

See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name	Description	Possible Values
AGGREGATE_FUNCTIONS	Supported aggregation functions.	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	Whether COUNT function is supported.	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	The opening character used to escape an identifier.	[
IDENTIFIER_QUOTE_CLOSE_CHAR	The closing character used to escape an identifier.	]
SUPPORTED_OPERATORS	A list of supported SQL operators.	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	Whether GROUP BY is supported, and, if so, the degree of support.	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIES	The supported varieties of outer joins supported.	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	Whether outer joins are supported.	YES, NO
SUBQUERIES	Whether subqueries are supported, and, if so, the degree of support.	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONS	Supported string functions.	LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS	Supported numeric functions.	ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS	Supported date/time functions.	NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES	Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS	A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN	String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION	Indicates if the provider supports transactions such as commit and rollback.	YES, NO
DIALECT	Indicates the SQL dialect to use.
KEY_PROPERTIES	Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS	Indicates if multiple schemas may exist for the provider.	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	Indicates if multiple catalogs may exist for the provider.	YES, NO
DATASYNCVERSION	The CData Data Sync version needed to access this driver.	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	The CData Data Sync category of this driver.	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	Whether enhanced SQL functionality beyond what is offered by the API is supported.	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	Whether batch operations are supported.	YES, NO
SQL_CAP	All supported SQL capabilities for this driver.	SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS	A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY	Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.	YES, NO
PSEUDO_COLUMNS	A string array indicating the available pseudo columns.
MERGE_ALWAYS	If the value is true, The Merge Mode is forcibly executed in Data Sync.	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION	Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE	Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY	A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION	Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID	Indicates whether the CheckCache statement should be done against the parent key column.	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns

Name	Type	Description
NAME	String	A component of SQL syntax, or a capability that can be processed on the server.
VALUE	String	Detail on the supported SQL or SQL syntax.

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

         SELECT * FROM sys_identity
          

Columns

Name	Type	Description
Id	String	The database-generated Id returned from a data modification operation.
Batch	String	An identifier for the batch. 1 for a single operation.
Operation	String	The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message	String	SUCCESS or an error message if the update in the batch failed.

Describes the available system information.

The following query retrieves all columns:

SELECT * FROM sys_information

Columns

Name	Type	Description
Product	String	The name of the product.
Version	String	The version number of the product.
Datasource	String	The name of the datasource the product connects to.
NodeId	String	The unique identifier of the machine where the product is installed.
HelpURL	String	The URL to the product's help documentation.
License	String	The license information for the product. (If this information is not available, the field may be left blank or marked as 'N/A'.)
Location	String	The file path location where the product's library is stored.
Environment	String	The version of the environment or rumtine the product is currently running under.
DataSyncVersion	String	The tier of CData Sync required to use this connector.
DataSyncCategory	String	The category of CData Sync functionality (e.g., Source, Destination).

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT operations with GraphQL.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from GraphQL, along with an indication of whether the procedure succeeded or failed.

CData Python Connector for GraphQL Stored Procedures

Name	Description
CreateSchema	Creates a schema file for the specified table or view.
GetOAuthAccessToken	Fetches the OAuth Access Token, which is used to authenticate and authorize API calls made to GraphQL.
GetOAuthAuthorizationURL	Retrieves the OAuth Authorization URL, allowing the client to direct the user's browser to the authorization server and initiate the OAuth process.
RefreshOAuthAccessToken	Refreshes an expired OAuth Access Token to maintain continuous authenticated access to GraphQL resources without requiring reauthorization from the user.

Creates a schema file for the specified table or view.

CreateSchema

Creates a local schema file (.rsd) from an existing table or view in the data model.

The schema file is created in the directory set in the Location connection property when this procedure is executed. You can edit the file to include or exclude columns, rename columns, or adjust column datatypes.

The connector checks the Location to determine if the names of any .rsd files match a table or view in the data model. If there is a duplicate, the schema file will take precedence over the default instance of this table in the data model. If a schema file is present in Location that does not match an existing table or view, a new table or view entry is added to the data model of the connector.

Input

Name	Type	Description
TableName	String	The name of the table or view.
FileName	String	The full file path and name of the schema to generate. Ex : 'C:\\Users\\User\\Desktop\\GraphQL\\Businesses.rsd'

Result Set Columns

Name	Type	Description
Result	String	Returns Success or Failure.

Fetches the OAuth Access Token, which is used to authenticate and authorize API calls made to GraphQL.

Input

Name	Type	Description
Other_Options	String	Other options to control behavior of OAuth.
Cert	String	Path for a personal certificate .pfx file. Only available for OAuth 1.0.
Cert_Password	String	Personal certificate password. Only available for OAuth 1.0.
AuthToken	String	The request token returned by GetOAuthAuthorizationUrl. Available only for OAuth 1.0.
AuthKey	String	The request secret key returned by GetOAuthAuthorizationUrl. Available only for OAuth 1.0.
AuthSecret	String	The legacy name for AuthKey, included for compatibility.
Sign_Method	String	The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
GrantType	String	Authorization grant type. Only available for OAuth 2.0. The allowed values are CODE, PASSWORD, CLIENT, REFRESH.
Post_Data	String	The post data to submit, if any.
AuthMode	String	The type of authentication mode to use. The allowed values are APP, WEB. The default value is WEB.
Verifier	String	The verifier code returned by the data source after permission for the app to connect has been granted. WEB AuthMode only.
Scope	String	The scope of access to the APIs. By default, access to all APIs used by this data provider will be specified.
CallbackURL	String	This field determines where the response is sent.
ApprovalPrompt	String	This field indicates if the user should be reprompted for consent. The default is AUTO, so a given user should only see the consent page for a given set of scopes the first time through the sequence. If set to FORCE, then the user sees a consent page even if they have previously given consent to your application for a given set of scopes.
AccessType	String	This field indicates if your application needs to access a Google API when the user is not present at the browser. This parameter defaults to ONLINE. If your application needs to refresh access tokens when the user is not present at the browser, then use OFFLINE. This will result in your application obtaining a refresh token the first time your application exchanges an authorization code for a user.
State	String	This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to GraphQL authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery.
PKCEVerifier	String	The PKCEVerifier returned by GetOAuthAuthorizationURL. Only required when AuthScheme=OAuthPKCE.

Result Set Columns

Name	Type	Description
OAuthAccessToken	String	The authentication token returned from GraphQL service. This can be used in subsequent calls to other operations for this particular service.
OAuthAccessTokenSecret	String	The OAuth Access Token Secret.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.
*	String	Other outputs that may be returned by the data source.

Retrieves the OAuth Authorization URL, allowing the client to direct the user's browser to the authorization server and initiate the OAuth process.

Input

Name	Type	Description
Cert	String	Path for a personal certificate .pfx file. Only available for OAuth 1.0.
Cert_Password	String	Personal certificate password. Only available for OAuth 1.0.
Sign_Method	String	The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
Scope	String	The scope of access to the APIs. By default, access to all APIs used by this data provider will be specified.
CallbackURL	String	The URL the user will be redirected to after authorizing your application.
ApprovalPrompt	String	This field indicates if the user should be reprompted for consent. The default is AUTO, so a given user should only see the consent page for a given set of scopes the first time through the sequence. If the value is FORCE, then the user sees a consent page even if they have previously given consent to your application for a given set of scopes.
AccessType	String	This field indicates if your application needs to access a Google API when the user is not present at the browser. This parameter defaults to ONLINE. If your application needs to refresh access tokens when the user is not present at the browser, then use OFFLINE. This will result in your application obtaining a refresh token the first time your application exchanges an authorization code for a user.
State	String	This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the GraphQL authorization server and back. Possible uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery.
Other_Options	String	Other options to control the behavior of OAuth.

Result Set Columns

Name	Type	Description
AuthToken	String	The authorization token, passed into the GetOAuthAccessToken stored procedure.
AuthKey	String	The authorization secret token, passed into the GetOAuthAccessToken stored procedure.
URL	String	The URL to complete user authentication.
PKCEVerifier	String	A random value used as input for GetOAuthAccessToken. Only provided when AuthScheme=OAuthPKCE.

Refreshes an expired OAuth Access Token to maintain continuous authenticated access to GraphQL resources without requiring reauthorization from the user.

Input

Name	Type	Description
OAuthRefreshToken	String	The refresh token returned from the original authorization code exchange.

Result Set Columns

Name	Type	Description
OAuthAccessToken	String	The authentication token returned from the data source. This can be used in subsequent calls to other operations for this particular service.
OAuthAccessTokenSecret	String	The new OAuth Access Token Secret returned from the service.
OAuthRefreshToken	String	The authentication token returned from the data source. This can be used in subsequent calls to other operations for this particular service.
ExpiresIn	String	The remaining lifetime on the access token.

This section provides a walk-through for writing GraphQL data access code in Python script.

For more information on the available data source entities and how to query them with SQL, see Data Model. For the SQL syntax, see SQL Compliance.

Connecting from Code

For information on how to deploy the connector and configure the connection to GraphQL, see Package Installation and Establishing a Connection.

For information on how to connect with the graphql.connector module and its related classes, see Connecting.

Executing SQL

The connection's cursor object is used to directly execute SQL queries. For information on how to execute SELECT statements and process the returned result sets, see Querying Data.

Executing Stored Procedures

You can call stored procedures by using the EXECUTE statement. For further information, see Calling Stored Procedures.

Connecting with the cdata.graphql Module:

The connector's module is used directly to establish a connection with the data source. It does this by using a connection string as its argument. For example:

import cdata.graphql as mod
conn = mod.connect("AuthScheme=OAuth;OAuthVersion=2.0;CallbackURL=http://localhost:33333;OauthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessTokenURL=https://mysite.com/login/oauth/access_token;")

Once the connection is created, you can use it to execute subsequent SQL queries.

After connecting as described in Connecting, you can use the open connection to execute SQL statements.

Executing Queries

To execute SQL statements that return data, use the execute() method. Once a query is executed, the result set is fetched from the cursor. This result set can then be iterated over to process the records individually.

For example:

cur = conn.execute("SELECT Name, Email FROM Users")
rs = cur.fetchall()
for row in rs:
	print(row)

Parameterized Queries

Various Python collections, such as arrays and tuples, can act as additional arguments for the execute() method. This enables you to parameterize the queries executed and help to prevent SQL Injection.

For example:

cmd = "SELECT Name, Email FROM Users WHERE UserLogin = ?"
params = ["mojombo"]
cur = conn.execute(cmd, params)
rs = cur.fetchall()
for row in rs:
	print(row)

You can execute stored procedures using either the execute() or callproc() method of the connection.

Calling Stored Procedures Using Execute()

When you call stored procedures by issuing EXECUTE commands, the stored procedure arguments are parameterized. For example:

cmd = "EXECUTE SelectEntries ObjectName = ?"
params = ["Account"]
conn.execute(cmd, params)

Calling Stored Procedures Using Callproc()

When you call stored procedured by issuing the callproc() method, the stored procedure arguments are a procedure name and a list of parameters. For example:

cur = conn.cursor()
params = ["Account"]
cur.callproc("SelectEntries", params)

The connector is integrated with other tools and packages within Python.

Python Integration Guides

The following sections show how to create and use connections with the connector in common packages in Python:

• From SQLAlchemy
• From Pandas
• From Matplotlib
• From Petl

Complete List of GraphQL Integration Quickstarts

For information on connecting from other applications, see GraphQL integration guides.

The CData Python Connector for GraphQL includes a Dialect class that enables integration with SQLAlchemy. Bear in mind that several aspects of connector functionality are not currently supported in SQLAlchemy 2.0 or above. If necessary, downgrade SQLAlchemy to version 1.4 or 1.3 before using this connector.

The following sections detail various aspects of this integration:

Connecting From SQLAlchemy

To construct a URL with which SQLAlchemy loads and uses the appropriate connector automatically, see Connecting

Reflecting Metadata With SQLAlchemy

To learn how to model GraphQL tables with mapped classes, see Reflecting Metadata.

Querying Data From SQLAlchemy

To learn how to use mapped classes to query the associated tables, see Querying Data.

Connecting With a Dialect URL

Establishing a connection using SQLAlchemy requires a specific URL format.

from sqlalchemy import create_engine
engine = create_engine("graphql:///?AuthScheme=OAuth;OAuthVersion=2.0;CallbackURL=http://localhost:33333;OauthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessTokenURL=https://mysite.com/login/oauth/access_token;")

For SQLAlchemy 2.0, the dialect name is graphql_2. To establish a connection, use the following URL format:

from sqlalchemy import create_engine
engine = create_engine("graphql_2:///?AuthScheme=OAuth;OAuthVersion=2.0;CallbackURL=http://localhost:33333;OauthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessTokenURL=https://mysite.com/login/oauth/access_token;")

SQLAlchemy can act as an Object-relational Map (ORM). This enables you to treat records of a database table as instantiable records. To leverage this functionality, you must reflect the underlying metadata in one of the following ways.

Note: The following examples employ SQLAlchemy 1.4.

Modeling Data Using a Mapping Class

Use "sqlalchemy.ext.declarative.declarative_base" to declare a mapping class for the table you wish to model in the ORM. A known table in the data model is modeled either partially or completely, as shown in the following example:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Users(Base):
	__tablename__ = "Users"
	Id = Column(String, primary_key=True)
	Name = Column(String)
	Email = Column(String)

Automatically Reflecting Metadata

Rather than mapping tables manually, SQLAlchemy can discover the metadata for one or more tables automatically. To accomplish this across the entire data model, use automap_base:

from sqlalchemy import MetaData
from sqlalchemy.ext.automap import automap_base
meta = MetaData()
abase = automap_base(metadata=meta)
abase.prepare(autoload_with=engine)
Users = abase.classes.Users

You can also reflect a single table with an inspector. When reflecting this way, providing a list of specific columns to map is optional:

from sqlalchemy import MetaData, Table
from sqlalchemy import inspect
meta = MetaData()
insp = inspect(engine)
Users_table = Table("Users", meta)
insp.reflect_table(Users_table, ["Id","Email"])

After you use the steps in Connecting to connect, and use one of the methods in Reflecting Metadata to reflect some of the metadata, you can use a session object to query data.

Querying Data Using the Query Method

If the mapping class has been prepared, use it with a session object to query the data source. After binding the engine to the session, provide the mapping class to the session's query method.

For example:

engine = create_engine("graphql:///?AuthScheme=OAuth;OAuthVersion=2.0;CallbackURL=http://localhost:33333;OauthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessTokenURL=https://mysite.com/login/oauth/access_token;")
factory = sessionmaker(bind=engine)
session = factory()
for instance in session.query(Users).filter_by(UserLogin="mojombo"):
	print("Id: ", instance.Id)
	print("Name: ", instance.Name)
	print("Email: ", instance.Email)
	print("---------")

Querying Data Using the Execute Method

The session object can also run the query with the execute() method alongside the appropriate Table object. Assuming you have an active session, the following is just as viable:

Users_table = Users.metadata.tables["Users"]
for instance in session.execute(Users_table.select().where(Users_table.c.UserLogin == "mojombo")):
	print("Id: ", instance.Id)
	print("FullName: ", instance.Name)
	print("City: ", instance.BillingCity)
	print("---------")

Implicit Joining

If mapped classes of related GraphQL objects have a singular foreign key relationship, the classes are implicitly joined. After importing the necessary objects, a relationship is established between your two mapped classes, as in the example below:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, DateTime, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()
class Contact(Base):
	__tablename__ = "Contact"
	Id = Column(Integer, primary_key=True)
	Name = Column(String)
	Email = Column(String)
	BirthDate = Column(DateTime)
	AccountId = Column(String, ForeignKey("Account.Id"))
	Account_Link = relationship("Account", back_populates="Contact_Link")

class Account(Base):
	__tablename__ = "Account"
	Id = Column(String, primary_key=True)
	Name = Column(String)
	BillingCity = Column(String)
	NumberOfEmployees = Column(Integer)
	Contact_Link = relationship("Contact", order_by=Contact.Id, back_populates="Account_Link")

Once the relationship is established, the tables are queried simultaneously using the session's query() method. For example:

rs = session.query(Account, Contact).filter(Account.Id == Contact.AccountId)
for Ac, Ct in rs:
  print("AccountId: ", Ac.Id)
  print("AccountName: ", Ac.Name)
  print("ContactId: ", Ct.Id)
  print("ContactName: ", Ct.Name)

Other Join Forms

In situations where mapped classes have either no foreign keys or multiple foreign keys, you may need different forms of the JOIN query to accommodate them. Using the earlier classes as examples, the following JOIN queries are possible as well:

• Explicit condition (necessary if there are no foreign keys in your mapped classes):
  

  rs = session.query(Account, Contact).join(Contact, Account.Id == Contact.AccountId)
  for Ac, Ct in rs:

• Left-to-right relationship:
  

  rs = session.query(Account, Contact).join(Account.Contact_Link)
  for Ac, Ct in rs:

• Left-to-right relationship with explicit target:
  

  rs = session.query(Account, Contact).join(Contact, Account.Contact_Link)
  for Ac, Ct in rs:

• String form of a left-to-right relationship:
  

  rs = session.query(Account, Contact).join("Contact_Link")
  for Ac, Ct in rs:

SQLAlchemy ORM also exposes support for other clauses in SQL, such as ORDER BY, GROUP BY, LIMIT, and OFFSET. All of these are supported by this connector:

ORDER BY

The following example sorts by a specified column using the session object's query() method:

rs = session.query(Users).order_by(Users.Size)
for instance in rs:
	print("Id: ", instance.Id)
	print("Name: ", instance.Name)
	print("Email: ", instance.Email)
	print("---------")

You can also use the session object's execute() method perform an ORDER BY. For example:

rs = session.execute(Users_table.select().order_by(Users_table.c.Size))
for instance in rs:

GROUP BY

The following example uses the session object's query() method to group records with a specified column:

rs = session.query(func.count(Users.Id).label("CustomCount"), Users.Name).group_by(Users.Name)
for instance in rs:
	print("Count: ", instance.CustomCount)
	print("Name: ", instance.Name)
	print("---------")

You can also use the session object's execute() method to perform a GROUP BY:

rs = session.execute(Users_table.select().with_only_columns([func.count(Users_table.c.Id).label("CustomCount"), Users_table.c.Name]).group_by(Users_table.c.Name))
for instance in rs:

LIMIT and OFFSET

The following example uses the session object's query() method to skip the first 100 records and fetch the following 25:

rs = session.query(Users).limit(25).offset(100)
for instance in rs:
	print("Id: ", instance.Id)
	print("Name: ", instance.Name)
	print("Email: ", instance.Email)
	print("---------")

You can also use the session object's execute() method to set a LIMIT or OFFSET:

rs = session.execute(Users_table.select().limit(25).offset(100))
for instance in rs:

Certain aggregate functions can also be used within SQLAlchemy by using the func module.

To import this module, execute:

from sqlalchemy.sql import func

Once func is imported, the following aggregate functions are available:

COUNT

The following example counts the number of records in a set of groups using the session object's query() method.

rs = session.query(func.count(Users.Id).label("CustomCount"), Users.Name).group_by(Users.Name)
for instance in rs:
	print("Count: ", instance.CustomCount)
	print("Name: ", instance.Name)
	print("---------")

You can also execute COUNT using the session object's execute() method:

rs = session.execute(Users_table.select().with_only_columns([func.count(Users_table.c.Id).label("CustomCount"), Users_table.c.Name])group_by(Users_table.c.Name))
for instance in rs:

SUM

This example calculates the cumulative amount of a numeric column in a set of groups.

rs = session.query(func.sum(Users.Size).label("CustomSum"), Users.Name).group_by(Users.Name)
for instance in rs:
	print("Sum: ", instance.CustomSum)
	print("Name: ", instance.Name)
	print("---------")

You can also invoke SUM using the session object's execute() method.

rs = session.execute(Users_table.select().with_only_columns([func.sum(Users_table.c.Size).label("CustomSum"), Users_table.c.Name]).group_by(Users_table.c.Name))
for instance in rs:

AVG

This example uses the session object's query() method to calculate the average amount of a numeric column in a set of groups:

rs = session.query(func.avg(Users.Size).label("CustomAvg"), Users.Name).group_by(Users.Name)
for instance in rs:
	print("Avg: ", instance.CustomAvg)
	print("Name: ", instance.Name)
	print("---------")

You can also use the session object's execute() method to invoke AVG:

rs = session.execute(Users_table.select().with_only_columns([func.avg(Users_table.c.Size).label("CustomAvg"), Users_table.c.Name]).group_by(Users_table.c.Name))
for instance in rs:

MAX and MIN

This example finds the maximum value and minimum value of a numeric column in a set of groups.

rs = session.query(func.max(Users.Size).label("CustomMax"), func.min(Users.Size).label("CustomMin"), Users.Name).group_by(Users.Name)
for instance in rs:
	print("Max: ", instance.CustomMax)
	print("Min: ", instance.CustomMin)
	print("Name: ", instance.Name)
	print("---------")

You can also use the session object's execute() method to invoke MAX and MIN:

rs = session.execute(Users_table.select().with_only_columns([func.max(Users_table.c.Size).label("CustomMax"), func.min(Users_table.c.Size).label("CustomMin"), Users_table.c.Name]).group_by(Users_table.c.Name))
for instance in rs:

When combined with the connector, Pandas can be used to generate data frames that contain your GraphQL data. Once created, a data frame can be passed to various other Python packages.

Connecting

Pandas relies on an SQLAlchemy engine to execute queries. Before you can use Pandas you must import it:

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine("graphql:///?AuthScheme=OAuth;OAuthVersion=2.0;CallbackURL=http://localhost:33333;OauthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessTokenURL=https://mysite.com/login/oauth/access_token;")

Querying Data

In Pandas, SELECT queries are provided in a call to the read_sql() method, alongside a relevant connection object. Pandas executes the query on that connection, and returns the results in the form of a data frame, which can be used for a variety of purposes.

df = pd.read_sql("""
	SELECT
	   Name,
	   Email,
     $exNumericCol;
	FROM Users;""", engine)
print(df)

Matplotlib contains a number of tools that can graphically model GraphQL data after being fed a data frame From Pandas.

Using PyPlot

Before any Matplotlib tool, such as pyplot, can be used, it must be imported:

from matplotlib import pyplot as plt

Once a Pandas data frame is obtained, it can be used to create a plot visualizing GraphQL data. For example, the following plot generates and displays a bar graph relating Name and Size values:

df.plot(kind="bar", x="Name", y=["Size"])
plt.show()

The connector can be used to create ETL applications and pipelines for CSV data in Python using Petl.

Install Required Modules

Install the Petl modules using the pip utility.

pip install petl

Connecting

After you import the modules, including the CData Python Connector for GraphQL, you can use the connector's connect function to create a connection using a valid GraphQL connection string. If you prefer not to use a direct connection, you can use a SQLAlchemy engine.

import petl as etl
import cdata.graphql as mod
cnxn = mod.connect("AuthScheme=OAuth;OAuthVersion=2.0;CallbackURL=http://localhost:33333;OauthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessTokenURL=https://mysite.com/login/oauth/access_token;")

Extract, Transform, and Load the GraphQL Data

Create a SQL query string and store the query results in a DataFrame.

sql = "SELECT	Name, Email FROM Users "
table1 = etl.fromdb(cnxn,sql)

Loading Data

With the query results stored in a DataFrame, you can load your data into any supported Petl destination. The following example loads the data into a CSV file.

etl.tocsv(table1,'output.csv')

The extension supports schema discovery by using SQL queries to available System Tables.

Using SQL

The following sections describe the discovery of metadata through several System Tables:

• Tables and Views
• Columns
• Procedures

The connector possesses system tables that are used to discover the tables and views available in the data model. Of these system tables, "sys_tables" and "sys_views" are used to fetch information about the available tables and views respectively:

Tables

import cdata.graphql as mod
conn = mod.connect("AuthScheme=OAuth;OAuthVersion=2.0;CallbackURL=http://localhost:33333;OauthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessTokenURL=https://mysite.com/login/oauth/access_token;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_tables"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

Views

import cdata.graphql as mod
conn = mod.connect("AuthScheme=OAuth;OAuthVersion=2.0;CallbackURL=http://localhost:33333;OauthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessTokenURL=https://mysite.com/login/oauth/access_token;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_views"
cur.execute(cmd, params)
rs = cur.fetchall()
for row in rs:
	print(row)

The available columns for any given table are fetched from a system table called "sys_tablecolumns". A specific table name is provided in the WHERE criteria to restrict the table from which the column information is fetched:

import cdata.graphql as mod
conn = mod.connect("AuthScheme=OAuth;OAuthVersion=2.0;CallbackURL=http://localhost:33333;OauthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessTokenURL=https://mysite.com/login/oauth/access_token;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_tablecolumns WHERE TableName = 'Users'"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

Procedures

A system table called "sys_procedures" is queried to obtain the available stored procedures that are executed:

import cdata.graphql as mod
conn = mod.connect("AuthScheme=OAuth;OAuthVersion=2.0;CallbackURL=http://localhost:33333;OauthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessTokenURL=https://mysite.com/login/oauth/access_token;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_procedures"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

Parameters

The input parameters of any stored procedure are similarly obtained from the "sys_procedureparameters" system table:

import cdata.graphql as mod
conn = mod.connect("AuthScheme=OAuth;OAuthVersion=2.0;CallbackURL=http://localhost:33333;OauthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;OAuthAccessTokenURL=https://mysite.com/login/oauth/access_token;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'SelectEntries'"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

This section details a selection of advanced features of the GraphQL connector.

User Defined Views

The connector supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views .

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats;. For further information, see the SSLServerCert property under "Connection String Options" .

Firewall and Proxy

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

Caching Data

Caching Data enables faster access to data and reduces the number of API calls, improving performance. The connector supports a simple caching model where multiple connections can also share the cache over time. When configuring the cache connection, you can specify automatic or explicit data caching.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to GraphQL and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Logging

For an overview of configuration settings that can be used to refine CData logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

Exception Handling

For an overview of how exceptions are reported and the components of an exception, see Exception Handling.

The CData Python Connector for GraphQL supports the use of user defined views: user-defined virtual tables whose contents are decided by a preconfigured query. User defined views are useful in situations where you cannot directly control the query being issued to the driver; for example, when using the driver from a tool.

Use a user defined view to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Defining Views Using a Configuration File

User defined views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This user defined view configuration file is formatted so that each root element defines the name of a view, and includes a child element, called query, which contains the custom SQL query for the view.

For example:

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

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"

Defining Views Using DDL Statements

The connector 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;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

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 is then 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]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

In order to avoid a view's name clashing with an actual entity in the data model, user defined views are exposed in the UserViews schema by default. To change the name of the schema used for UserViews, reset the UserViewsSchemaName property.

Working with User Defined Views

For example, a SQL statement with a user defined view called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

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 when appropriate.

Customizing the SSL Configuration

To enable TLS, set the following:

• URL: Prefix the connection string with https://

With this configuration, the connector attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Client SSL Certificates

The GraphQL connector also supports setting client certificates. Set the following to connect using a client certificate.

• SSLClientCert: The name of the certificate store for the client certificate.
• SSLClientCertType: The type of key store containing the TLS/SSL client certificate.
• SSLClientCertPassword: The password for the TLS/SSL client certificate.
• SSLClientCertSubject: The subject of the TLS/SSL client certificate.

Connecting Through a Firewall or Proxy

HTTP Proxies

Note: The connector uses the system proxy settings by default, without further configuration needed. If you want to connect to other proxies, set ProxyAutoDetect to False and read further.

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the connector uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

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.

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 connector'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 connector 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 connector uses a platform-dependent default location.

CacheConnection

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

CacheDriver and CacheProvider

Both the CacheDriver and CacheProvider properties are supported. Each specifies a database driver and the connection string to the caching database. CacheDriver is designed for Linux and MacOS; CacheProvider is Windows-based.

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 connector 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.

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 connector automatically initializes and builds a cache in the background. When AutoCache = true, the connector uses the cache for subsequent query executions, resulting in faster response times.

Configuring Automatic Caching

Caching the Users Table

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

SELECT Name, Email FROM Users WHERE UserLogin = 'mojombo'

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 connector.

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.

CACHE SELECT * FROM Users WHERE UserLogin = 'mojombo'

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.

  CACHE WITH TRUNCATE SELECT * FROM Users WHERE UserLogin = 'mojombo'
  

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 Users#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.

SELECT * FROM Users WHERE UserLogin='mojombo' ORDER BY Email ASC

Delete Data from the Cache

You can delete data from the cache by building a direct connection to the database. Note that the connector 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 connector 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

Query Processing

CData has a client-side SQL engine built into the connector library. This enables support for the full capabilities that SQL-92 offers, including filters, aggregations, functions, etc.

For sources that do not support SQL-92, the connector offloads as much of SQL statement processing as possible to GraphQL and then processes the rest of the query in memory (client-side). This results in optimal performance.

For data sources with limited query capabilities, the connector handles transformations of the SQL query to make it simpler for the connector. The goal is to make smart decisions based on the query capabilities of the data source to push down as much of the computation as possible. The GraphQL Query Evaluation component examines SQL queries and returns information indicating what parts of the query the connector is not capable of executing natively.

The GraphQL Query Slicer component is used in more specific cases to separate a single query into multiple independent queries. The client-side Query Engine makes decisions about simplifying queries, breaking queries into multiple queries, and pushing down or computing aggregations on the client-side while minimizing the size of the result set.

There's a significant trade-off in evaluating queries, even partially, client-side. There are always queries that are impossible to execute efficiently in this model, and some can be particularly expensive to compute in this manner. CData always pushes down as much of the query as is feasible for the data source to generate the most efficient query possible and provide the most flexible query capabilities.

More Information

For a full discussion of how CData handles query processing, see CData Architecture: Query Execution.

Logging

Capturing connector logging can be very helpful when diagnosing error messages or other unexpected behavior.

Basic Logging

To begin capturing connector logging, set these properties:

• Logfile: A filepath that designates the name and location of the log file.
• Verbosity: A numerical value (1-5) that determines the amount of detail in the log. See the page in the Connection Properties section for an explanation of the five levels.
• MaxLogFileSize: When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 100 MB. Values lower than 100 kB will use 100 kB as the value instead.
• MaxLogFileCount: A string specifying the maximum file count of log files. When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted. Minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.

Once these properties are set, the connector populates the log file as it carries out various tasks, such as when authentication is performed or queries are executed. If the specified file doesn't already exist, it is created.

Log Verbosity

The verbosity level determines the amount of detail that the connector reports to the Logfile. Supported Verbosity levels range from 1 to 5.

The following list describes each level:

1	Setting Verbosity to 1 logs the query, the number of rows returned by it, the start of execution and the time taken, and any errors.
2	Setting Verbosity to 2 logs everything included in Verbosity 1, cache queries, and additional information about the request.
3	Setting Verbosity to 3 also logs HTTP headers, as well as the body of the request and the response.
4	Setting Verbosity to 4 also logs transport-level communication with the data source. This includes SSL negotiation.
5	Setting Verbosity to 5 also logs communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.

For normal operations, Verbosity should not be set to greater than 1. At higher verbosities you can log substantial amounts of data, which can delay execution times.

To refine the logged content further by showing/hiding specific categories of information, see LogModules.

Sensitive Data

Verbosity levels of 3 and higher may capture information that you do not want shared outside of your organization. The following lists information of concern for each level:

• Verbosity 3: The full body of the request and the response, which includes all the data returned by the connector
• Verbosity 4: SSL certificates
• Verbosity 5: Any extra transfer data not included at Verbosity 3, such as non human-readable binary transfer data

Note: Although we mask sensitive values, such as passwords, in the connection string and any request in the log, it is always best practice to review the logs for any sensitive information before sharing outside your organization.

Advanced Logging

You may want to refine the exact information that is recorded to the log file. This can be accomplished using the LogModules property. This property allows you to filter the logging using a semicolon-separated list of logging modules.

Example property value:

LogModules=INFO;EXEC;SSL;SQL;META;

Note that the logfile filtering triggered by the Verbosity connection property takes precedence over the filtering imposed by this connection property. This means that operations of a higher verbosity level than the level specified in the Verbosity connection property are not printed in the logfile, even if they belong to one of the modules specified in this connection property.

The available modules and submodules are:

Module Name	Module Description	Submodules
INFO	General Information. Includes the connection string, product version (build number), and initial connection messages.	Connec – Information related to creating or destroying connections. Messag – Generic label for messages pertaining to connections, the connection string, and product version. These messages are typically specific to the connector, rather than being received and passed along directly from the service.
EXEC	Query Execution. Includes execution messages for user-written SQL queries, parsed SQL queries, and normalized SQL queries. Success/failure messages for queries and query pages appear here as well.	Messag – Messages pertaining to query execution. These messages are typically specific to the connector, rather than being received and passed along directly from the service. Normlz – Query normalization steps. Query normalization is when the product takes the user-submitted query and rewrites the query to get the same results with optimal performance. Origin – This label applies to any messages recording a user's original query (the exact, unaltered, non-normalized query executed by the user). Page – Messages related to query paging. Parsed – Query parsing steps. Parsing is the process of converting the user-submitted query into a standardized format for easier processing.
HTTP	HTTP protocol messages. Includes HTTP requests/responses (including POST messages), as well as Kerberos related messages.	KERB – HTTP requests related to Kerberos. Messag – Messages pertaining to HTTP protocols. These messages are typically specific to the connector, rather than being received and passed along directly from the service. Unpack – This label applies to messages about zipped data being returned from the service API and unpacked by the product. Res – Messages containing HTTP responses. Req – Messages containing HTTP requests.
WSDL	Messages pertaining to the generation of WSDL/XSD files.	—
SSL	SSL certificate messages.	Certif – Messages pertaining to SSL certificates.
AUTH	Authentication related failure/success messages.	Messag – Messages pertaining to authentication. These messages are typically specific to the connector, rather than being received and passed along directly from the service. OAuth – Messages related to OAuth authentication. Krbros – Kerberos-related authentication messages.
SQL	Includes SQL transactions, SQL bulk transfer messages, and SQL result set messages.	Bulk – Messages pertaining to bulk query execution. Cache – Messages related to reading row data from and writing row data to the product's cache for better performance. Messag – Messages pertaining to SQL transactions. These messages are typically specific to the connector, rather than being received and passed along directly from the service. ResSet – Query resultsets. Transc – Messages related to handling transactions, including information about the number of jobs executed and backup table handling.
META	Metadata cache and schema messages.	Cache – Messages related to reading from and modifying column and table definitions in the product's cache for better performance. Schema – Messages related to retrieving metadata from or modifying the service schema. MemSto – Messages related to writing to or reading from in-memory metadata cache. Storag – Messages relating to storing metadata on disk or in an external data store, rather than in memory.
FUNC	Information related to executing SQL functions.	Errmsg – Error messages related to executing SQL functions.
TCP	Incoming and outgoing raw bytes on TCP transport layer messages.	Send – Raw data sent via the TCP protocol. Receiv – Raw data received via the TCP protocol.
FTP	Messages pertaining to the File Transfer Protocol.	Info – Status messages related to communication in the FTP protocol. Client – Messages related to actions taken by the FTP client (the product) during FTP communication. Server – Messages related to actions taken by the FTP server during FTP communication.
SFTP	Messages pertaining to the Secure File Transfer Protocol.	Info – Status messages related to communication in the SFTP protocol. To_Server – Messages related to actions taken by the SFTP client (the product) during SFTP communication. From_Server – Messages related to actions taken by the SFTP server during SFTP communication.
POP	Messages pertaining to data transferred via the Post Office Protocol.	Client – Messages related to actions taken by the POP client (the product) during POP communication. Server – Messages related to actions taken by the POP server during POP communication. Status – Status messages related to communication in the POP protocol.
SMTP	Messages pertaining to data transferred via the Simple Mail Transfer Protocol.	Client – Messages related to actions taken by the SMTP client (the product) during SMTP communication. Server – Messages related to actions taken by the SMTP server during SMTP communication. Status – Status messages related to communication in the SMTP protocol.
CORE	Messages relating to various internal product operations not covered by other modules.	—
DEMN	Messages related to SQL remoting.	—
CLJB	Messages about bulk data uploads (cloud job).	Commit – Submissions for bulk data uploads.
SRCE	Miscellaneous messages produced by the product that don't belong in any other module.	—
TRANCE	Advanced messages concerning low-level product operations.	—

Exception Handling

Exceptions can be surfaced from either the API or the CData Python Connector for GraphQL. Each exception will have an error code, an error message, and a SQL state.

Error Codes

The error code classifies the type of error.

0	NONE	Used for unclassified errors and internally handled errors. This code also covers data source-specific errors that do not fit in any specific category.
65537	TCP_UNKNOWN_HOST	Unable to resolve a hostname (DNS failure).
65538	TCP_CONNECTION_REFUSED	Could not connect to the remote port.
65539	TCP_AUTH_FAILED	Login failed when using a binary authentication protocol. Use this for auth errors when the protocol is not HTTP (LDAP, SASL, Kerberos, ...).
65540	TCP_TIMEOUT	Did not receive a response after sending a request to the server.
65541	TCP_PROTOCOL	For wire protocol drivers. Either the server sent a bad packet that we are unable to process, or we cannot construct a packet to send.
131073	TLS_SERVER_UNTRUSTED	Could not verify SSL server certificate.
131074	TLS_CLIENT_UNTRUSTED	Server did not accept the client certificate we sent.
196609	OAUTH_DECRYPT_FAILED	OAuthEncryptKey did not decrypt the OAuthSettings file.
196610	OAUTH_MISSING_CLIENT_INFO	OAuthClientId / OAuthClientSecret / OAuthJWTCert is missing.
196611	OAUTH_MISSING_PROP	General OAuth property missing. OAUTH_MISSING_CLIENT_INFO is used for missing client ID/secret and JWT cert.
196612	OAUTH_NO_ACCESS_TOKEN	Unable to retrieve access token. Only use this when getting a token in GetOAuthAccessToken / RefreshOAuthAccessToken.
196613	OAUTH_TOKEN_EXPIRED	The access token expired. Normally used with a RefreshOAuth/OAuthException behavior.
196614	OAUTH_INVALID_PROP	OAuth property has an invalid value. OAUTH_MISSING_CLIENT_INFO / OAUTH_MISSING_PROP is used if the value is not set.
262145	HTTP_REQUEST_TIMEOUT	Did not receive a response from the HTTP server.
262146	HTTP_CLIENT_ERROR	Generic HTTP 4xx error. Only use for 4xx errors not covered by other codes.
262147	HTTP_AUTH_FAILED	HTTP 401 error.
262148	HTTP_LIMIT_EXCEEDED	HTTP 429 error.
262149	HTTP_SERVER_ERROR	HTTP 5xx error.
262150	HTTP_NOT_FOUND_ERROR	HTTP 404 error.
327681	CORE_TIMEOUT	General timeout. Not related to a specific network request.
327682	CORE_OP_NOT_ALLOWED	Operation blocked by provider permissions.
327683	CORE_CONNECTION_CONFIG	Connection configuration is not valid.
327684	CORE_SERIALIZE	Failed to encode data into a specific format (XML, JSON, CSV, ...).
327685	CORE_DESERIALIZE	Failed to decode data from a specific format (XML, JSON, CSV, ...).
393217	SQL_SYNTAX_ERROR	Unable to parse a SQL query.
393218	SQL_MISSING_COLUMNS	Query did not include required columns.
393219	SQL_MISSING_PARAMS	Stored procedure call did not include required parameters.
393220	SQL_QUERY_NOT_SUPPORTED	A part of the query is not allowed in the current context.
458753	SSH_SERVER_UNTRUSTED	Could not verify SSH server.
524289	STORAGE_LIST_EXCEPTION	Issue listing storage resources.
524290	STORAGE_RESOURCE_NOT_FOUND	Issue finding storage resources.
524291	STORAGE_ROOT_RESOURCE_NOT_FOUND	The root resource (bucket/share/drive) was not found; cannot create it in flat file drivers.
524292	STORAGE_RESOURCE_NOT_A_DIRECTORY	Storage resource is not a directory.
524293	STORAGE_RESOURCE_NOT_A_FILE	Storage resource is not a file.
524294	STORAGE_PERMISSIONS_DENIED	Storage permissions denied.

SQL State

The SQL state is used when throwing generic provider errors to the wrapper and indicates the success or failure of a call.

Some of the common SQL states are listed below:

07007	REQUIRED_CLAUSE	Class Code 07: Dynamic SQL Error.
08001	OPEN_CONNECTION	Class Code 08: Connection Exception. The connection was unable to be established to the application server or other server.
08004	REJECT_CONNECTION	The application server rejected establishment of the connection.
42501	PRIVILEGE_IDENTIFIED_OBJECT	Class Code 42: Syntax Error or Access Rule Violation. The authorization ID does not have the privilege to perform the specified operation on the identified object.
42506	AUTH_FAILED	Owner authorization failure occurred.
42601	SQL_SYNTAX	A character, token, or clause is invalid or missing.

Error Message

The error message provides more detailed reasoning about why the error occurred. It provides an explanation of the issue, and may include steps on how to resolve it.

SELECT Statements

See SELECT Statements for a syntax reference and examples.

CACHE Statements

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

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

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').

The connector provides functions that are similar to those that are available with most standard databases. These functions are implemented in the CData 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 connector 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 connector syntax for the DATENAME function:

• SQL Server:
  

  SELECT DATENAME(yy,GETDATE())

• connector:
  

  SELECT DATENAME('yy',GETDATE())

String Functions

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

Date Functions

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

Math Functions

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

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
                    

BASE64_ENCODE(input_binary)

Returns the Base64-encoded string form of a binary input.

• input_binary: The binary value to encode.

                        SELECT BASE64_ENCODE(BinaryData);
                    -- Result: 'QmFzZTY0RW5jb2RlZA=='
                    

BASE64_DECODE(input_string)

Returns the binary result of decoding a Base64-encoded string.

• input_string: The Base64-encoded string.

                        SELECT BASE64_DECODE('QmFzZTY0RW5jb2RlZA==');
                    -- Result: (binary output)
                    

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: An 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 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.
• string_valueN: (optional) Any 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 from which to read the size.

				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'
                    

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: An optional character position to start searching for expressionToFind in expressionToSearch.

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

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

ISALPHABETIC(character_expression)

Returns 1 if the character expression consists only of alphabetic characters; otherwise, 0.

• character_expression: The string expression to evaluate.

                      SELECT ISALPHABETIC('Hello');
                      -- Result: 1

                      SELECT ISALPHABETIC('Hello123');
                      -- Result: 0

                      SELECT ISALPHABETIC('Hello!');
                      -- Result: 0
                    

ISALPHANUMERIC(character_expression)

Returns 1 if the character expression consists only of alphabetic and numeric characters; otherwise, 0.

• character_expression: The string expression to evaluate.

                      SELECT ISALPHANUMERIC('Hello123');
                      -- Result: 1

                      SELECT ISALPHANUMERIC('123');
                      -- Result: 1

                      SELECT ISALPHANUMERIC('Hello.123');
                      -- Result: 0
                    

ISNUMERIC(character_expression)

Returns 1 if the character expression consists only of numeric digits and up to one decimal point; otherwise, 0.

• character_expression: The string expression to evaluate.

                      SELECT ISNUMERIC('123');
                      -- Result: 1

                      SELECT ISNUMERIC('123.45');
                      -- Result: 1

                      SELECT ISNUMERIC('123.45.67');
                      -- Result: 0

                      SELECT ISNUMERIC('12a3');
                      -- Result: 0
                    

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
                    

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 is searched for the substring.
• start locations: An optional integer that sets the character position (offset) from which to start searching.

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

                SELECT LOCATE('sample', 'XXXXXsampleXXXXX', 7)
                -- Result: 0
				

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 65535 (0 through xFFFF).

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: An optional single character to be used as the delimiter. These include:
  • a single quotation mark (')
  • a left or right bracket ([])
  • a double quotation mark (")
  • a left or right parenthesis ( () )
  • a greater or less than sign (><)
  • a left or right brace ({})
  • a backtick (`)

  If quote_character is not specified brackets are used. If an unacceptable character is supplied, it returns NULL.

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

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

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

REGEXP_REPLACE(expr, pattern [, replacement [, position [, occurrence [, match_type]]]])

Replaces occurrences of a regular expression pattern in the input string with a specified value and returns the resulting string.

• expr: The string expression to be searched.
• pattern: The regular expression pattern to match.
• replacement: (optional) The string to replace each matched occurrence of pattern with. Supports backreferences \1 through \9 and escape sequences \n, \r, \t, and \\. By default, this argument is an empty string, meaning matched portions are removed from the output string.
• position: (optional) The 1-based starting position used when searching for regular expression matches in expr. The default is 1. All characters prior to the starting position are included in the output string unaltered. Skipped characters are ignored when calculating regular expression matches, even if they match pattern.
• occurrence: (optional) Specifies whether all occurrences of pattern,, or only a specific occurrence of pattern are replaced. The default is 0, which means all occurrences of pattern are replaced with replacement. Set to 1 to only replace the first instance of the pattern; 2 to replace the second; etc.
• match_type: (optional) Modifiers used to customize matching behavior. Supported values are: 'c' (case-sensitive, default), 'i' (case-insensitive), 'm' (multiline), 'n' (dot matches newline), 'x' (extended mode). These can be freely combined by including the letters back to back. For example, 'im' applies the functionality of both 'i' and 'm'. The regular expression syntax used is that of the Extended mode ('x') ignores whitespace and allows inline comments. If the pattern needs to match a literal space, it must be explicitly escaped.

                      SELECT REGEXP_REPLACE('abc123def456', '\d+', 'NUM');
                      -- Result: 'abcNUMdefNUM'

                      SELECT REGEXP_REPLACE('Hello\nHELLO\nhello', '^hello', 'X', 1, 0, 'im');
                      -- Result: 'X\nX\nX'
                    

REPLACE(string_expression, string_pattern, string_replacement)

Replaces all occurrences of a string with another string.

• string_expression: The string expression to be searched. This 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'
                    

You can also drop the FROM and FOR clauses:

                    SELECT SUBSTRING('1234567890', 3, 2)
                    --Result: '34'
                    SELECT SUBSTRING('1234567890', 3)
                    --Result: '34567890'
                    

TEXT_ENCODE(input_string, charset)

Returns binary output by encoding a string using the specified character set.

• input_string: The plain text string.
• charset: The character set to use, such as 'UTF-8', 'ISO-8859-1'.

                    SELECT TEXT_ENCODE('Café', 'UTF-8');
                    -- Result: (binary output)
                    

TEXT_DECODE(input_binary, charset)

Returns a string decoded from binary data using the specified character set.

• input_binary: The binary value to decode.
• charset: The character set used for decoding.

                    SELECT TEXT_DECODE(BinaryData, 'UTF-8');
                    -- Result: 'Café'
                    

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'
                    

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
				

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_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
                    

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 omitted, 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
				

_ROW_NUMBER_()

Returns a row index as an additional column.

				SELECT ColumnName, _ROW_NUMBER_() FROM TableName
				-- Result: ColumnData, 0
				ColumnData2, 1
				ColumnData3, 2
				

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)
  • week (wk, ww)
  • weekday (dw)
  • dayofyear (dy, y)
  • day (dd, d)
  • hour (hh)
  • minute (mi, n)
  • second (ss, s)
  • 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:
  • Year (year, yyyy, yy)
  • Quarter (quarter, qq, q)
  • Month (month, mm, m)
  • Week (week, wk, ww)
  • Weekday (weekday, dw)
  • Dayofyear (dayofyear, dy, y)
  • Day (day, dd, d)
  • Hour (hour, hh)
  • Minute (minute, mi, n)
  • Second (second, ss, s)
  • Millisecond (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
                

DATE_FORMAT(date,format)

Returns the date or timestamp in the format specified. This function mirrors the MySQL DATE_FORMAT function.

• date: A date or timestamp string.
• format: The specifier string of the desired output format. The list of supported format specifiers comes from the MySQL DATE_FORMAT function (see link to MySQL documentation above).

					SELECT DATE_FORMAT('9/4/2021 3:11:53 AM','%h')
					-- Result: 03
				  

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), and nanosecond (ns).
• 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), 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
                  

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
                  

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
                  

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
                  

DAYNAME(date)

Returns the name of the day of the week of the specified date.

• date: The datetime string that specifies the date.

                    SELECT DAYNAME('8/18/2021');
                    -- Result: Wednesday
                  

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
                

EXTRACT(date_part FROM date_column_name)

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

• date_part: One of the following date components: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
• date_column_name: The name of a date column in a table.

                  SELECT EXTRACT(YEAR FROM DateColumn)
                  -- Result: 2021
                

FDWEEK(date)

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

• date: The datetime string that specifies the date.
• weeks to add: An optional integer expression specifying the number of months to add to the date before calculating the first day of the week.

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

          SELECT FDWEEK('02-08-2018', 1)
          --Result: 02/11/2018
				  

FDMONTH(date)

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

• date: The datetime string that specifies the date.
• month to add: An optional integer expression specifying the number of months to add to the date before calculating the first day of the month.

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

          SELECT FDMONTH('02-08-2018', 1) 
          --Result: 03/01/2018
				  

FDQUARTER(date)

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

• date: The datetime string that specifies the date.
• quarters to add: An optional integer expression specifying the number of months to add to the date before calculating the first day of the quarter.

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

          SELECT FDQUARTER('05-08-2018',1)
          --Result: 07/01/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
				

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
                    

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.

In addition, GETUTCDATE can take an optional second parameter, a date and time that are converted to UTC.

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

                  SELECT GETUTCDATE('2020/08/31 13:56:00')
                  --Result: '2020-08-31 17:56:00'
                

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 00:00:00
				

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 00:00:00
				

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 00:00:00
				

LDWEEK(date)

Returns the last day of the provided week.

• date: The datetime string.
• weeks to add: An optional integer expression specifying the number of months to add to the date before calculating the last day of the week.

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

LDMONTH(date)

Returns the last day of the provided month.

• date: The datetime string.
• months to add: An optional integer expression specifying the number of months to add to the date before calculating the last day of the month.

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

        SELECT LDMONTH('02-08-2020', 1)
        --Result: 03/31/2020
				

LDQUARTER(date)

Returns the last day of the provided quarter.

• date: The datetime string.
• quarters to add: An optional integer expression specifying the number of months to add to the date before calculating the last day of the quarter.

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

        SELECT LDQUARTER('02-02-2020',1)
        --Result: 06/30/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. 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
                    

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)

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 connector:

SELECT {
  [ TOP <numeric_literal> | DISTINCT ]
  { 
    * 
    | { 
        <expression> [ [ AS ] <column_reference> ] 
        | { <table_name> | <correlation_name> } .* 
      } [ , ... ] 
  }
  { 
    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> 
    ]
  ] 
}

<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 
  | {RANK() | DENSE_RANK()} OVER ([PARTITION BY <column_reference>] {ORDER BY <column_reference>})
  | <literal>
  | <sql_function> 

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

Examples

1. Return all columns:
   

   SELECT * FROM Users

2. Rename a column:
   

   SELECT [Email] AS MY_Email FROM Users

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

   SELECT CAST(Size AS VARCHAR) AS Str_Size FROM Users

4. Search data:
   

   SELECT * FROM Users WHERE UserLogin = 'mojombo'

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

   SELECT COUNT(*) AS MyCount FROM Users 

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

   SELECT COUNT(DISTINCT Email) FROM Users 

7. Return the unique items matching the query criteria:
   

   SELECT DISTINCT Email FROM Users 

8. Sort a result set in ascending order:
   

   SELECT Name, Email FROM Users  ORDER BY Email ASC

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

   SELECT Name, Email FROM Users LIMIT 10 

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

    SELECT * FROM Users WHERE UserLogin = @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 GraphQL.

    SELECT * FROM Users WHERE Pseudo = '@Pseudo'
    

Aggregate Functions

For SELECT examples using aggregate functions, see Aggregate Functions.

JOIN Queries

See JOIN Queries for SELECT query examples using JOINs.

Date Literal Functions

Date Literal Functions contains SELECT examples with date literal functions.

Window Functions

See Window Functions for SELECT examples containing window functions.

Table-Valued Functions

See Table-Valued Functions for SELECT examples with table-valued functions.

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM Users WHERE UserLogin = 'mojombo'

COUNT(DISTINCT)

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

SELECT COUNT(DISTINCT Name) AS DistinctValues FROM Users WHERE UserLogin = 'mojombo'

AVG

Returns the average of the column values.

SELECT Email, AVG(Size) FROM Users WHERE UserLogin = 'mojombo'  GROUP BY Email

MIN

Returns the minimum column value.

SELECT MIN(Size), Email FROM Users WHERE UserLogin = 'mojombo' GROUP BY Email

MAX

Returns the maximum column value.

SELECT Email, MAX(Size) FROM Users WHERE UserLogin = 'mojombo' GROUP BY Email

SUM

Returns the total sum of the column values.

SELECT SUM(Size) FROM Users WHERE UserLogin = 'mojombo'

The CData Python Connector for GraphQL 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 Commits.AuthorName, CommitComments.Body FROM Commits, CommitComments WHERE Commits.Sha=CommitComments.CommitSha

Left Join

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

SELECT Commits.AuthorName, CommitComments.Body FROM Commits LEFT OUTER JOIN CommitComments ON Commits.Sha=CommitComments.CommitSha

Window functions allow you to create computed fields from a group of rows (a window) that return a result for each row, as opposed to one computed result for a set of rows, as is the case with aggregate functions. The connector supports the following window function syntax.

Note: Window function support is an experimental feature of the connector. This functionality extends beyond the connector's core scope of being SQL-92 compliant. As such, performance with window functions may not be optimal.

Window Function Clauses

OVER

The OVER clause defines the window over which window functions are performed.

SELECT A, B, <window function> OVER (<window frame>) FROM TableName

The <window function> refers to any supported window function clause, and the <window frame> refers to one or more clauses that specify the logic by which the window is defined.

PARTITION BY

The PARTITION BY clause subdivides a window into sub-windows called partitions. For each unique value in the column specified in the PARTITION BY clause, every record with that value collectively forms an individual partition.

SELECT A, B, <window function> OVER (PARTITION BY A ORDER BY B) From Users

The <window function> refers to any supported window function clause.

Window Functions

The connector supports math, ranking, and analytic window functions.

Math

These window functions perform mathematical operations on the records within the window.

COUNT()

Calculates the number of records in each partition. The calculated column is of the data type "int".

In each partition, every record will display the total number of records in that partition.

SELECT Name, Role, Earnings, COUNT() OVER (PARTITION BY Role) FROM Employees

COUNT_BIG()

Calculates the number of records in each partition. The calculated column is of the data type "bigint".

In each partition, every record will display the total number of records in that partition.

SELECT Name, Role, Earnings, COUNT_BIG() OVER (PARTITION BY Role) FROM Employees

MIN(numeric_column)

Calculates the minimum value of a numerical column per partition.

In each partition, every record will display the minimum value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, MIN(Earnings) OVER (PARTITION BY Role) FROM Employees

MAX(numeric_column)

Calculates the maximum value of a numerical column per partition.

In each partition, every record will display the maximum value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, MAX(Earnings) OVER (PARTITION BY Role) FROM Employees

SUM(numeric_column)

Calculates the sum of a numerical column per partition.

In each partition, every record will display the sum of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, SUM(Earnings) OVER (PARTITION BY Role) FROM Employees

AVG(numeric_column)

Calculates the average value of a numerical column per partition.

In each partition, every record will display the average value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, AVG(Earnings) OVER (PARTITION BY Role) FROM Employees

MEDIAN(numeric_column)

Calculates the median value of a numerical column per partition.

In each partition, every record will display the median value of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, MEDIAN(Earnings) OVER (PARTITION BY Role) FROM Employees

STDEV(numeric_column)

Calculates the standard deviation of a numerical column per partition.

In each partition, every record will display the standard deviation of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, STDEV(Earnings) OVER (PARTITION BY Role) FROM Employees

STDEVP(numeric_column)

Calculates the population standard deviation of a numerical column per partition.

In each partition, every record will display the population standard deviation of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, STDEVP(Earnings) OVER (PARTITION BY Role) FROM Employees

VAR(numeric_column)

Calculates the statistical standard variance of a numerical column per partition.

In each partition, every record will display the statistical standard variance of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, VAR(Earnings) OVER (PARTITION BY Role) FROM Employees

VARP(numeric_column)

Calculates the variance population of a numerical column per partition.

In each partition, every record will display the variance population of numeric_column across the records in that partition.

SELECT Name, Role, Earnings, VARP(Earnings) OVER (PARTITION BY Role) FROM Employees

Ranking

These window functions rank records that fall within the window and its partitions.

RANK()

Assigns a rank number to each record in a window based on the value of the column specified in the required ORDER BY clause.

If two or more records have an equal value in the in ranked column, they all receive the same rank number and the rank count increments internally, skipping ahead one rank number for each record with a duplicate value in the ORDER BY column.

SELECT Name, Email, RANK() OVER (ORDER BY Email) AS Rank FROM Users

If you add a PARTITION BY clause, a separate set of ranks is calculated for each partition.

SELECT Name, Email, RANK() OVER (PARTITION BY Name ORDER BY Email) AS Rank FROM Users

DENSE_RANK()

Operates like the RANK() function, but it doesn't increment the internal rank counter for each record with a duplicate value in the ranked column.

This means that, while records with identical values in the ORDER BY column still share a rank number, the function never skips a rank number.

SELECT Name, Email, DENSE_RANK() OVER (PARTITION BY Name ORDER BY Email) AS Rank FROM Users

If you add a PARTITION BY clause, a separate set of ranks is calculated for each partition.

SELECT Name, Email, DENSE_RANK() OVER (PARTITION BY Name ORDER BY Email) AS Rank FROM Users

ROW_NUMBER()

Calculates a row number for each record. An ORDER BY clause in the OVER clause is required.

SELECT Name, Role, Earnings, ROW_NUMBER() OVER (ORDER BY Role) FROM Employees

If you define multiple partitions with PARTITION BY, a new set of row numbers are calculated for each partition.

SELECT Name, Role, Earnings, ROW_NUMBER() OVER (PARTITION BY Role ORDER BY Earnings) FROM Employees

NTILE()

Distributes rows of an ordered partition into a specified number of approximately equal groups, or buckets. It assigns each group a bucket number starting from one. For each row in a group, the NTILE() function assigns a bucket number representing the group to which the row belongs.

The syntax of NTILE() is:

NTILE(buckets) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

The following are paramaters that NTILE() supports:

• buckets: The number of buckets into which the rows are divided. The buckets can be an expression or subquery that evaluates to a positive integer. It cannot be a window function.
• PARTITION BY: distributes rows of a result set into partitions to which the NTILE() function is applied.
• ORDER BY is clause that specifies the logical order of rows in each partition to which the NTILE() is applied.

If the number of rows is not divisible by the buckets, the NTILE() function returns groups of two sizes with the difference by one. The larger groups always precede the smaller group in the order set by ORDER BY in the OVER() clause.

If the total of rows is divisible by the number of buckets, the function divides the rows evenly among buckets. The following statement creates a new table named ntile_demo that stores 10 integers:

CREATE TABLE sales.ntile_demo (
	v INT NOT NULL
);
	
INSERT INTO sales.ntile_demo(v) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);	
	
SELECT * FROM sales.ntile_demo;

This statement uses the NTILE() function to divide ten rows into three groups:

SELECT 
	v, 
	NTILE (3) OVER (
		ORDER BY v
	) buckets
FROM 
	sales.ntile_demo;

Analytical

These window functions perform analytical operations on the records within the window.

PERCENT_RANK()

Calculates the relative rank SQL Percentile of each row. It returns values greater than zero, but the maximum value is one. It does not count any NULL values. This function is nondeterministic.

The syntax of PERCENT_RANK() is:

PERCENT_RANK() OVER (
      [PARTITION BY partition_expression, ... ]
      ORDER BY sort_expression [ASC | DESC], ...
  )
  

This syntax uses the following parameters.

• PARTITION BY: By default, SQL Server treats the whole data set as a single set. You can specify the PARTITION BY clause to divide data into multiple sets. The Percent_Rank function performs the analytical calculations on each set. This parameter is optional.
• ORDER BY: Sorts the data in either ascending or descending order. This parameter is required.

Table-valued functions are functions that return a table (rowset).

Note: Table-valued function support is an experimental feature of the connector. This functionality extends beyond the connector's core scope of being SQL-92 compliant. As such, performance with these functions may not be optimal.

Table-Valued Function Clauses

CROSS APPLY

The CROSS APPLY operator is used to perform a subquery on each row of a table or resultset produced by a preceding table expression.

<table_expression_1> CROSS APPLY <table_expression_2>

The second table expression can reference results from the first table expression to create derived columns or an altered recordset via a table-valued function.

Each resulting record is an instance of the record it's splitting, with all the same column values, except for the column(s) containing the value split by the function.

WITH

The WITH clause is used alongside certain table-valued functions to match against constructs within the structure being split (keys, element names, attribute names, etc.) and/or to specify metadata for the columns generated from the function.

SELECT A.ColumnName, X.DerivedColumnName FROM TableName A CROSS APPLY <table-valued function> WITH (DerivedColumnName varchar(255)) AS X

Table-Valued Functions

STRING_SPLIT(input_text,delimiter)

Takes each record in the recordset of the preceding table expression, splits the column containing delimiters (input_text) into substrings separated by the delimiter, and returns one record per substring.

• input_text: A column whose value you want to parse.
• delimiter: The character used to split the value of the column specified in input_text.

Suppose there is a column called "SplitColumn" with the following content:

One-Two-Three

To unpack this value across multiple records:

SELECT A.ID, X.Value FROM [TableWithDelimitedStringField] A CROSS APPLY STRING_SPLIT(A.SplitColumn,'-') WITH (Value VARCHAR(255)) AS X

-- Results:
-----------
|ID|Value|
|1|One|
|1|Two|
|1|Three|

JSONTABLE(json_content,[jsonpath])

For each record in the recordset of the preceding table expression, returns one record for each instance of a key in a JSON array (json_content) that matches the key(s) specified in the WITH clause, at the scope specified by the "jsonpath" input.

• json_content: A JSON "table" (array of objects). The contents can nest, but this must be a single JSON array, not any other JSON structure, at the root level.
  • The values of every instance of the key(s) provided in the WITH clause are retrievable only for substructures which are immediate children of the root-level JSON array.
• jsonpath: An optional JSONPath query defining the scope, within the json_content array, that you want to pull content from. The JSON key(s) identified in the WITH clause must exist at the scope defined in this parameter. This defaults to the JSON root ($).

Consider a sample table with a single record, including an ID column and column with JSON content called "JSONColumn" with the following content:

[
	{
		"name": "Samuel",
		"email": "sam@gmail.com",
		"extrainfo": {
			"city": "Seattle"
		}
	},
	{
		"name": "Katherine",
		"email": "kat@gmail.com",
	},
	{
		"name": "George",
		"email": "george23@gmail.com",
	},
	{
		"name": "Carlos",
		"email": "carlos32@gmail.com",
	}
]

To extract all values for a certain key, specify the scope in the JSONTABLE function and provide the desired key(s) in the WITH clause.

SELECT A.ID, X.name FROM [TableWithJSONField] A CROSS APPLY JSONTABLE(A.JSONColumn) WITH (name VARCHAR(255)) AS X

-- Results: 
|ID|name|
---------
|1 |Samuel|
|1 |Katherine|
|1 |George|
|1 |Carlos|

XMLTABLE(xml_content,[xpath,child_type])

For each record in the resultset of the preceding table expression, returns one record for each of the elements and/or attributes in an XML structure (xml_content) that match the tag name(s) and/or attribute name(s) specified in the WITH clause, at the scope specified in the "xpath" input.

• xml_content: A column containing an XML structure.
• xpath: An optional XPath that specifies the scope within the XML structure at which the connector extracts content matching the tag/attribute name(s) specified in the WITH clause.
  • When extracting the content of sub-elements, the connector can retrieve all content from tags at the root level, (depth 0) immediate children of the root (depth 1), and children of those children (depth 2).
  • When extracting element attribute content, the connector can retrieve all content from tags containing the specified attribute at the root level (depth 0) and from immediate children of root-level elements (depth 1).
• child_type: An optional parameter that specifies the part(s) of the parent element (specified in the xpath input) that the column(s) provided in the WITH clause are checked against to identify content.
  • You can supply the following values:
    • 0: The column(s) in the WITH clause are checked for matches against the parent element's attribute names and sub-element tag names.
    • 1: The column(s) in the WITH clause are checked for matches against the parent element's attribute names.
    • 2: The column(s) in the WITH clause are checked for matches against the parent element's sub-element tag names.
  • When not supplied, this defaults to 0.

Extracting Sub-Element Values

Consider a sample table with a single record, including an ID column and a column with XML content called "XMLContent" with the following content:

<shoppingList>
    <item>
        <name>Apples</name>
        <quantity>3</quantity>
        <unit>Kg</unit>
    </item>
    <item>
        <name>Bread</name>
        <quantity>2</quantity>
        <unit>Loaf</unit>
		<extrainfo>
			<Type>Whole-Grain</Type>
		</extrainfo>
    </item>
    <item>
        <name>Milk</name>
        <quantity>1</quantity>
        <unit>Carton</unit>
    </item>
    <item>
        <name>Eggs</name>
        <quantity>12</quantity>
        <unit></unit>
    </item>
</shoppingList>

To extract sub-element content, specify the scope in the XMLTABLE function and provide the desired element name(s) in the WITH clause. Note that this will not work if the XMLTABLE function's child_type input is set to 1.

SELECT A.ID, X.name FROM [TableWithXMLField] A CROSS APPLY XMLTABLE(A.XMLContent,'//*/item') WITH (name VARCHAR(255)) AS X

-- Results: 
|ID|name|
---------
|1|Apples|
|1|Bread|
|1|Milk|
|1|Eggs|

Extracting Values Using Element Tag Attributes

Suppose you have this sample table with a single record, including an ID column and a column with XML content called "XMLContent" with the following content:

<restaurant>
  <dish type="appetizer">
    <name lang="en">Caprese Salad</name>
    <chef>Chef Giovanni</chef>
    <price currency="USD">9.99</price>
  </dish>
  <dish type="main-course">
    <name lang="fr">Boeuf Bourguignon</name>
    <chef>Chef Marie</chef>
    <price currency="EUR">19.99</price>
  </dish>
  <dish type="dessert">
    <name lang="es">Tres Leches Cake</name>
    <chef>Chef Alejandro</chef>
    <price currency="MXN">89.99</price>
  </dish>
</restaurant>

To extract attribute content, specify the scope in the XMLTABLE function and provide the desired attribute name(s) in the WITH clause. Note that this will not work if the XMLTABLE function's child_type input is set to 2.

SELECT A.ID, X.type FROM [TableWithXMLField] A CROSS APPLY XMLTABLE(A.XMLContent,'//*/dish') WITH (type VARCHAR(255)) AS X

-- Results: 
|ID|type|
---------
|1|appetizer|
|1|main-course|
|1|dessert|

CSVTABLE(csv_content,[delimiter])

For each record in the resultset of the preceding table expression, reads from a column that contains a CSV table (csv_content) and for each record in that CSV table, returns one record containing the value of the CSV column(s) specified in the WITH clause.

• csv_content: A column containing a CSV table.
• delimiter: An optional custom delimiter (instead of a comma) which splits the CSV content contained in the csv_content input.

Consider a sample table with a single record, including an ID column and a column containing CSV table called "CSVContent" with the following content:

Name;Category;Price
Apple;Fruit;0.99
Spaghetti;Pasta;5.49
Chicken Breast;Meat;8.99
Broccoli;Vegetable;2.49

To select every value in the "Name" column and account for the custom delimiter (;):

SELECT A.ID, X.Name FROM [TableWithCSVField] A CROSS APPLY CSVTABLE(A.CSVContent,';') WITH (Name VARCHAR(255)) AS X

-- Results:
|ID|Name|
-----------
|1|Apple|
|1|Spaghetti|
|1|Chicken Breast|
|1|Broccoli|

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 connector 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 connector 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 connector 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 connector 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 connector creates the cache table based on the SELECT statement without executing the query.

ALTER SCHEMA

If this option is set, the connector 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 Users

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

CACHE CachedUsers SELECT * FROM Users

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 CachedUsers SELECT * FROM Users 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 Name and Email even though the cache table CachedUsers has all the columns in Users.

CACHE CachedUsers SCHEMA ONLY SELECT * FROM Users
CACHE CachedUsers SELECT Name, Email FROM Users

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; 

PIVOT and UNPIVOT can be used to change a table-valued expression into another table.

PIVOT

PIVOT rotates a table-value expression by turning unique values from one column into multiple columns in the output. PIVOT can run aggregations where required on any column value.

PIVOT Synax

 
"SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]
FROM
(
SELECT DaysToManufacture, StandardCost
FROM Production.Product
) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;"

UNPIVOT

UNPIVOT carries out nearly the opposite to PIVOT by rotating columns of a table-valued expressions into column values.

UNPIVOT Sytax

 
"SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;"

For further information on PIVOT and UNPIVOT, see FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

---

Property	Description
AuthScheme	Specifies the authentication method to use when connecting to remote services.
URL	Specifies the endpoint URL of the GraphQL service.
User	Specifies the authenticating user's user ID.
Password	Specifies the authenticating user's password.

AWS Authentication

---

Property	Description
CredentialsLocation	The location of the settings file where MFA credentials are saved.
AWSCognitoRegion	The hosting region for AWS Cognito.
AWSUserPoolId	The User Pool Id.
AWSUserPoolClientAppId	The User Pool Client App Id.
AWSUserPoolClientAppSecret	Optional. The User Pool Client App Secret.

OAuth

---

Property	Description
InitiateOAuth	Specifies the process for obtaining or refreshing the OAuth access token, which maintains user access while an authenticated, authorized user is working.
OAuthVersion	Identifies the version of OAuth being used.
OAuthClientId	Specifies the client ID (also known as the consumer key) assigned to your custom OAuth application. This ID is required to identify the application to the OAuth authorization server during authentication.
OAuthClientSecret	Specifies the client secret assigned to your custom OAuth application. This confidential value is used to authenticate the application to the OAuth authorization server. (Custom OAuth applications only.).
OAuthAccessToken	Specifies the OAuth access token used to authenticate requests to the data source. This token is issued by the authorization server after a successful OAuth exchange.
OAuthAccessTokenSecret	The OAuth access token secret for connecting using OAuth.
OAuthSettingsLocation	Specifies the location of the settings file where OAuth values are saved.
CallbackURL	Identifies the URL users return to after authenticating to GraphQL via OAuth (Custom OAuth applications only).
Scope	Specifies the scope of the authenticating user's access to the application, to ensure they get appropriate access to data. If a custom OAuth application is needed, this is generally specified at the time the application is created.
OAuthIncludeCallbackURL	Whether to include the callback URL in an access token request.
OAuthAuthorizationURL	The authorization URL for the OAuth service.
OAuthAccessTokenURL	The URL from which the OAuth access token is retrieved.
OAuthRefreshTokenURL	The URL to refresh the OAuth token from.
OAuthRequestTokenURL	The URL the service provides to retrieve request tokens from. This is required in OAuth 1.0.
OAuthVerifier	Specifies a verifier code returned from the OAuthAuthorizationURL . Used when authenticating to OAuth on a headless server, where a browser can't be launched. Requires both OAuthSettingsLocation and OAuthVerifier to be set.
PKCEVerifier	The PKCE code verifier generated from executing the GetOAuthAuthorizationUrl stored procedure for PKCE authentication schemes.
AuthToken	The authentication token used to request and obtain the OAuth Access Token.
AuthKey	The authentication secret used to request and obtain the OAuth Access Token.
OAuthParams	A comma-separated list of other parameters to submit in the request for the OAuth access token in the format paramname=value.
OAuthRefreshToken	Specifies the OAuth refresh token used to request a new access token after the original has expired.
OAuthExpiresIn	Specifies the duration in seconds, of an OAuth Access Token's lifetime. The token can be reissued to keep access alive as long as the user keeps working.
OAuthTokenTimestamp	Displays a Unix epoch timestamp in milliseconds that shows how long ago the current access token was created.

JWT OAuth

---

Property	Description
OAuthJWTCert	Supplies the name of the client certificate's JWT Certificate store.
OAuthJWTCertType	Identifies the type of key store containing the JWT Certificate.
OAuthJWTCertPassword	Provides the password for the OAuth JWT certificate used to access a password-protected certificate store. If the certificate store does not require a password, leave this property blank.
OAuthJWTCertSubject	Identifies the subject of the OAuth JWT certificate used to locate a matching certificate in the store. Supports partial matches and the wildcard '*' to select the first certificate.

SSL

---

Property	Description
SSLClientCert	Specifies the TLS/SSL client certificate store for SSL Client Authentication (2-way SSL). This property works in conjunction with other SSL-related properties to establish a secure connection.
SSLClientCertType	Specifies the type of key store containing the TLS/SSL client certificate for SSL Client Authentication. Choose from a variety of key store formats depending on your platform and certificate source.
SSLClientCertPassword	Specifes the password required to access the TLS/SSL client certificate store. Use this property if the selected certificate store type requires a password for access.
SSLClientCertSubject	Specifes the subject of the TLS/SSL client certificate to locate it in the certificate store. Use a comma-separated list of distinguished name fields, such as CN=www.server.com, C=US. The wildcard * selects the first certificate in the store.
SSLServerCert	Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Firewall

---

Property	Description
FirewallType	Specifies the protocol the provider uses to tunnel traffic through a proxy-based firewall.
FirewallServer	Identifies the IP address, DNS name, or host name of a proxy used to traverse a firewall and relay user queries to network resources.
FirewallPort	Specifies the TCP port to be used for a proxy-based firewall.
FirewallUser	Identifies the user ID of the account authenticating to a proxy-based firewall.
FirewallPassword	Specifies the password of the user account authenticating to a proxy-based firewall.

Proxy

---

Property	Description
ProxyAutoDetect	Specifies whether the provider checks your system proxy settings for existing proxy server configurations, rather than using a manually specified proxy server.
ProxyServer	Identifies the hostname or IP address of the proxy server through which you want to route HTTP traffic.
ProxyPort	Identifies the TCP port on your specified proxy server that has been reserved for routing HTTP traffic to and from the client.
ProxyAuthScheme	Specifies the authentication method the provider uses when authenticating to the proxy server specified in the ProxyServer connection property.
ProxyUser	Provides the username of a user account registered with the proxy server specified in the ProxyServer connection property.
ProxyPassword	Specifies the password of the user specified in the ProxyUser connection property.
ProxySSLType	Specifies the SSL type to use when connecting to the proxy server specified in the ProxyServer connection property.
ProxyExceptions	Specifies a semicolon-separated list of destination hostnames or IPs that are exempt from connecting through the proxy server set in the ProxyServer connection property.

Logging

---

Property	Description
Logfile	Specifies the file path to the log file where the provider records its activities, such as authentication, query execution, and connection details.
Verbosity	Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.
LogModules	Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged.
MaxLogFileSize	Specifies the maximum size of a single log file in bytes. For example, '10 MB'. When the file reaches the limit, the provider creates a new log file with the date and time appended to the name.
MaxLogFileCount	Specifies the maximum number of log files the provider retains. When the limit is reached, the oldest log file is deleted to make space for a new one.

Schema

---

Property	Description
Location	Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .
Tables	Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC .
Views	Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC .
ExpandArgumentsDepth	Specifies the depth the provider searches for columns within nested GraphQL arguments of type INPUT_OBJECT. Higher values expand deeper levels of nested fields, while lower values limit the expansion.
ExpandTablesDepth	Specifies how deeply the provider explores nested child tables in the GraphQL schema when building the relational model. This setting only takes effect if the ExposeObjectTables property is set to DEEP.
ExpandTemporaryTablesDepth	Specifies the depth at which the provider includes nested child temporary tables in the schema. This property only takes effect when the ExposeDynamicProcedures property is set to true.
ExpandColumnsDepth	Specifies the depth at which the provider searches for columns within nested GraphQL objects, exposing those fields as columns.
IncludeDeprecatedMetadata	Specifies whether the provider includes deprecated tables and columns in the schema.
ExposeDynamicProcedures	Specifies whether the provider exposes GraphQL mutations as dynamic procedures in the schema.
ExposeObjectTables	Specifies the scope of GraphQL object type fields that the provider exposes as tables in the schema.
ExposeAbstractTypes	Specifies the scope of GraphQL abstract types (interfaces and unions) that the provider exposes in the schema.
ExpandTypeRecursion	Specifies how the provider handles recursive types during GraphQL schema exploration.

Caching

---

Property	Description
AutoCache	Specifies whether the content of tables targeted by SELECT queries is automatically cached to the specified cache database.
CacheProvider	The namespace of an ADO.NET provider. The specified provider is used as the target database for all caching operations.
CacheDriver	The driver class of a JDBC driver. The specified driver is used to connect to the target database for all caching operations.
CacheConnection	Specifies the connection string for the specified cache database.
CacheLocation	Specifies the path to the cache when caching to a file.
CacheTolerance	Notes the tolerance, in seconds, for stale data in the specified cache database. Requires AutoCache to be set to True.
Offline	Gets the data from the specified cache database instead of live GraphQL data.
CacheMetadata	Determines whether the provider caches table metadata to a file-based cache database.

Miscellaneous

---

Property	Description
CustomHeaders	Specifies additional HTTP headers to append to the request headers created from other properties, such as ContentType and From. Use this property to customize requests for specialized or nonstandard APIs.
GenerateSchemaFiles	Indicates the user preference as to when schemas should be generated and saved.
MaxRows	Specifies the maximum number of rows returned for queries that do not include either aggregation or GROUP BY.
Other	Specifies advanced connection properties for specialized scenarios. Use this property only under the guidance of our Support team to address specific issues.
Pagesize	Specifies the maximum number of results returned per page from GraphQL.
PseudoColumns	Specifies the pseudocolumns to expose as table columns, expressed as a string in the format 'TableName=ColumnName;TableName=ColumnName'.
RTK	Specifies the runtime key for licensing the provider. If unset or invalid, the provider defaults to the standard licensing method. This property is only required in environments where the standard licensing method is unsupported or requires a runtime key.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error.
UserDefinedViews	Specifies a filepath to a JSON configuration file that defines custom views. The provider automatically detects and uses the views specified in this file.