JDBC Driver API Support

The Snowflake JDBC driver is a JDBC type 4 driver that supports the core JDBC functionality in version 1.0 of the JDBC API. For the complete API reference, see the Java SE Technologies documentation. You are welcome to try methods from later versions of the API, but Snowflake does not guarantee that these methods are supported.

The Snowflake JDBC driver requires Java 1.8 (or higher). The driver requires the java.sql package, which is included in the Standard Edition (SE) and the Enterprise Edition (EE) of Java. As of August, 2019, the java.sql package documentation is available at https://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html

The driver can be used with most client tools/applications that support JDBC for connecting to a database server.

This topic does not document the entire JDBC API. Instead, the topic:

  • Lists the supported interfaces from the JDBC API and the supported methods within each interface.

  • Documents areas where Snowflake extends the JDBC API standard.

  • Documents areas where the JDBC API standard is ambiguous and the Snowflake implementation might behave differently from other systems.

In general, if a method is called and fails, the method will raise an exception (e.g. SQLException).

The supported JDBC interfaces are listed alphabetically and paired with their corresponding Snowflake extension classes (where applicable).

In this Topic:

Object: CallableStatement

A CallableStatement is used to execute a stored procedure.

Methods

Method Name

Notes

Supported Methods

getBigDecimal(int, int)

getBoolean(int)

getByte(int)

getBytes(int)

getDate(int)

getDouble(int)

getFloat(int)

getInt(int)

getLong(int)

getObject(int)

getShort(int)

getString(int)

getTime(int)

getTimestamp(int)

registerOutParameter(int, int, int)

registerOutParameter(int, int)

wasNull()

Unsupported Methods

None.

Snowflake-specific Behavior

None.

Interface: SnowflakeCallableStatement

The SnowflakeCallableStatement interface contains Snowflake-specific methods. When you use the Snowflake JDBC driver to create an object of type CallableStatement, for example by calling the Connection.prepareCall() method, you actually get an object of a different (hidden) Snowflake-specific type, which implements both the JDBC CallableStatement interface and the SnowflakeCallableStatement interface. To access the SnowflakeCallableStatement methods in that object, you unwrap the object.

Additional Methods

Method Name

Description

getQueryID()

Returns the Snowflake query ID of the most recently executed query of this CallableStatement

getQueryID()
Purpose

This method returns the Snowflake query ID of the most recently executed query of this CallableStatement. If no query has been executed yet with the callable statement, the method returns null.

Arguments

None.

Returns

This method returns the ID as a String that contains a UUID. Information about UUIDs is included in the description of the SQL function UUID_STRING.

Throws

The method can throw SQLException.

Object: Connection

A Connection object represents a connection to a database server. The connection object allows users not only to connect to a particular database server, but also create Statement objects, which can be used to execute SQL statements.

Methods

Method Name

Notes

Supported Methods

abort()

clearWarnings()

close()

Snowflake-specific behavior (see below).

commit()

createStatement()

createStatement(int, int)

createStatement(int, int, int)

getAutoCommit()

getCatalog()

getClientInfo()

getHoldability()

getMetaData()

Snowflake-specific behavior (see below).

getSchema()

getTransactionIsolation()

getTypeMap()

getWarnings()

isClosed()

isReadOnly()

nativeSQL(String)

prepareCall(String)

prepareCall(String, boolean)

prepareCall(String, int, int)

prepareCall(String, int, int, int)

prepareStatement(String)

prepareStatement(String, int)

prepareStatement(String, int[])

prepareStatement(String, String[])

prepareStatement(String, int, int)

prepareStatement(String, int, int, int)

Snowflake-specific behavior (see below)

prepareStatement(String, boolean)

setAutoCommit(boolean)

setCatalog(String)

setClientInfo(String, String)

Calling this method causes a SQLClientInfoException.

setClientInfo(Properties)

Calling this method causes a SQLClientInfoException.

setSchema(String)

Unsupported Methods

rollback()

setReadOnly(boolean)

setTransactionIsolation(int)

Snowflake-specific Behavior

  • close()

    Closes the object. After an object has been closed, calling almost any method of the closed object will raise a SQLException. Calling close on an already closed object is harmless and will not raise an exception.

  • getMetaData()

    Lets you get metadata about the JDBC driver and Snowflake. For example, you can find out whether transactions are supported.

    For more information about the methods that you can call on the returned value, see Object: DatabaseMetaData.

  • prepareStatement(String sql)

    This method returns a preparedStatement object that can be used to execute the SQL statement. The preparedStatement object’s execute() method can be called to execute the statement. The statement can be executed as-is, or after binding values to the statement.

    Note

    In some systems, after a statement has been prepared, that statement can be executed repeatedly without re-compiling the statement. Preparing once and executing repeatedly can save a small amount of time and resources.

    In Snowflake, prepareStatement() does not actually compile the code. Instead, PreparedStatement.execute(), PreparedStatement.executeQuery(), and PreparedStatement.executeUpdate() compile and execute the statement. Therefore preparing the statement before execution does not save resources compared to simply using Statement.execute().

  • prepareCall(String sql)

  • prepareCall(String sql, boolean)

  • prepareCall(String sql, int, int)

  • prepareCall(String sql, int, int, int)

    As in most JDBC implementations, the prepareCall methods can be used to bind parameters to a stored procedure. For example, the following is supported:

    CallableStatement stmt = testConnection.prepareCall("call read_result_set(?,?) ");
    

    However, in the Snowflake JDBC Driver, the prepareCall methods do not support the ? = syntax to support binding the return value of a stored procedure. For example, the following is not supported:

    CallableStatement stmt = testConnection.prepareCall("? = call read_result_set() ");  -- INVALID
    

Interface: SnowflakeConnection

The SnowflakeConnection interface contains Snowflake-specific methods. When you use the Snowflake JDBC driver to create an object of type Connection, for example by calling the DriverManager.getConnection() method, you actually get an object of a different (hidden) Snowflake-specific type, which implements both the JDBC Connection interface and the SnowflakeConnection interface. To access the SnowflakeConnection methods in that object, you unwrap the object.

Additional Methods

These methods are in addition to the methods supported by the JDBC Connection interface.

Method Name

Description

createResultSet(String)

Given the query ID of an asynchronously-launched SQL statement, retrieves the query results and returns them in a ResultSet object.

downloadStream(String, String, boolean)

Downloads a file from the given stage and returns an InputStream.

getSessionID()

Gets the session ID of the current session.

prepareStatement(String, Boolean)

Overloaded prepareStatement() method (see below for details).

uploadStream(String, String, InputStream, String, boolean)

Compresses data from a stream and uploads it at a stage location.

public ResultSet createResultSet(String queryID)
Purpose

Given the queryID of an asynchronously-launched SQL statement, retrieve the query results and return them in a ResultSet object.

This method can typically be called up to 24 hours after the SQL statement finished.

Arguments

queryID: The query ID of the query for which you want the results.

Returns

The ResultSet. If the query has not yet finished running, the server returns an “empty” ResultSet. The user can call resultSet.unwrap(SnowflakeResultSet.class).getStatus() to find out when the data is available.

Throws

This method can throw SQLException.

Notes

This method is a Snowflake extension to the JDBC standard. To use this method, you must unwrap the Connection object.

Examples
ResultSet resultSet;
resultSet = connection.unwrap(SnowflakeConnection.class).createResultSet(queryID);

See Examples of Asynchronous Queries for a more extensive example that includes a call to this method.

public InputStream downloadStream(String stageName, String sourceFileName, boolean decompress)
Purpose

This method downloads a file from the given stage and returns an input stream.

Arguments

stageName: Stage name.

sourceFileName: File path in stage.

decompress: True if file compressed.

Returns

This method returns an InputStream.

Throws

This method throws SQLException if a SQL error occurs.

Examples

For a partial example, see Download Data Files Directly from an Internal Stage to a Stream.

public String getSessionID()
Purpose

This method returns the session ID of the current session.

Arguments

None

Returns

Returns the session ID as a String.

Throws

This method throws SQLException if any SQL error occurs, for example if the connection is closed.

Usage Notes

Since the session ID does not change while the connection is open, the session ID is cached locally (rather than retrieved from the server each time) to improve performance.

public prepareStatement(String sql, Boolean skipParsing)

This method is deprecated. The skipParsing parameter no longer affects the behavior of the method; this method behaves the same as the prepareStatement(String sql) method, regardless of the setting of the skipParsing parameter.

New code should use the method prepareStatement(String sql).

When convenient, existing code that uses the two-argument version of this method should be updated to use the one-argument method prepareStatement(String sql).

public void uploadStream(String stageName, String destPrefix, InputStream inputStream, String destFileName, boolean compressData)
Purpose

This method compresses data from a stream and uploads it at a stage location. The data will be uploaded as one file. No splitting is done in this method.

Arguments

stageName: Stage name (e.g. ~ or table name or stage name).

destPrefix: Path / prefix under which the data should be uploaded on the stage.

inputStream: Input stream from which the data will be uploaded.

destFileName: Destination file name to use.

compressData: Compress data or not before uploading stream.

Returns

Nothing.

Throws

This method throws a java.sql.SQLException if it failed to compress and put data from a stream at the stage.

Notes

The caller is responsible for releasing the inputStream after the method is called.

Examples

For a partial example, see Upload Data Files Directly from a Stream to an Internal Stage.

Object: DatabaseMetaData

The DatabaseMetaData class provides information about the features that the database server (in this case, Snowflake) supports.

Methods

Method Name

Notes

Supported Methods

allProceduresAreCallable()

allTablesAreSelectable()

dataDefinitionCausesTransactionCommit()

dataDefinitionIgnoredInTransactions()

doesMaxRowSizeIncludeBlobs()

getCatalogs()

getCatalogSeparator()

getCatalogTerm()

getColumnPrivileges(String, String, String, String)

Supports wildcards for the columnNamePattern argument. Supports null for the catalog, schemaPattern, tableNamePattern, and columnNamePattern arguments. See Snowflake-specific Behavior for additional information about this method.

getColumns(String, String, String, String)

Supports wildcards for the schemaPattern, tableNamePattern, and columnNamePattern arguments. Supports null for the catalog, schemaPattern, tableNamePattern, and columnNamePattern arguments.

getCrossReference(String, String, String, String, String, String)

Supports null for the parentCatalog, parentSchema, parentTable, foreignCatalog, foreignSchema, and foreignTable arguments.

getDatabaseProductName()

getDatabaseProductVersion()

getDefaultTransactionIsolation()

getDriverMajorVersion()

getDriverMinorVersion()

getDriverName()

getDriverVersion()

getExportedKeys(String, String, String)

Supports null for the catalog, schema, and table arguments.

getExtraNameCharacters()

getFunctionColumns()

Supports wildcards for the schemaPattern, functionNamePattern, and columnNamePattern arguments. Supports null for the columnNamePattern argument.

getIdentifierQuoteString()

getImportedKeys(String, String, String)

Supports null for the catalog, schema, and table arguments.

getIndexInfo(String, String, String, boolean, boolean)

getMaxBinaryLiteralLength()

getMaxCatalogNameLength()

getMaxCharLiteralLength()

getMaxColumnNameLength()

getMaxColumnsInGroupBy()

getMaxColumnsInIndex()

getMaxColumnsInOrderBy()

getMaxColumnsInSelect()

getMaxColumnsInTable()

getMaxConnections()

getMaxCursorNameLength()

getMaxIndexLength()

getMaxProcedureNameLength()

getMaxRowSize()

getMaxSchemaNameLength()

getMaxStatementLength()

getMaxStatements()

getMaxTableNameLength()

getMaxTablesInSelect()

getMaxUserNameLength()

getNumericFunctions()

getPrimaryKeys(String, String, String)

Supports null for the catalog, schema, and table arguments.

getProcedureColumns(String, String, String, String)

Supports wildcards for the schemaPattern, procedureNamePattern, and columnNamePattern arguments. Supports null for the columnNamePattern argument.

getProcedures(String, String, String)

Supports wildcards for the schemaPattern and procedureNamePattern arguments. Supports null for the columnNamePattern argument.

getProcedureTerm()

getSchemas()

getSchemas(String, String)

Supports wildcards for the schemaPattern argument. Supports null for the catalogName and schemaPattern arguments.

getSchemaTerm()

getSearchStringEscape()

getSQLKeywords()

getStringFunctions()

getSystemFunctions()

getTablePrivileges(String, String, String)

Supports wildcards for the schemaPattern and tableNamePattern arguments. Supports null for the catalog and schemaPattern arguments.

getTables(String, String, String, String[])

Supports wildcards for the schemaPattern and tableNamePattern arguments. Supports null for the catalog, schemaPattern, tableNamePattern, and types arguments.

getTableTypes()

getTimeDateFunctions()

getTypeInfo()

getURL()

getUserName()

isCatalogAtStart()

isReadOnly()

nullPlusNonNullIsNull()

nullsAreSortedAtEnd()

nullsAreSortedAtStart()

nullsAreSortedHigh()

nullsAreSortedLow()

storesLowerCaseIdentifiers()

storesLowerCaseQuotedIdentifiers()

storesMixedCaseIdentifiers()

storesMixedCaseQuotedIdentifiers()

storesUpperCaseIdentifiers()

storesUpperCaseQuotedIdentifiers()

supportsAlterTableWithAddColumn()

supportsAlterTableWithDropColumn()

supportsANSI92EntryLevelSQL()

supportsANSI92FullSQL()

supportsANSI92IntermediateSQL()

supportsCatalogsInDataManipulation()

supportsCatalogsInIndexDefinitions()

supportsCatalogsInPrivilegeDefinitions()

supportsCatalogsInProcedureCalls()

supportsCatalogsInTableDefinitions()

supportsColumnAliasing()

supportsConvert()

supportsConvert(int, int)

supportsCoreSQLGrammar()

supportsCorrelatedSubqueries()

supportsDataDefinitionAndDataManipulationTransactions()

supportsDataManipulationTransactionsOnly()

supportsDifferentTableCorrelationNames()

supportsExpressionsInOrderBy()

supportsExtendedSQLGrammar()

supportsFullOuterJoins()

supportsGroupBy()

supportsGroupByBeyondSelect()

supportsGroupByUnrelated()

supportsIntegrityEnhancementFacility()

supportsLikeEscapeClause()

supportsLimitedOuterJoins()

supportsMinimumSQLGrammar()

supportsMixedCaseIdentifiers()

supportsMixedCaseQuotedIdentifiers()

supportsMultipleResultSets()

supportsMultipleTransactions()

supportsNonNullableColumns()

supportsOpenCursorsAcrossCommit()

supportsOpenCursorsAcrossRollback()

supportsOpenStatementsAcrossCommit()

supportsOpenStatementsAcrossRollback()

supportsOrderByUnrelated()

supportsOuterJoins()

supportsPositionedDelete()

supportsPositionedUpdate()

supportsSchemasInDataManipulation()

supportsSchemasInIndexDefinitions()

supportsSchemasInPrivilegeDefinitions()

supportsSchemasInProcedureCalls()

supportsSchemasInTableDefinitions()

supportsSelectForUpdate()

supportsStoredProcedures()

supportsSubqueriesInComparisons()

supportsSubqueriesInExists()

supportsSubqueriesInIns()

supportsSubqueriesInQuantifieds()

supportsTableCorrelationNames()

supportsTransactionIsolationLevel(int)

supportsTransactions()

supportsUnion()

supportsUnionAll()

usesLocalFilePerTable()

usesLocalFiles()

Unsupported Methods

getBestRowIdentifier(String, String, String, int, boolean)

getVersionColumns(String, String, String)

Snowflake-specific Behavior

getColumnPrivileges(String, String, String, String)

This method always returns an empty set because Snowflake does not support column-level privileges.

Support for null Parameters

Some DatabaseMetaData methods accept null values for database object names (e.g. table/catalog names). By default, a null value means that the method does not filter on that argument. For example, if you pass getColumns() a null value for the schemaPattern argument, then getColumns() returns values for all schemas.

For some of those methods, the default behavior for null arguments can be overridden with the following parameters:

Support for Wildcards in Database Object Names

Some DatabaseMetaData methods support pattern-matching wildcards in database object names, such as table/catalog names. The supported wildcard characters are:

  • %: Matches any string of zero or more characters.

  • _: Matches any one character.

The following example shows what to pass to the getColumns() method to get the names of all tables and all columns in the specified database (TEMPORARYDB1) and schema (TEMPORARYSCHEMA1):

getColumns( connection,
    "TEMPORARYDB1",      // Database name.
    "TEMPORARYSCHEMA1",  // Schema name.
    "%",                 // All table names.
    "%"                  // All column names.
    );

It is common for database object names, such as table names, to contain underscores, for example SHIPPING_ADDRESSES. Searching for SHIPPING_ADDRESSES without escaping the underscore will of course find not only the table named SHIPPING_ADDRESSES, but also tables such as SHIPPING2ADDRESSES. If you want to search for SHIPPING_ADDRESSES, but not SHIPPING2ADDRESSES, then you need to escape the wildcard character to indicate that you want it treated as a literal. To escape the character, precede it with a backslash.

The backslash character itself must also be escaped if you want to use it as a literal character. For example, to search for a table named T_&\ , in which the underscore, the ampersand, and the backslash are literal parts of the name, not wildcard characters or escape characters, the method call should look similar to the following:

getColumns(
    connection, "TEMPORARYDB1", "TEMPORARYSCHEMA1", "T\\_\\\\", "%" // All column names.
    );

Each backslash above must be escaped an extra time because the Java compiler expects backslashes to be escaped:

Java sees...............: T\\_\\%\\\\
SQL sees................: T\_\%\\
The actual table name is: T_%\

Object: Driver

The Driver provides methods that allow you to get a connection to the database, as well as get information about the driver itself.

Methods

Method Name

Notes

Supported Methods

acceptsURL(String)

connect(String, Properties)

getMajorVersion()

getMinorVersion()

getPropertyInfo(String, Properties)

isDisableIncidents()

jdbcCompliant()

setDisableIncidents()

Snowflake-specific Behavior

None.

Examples

The following code snippet shows part of a program to get property information:

  // Demonstrate the Driver.getPropertyInfo() method.
  public static void do_the_real_work(String connectionString) throws Exception {
    Properties properties = new Properties();
    Driver driver = DriverManager.getDriver(connectionString);
    DriverPropertyInfo[] dpinfo = driver.getPropertyInfo("", properties);
    System.out.println(dpinfo[0].description);
  }

Note that in the general case, the call to this method should be inside a loop. If you retrieve information about a property and then set that property, the new setting might make additional properties relevant, so you might need to retrieve those and set them.

Object: ParameterMetaData

This provides information about parameters in a PreparedStatement.

Methods

Method Name

Notes

Supported Methods

getParameterCount()

getParameterType(int)

getParameterTypeName(int)

getPrecision(int)

getScale(int)

isNullable

Unsupported Methods

getParameterClassName(int)

getParameterMode()

isSigned

Snowflake-specific Behavior

None.

Object: PreparedStatement

The PreparedStatement interface describes methods that, for example, allow you to execute queries.

Methods

Method Name

Notes

Supported Methods

addBatch()

Snowflake-specific behavior (see below for details).

clearParameters()

getParameterMetaData()

execute()

Snowflake-specific behavior (see below for details).

executeQuery()

Snowflake-specific behavior (see below for details).

executeUpdate()

Snowflake-specific behavior (see below for details).

setBigDecimal(int, BigDecimal)

setBoolean(int, boolean)

setByte(int, byte)

setBytes(int, byte[])

setDate(int, Date)

setDouble(int, double)

setFloat(int, float)

setInt(int, int)

setLong(int, long)

setNull(int, int)

setObject(int, Object, int, int)

setObject(int, Object, int)

setObject(int, Object)

setShort(int, short)

setString(int, String)

setTime(int, Time)

setTimestamp(int, Timestamp)

Unsupported Methods

setAsciiStream(int, InputStream, int)

setBinaryStream(int, InputStream, int)

setUnicodeStream(int, InputStream, int)

Snowflake-specific Behavior

  • addBatch()

    Supported for INSERT statements only.

    The addBatch method (combined with executeBatch) allows multiple rows of data to be inserted as part of a single INSERT statement.

    The difference between using a batch and not using a batch is similar to the difference between using a multi-row insert and a single-row insert:

    INSERT INTO t1 (c1, c2) VALUES (1, 'One');   -- single row inserted.
    
    INSERT INTO t1 (c1, c2) VALUES (1, 'One'),   -- multiple rows inserted.
                                   (2, 'Two'),
                                   (3, 'Three');
    

    Inserting batches of rows is usually more efficient than inserting the same number of rows in individual INSERT statements. The advantage is even greater when using AUTOCOMMIT (i.e. when each INSERT is an individual transaction).

    For an example of using addBatch, see Batch Inserts.

    Note

    There is an upper limit to the size of data that you can bind, or that you can combine in a batch. For details, see Limits on Query Text Size.

  • execute()

    This method compiles and executes the SQL statement that was provided when the PreparedStatement object was created. The statement can be any type of SQL statement. The execute() method does not return a ResultSet.

    This method does not return anything. If you are executing a query and need to get a ResultSet back when the statement executes, then use the executeQuery() method.

  • executeQuery()

    This method compiles and executes the SQL statement that was provided when the PreparedStatement object was created, and returns a ResultSet.

  • executeUpdate()

    This method compiles and executes the SQL statement that was provided when the PreparedStatement object was created. The statement must be a DML statement (INSERT, UPDATE, DELETE, etc.) or a SQL statement that does not return anything (e.g. a DDL statement).

    The executeUpdate() method returns an integer, which is the number of rows updated if the statement was a DML statement. If the statement did not update any rows, the function returns 0.

    If you need to execute a SQL statement that returns a ResultSet, then use a different method, such as executeQuery().

Interface: SnowflakePreparedStatement

The SnowflakePreparedStatement interface contains Snowflake-specific methods. When you use the Snowflake JDBC driver to create an object of type PreparedStatement, for example by calling the Connection.prepareStatement() method, you actually get an object of a different (hidden) Snowflake-specific type, which implements both the JDBC PreparedStatement interface and the SnowflakePreparedStatement interface. To access the SnowflakePreparedStatement methods in that object, you unwrap the object.

Additional Methods

The methods below are in addition to the methods supported by the PreparedStatement interface.

Method Name

Description

executeAsyncQuery()

Performs an asynchronous query.

getQueryID()

Returns the Snowflake query ID of the most recently executed query of this SnowflakePreparedStatement.

executeAsyncQuery()
Purpose

This method performs an asynchronous query, which involves submitting an SQL statement for execution, then returning control to the caller without waiting for the query to finish.

Any SQL statement that is valid for executeQuery() is also valid for executeAsyncQuery().

Note

File transfer statements, such as PUT and GET, are valid for executeAsyncQuery(), but behave synchronously.

Arguments

None.

Returns

An “empty” ResultSet. The user should poll the result set by calling resultSet.unwrap(SnowflakeResultSet.class).getStatus() until the query results become available.

Throws

This method can throw SQLException.

Notes

This method is a Snowflake extension to the JDBC standard. To use this method, you must unwrap the PreparedStatement object.

Examples
...
PreparedStatement prepStatement = connection.prepareStatement("insert into testTable values (?)");
prepStatement.setInt(1, 33);
ResultSet rs = prepStatement.executeAsyncQuery();
...

See Examples of Asynchronous Queries for a more extensive example using the very similar SnowflakeStatement.executeAsyncQuery() method.

getQueryID()
Purpose

This method returns the Snowflake query ID of the most recently executed query of this SnowflakePreparedStatement. If no query has been executed yet with this prepared statement, the method returns null.

Arguments

None.

Returns

The method returns the ID as a String that contains a UUID.

Throws

The method can throw SQLException.

Notes

This method is a Snowflake extension to the JDBC standard. To use this method, you must unwrap the SnowflakePreparedStatement.

For asynchronous queries, the query ID does not become available until the execution of the statement completes. If you call SnowflakePreparedStatement.getQueryID() after calling executeAsyncQuery() but before the statement finishes executing, the return value could be NULL. Instead, call resultSet.unwrap(SnowflakeResultSet.class).getQueryID() on the ResultSet object returned by executeAsyncQuery().

Examples

This partial example shows how to call the method:

    // Retrieve the query ID from the PreparedStatement.
    String queryID;
    queryID = preparedStatement.unwrap(SnowflakePreparedStatement.class).getQueryID();

Enum: QueryStatus

The enum type is a Snowflake-specific type that:

  • Defines the constants that represent the status of an asynchronous query.

  • Defines methods that return details about any errors that occurred when executing SQL statements.

This enum type is in the net.snowflake.client.core package.

Enum Constants

Each enum constant represents a different possible status for the asynchronous query.

Enum Constant

Description

RUNNING

The query is still running.

ABORTING

The query is in the process of being aborted on the server side.

SUCCESS

The query finished successfully.

FAILED_WITH_ERROR

The query finished unsuccessfully.

QUEUED

The query is queued for execution (i.e. has not yet started running), typically because it is waiting for resources.

DISCONNECTED

The session’s connection is broken. The query’s state will change to “FAILED_WITH_ERROR” soon.

RESUMING_WAREHOUSE

The warehouse is starting up and the query is not yet running.

BLOCKED

The statement is waiting on a lock held by another statement.

NO_DATA

Data about the statement is not yet available, typically because the statement has not yet started executing.

Methods

The enum type defines the following methods, which you can use to get details about an error when the query status is FAILED_WITH_ERROR.

Method Name

Description

getErrorCode()

Returns the error code from the server if an error occurred during query execution.

getErrorMessage()

Returns the error message from the server if an error occurred during query execution.

getErrorCode()
Purpose

If an error occurred during the execution of the query, this method returns the error code from the server.

Arguments

None.

Returns

The method returns the error code as an int. If no error occurred, the method returns the value 0.

Examples
QueryStatus queryStatus = resultSet.unwrap(SnowflakeResultSet.class).getStatus();
if (queryStatus == queryStatus.FAILED_WITH_ERROR) {
  // Print the error code to stdout
  System.out.format("Error code: %d%n", queryStatus.getErrorCode());
}

See Examples of Asynchronous Queries for a more extensive example that includes a call to this method.

getErrorMessage()
Purpose

If an error occurred during the execution of the query, this method returns the error message from the server.

Arguments

None.

Returns

The method returns the error message as a String. If no error occurred, the method returns the value No error reported.

Examples
QueryStatus queryStatus = resultSet.unwrap(SnowflakeResultSet.class).getStatus();
if (queryStatus == queryStatus.FAILED_WITH_ERROR) {
  // Print the error message to stdout
  System.out.format("Error message: %s%n", queryStatus.getErrorMessage());
}

See Examples of Asynchronous Queries for a more extensive example that includes a call to this method.

Object: ResultSet

The ResultSet interface documents methods that retrieve the results of queries, for example to read the rows and columns returned by a SELECT statement.

A Snowflake ResultSet is a read-only object; it is not updatable.

Methods

Method Name

Notes

Supported Methods

close()

Snowflake-specific behavior (see below for details).

findColumn(String)

getBigDecimal(int, int)

getBigDecimal(String, int)

getBoolean(int)

getBoolean(String)

getByte(int)

getByte(String)

getBytes(int)

getBytes(String)

getDate(int)

getDate(String)

getDouble(int)

getDouble(String)

getFloat(int)

getFloat(String)

getInt(int)

getInt(String)

getLong(int)

getLong(String)

getMetaData()

Snowflake-specific behavior (see below for details).

getObject(int)

getObject(String)

getShort(int)

getShort(String)

getString(int)

getString(String)

getTime(int)

getTime(String)

getTimestamp(int)

getTimestamp(String)

next()

Snowflake-specific behavior (see below for details).

wasNull()

Unsupported Methods

clearWarnings()

getAsciiStream(int)

getAsciiStream(String)

getBinaryStream(int)

getBinaryStream(String)

getCursorName()

getUnicodeStream(int)

getUnicodeStream(String)

getWarnings()

Snowflake-specific Behavior

  • close()

    Closes the object. After an object has been closed, calling almost any method of the closed object will raise a SQLException. Calling close on an already closed object is harmless and will not raise an exception.

  • getMetaData()

    If the ResultSet object is for an asynchronous query, then this method will block until the query has finished executing. You can use resultSet.unwrap(SnowflakeResultSet.class).getStatus() to get the query status before calling this method.

  • next()

    This makes the next row in the result set the “current” row. Calls to the get*() methods, such as getInt(), get values from the current row.

    If the ResultSet has been closed by a call to the close method, then subsequent calls to next return false, rather than raise an exception.

    If the ResultSet object is for an asynchronous query, then this method will block until the results are available. You can use resultSet.unwrap(SnowflakeResultSet.class).getStatus() to get the query status before calling this method.

Interface: SnowflakeResultSet

The SnowflakeResultSet interface contains Snowflake-specific methods. When you use the Snowflake JDBC driver to create an object of type ResultSet, for example by calling the Statement.getResultSet() method, you actually get an object of a different (hidden) Snowflake-specific type, which implements both the JDBC ResultSet interface and the SnowflakeResultSet interface. To access the SnowflakeResultSet methods in that object, you unwrap the object.

Additional Methods

Method Name

Description

getQueryID()

Returns the Snowflake query ID of the statement that generated this result set.

getStatus()

For a ResultSet returned by an asynchronous query, returns the status of the query.

getQueryID()
Purpose

This method returns the Snowflake query ID of the statement that generated this result set.

Arguments

None.

Returns

The method returns the ID as a String that contains a UUID.

Notes

This method is a Snowflake extension to the JDBC standard. To use this method, you must unwrap the ResultSet.

Examples
    String queryID2;
    queryID2 = resultSet.unwrap(SnowflakeResultSet.class).getQueryID();
getStatus()
Purpose

For a ResultSet returned by an asynchronous query, such as SnowflakeStatement.executeAsyncQuery(), this method returns the status of the query. The status indicates whether the query finished successfully, finished unsuccessfully, or has not yet finished.

Arguments

None.

Returns

A QueryStatus enum constant.

Throws

This method can throw SQLException.

Notes

This method is a Snowflake extension to the JDBC standard. To use this method, you must unwrap the ResultSet object.

Examples
QueryStatus queryStatus = resultSet.unwrap(SnowflakeResultSet.class).getStatus();

See Examples of Asynchronous Queries for a more extensive example that includes a call to this method.

Object: ResultSetMetaData

This provides information about a ResultSet, for example, the number of columns in the ResultSet.

Methods

Method Name

Notes

Supported Methods

getCatalogName(int)

Snowflake-specific behavior (see below for details).

getColumnCount()

getColumnDisplaySize(int)

getColumnLabel(int)

getColumnName(int)

getColumnType(int)

getColumnTypeName(int)

getPrecision(int)

getScale(int)

getSchemaName(int)

Snowflake-specific behavior (see below for details).

getTableName(int)

Snowflake-specific behavior (see below for details).

isAutoIncrement(int)

isCaseSensitive(int)

isCurrency(int)

isDefinitelyWritable(int)

isNullable(int)

isReadOnly(int)

isSearchable(int)

isSigned(int)

isWritable(int)

Unsupported Methods

None.

Snowflake-specific Behavior

  • The ResultSetMetaData class does not have a close() method. An open ResultSetMetaData object is implicitly closed when the user closes the ResultSet from which the ResultSetMetaData object was created.

  • getCatalogName(), getSchemaName(), getTableName()

    If the ResultSet object is for an asynchronous query, these methods return empty strings.

Interface: SnowflakeResultSetMetaData

The SnowflakeResultSetMetaData interface contains Snowflake-specific methods. When you use the Snowflake JDBC driver to create an object of type ResultSetMetaData, for example by calling the ResultSet.getMetaData() method, you actually get an object of a different (hidden) Snowflake-specific type, which implements both the JDBC ResultSetMetaData interface and the SnowflakeResultSetMetaData interface. To access the SnowflakeResultSetMetaData methods in that object, you unwrap the object.

Additional Methods

Method Name

Notes

Supported Methods

getColumnIndex(String columnName)

getColumnNames()

getInternalColumnType(int column)

getQueryID()

getColumnIndex(String columnName):
Purpose

Returns the index of the column that corresponds to the columnName. For example, if a column named “BirthDate” is the third column in the table, then getColumnIndex(“BirthDate”) returns 2. (Indexes are 0-based, not 1-based.)

Arguments

The name of the column for which you want to find the index.

Returns

Returns an integer that contains the index of the column that corresponds to the columnName. If the columnName does not match any column in the result set, this returns -1.

Throws

The method can throw SQLException.

getColumnNames():
Purpose

This function returns the list of all column names in the resultset.

This is different from the function getColumnName(int column) in ResultSetMetaData, which returns a single column name based on an index.

Arguments

None.

Returns

The data type of the returned value is “List<String>”. The list contains the names of the columns. The names are in the same order as the column indexes.

Throws

The method can throw SQLException.

getInternalColumnType(int column):
Purpose

Returns the data type of the specified column.

Arguments

column: This indicates the index (1-based) of the column for which you want the data type.

Returns

Returns the data type of the specified column. The data type is an integer.

Throws

The method can throw SQLException.

getQueryID()
Purpose

Returns the Snowflake query ID of the query to which this metadata applies.

Arguments

None.

Returns

This method returns the query ID of the query for which this metadata was generated. The query ID is a String that contains a UUID. Information about UUIDs is included in the description of the SQL function UUID_STRING.

Throws

The method can throw SQLException.

Object: Statement

A Statement object represents a SQL statement. The statement object allows users to perform tasks such as:

  • Execute a SQL statement.

  • Set a timeout for the execution of the statement.

  • Retrieve a result set for a query.

Methods

Method Name

Notes

Supported Methods

cancel()

close()

Snowflake-specific behavior (see below for details).

execute(String)

executeQuery(String)

executeUpdate(String)

getBatchQueryID()

Snowflake-specific behavior (see below for details).

getMaxFieldSize()

getMaxRows()

getMoreResults()

getQueryTimeout()

getResultSet()

getUpdateCount()

Snowflake-specific behavior (see below for details).

setCursorName(String)

setMaxRows(int)

setQueryTimeout(int)

Unsupported Methods

clearWarnings()

getWarnings()

setEscapeProcessing(boolean)

setMaxFieldSize(int)

Snowflake-specific Behavior

  • close()

    This method closes the object. After an object has been closed, calling almost any method of the closed object will raise a SQLException. Calling close on an already closed object is harmless and will not raise an exception.

  • getBatchQueryID()

    This method returns a list of the Snowflake query IDs of the most recently executed query batch of this Statement. If no query has been executed yet with the statement, the method returns null.

    This method is a Snowflake extension to the JDBC standard. To use this method, you must unwrap the statement. For example:

        connection.setAutoCommit(false);
        statement.addBatch("SELECT 1;");
        statement.addBatch("SELECT 2;");
        statement.executeBatch();
        connection.commit();
        connection.setAutoCommit(true);
        List<String> batchQueryIDs1;
        // Since getQueryID is not standard JDBC API, we must call unwrap() to
        // use these Snowflake methods.
        batchQueryIDs1 = statement.unwrap(SnowflakeStatement.class).getBatchQueryIDs();
        int num_query_ids = batchQueryIDs1.size();
        if (num_query_ids != 2) {
          System.out.println("ERROR: wrong number of query IDs in batch 1.");
        }
        // Check that each query ID is plausible.
        for (int i = 0; i < num_query_ids; i++) {
          String qid = batchQueryIDs1.get(i);
          if (!is_plausible_query_id(qid)) {
            msg = "SEVERE WARNING: suspicious query ID in batch";
            System.out.println("msg");
            System.out.println(qid);
          }
        }
    
  • getUpdateCount()

    This method returns the number of rows updated by the most recently executed SQL statement.

    • If the statement was a DML statement (INSERT, UPDATE, DELETE, etc.), then getUpdateCount() returns the number of rows that were added, deleted, or changed. Note that this value can be 0 if no rows were changed.

    • If the statement was a SELECT statement, then getUpdateCount() returns -1.

    • If the statement was a DDL statement, then getUpdateCount() returns -1.

Interface: SnowflakeStatement

The SnowflakeStatement interface contains Snowflake-specific methods. When you use the Snowflake JDBC driver to create an object of type Statement, for example by calling the Connection.createStatement() method, you actually get an object of a different (hidden) Snowflake-specific type, which implements both the JDBC Statement interface and the SnowflakeStatement interface. To access the SnowflakeStatement methods in that object, you unwrap the object.

Additional Methods

Method Name

Description

executeAsyncQuery()

Performs an asynchronous query.

getQueryID()

Returns the Snowflake query ID of the most recently executed query of this Statement.

setParameter(String, Value)

Sets Snowflake-specific parameters.

executeAsyncQuery(String)
Purpose

This method performs an asynchronous query, which involves submitting an SQL statement for execution, then returning control to the caller without waiting for the query to finish.

Arguments

A string containing the SQL command to execute. Any SQL statement that is valid for executeQuery() is also valid for executeAsyncQuery().

Note

File transfer statements, such as PUT and GET, are valid for executeAsyncQuery(), but behave synchronously.

Returns

An “empty” ResultSet. The user should poll the result set by calling resultSet.unwrap(SnowflakeResultSet.class).getStatus() until the query results become available.

Throws

The method can throw SQLException.

Notes

This method is a Snowflake extension to the JDBC standard. To use this method, you must unwrap the Statement object.

Examples

See Examples of Asynchronous Queries for an example that includes a call to this method.

getQueryID()
Purpose

This method returns the Snowflake query ID of the most recently executed query of this Statement.

Arguments

None.

Returns

The query ID of the most recently executed query of this statement. The query ID is a String that contains a UUID. If no query has been executed yet with the statement, the method returns null.

Throws

The method can throw SQLException.

Notes

This method is a Snowflake extension to the JDBC standard. To use this method, you must unwrap the Statement.

For asynchronous queries, the query ID does not become available until the execution of the statement completes. If you call SnowflakeStatement.getQueryID() after calling executeAsyncQuery() but before the statement finishes executing, the return value could be NULL. Instead, call resultSet.unwrap(SnowflakeResultSet.class).getQueryID() on the ResultSet object returned by executeAsyncQuery().

Examples
    String queryID1;
    queryID1 = statement.unwrap(SnowflakeStatement.class).getQueryID();
setParameter(String parameter_name, <type> <value>)
Purpose

The SnowflakeStatement class provides the setParameter method as a Snowflake extension. This allows the caller to set Snowflake-specific JDBC parameters.

The method is overloaded. Different JDBC parameters require different data types. A method exists for each valid data type that can be passed as the second argument to the function.

Arguments
parameter_name:

This string must contain the name of a pre-defined Snowflake JDBC parameter. The pre-defined JDBC parameters (and their valid values or ranges) are listed below:

JDBC Parameter

Notes

MULTI_STATEMENT_COUNT

Integer specifying the number of statements (0 = unlimited number of statements; 1 or higher indicates the exact number of statements that should be executed).

value:

This is the value to assign to the specified JDBC parameter. Make sure that the data type is compatible with the JDBC parameter you specified.

Returns

Nothing.

Throws

This function can throw SQLException.

Notes

This method is a Snowflake extension to the JDBC standard. To use this method, you must unwrap the Statement.

Examples
Statement statement1;
...
// Tell Statement to expect to execute 2 statements:
statement1.unwrap(SnowflakeStatement.class).setParameter(
        "MULTI_STATEMENT_COUNT", 2);

Interface: SQLException

SQLException objects are thrown by JDBC driver methods when an error occurs, and contain information about that error.

Method Name

Description

getErrorCode()

Returns a Snowflake-specific error code.

getMessage()

This returns a string that describes the error.

getSQLState()

Returns the SQLState.

getErrorCode()
Purpose

This method returns a custom Snowflake error code.

Arguments

None.

Returns

A Snowflake-specific error code.

Notes

See also the getSQLState() method.

getMessage()
Purpose

This method returns a string that describes the error.

Arguments

None.

Returns

A Snowflake-specific error message.

getSQLState()
Purpose

This method returns a string that contains a 5-character alphanumeric value based on the error.

Arguments

None.

Returns

A Snowflake-specific SQLState. An SQLState is a 5-character alphanumeric string that indicates the specific error that occurred.