JDBC
OmniSciDB supports JDBC connections.
Resource | Description |
JAR file | $OMNISCI_PATH/bin/omniscijdbc-<major_version>.<minor_version>.<revision>.jar |
Code samples | $OMNISCI_PATH/samples |
JDBC driver | com.omnisci.jdbc.OmniSciDriver |
URL | jdbc:omnisci:<host>:[<keyvalue1>:<keyvalue2>:<keyvalue3>...][?<key1=value1>&<key2=value2>&<key3=value3>...] |
You can connect securely to a JDBC session by defining the database URL (a colon-separated connection string with optional query string) and the keys/values in
DriverManager.getConnection
properties.You can define key values separated by colons in the main part of the connection string , or as key/value pairs separated by an ampersand (&) in an optional query string following the ? character.
jdbc:omnisci:<host_name>
must be defined in the main part of the connection string.Valid keys in the connection URL are the same as those in the properties object. If defined in the main part of the connection string, they must appear in the following order:
host_name
- Name of the host. Must be defined in the main part of the connection string.port_num
- Port numberdb_name
- Name of the database.protocol:
<binary
|binary_tls
|http
|https
|https_insecure
>- To connect using a secure connection through binary mode, the OmniSci server must be running with an encrypted main port open.
- Use binary TLS mode to explicitly connect using SSL with the default Java trust stores.
- To connect using HTTPS, the OmniSci web server must be running with HTTPS enabled.
- To create an encrypted connection with no server validation, use
HTTPS_INSECURE
. Note: UsingHTTPS_INSECURE
is not recommended in a production environment.
server_trust_store:
trust_store_path
- The path to a Java trust store holding the OmniSci server public certificate.server_trust_store_pwd:
password
- The password to the trust store.pkiauth: true | false
- Enable PKI authentication. Iftrue
:- The client X509 public certificate is supplied to the server for verification. Use the
sslcert
parameter to supply a file containing the certificate. - The
user
parameter and thepassword
parameters are ignored and should be blank.
sslcert
certificate-path
- Path to the certificate and private key file, in PKCS12 format.sslkey
key-path
- Path to the SSL key.sslkey_password password
- Password for the SSL key defined insslkey
key-path
.max_rows
max_rows
- The maximum number of rows to return.
You can use the following connection URLs to connect to an OmniSci database called
test_DB
using port 6274
with max_rows
set to 10000.jdbc:omnisci:localhost:6274:test_DB?max_rows=10000
jdbc:omnisci:localhost:6274?db_name=test_DB&max_rows=10000
If duplicate information is supplied in different sources of connection information, sources have priority as follows:
- 1.The main part of the URL.
- 2.The query portion of the URL.
- 3.The Properties object.
In the following connection URL, the database
test_DB
would be used, and if logging is turned on, the driver would issue a warning:jdbc:omnisci:localhost:6274?dbname=test_DB&max_rows=10000&db_name=test_WRONGDB
The fields in the "main" colon-separated portion of the URL are interpreted strictly in the order defined in the list of keys:
host_name
followed by port_num
, followed by db_name
, and so on. All of these fields can be specified in this portion of the connection string; however to specify a max_rows
field this way, all the preceding values must also be specified. Empty fields are not allowed. In this case, consider using a Properties object or a the query string portion of the URL.You can also provide all options and parameters for a JDBC connection in a Java properties file; for example:
server_trust_store=/tls_certs/server/trust_store_server.jks
server_trust_store_password=jks_truststore_password
pkiauth=true
sslcert=/tls_certs/client1_ca_primary_signed/client1.p12
sslkey_password=ssl_certfile_password
protocol=https
Method | Description |
Connection class | |
clearWarnings | Clear all warnings reported for this connection object. After calling clearWarnings , calling getWarnings returns null until a new warning is reported for this connection object. |
close | Disconnect the JDBC client session and frees associated resources. |
createStatement | Get a new, empty Statement object. |
getCatalog | Get the Connection object current catalog name. |
getMetaData | Get a DatabaseMetaData object, which contains metadata about the database for which this Connection object represents a connection. The metadata includes information about the database tables, supported SQL grammar, stored procedures, and the capabilities of this connection. |
getWarnings | Get the first warning reported by calls on this Connection object. |
isClosed | Get the closed status of this Connection object:
|
isValid | Get the connection status:
|
prepareStatement | Create a PreparedStatement object. OmniSciDB makes no distinction between prepared and directly executed statements and queries. |
Driver | |
acceptsURL | Get the driver determination of whether or not it can open a connection to the URL. |
connect | Make a database connection to the specified URL. |
getMajorVersion | Get the driver major version number. |
getMinorVersion | Get the driver minor version number. |
getParentLogger | Get the parent Logger of all Loggers used by this driver. |
getPropertyInfo | Get driver property information. |
jdbcCompliant | Test if this driver is a JDBC-compliant driver. |
Statement | |
executeQuery | Execute a SELECT query. |
executeUpdate | Execute an INSERT or DROP statement. |
getMaxFieldSize | Get the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object. |
getMaxRows | Get the maximum number of rows that a ResultSet object produced by this Statement object can contain. |
getMetaData | Get a new DatabaseMetaData object. |
setMaxFieldSize | Set the limit for the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object. |
setMaxRows | Set the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the given number. Default is 100,000 rows.
If you do not specify a LIMIT clause in your query through this Statement object, this setting automatically appends LIMIT maxRows to the query. Setting maxRows to 0 allows an unlimited number of rows to be returned, and does not append a LIMIT clause to the query through this Statement object. |
PreparedStatement |