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