JDBC

OmniSci Core database 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>:<port>:<dbName>

You can connect securely to a JDBC session by defining the database URL and the following keys/values in DriverManager.getConnection properties:

  • protocol: <binary|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.
    • 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 HTTP_INSECURE is not recommended in a production environment.
    • For more information, see OmniSci server configuration.

  • key_store: key_store_path - The path to a Java key store holding the OmniSci server public certificate.
  • key_store_pwd: password - The password to the Java key store.

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. OmniSci Core 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. OmniSci Core 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
closeReleases this ResultSet's database and JDBC resources immediately.
getBigDecimalGet a BigDecimal object.
getBooleanGet a boolean object.
getDateGet a java.sql.Date object.
getDoubleGet a double object.
getFloatGet a float object.
getIntGet an integer object.
getLongGet a long integer object
getObjectGet a generic Object class representing the column value.
getShortGet a short integer.
getStringGet a String object.
getTimeGet a java.sql.Time object.
getTimestampGet a java.sql.Timestamp object.
ResultSetMetaData
getColumnCountGet the designated column's table's catalog name.
getColumnDisplaySizeGet the designated column's normal maximum width in characters.
getColumnLabelGet the designated column's suggested title for use in printouts and displays.
getColumnNameGet the designated column's name.
getColumnTypeGet the designated column's SQL type.
getColumnTypeNameGet the designated column's database-specific type name.
getPrecisionGet the designated column's specified column size.
getScaleGet the designated column's number of digits to right of the decimal point.
getSchemaNameGet the designated column's table's schema.
getTableNameGet the designated column's table name.
isAutoIncrementGet whether the designated column is automatically numbered.
isCaseSensitiveGet whether a column's case matters.
isCurrencyGet whether the designated column is a cash value.
isDefinitelyWritableGet whether a write on the designated column will definitely succeed.
isNullableGet the nullability of values in the designated column.
isReadOnlyGet whether the designated column is definitely not writable.
isSearchableGet whether the designated column can be used in a where clause.
isSignedGet whether values in the designated column are signed numbers.
isWritableGet whether it is possible to write to the designated column.
DatabaseMetaData
allTablesAreSelectableGet whether the current user can use all the tables returned by the method getTables in a SELECT statement.
autoCommitFailureClosesAllResultSetsGet whether a SQLException while autoCommit is true inidcates that all open ResultSets are closed, even ones that are holdable.
dataDefinitionCausesTransactionCommitGet whether a data definition statement within a transaction forces the transaction to commit.
dataDefinitionIgnoredInTransactionsGet whether this database ignores a data definition statement within a transaction.
doesMaxRowSizeIncludeBlobsGet whether the return value for the method getMaxRowSize includes the SQL data types LONGVARCHAR and LONGVARBINARY.
generatedKeyAlwaysReturnedGet 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.
getBestRowIdentifierGet a description of a table's optimal set of columns that uniquely identifies a row.
getCatalogsGet the catalog names available in this database.
getCatalogSeparatorGet the String that this database uses as the separator between a catalog and table name.
getCatalogTermGet the database vendor's preferred term for "catalog".
getColumnsGet a description of table columns available in the specified catalog.
getConnectionGet the connection that produced this metadata object.
getDatabaseMajorVersionGet the major version number of the underlying database.
getDatabaseMinorVersionGet the minor version number of the underlying database.
getDatabaseProductNameGet the name of this database product.
getDatabaseProductVersionGet the version number of this database product.
getDefaultTransactionIsolationGet this database's default transaction isolation level.
getDriverMajorVersionGet this JDBC driver's major version number.
getDriverMinorVersionGet this JDBC driver's minor version number.
getDriverNameGet the name of this JDBC driver.
getDriverVersionGet the version number of this JDBC driver as a String.
getExtraNameCharactersGet all the "extra" characters that can be used in unquoted identifier names (those beyond a-z, A-Z, 0-9 and _).
getIdentifierQuoteGet the string used to quote SQL identifiers.
getJDBCMajorVersionGet the major JDBC version number for this driver.
getJDBCMinorVersionGet the minor JDBC version number for this driver.
getMaxBinaryLiteralLengthGet the maximum number of hex characters this database allows in an inline binary literal.
getMaxCatalogNameLengthGet the maximum number of characters that this database allows in a catalog name.
getMaxCharLiteralLengthGet the maximum number of characters this database allows for a character literal.
getMaxColumnNameLengthGet the maximum number of characters this database allows for a column name.
getMaxColumnsInGroupByGet the maximum number of columns this database allows in a GROUP BY clause.
getMaxColumnsInIndexGet the maximum number of columns this database allows in an index.
getMaxColumnsInOrderByGet the maximum number of columns this database allows in an ORDER BY clause.
getMaxColumnsInSelectGet the maximum number of columns this database allows in a SELECT list.
getMaxColumnsInTableGet the maximum number of columns this database allows in a table.
getMaxConnectionsGet the maximum number of concurrent connections to this database that are possible.
getMaxCursorNameLengthGet the maximum number of characters that this database allows in a cursor name.
getMaxIndexLengthGet the maximum number of bytes this database allows for an index, including all of the parts of the index.
getMaxProcedureNameLengthGet the maximum number of characters that this database allows in a procedure name.
getMaxRowSizeGet the maximum number of bytes this database allows in a single row.
getMaxSchemaNameLengthGet the maximum number of characters that this database allows in a schema name.
getMaxStatementLengthGet the maximum number of characters this database allows in an SQL statement.
getMaxStatementsGet the maximum number of active statements to this database that can be open at the same time.
getMaxTableNameLengthGet the maximum number of characters this database allows in a table name.
getMaxTablesInSelectGet the maximum number of tables this database allows in a SELECT statement.
getMaxUserNameLengthGet the maximum number of characters this database allows in a user name.
getNumericFunctionsGet a comma-separated list of math functions available with this database.
getSchemasGet the schema names available in this database.
getSchemaTermGet the database vendor's preferred term for "schema".
getSearch EscapeGet the string that can be used to escape wildcard characters.
getSQLKeywordsGet a comma-separated list of all of this database's SQL keywords that are NOT also SQL:2003 keywords.
getSQLStateTypeIndicates whether the SQLSTATE returned by SQLException.getSQLState is X/Open (now known as Open Group) SQL CLI or SQL:2003.
getTablePrivilegesGet a description of the access rights for each table available in a catalog.
getTablesGet a description of the tables available in the given catalog.
getTableTypesGet the table types available in this database.
getTimeDateFunctionsGet a comma-separated list of the time and date functions available with this database.
getTypeInfoGet a description of all the data types supported by this database.
getURLGet the URL for this DBMS.
getUserNameGet the user name as known to this database.
isCatalogAtStartGet whether a catalog appears at the start of a fully qualified table name.
nullPlusNonNullIsNullGet whether this database supports concatenations between NULL and non-NULL values being NULL.
nullsAreSortedAtEndGet whether NULL values are sorted at the end regardless of sort order.
nullsAreSortedAtStartGet whether NULL values are sorted at the start regardless of sort order.
nullsAreSortedHighGet whether NULL values are sorted high.
nullsAreSortedLowGet whether NULL values are sorted low.
othersDeletesAreVisibleGet whether deletes made by others are visible.
othersInsertsAreVisibleGet whether inserts made by others are visible.
othersUpdatesAreVisibleGet whether updates made by others are visible.
ownDeletesAreVisibleGet whether a result set's own deletes are visible.
ownInsertsAreVisibleGet whether a result set's own inserts are visible.
ownUpdatesAreVisibleGet whether for the given type of ResultSet object, the result set's own updates are visible.
storesLowerCaseIdentifiersGet whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in lower case.
storesLowerCaseQuotedIdentifiersGet whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in lower case.
storesMixedCaseIdentifiersGet whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in mixed case.
storesMixedCaseQuotedIdentifiersGet whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in mixed case.
storesUpperCaseIdentifiersGet whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in upper case.
storesUpperCaseQuotedIdentifiersGet whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in upper case.
supportsAlterTableWithAddColumnGet whether this database supports ALTER TABLE with add column.
supportsAlterTableWithDropColumnGet whether this database supports ALTER TABLE with drop column.
supportsANSI92EntryLevelSQLGet whether this database supports the ANSI92 entry level SQL grammar.
supportsANSI92FullSQLGet whether this database supports the ANSI92 full SQL grammar supported.
supportsANSI92IntermediateSQLGet whether this database supports the ANSI92 intermediate SQL grammar supported.
supportsBatchUpdatesGet whether this database supports batch updates.
supportsCatalogsInDataManipulationGet whether a catalog name can be used in a data manipulation statement.
supportsCatalogsInIndexDefinitionsGet whether a catalog name can be used in an index definition statement.
supportsCatalogsInPrivilegeDefinitionsGet whether a catalog name can be used in a privilege definition statement.
supportsCatalogsInProcedureCallsGet whether a catalog name can be used in a procedure call statement.
supportsCatalogsInTableDefinitionsGet whether a catalog name can be used in a table definition statement.
supportsColumnAliasingGet whether this database supports column aliasing.
supportsConvertGet whether this database supports the JDBC scalar function CONVERT for the conversion of one JDBC type to another.
supportsConvertGet whether this database supports the JDBC scalar function CONVERT for conversions between the JDBC types fromType and toType.
supportsCoreSQLGrammarGet whether this database supports the ODBC Core SQL grammar.
supportsCorrelatedSubqueriesGet whether this database supports correlated subqueries.
supportsDataDefinitionAndDataManipulationTransactionsGet whether this database supports both data definition and data manipulation statements within a transaction.
supportsDataManipulationTransactionsOnlyGet whether this database supports only data manipulation statements within a transaction.
supportsDifferentTableCorrelationNamesGet whether, when table correlation names are supported, they are restricted to being different from the names of the tables.
supportsExpressionsInOrderByGet whether this database supports expressions in ORDER BY lists.
supportsExtendedSQLGrammarGet whether this database supports the ODBC Extended SQL grammar.
supportsFullOuterJoinsGet whether this database supports full nested outer joins.
supportsGroupByGet whether this database supports some form of GROUP BY clause.
supportsGroupByBeyondSelectGet 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
supportsGroupByUnrelatedGet whether this database supports using a column that is not in the SELECT statement in a GROUP BY clause.
supportsIntegrityEnhancementFacilityGet whether this database supports the SQL Integrity Enhancement Facility.
supportsLikeEscapeClauseGet whether this database supports specifying a LIKE escape clause.
supportsLimitedOuterJoinsGet whether this database provides limited support for outer joins.
supportsMinimumSQLGrammarGet whether this database supports the ODBC Minimum SQL grammar.
supportsMixedCaseIdentifiersGet whether this database treats mixed case unquoted SQL identifiers as case sensitive and as a result stores them in mixed case.
supportsMixedCaseQuotedIdentifiersGet whether this database treats mixed case quoted SQL identifiers as case sensitive and as a result stores them in mixed case.
supportsMultipleOpenResultsGet whether it is possible to have multiple ResultSet objects returned from a CallableStatement object simultaneously.
supportsMultipleResultSetsGet whether this database supports getting multiple ResultSet objects from a single call to the method execute.
supportsMultipleTransactionsGet whether this database allows having multiple transactions open at once (on different connections).
supportsNamedParametersGet whether this database supports named parameters to callable statements.
supportsNonNullableColumnsGet whether columns in this database may be defined as non-nullable.
supportsOpenCursorsAcrossCommitGet whether this database supports keeping cursors open across commits.
supportsOpenCursorsAcrossRollbackGet whether this database supports keeping cursors open across rollbacks.
supportsOpenStatementsAcrossCommitGet whether this database supports keeping statements open across commits.
supportsOpenStatementsAcrossRollbackGet whether this database supports keeping statements open across rollbacks.
supportsOrderByUnrelatedGet whether this database supports using a column that is not in the SELECT statement in an ORDER BY clause.
supportsOuterJoinsGet whether this database supports some form of outer join.
supportsPositionedDeleteGet whether this database supports positioned DELETE statements.
supportsPositionedUpdateGet whether this database supports positioned UPDATE statements.
supportsResultSetConcurrencyGet whether this database supports the given concurrency type in combination with the given result set type.
supportsResultSetHoldabilityGet whether this database supports the given result set holdability.
supportsResultSetTypeGet whether this database supports the given result set type.
supportsSavepointsGet whether this database supports savepoints.
supportsSchemasInDataManipulationGet whether a schema name can be used in a data manipulation statement.
supportsSchemasInIndexDefinitionsGet whether a schema name can be used in an index definition statement.
supportsSchemasInPrivilegeDefinitionsGet whether a schema name can be used in a privilege definition statement.
supportsSchemasInTableDefinitionsGet whether a schema name can be used in a table definition statement.
supportsSelectForUpdateGet whether this database supports SELECT FOR UPDATE statements.
supportsStatementPoolingGet whether this database supports statement pooling.
supportsStoredFunctionsUsingCallSyntaxGet whether this database supports invoking user-defined or vendor functions using the stored procedure escape syntax
supportsStoredProceduresGet whether this database supports stored procedure calls that use the stored procedure escape syntax.
supportsSubqueriesInComparisonsGet whether this database supports subqueries in comparison expressions.
supportsSubqueriesInExistsGet whether this database supports subqueries in EXISTS expressions.
supportsSubqueriesInInsGet whether this database supports subqueries in IN expressions.
supportsSubqueriesInQuantifiedsGet whether this database supports subqueries in quantified expressions.
supportsTableCorrelationNamesGet whether this database supports table correlation names.
supportsTransactionIsolationLevelGet whether this database supports the given transaction isolation level.
supportsTransactionsGet whether this database supports transactions.
supportsUnionGet whether this database supports SQL UNION.
supportsUnionAllGet whether this database supports SQL UNION ALL.
usesLocalFilePerTableGet whether this database uses a file for each table.
usesLocalFilesGet whether this database stores tables in a local file.

Unsupported Features

  • Transaction statements
  • Cursors
  • Table updates, alterations or deletions
  • Multiple result sets
  • Domains
  • Rules
  • Database procedures
  • Indexes
  • Query cancellation
  • Keys
  • Constraints
  • Schemas (table ownership) - Any client can see tables and views created by any user without restriction.

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:
static final String JDBC_DRIVER = "com.omnisci.jdbc.OmniSciDriver";
static final String DB_URL = "jdbc:omnisci:localhost:6274:mapd";
static final String USER = "myUserName";
static final String PASS = "myPassWord";
  1. Register the JDBC driver:
Class.forName(JDBC_DRIVER);
  1. Open a OmniSci server connection:
conn = DriverManager.getConnection(DB_URL, USER, PASS);
  1. 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);
  1. Extract data from the result set:
while (rs.next()) {
    String uniquecarrier = rs.getString("uniquecarrier");
    System.out.println("uniquecarrier: " + uniquecarrier);
}

Compile and Run

javac Flights.java
java -cp /home/mapd/mapd/bin/omnisci-jdbc-4.6.0.jar:./  Flights

Source Code

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:mapd";
    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();
       }
    }
}