JDBC Driver Pack

Advanced Features

Our software includes many advanced features that serve specific purposes. These features include SSL and proxy settings, cache configurations, multithreaded processing, advanced logging capabilities, and file attachment support.

SSL and Proxy

Secure communication between the driver and the server is crucial and is achieved via SSL/TLS when working with HTTP based APIs. When working with such APIs based on a HTTPS service endpoint, the driver validates the server’s certificate against the system’s trusted certificate authoritities when establishing the connection. You can configure the driver to bypass such certificate verifications by setting IgnoreCertificateErrors property to true. Please note that doing so carries security risks, you should only do so when there is a specific reason and you are confident that your infrastructure is securely protected using necessary tools or options.

Properties connectionProps = new Properties();
connectionProps.put("IgnoreCertificateErrors", "true");

Our HTTP based API drivers support ProxyServer, ProxyServerPort, ProxyUserName, and ProxyPassword properties which can be used to configure a web proxy when making service calls.

Properties connectionProps = new Properties();
connectionProps.put("ProxyServer", "localhost");
connectionProps.put("ProxyServerPort", "8888");

Cache

The drivers cache metadata, which helps improve overall performance by avoiding frequent repetive metadata service calls. By default, the metadata cache expires after 30 minutes. You may set the CacheExpirationTime property to 0 to disable caching entirely. It is important to note metadata requests can be expensive for some services, it is not recommended to turn it off entirely unless there is a specific reason.

Note: The JDBC driver only caches metadata in memory and does not cache any execution results.

Properties connectionProps = new Properties();
connectionProps.put("CacheExpirationTime", "60");

Execution Result Analysis

The SaveResult property allows you to save the execution results to the file system for further analysis, which can be useful for troubleshooting complex queries or reviewing results later. When enabled, the execution result is saved in the same folder where the .jar library is located.

Properties connectionProps = new Properties();
connectionProps.put("SaveResult", "true");

Multithreaded Processing

The driver supports multithreading to achieve a greater performance for large-volume write operations. By configuring the TotalThreads property, you can set the number of threads to run concurrently during large-volume data operations. By default, this property has a value of 0, which means that multithreading is disabled.

Properties connectionProps = new Properties();
connectionProps.put("TotalThreads", "20");

Logging

Logging is an important tool made available for debugging and tracking the performance of queries when using our JDBC drivers. Different log levels are offered for specific troubleshooting needs.

Log Settings

Users can configure logging using the following properties:

• LogLevel: The logging level for the JDBC driver. By default, it is set to 'INFO'. For more details refer to the Log Levels section.
• LogPath: The directory where log files will be stored.
• LogFileSize: The maximum size in bytes for a log file, the default size is 10 MB (10485760 bytes).

Log Levels

Logging levels allow users to quickly identify critical issues by categorizing logs by type and severity. The driver supports the following log levels:

Properties connectionProps = new Properties();
connectionProps.put("LogLevel", "INFO");
connectionProps.put("LogPath", "./jdbcLogs");
connectionProps.put("LogFileSize", "10485760");

System Virtual Tables

System Virtual Tables are special tables offered by the JDBC drivers that help you understand how the connector or API works when writing SQL queries.

system.tables

The system.tables table contains the list of API objects or entities available in the system.

Sample Code:

String sql = "SELECT * FROM system.tables WHERE table_name = 'users'";
    try {
        ResultSet resultSet = statement.executeQuery(sql);
        LOGGER.info(resultSet.toString());
    } catch (SQLException e) {
        LOGGER.severe(e.toString());
    }

Sample Output:

is_creatable,is_deletable,is_mutable,is_readable,is_updatable,is_upsertable,table_name
false,false,false,true,false,false,users

The system.tables entity generally returns the following metadata columns:

Note: Some services might return more columns than the above list.

system.columns

The system.columns table can be used to retrieve the columns available for each API operation, along with their data types and formats. Users can use a SELECT statement to query the system.columns table in order to retrieve all available columns for each operation or each object.

Sample Code

String sql = "SELECT * FROM system.columns WHERE table_name = 'users'";
    try {
        ResultSet resultSet = statement.executeQuery(sql);
        LOGGER.info(resultSet.toString());
    } catch (SQLException e) {
        LOGGER.severe(e.toString());
    }

Sample Output:

table_name,name,type,format,is_searchable,is_creatable,is_updatable,is_deletable,is_readable,is_upsertable
users,emails,array,comma(,) delimited,true,false,false,false,false,false
users,phone_numbers,array,comma(,) delimited,true,false,false,false,false,false
users,ids,array,comma(,) delimited,true,false,false,false,false,false
users,sort_by,array,comma(,) delimited,true,false,false,false,false,false
users,filter_operator,string,null,true,false,false,false,false,false
users,states,array,comma(,) delimited,true,false,false,false,false,false

The system.columns entity returns the following metadata columns:

system.functions

The system.functions table can be used to retrieve the list of available functions or mutations (if it is a GraphQL based service) supported by the API. This table supports using standard SELECT statement to retrieve the list.

NOTE: The system.functions table is only available for some APIs.

Sample Code:

String sql = "SELECT * FROM system.functions";
try {
    ResultSet resultSet = statement.executeQuery(sql);
    LOGGER.info(resultSet.toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

Sample Output:

abandonmentUpdateActivitiesDeliveryStatuses
appPurchaseOneTimeCreate
appRevokeAccessScopes
appSubscriptionCancel
appSubscriptionCreate
......

The system.functions entity returns the following metadata columns:

system.functions.columns

The system.functions.columns table contains the list of columns used as input or output parameters for each function or mutation from the system.functions table.

NOTE: The system.functions.columns table is only available for some APIs.

Sample Code:

String sql = "SELECT * FROM system.functions.columns WHERE function_name = 'createCustomer'";
try {
    ResultSet resultSet = statement.executeQuery(sql);
    LOGGER.info(resultSet.toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

Sample Output:

function_name,name,type,format,is_input,is_output
createCustomer,customer-addresses-city,String,null,false,true
createCustomer,customer-addresses-company,String,null,false,true
createCustomer,input-dob,String,null,true,false
createCustomer,input-email,String,null,true,false
createCustomer,input-firstname,String,null,true,false
......

The system.functions entity returns the following metadata columns: