The KingswaySoft JDBC Driver Pack comes with a range of advanced features designed to address different needs. 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 using SSL/TLS when accessing with HTTP based APIs. When working with 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. If needed (and with caution), you can disable this check by setting the IgnoreCertificateErrors property to true. Keep in mind, though, that bypassing certificate verification can expose you to security risks.

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

Our HTTP-based API drivers let you configure a web proxy with the following properties: ProxyServer, ProxyServerPort, ProxyUserName, and ProxyPassword.

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

Cache

The drivers cache metadata to boost performance by reducing repetitive service calls. By default, this cache expires after 30 minutes. If you prefer not to cache metadata, set the CacheExpirationTime property to 0—but note that metadata requests can be resource intensive, so disabling caching is generally not recommended without 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

With the SaveResult property enabled, execution results are automatically saved to the same folder as the .jar library. This feature is handy for troubleshooting complex queries or reviewing results later. The ResultPath property can be used to change the directory execution results are saved to.

Properties connectionProps = new Properties();
connectionProps.put("SaveResult", "true");
connectionProps.put("ResultPath", "C:\\ExecutionResults");

Multithreaded Processing

The driver supports multithreading for improved performance during large-volume write operations. By setting the TotalThreads property, you can set the number of threads to run concurrently during large-volume data operations. By default, this property is set to 0, meaning multithreading is off until you enable it.

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

LogLevel Contains Description
SEVERE Use this level for critical failures, such as connection issues and fatal service errors.
WARNING SEVERE Use this level to monitor potential issues that don't immediately stop execution.
INFO WARNING, SEVERE The default level, useful for investigating general query performance and result details. Captures details for the executed query, the number of returned rows, execution time tracking and errors.
CONFIG INFO, WARNING, SEVERE Use this level to receive details of the request and response, as well as the service URL used in the request if it is an HTTP based API.
FINE/FINER/FINEST ALL Use these levels when detailed debugging or analysis is required, such as SSL handshake details and system information. Logs at these levels also include messages from all lower levels (e.g., FINER includes FINE, but not FINEST).
ALL ALL Use this level to output all possible logs.
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 KingswaySoft 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:

Column Name Description
table_name The name of the table, API object, or entity.
is_creatable If this table can be used in INSERT queries.
is_updatable If this table can be used in UPDATE queries.
is_deletable If this table can be used in DELETE queries.
is_readable If this table can be used in SELECT queries.
is_upsertable If this table can be used in UPSERT queries.

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 = 'email' ORDER BY name";
	try {
		ResultSet resultSet = statement.executeQuery(sql);
		LOGGER.info(resultSet.toString());
	} catch (SQLException e) {
		LOGGER.severe(e.toString());
	}

Sample Output:

table_name,name,data_type,format,app_field_type,string_max_length,numeric_precision,numeric_scale,is_searchable,is_creatable,is_updatable,is_deletable,is_readable,is_upsertable
email,acceptingentityid,VARCHAR,null,Lookup,null,0,0,false,true,false,false,true,false
email,acceptingentityidname,VARCHAR,null,String,200,0,0,false,true,false,false,true,false
email,acceptingentitytypecode,INTEGER,null,EntityName,null,0,0,false,true,false,false,true,false
email,activityadditionalparams,VARCHAR,null,Memo,null,0,0,false,true,true,false,true,true
email,activityid,VARCHAR,null,Uniqueidentifier,36,0,0,false,true,false,false,true,false
email,activitytypecode,INTEGER,null,EntityName,null,0,0,false,false,false,false,true,false
email,activitytypecodename,VARCHAR,null,Virtual,null,0,0,false,false,false,false,true,false
email,actualdurationminutes,INTEGER,null,Integer,null,0,0,false,true,true,false,true,true
email,actualend,TIMESTAMP,null,DateTime,null,0,0,false,true,true,false,true,true
email,actualstart,TIMESTAMP,null,DateTime,null,0,0,false,true,true,false,true,true
email,attachmentcount,INTEGER,null,Integer,null,0,0,false,false,false,false,true,false
...

The system.columns entity returns the following metadata columns:

Column Name Description
table_name The name of the table being queried
name The name of the column
data_type The data type of the column
format The format of the values contained in this column or the required format when used in a query
app_field_type The data type of the column on the API side
string_max_length The maximum length of a string that the column allows. If undefined by the API, it will display NULL
numeric_precision The number of digits in a number value
numeric_scale The number of digits allowed after the decimal point of a number value
is_searchable If this column can be used in a WHERE clause.
is_creatable If this column can be used in an INSERT query.
is_updatable If this column can be used in an UPDATE query.
is_deletable If this column can be used in a DELETE query.
is_readable If this column can be used as a SELECT column.
is_upsertable If this column can be used in a UPSERT query.

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:

Column Name Description
FUNCTION_NAME The description of the function or mutation.

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:

Column Name Description
function_name The name of the function or mutation.
name The name of the column.
type The data type of the column.
format The format of the values contained in this column or the required format when used in a query.
is_input If this column is used for input.
is_output If this column is used for output.