JDBC Driver Pack

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:

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:

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:

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: