Search…
JDBC
OmniSciDB supports JDBC connections.

Resources

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 number
    db_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: Using HTTPS_INSECURE is not recommended in a production environment.
    For more information, see OmniSci server configuration.
    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. If true:
      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 the password 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 in sslkey key-path.
    max_rows max_rows - The maximum number of rows to return.

Examples

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.
1
jdbc:omnisci:localhost:6274:test_DB?max_rows=10000
Copied!
1
jdbc:omnisci:localhost:6274?db_name=test_DB&max_rows=10000
Copied!
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:
1
jdbc:omnisci:localhost:6274?dbname=test_DB&max_rows=10000&db_name=test_WRONGDB
Copied!
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:
1
server_trust_store=/tls_certs/server/trust_store_server.jks
2
server_trust_store_password=jks_truststore_password
3
pkiauth=true
4
sslcert=/tls_certs/client1_ca_primary_signed/client1.p12
5
sslkey_password=ssl_certfile_password
6
protocol=https
Copied!

Supported JDBC Methods

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:
    true = connection closed
    false = connection valid
isValid
Get the connection status:
    true = valid connection
    false = connection closed
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
addBatch
Add an INSERT statement to a batch.
execute
Execute a prepared query. OmniSciDB makes no distinction between prepared and direct query execution.
executeBatch
Execute a batch of queries.
executeUpdate
Create a prepared statement object for batch updates.
set[obj]
Set a dynamic parameter for batch statements. Dynamic parameters are supported with batch inserts only.
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.
ResultSet
close
Releases this ResultSet's database and JDBC resources immediately.
getBigDecimal
Get a BigDecimal object.
getBoolean
Get a boolean object.
getDate
Get a java.sql.Date object.
getDouble
Get a double object.
getFloat
Get a float object.
getInt
Get an integer object.
getLong
Get a long integer object
getObject
Get a generic Object class representing the column value.
getShort
Get a short integer.
getString
Get a String object.
getTime
Get a java.sql.Time object.
getTimestamp
Get a java.sql.Timestamp object.
ResultSetMetaData
getColumnCount
Get the designated column's table's catalog name.
getColumnDisplaySize
Get the designated column's normal maximum width in characters.
getColumnLabel
Get the designated column's suggested title for use in printouts and displays.
getColumnName
Get the designated column's name.
getColumnType
Get the designated column's SQL type.
getColumnTypeName
Get the designated column's database-specific type name.
getPrecision
Get the designated column's specified column size.
getScale
Get the designated column's number of digits to right of the decimal point.
getSchemaName
Get the designated column's table's schema.
getTableName
Get the designated column's table name.
isAutoIncrement
Get whether the designated column is automatically numbered.
isCaseSensitive
Get whether a column's case matters.
isCurrency
Get whether the designated column is a cash value.
isDefinitelyWritable
Get whether a write on the designated column will definitely succeed.
isNullable
Get the nullability of values in the designated column.
isReadOnly
Get whether the designated column is definitely not writable.
isSearchable
Get whether the designated column can be used in a where clause.
isSigned
Get whether values in the designated column are signed numbers.
isWritable
Get whether it is possible to write to the designated column.
DatabaseMetaData
allTablesAreSelectable
Get whether the current user can use all the tables returned by the method getTables in a SELECT statement.
autoCommitFailureClosesAllResultSets
Get whether a SQLException while autoCommit is true inidcates that all open ResultSets are closed, even ones that are holdable.
dataDefinitionCausesTransactionCommit
Get whether a data definition statement within a transaction forces the transaction to commit.
dataDefinitionIgnoredInTransactions
Get whether this database ignores a data definition statement within a transaction.
doesMaxRowSizeIncludeBlobs
Get whether the return value for the method getMaxRowSize includes the SQL data types LONGVARCHAR and LONGVARBINARY.
generatedKeyAlwaysReturned
Get whether a generated key will always be returned if the column name(s) or index(es) specified for the auto generated key column(s) are valid and the statement succeeds.
getBestRowIdentifier
Get a description of a table's optimal set of columns that uniquely identifies a row.
getCatalogs
Get the catalog names available in this database.
getCatalogSeparator
Get the String that this database uses as the separator between a catalog and table name.
getCatalogTerm
Get the database vendor's preferred term for "catalog".
getColumns
Get a description of table columns available in the specified catalog.
getConnection
Get the connection that produced this metadata object.
getDatabaseMajorVersion
Get the major version number of the underlying database.
getDatabaseMinorVersion
Get the minor version number of the underlying database.
getDatabaseProductName
Get the name of this database product.
getDatabaseProductVersion
Get the version number of this database product.
getDefaultTransactionIsolation
Get this database's default transaction isolation level.
getDriverMajorVersion
Get this JDBC driver's major version number.
getDriverMinorVersion
Get this JDBC driver's minor version number.
getDriverName
Get the name of this JDBC driver.
getDriverVersion
Get the version number of this JDBC driver as a String.
getExtraNameCharacters
Get all the "extra" characters that can be used in unquoted identifier names (those beyond a-z, A-Z, 0-9 and _).
getIdentifierQuote
Get the string used to quote SQL identifiers.
getJDBCMajorVersion
Get the major JDBC version number for this driver.
getJDBCMinorVersion
Get the minor JDBC version number for this driver.
getMaxBinaryLiteralLength
Get the maximum number of hex characters this database allows in an inline binary literal.
getMaxCatalogNameLength
Get the maximum number of characters that this database allows in a catalog name.
getMaxCharLiteralLength
Get the maximum number of characters this database allows for a character literal.
getMaxColumnNameLength
Get the maximum number of characters this database allows for a column name.
getMaxColumnsInGroupBy
Get the maximum number of columns this database allows in a GROUP BY clause.
getMaxColumnsInIndex
Get the maximum number of columns this database allows in an index.
getMaxColumnsInOrderBy
Get the maximum number of columns this database allows in an ORDER BY clause.
getMaxColumnsInSelect
Get the maximum number of columns this database allows in a SELECT list.
getMaxColumnsInTable
Get the maximum number of columns this database allows in a table.
getMaxConnections
Get the maximum number of concurrent connections to this database that are possible.
getMaxCursorNameLength
Get the maximum number of characters that this database allows in a cursor name.
getMaxIndexLength
Get the maximum number of bytes this database allows for an index, including all of the parts of the index.
getMaxProcedureNameLength
Get the maximum number of characters that this database allows in a procedure name.
getMaxRowSize
Get the maximum number of bytes this database allows in a single row.
getMaxSchemaNameLength
Get the maximum number of characters that this database allows in a schema name.
getMaxStatementLength
Get the maximum number of characters this database allows in an SQL statement.
getMaxStatements
Get the maximum number of active statements to this database that can be open at the same time.
getMaxTableNameLength
Get the maximum number of characters this database allows in a table name.
getMaxTablesInSelect
Get the maximum number of tables this database allows in a SELECT statement.
getMaxUserNameLength
Get the maximum number of characters this database allows in a user name.
getNumericFunctions
Get a comma-separated list of math functions available with this database.
getSchemas
Get the schema names available in this database.
getSchemaTerm
Get the database vendor's preferred term for "schema".
getSearch Escape
Get the string that can be used to escape wildcard characters.
getSQLKeywords
Get a comma-separated list of all of this database's SQL keywords that are NOT also SQL:2003 keywords.
getSQLStateType
Indicates whether the SQLSTATE returned by SQLException.getSQLState is X/Open (now known as Open Group) SQL CLI or SQL:2003.
getTablePrivileges
Get a description of the access rights for each table available in a catalog.
getTables
Get a description of the tables available in the given catalog.
getTableTypes
Get the table types available in this database.
getTimeDateFunctions
Get a comma-separated list of the time and date functions available with this database.
getTypeInfo
Get a description of all the data types supported by this database.
getURL
Get the URL for this DBMS.
getUserName
Get the user name as known to this database.
isCatalogAtStart
Get whether a catalog appears at the start of a fully qualified table name.
nullPlusNonNullIsNull
Get whether this database supports concatenations between NULL and non-NULL values being NULL.
nullsAreSortedAtEnd
Get whether NULL values are sorted at the end regardless of sort order.
nullsAreSortedAtStart
Get whether NULL values are sorted at the start regardless of sort order.
nullsAreSortedHigh
Get whether NULL values are sorted high.
nullsAreSortedLow
Get whether NULL values are sorted low.
othersDeletesAreVisible
Get whether deletes made by others are visible.
othersInsertsAreVisible
Get whether inserts made by others are visible.
othersUpdatesAreVisible
Get whether updates made by others are visible.
ownDeletesAreVisible
Get whether a result set's own deletes are visible.
ownInsertsAreVisible
Get whether a result set's own inserts are visible.
ownUpdatesAreVisible
Get whether for the given type of ResultSet object, the result set's own updates are visible.
storesLowerCaseIdentifiers
Get whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in lower case.
storesLowerCaseQuotedIdentifiers
Get whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in lower case.
storesMixedCaseIdentifiers
Get whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in mixed case.
storesMixedCaseQuotedIdentifiers
Get whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in mixed case.
storesUpperCaseIdentifiers
Get whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in upper case.
storesUpperCaseQuotedIdentifiers
Get whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in upper case.
supportsAlterTableWithAddColumn
Get whether this database supports ALTER TABLE with add column.
supportsAlterTableWithDropColumn
Get whether this database supports ALTER TABLE with drop column.
supportsANSI92EntryLevelSQL
Get whether this database supports the ANSI92 entry level SQL grammar.
supportsANSI92FullSQL
Get whether this database supports the ANSI92 full SQL grammar supported.
supportsANSI92IntermediateSQL
Get whether this database supports the ANSI92 intermediate SQL grammar supported.
supportsBatchUpdates
Get whether this database supports batch updates.
supportsCatalogsInDataManipulation
Get whether a catalog name can be used in a data manipulation statement.
supportsCatalogsInIndexDefinitions
Get whether a catalog name can be used in an index definition statement.
supportsCatalogsInPrivilegeDefinitions
Get whether a catalog name can be used in a privilege definition statement.
supportsCatalogsInProcedureCalls
Get whether a catalog name can be used in a procedure call statement.
supportsCatalogsInTableDefinitions
Get whether a catalog name can be used in a table definition statement.
supportsColumnAliasing
Get whether this database supports column aliasing.
supportsConvert
Get whether this database supports the JDBC scalar function CONVERT for the conversion of one JDBC type to another.
supportsConvert
Get whether this database supports the JDBC scalar function CONVERT for conversions between the JDBC types fromType and toType.
supportsCoreSQLGrammar
Get whether this database supports the ODBC Core SQL grammar.
supportsCorrelatedSubqueries
Get whether this database supports correlated subqueries.
supportsDataDefinitionAndDataManipulationTransactions
Get whether this database supports both data definition and data manipulation statements within a transaction.
supportsDataManipulationTransactionsOnly
Get whether this database supports only data manipulation statements within a transaction.
supportsDifferentTableCorrelationNames
Get whether, when table correlation names are supported, they are restricted to being different from the names of the tables.
supportsExpressionsInOrderBy
Get whether this database supports expressions in ORDER BY lists.
supportsExtendedSQLGrammar
Get whether this database supports the ODBC Extended SQL grammar.
supportsFullOuterJoins
Get whether this database supports full nested outer joins.
supportsGroupBy
Get whether this database supports some form of GROUP BY clause.
supportsGroupByBeyondSelect
Get whether this database supports using columns not included in the SELECT statement in a GROUP BY clause provided that all of the columns in the SELECT statement are included in the GROUP BY c
supportsGroupByUnrelated
Get whether this database supports using a column that is not in the SELECT statement in a GROUP BY clause.
supportsIntegrityEnhancementFacility
Get whether this database supports the SQL Integrity Enhancement Facility.
supportsLikeEscapeClause
Get whether this database supports specifying a LIKE escape clause.
supportsLimitedOuterJoins
Get whether this database provides limited support for outer joins.
supportsMinimumSQLGrammar
Get whether this database supports the ODBC Minimum SQL grammar.
supportsMixedCaseIdentifiers
Get whether this database treats mixed case unquoted SQL identifiers as case sensitive and as a result stores them in mixed case.
supportsMixedCaseQuotedIdentifiers
Get whether this database treats mixed case quoted SQL identifiers as case sensitive and as a result stores them in mixed case.
supportsMultipleOpenResults
Get whether it is possible to have multiple ResultSet objects returned from a CallableStatement object simultaneously.
supportsMultipleResultSets
Get whether this database supports getting multiple ResultSet objects from a single call to the method execute.
supportsMultipleTransactions
Get whether this database allows having multiple transactions open at once (on different connections).
supportsNamedParameters
Get whether this database supports named parameters to callable statements.
supportsNonNullableColumns
Get whether columns in this database may be defined as non-nullable.
supportsOpenCursorsAcrossCommit
Get whether this database supports keeping cursors open across commits.
supportsOpenCursorsAcrossRollback
Get whether this database supports keeping cursors open across rollbacks.
supportsOpenStatementsAcrossCommit
Get whether this database supports keeping statements open across commits.
supportsOpenStatementsAcrossRollback
Get whether this database supports keeping statements open across rollbacks.
supportsOrderByUnrelated
Get whether this database supports using a column that is not in the SELECT statement in an ORDER BY clause.
supportsOuterJoins
Get whether this database supports some form of outer join.
supportsPositionedDelete
Get whether this database supports positioned DELETE statements.
supportsPositionedUpdate
Get whether this database supports positioned UPDATE statements.
supportsResultSetConcurrency
Get whether this database supports the given concurrency type in combination with the given result set type.
supportsResultSetHoldability
Get whether this database supports the given result set holdability.
supportsResultSetType
Get whether this database supports the given result set type.
supportsSavepoints
Get whether this database supports savepoints.
supportsSchemasInDataManipulation
Get whether a schema name can be used in a data manipulation statement.
supportsSchemasInIndexDefinitions
Get whether a schema name can be used in an index definition statement.
supportsSchemasInPrivilegeDefinitions
Get whether a schema name can be used in a privilege definition statement.
supportsSchemasInTableDefinitions
Get whether a schema name can be used in a table definition statement.
supportsSelectForUpdate
Get whether this database supports SELECT FOR UPDATE statements.
supportsStatementPooling
Get whether this database supports statement pooling.
supportsStoredFunctionsUsingCallSyntax
Get whether this database supports invoking user-defined or vendor functions using the stored procedure escape syntax
supportsStoredProcedures
Get whether this database supports stored procedure calls that use the stored procedure escape syntax.
supportsSubqueriesInComparisons
Get whether this database supports subqueries in comparison expressions.
supportsSubqueriesInExists
Get whether this database supports subqueries in EXISTS expressions.
supportsSubqueriesInIns
Get whether this database supports subqueries in IN expressions.
supportsSubqueriesInQuantifieds
Get whether this database supports subqueries in quantified expressions.
supportsTableCorrelationNames
Get whether this database supports table correlation names.
supportsTransactionIsolationLevel
Get whether this database supports the given transaction isolation level.
supportsTransactions
Get whether this database supports transactions.
supportsUnion
Get whether this database supports SQL UNION.
supportsUnionAll
Get whether this database supports SQL UNION ALL.
usesLocalFilePerTable
Get whether this database uses a file for each table.
usesLocalFiles
Get whether this database stores tables in a local file.

Unsupported Features

    Transaction statements
    Cursors
    Multiple result sets
    Domains
    Rules
    Database procedures
    Indexes
    Keys
    Constraints

Example

This example uses the JDBC interface to create a flight information database, then reads carrier information from the database.

Sequence

The key steps are:
    1.
    Set connection parameters and credentials:
    1
    static final String JDBC_DRIVER = "com.omnisci.jdbc.OmniSciDriver";
    2
    static final String DB_URL = "jdbc:omnisci:localhost:6274:omnisci";
    3
    static final String USER = "myUserName";
    4
    static final String PASS = "myPassWord";
    Copied!
    2.
    Register the JDBC driver:
    1
    Class.forName(JDBC_DRIVER);
    Copied!
    3.
    Open a OmniSci server connection:
    1
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    Copied!
    4.
    Create a table, add data, and query the database:
    1
    stmt = conn.createStatement();
    2
    3
    String sql = "CREATE table flights(arr_timestamp timestamp, dep_timestamp timestamp, uniquecarrier varchar(50))";
    4
    stmt.executeUpdate(sql);
    5
    6
    sql = "insert into flights values('2017-04-23 06:30:0’, '2017-04-23 07:45:00’, 'Southwest’)";
    7
    stmt.executeUpdate(sql);
    8
    sql = "insert into flights values('2017-04-23 06:50:0’, '2017-04-23 09:45:00’, 'American’)";
    9
    stmt.executeUpdate(sql);
    10
    sql = "insert into flights values('2017-04-23 09:30:0’,’ 2017-04-23 12:45:00’, 'United’)";
    11
    stmt.executeUpdate(sql);
    12
    13
    sql = "SELECT uniquecarrier from flights";
    14
    ResultSet rs = stmt.executeQuery(sql);
    Copied!
    5.
    Extract data from the result set:
    1
    while (rs.next()) {
    2
    String uniquecarrier = rs.getString("uniquecarrier");
    3
    System.out.println("uniquecarrier: " + uniquecarrier);
    4
    }
    Copied!

Compile and Run

1
javac Flights.java
2
java -cp /home/omnisci/omnisci/bin/omnisci-jdbc-4.6.0.jar:./ Flights
Copied!

Source Code

1
import java.sql.Connection;
2
import java.sql.DriverManager;
3
import java.sql.ResultSet;
4
import java.sql.SQLException;
5
import java.sql.Statement;
6
7
public class Flights {
8
9
static final String JDBC_DRIVER = "com.omnisci.jdbc.OmniSciDriver";
10
static final String DB_URL = "jdbc:omnisci:localhost:6274:omnisci";
11
static final String USER = "myUserName";
12
static final String PASS = "myPassWord";
13
14
public static void main(String[] args) {
15
Connection conn = null;
16
Statement stmt = null;
17
try {
18
Class.forName(JDBC_DRIVER);
19
20
conn = DriverManager.getConnection(DB_URL, USER, PASS);
21
22
stmt = conn.createStatement();
23
String sql = "CREATE table flights(arr_timestamp timestamp, dep_timestamp timestamp, uniquecarrier varchar(50))";
24
stmt.executeUpdate(sql);
25
sql = "insert into flights values('2017-04-23 06:30:0’, '2017-04-23 07:45:00’, 'Southwest’)";
26
stmt.executeUpdate(sql);
27
sql = "insert into flights values('2017-04-23 06:50:0’, '2017-04-23 09:45:00’, 'American’)";
28
stmt.executeUpdate(sql);
29
sql = "insert into flights values('2017-04-23 09:30:0’,’ 2017-04-23 12:45:00’, 'United’)";
30
stmt.executeUpdate(sql);
31
32
sql = "SELECT uniquecarrier from flights";
33
ResultSet rs = stmt.executeQuery(sql);
34
35
while (rs.next()) {
36
String uniquecarrier = rs.getString("uniquecarrier");
37
System.out.println("uniquecarrier: " + uniquecarrier);
38
}
39
40
rs.close();
41
stmt.close();
42
conn.close();
43
} catch (SQLException se) {
44
//Handle errors for JDBC
45
se.printStackTrace();
46
} catch (Exception e) {
47
e.printStackTrace();
48
if (stmt != null)
49
stmt.close();
50
if (conn != null)
51
conn.close();
52
}
53
}
54
}
Copied!
Last modified 5mo ago