JDBC

HeavyDB supports JDBC connections.

Resources

Resource	Description
JAR file	$HEAVYAI_PATH/bin/heavyai-jdbc-<major_version>.<minor_version>.<revision>.jar
Code samples	$HEAVYAI_PATH/samples
JDBC driver	ai.heavy.jdbc.HeavyAIDriver
URL	jdbc:heavyai:<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 HEAVY.AI 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 HEAVY.AI 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 HEAVY.AI server configuration.

• server_trust_store: trust_store_path - The path to a Java trust store holding the HEAVY.AI 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 HEAVY.AI database called test_DB using port 6274 with max_rows set to 10000.

jdbc:heavyai:localhost:6274:test_DB?max_rows=10000

jdbc:heavyai: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:heayai: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

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

   static final String JDBC_DRIVER = "ai.heavy.jdbc.HeavyAIDriver";
   static final String DB_URL = "jdbc::localhost:6274:heavyai";
   static final String USER = "myUserName";
   static final String PASS = "myPassWord";

2. Register the JDBC driver:

   Class.forName(JDBC_DRIVER);

3. Open a connection:

   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);
   }

Compile and Run

Dependency components are also required in the classpath.

javac Flights.java

java -cp /opt/heavyai/bin/heavyai-jdbc-6.4.1.jar;. Flights_Demo.java

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 = "ai.heavy.jdbc.HeavyAIDriver";

static final String USER = "admin";
static final String PASS = "HyperInteractive";

public static void main(String[] args) throws SQLException {
  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 if not exists flights_java(arr_timestamp timestamp, dep_timestamp timestamp, uniquecarrier varchar(50))";
    stmt.executeUpdate(sql);
    sql = "insert into flights_java values('2017-04-23 06:30:0', '2017-04-23 07:45:00', 'Southwest')";
    stmt.executeUpdate(sql);
    sql = "insert into flights_java values('2017-04-23 06:50:0', '2017-04-23 09:45:00', 'American')";
    stmt.executeUpdate(sql);
    sql = "insert into flights_java values('2017-04-23 09:30:0','2017-04-23 12:45:00', 'United')";
    stmt.executeUpdate(sql);
    sql = "SELECT uniquecarrier from flights_java";
    ResultSet rs = stmt.executeQuery(sql);

    sql ="delete from SMRY where etl_dttm < DATEADD('DAY', '-4', NOW()) and actv_ind = 'N'";
    stmt.executeUpdate(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();
   }
}