JDBC

OmniSci Core Database supports JDBC connections.

Resources

Resource Description
JAR file $MAPD_PATH/bin/mapdjdbc-1.0-SNAPSHOT-jar-with-dependencies.jar
Code samples $MAPD_PATH/samples
JDBC driver com.mapd.jdbc.MapDDriver
URL jdbc:mapd:<host>:<port>:<dbName>

You can connect to a JDBC session using the HTTP protocol by appending http to a URL. For example, jdbc:mapd:localhost:9092:mapd:http.

Supported JDBC Methods

Method Description
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.
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.
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
  • 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.mapd.jdbc.MapDDriver";
static final String DB_URL = "jdbc:mapd:localhost:9091: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 $MAPD_PATH/bin/mapdjdbc-1.0-SNAPSHOT-jar-with-dependencies.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.mapd.jdbc.MapDDriver";
    static final String DB_URL = "jdbc:mapd:localhost:9091: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();
       }
    }
}