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_INSECUREis 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
sslcertparameter to supply a file containing the certificate. - The
userparameter and thepasswordparameters are ignored and should be blank.
sslcertcertificate-path- Path to the certificate and private key file, in PKCS12 format.sslkeykey-path- Path to the SSL key.sslkey_password password- Password for the SSL key defined insslkeykey-path.max_rowsmax_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 | |
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. |
- Transaction statements
- Cursors
- Multiple result sets
- Domains
- Rules
- Database procedures
- Indexes
- Keys
- Constraints
This example uses the JDBC interface to create a flight information database, then reads carrier information from the database.
The key steps are:
- 1.Set connection parameters and credentials:static final String JDBC_DRIVER = "com.omnisci.jdbc.OmniSciDriver";static final String DB_URL = "jdbc:omnisci:localhost:6274:omnisci";static final String USER = "myUserName";static final String PASS = "myPassWord";
- 2.Register the JDBC driver:Class.forName(JDBC_DRIVER);
- 3.Open a OmniSci server connection:conn = DriverManager.getConnection(DB_URL, USER, PASS);
- 4.Create a table, add data, and query the database:stmt = conn.createStatement();String sql = "CREATE table flights(arr_timestamp timestamp, dep_timestamp timestamp, uniquecarrier varchar(50))";stmt.executeUpdate(sql);sql = "insert into flights values('2017-04-23 06:30:0’, '2017-04-23 07:45:00’, 'Southwest’)";stmt.executeUpdate(sql);sql = "insert into flights values('2017-04-23 06:50:0’, '2017-04-23 09:45:00’, 'American’)";stmt.executeUpdate(sql);sql = "insert into flights values('2017-04-23 09:30:0’,’ 2017-04-23 12:45:00’, 'United’)";stmt.executeUpdate(sql);sql = "SELECT uniquecarrier from flights";ResultSet rs = stmt.executeQuery(sql);
- 5.Extract data from the result set:while (rs.next()) {String uniquecarrier = rs.getString("uniquecarrier");System.out.println("uniquecarrier: " + uniquecarrier);}
javac Flights.java
java -cp /home/omnisci/omnisci/bin/omnisci-jdbc-4.6.0.jar:./ Flights
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Flights {
static final String JDBC_DRIVER = "com.omnisci.jdbc.OmniSciDriver";
static final String DB_URL = "jdbc:omnisci:localhost:6274:omnisci";
static final String USER = "myUserName";
static final String PASS = "myPassWord";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PASS);
stmt = conn.createStatement();
String sql = "CREATE table flights(arr_timestamp timestamp, dep_timestamp timestamp, uniquecarrier varchar(50))";
stmt.executeUpdate(sql);
sql = "insert into flights values('2017-04-23 06:30:0’, '2017-04-23 07:45:00’, 'Southwest’)";
stmt.executeUpdate(sql);
sql = "insert into flights values('2017-04-23 06:50:0’, '2017-04-23 09:45:00’, 'American’)";
stmt.executeUpdate(sql);
sql = "insert into flights values('2017-04-23 09:30:0’,’ 2017-04-23 12:45:00’, 'United’)";
stmt.executeUpdate(sql);
sql = "SELECT uniquecarrier from flights";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
String uniquecarrier = rs.getString("uniquecarrier");
System.out.println("uniquecarrier: " + uniquecarrier);
}
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
//Handle errors for JDBC
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
}
}
}
Last modified 1yr ago