This chapter documents the support for standard JDBC data types, return values, andf error messages provided by the FairCom DB SQL JDBC Driver.
Supported Data Types
The FairCom DB SQL JDBC Driver supports standard JDBC mapping of JDBC types to corresponding Java types.
In the JDBC methods CallableStatement.get..XXX() and PreparedStatement.set...XXX() methods, XXX is a Java type:
- For ...setXXX() methods, the driver converts the Java type to the FairCom DB SQL JDBC type shown in the following table before sending it to the database.
- For ...getXXX() methods, the driver converts the FairCom DB SQL JDBC type returned by the database to the Java type shown in “Mapping Between JDBC and Java Data Types” before returning it to the ...getXXX() method.
Mapping Between Java and JDBC Data Types
| Java Type | JDBC type | Java Type | JDBC type |
|---|---|---|---|
| String | VARCHAR or LONGVARCHAR | float | REAL |
| java.math.BigDecimal | NUMERIC | double | DOUBLE |
| boolean | BIT | byte[] | VARBINARY or LONGVARBINARY |
| byte | TINYINT | java.sql.Date | DATE |
| short | SMALLINT | java.sql.Time | TIME |
| int | INTEGER | java.sql.Timestamp | TIMESTAMP |
| long | BIGINT |
Mapping Between JDBC and Java Data Types
| JDBC type | Java type | JDBC type | Java type |
|---|---|---|---|
| CHAR | String | REAL | float |
| VARCHAR | String | FLOAT | double |
| LONGVARCHAR | String | DOUBLE | double |
| NUMERIC | java.math.BigDecimal | BINARY | byte[] |
| DECIMAL | java.math.BigDecimal | VARBINARY | byte[] |
| BIT | boolean | LONGVARBINARY | byte[] |
| TINYINT | byte | DATE | java.sql.Date |
| SMALLINT | short | TIME | java.sql.Time |
| INTEGER | int | TIMESTAMP | java.sql.Timestamp |
| BIGINT | long |
Return Values for DatabaseMetaData Methods
Applications call methods of the DatabaseMetaData class to retrieve details about the JDBC support provided by a specific driver.
The following table lists each method of the DatabaseMetaData class and shows what the FairCom DB SQL JDBC Driver returns when an applications calls the method. For details on the format and usage of each method, see the Java Platform Core API documentation.
The following example shows an excerpt from the sample program that illustrates calling methods of DatabaseMetaData.
Getting Driver Information Through DatabaseMetaData Methods
Connection con = DriverManager.getConnection ( url, prop);
.
.
.
// Get the DatabaseMetaData object and display
// some information about the connection
DatabaseMetaData dma = con.getMetaData ();
o.println("\nConnected to " + dma.getURL());
o.println("Driver " + dma.getDriverName());
o.println("Version " + dma.getDriverVersion());
Many of the methods return lists of information as an object of type ResultSet. Use the normal ResultSet methods such as getString() and getInt() to retrieve the data from the result sets.
Return Values for DatabaseMetaData Methods
| Method | Description | Returns |
|---|---|---|
| allProceduresAreCallable() | Can all the procedures returned by getProcedures be called by the current user? | True |
| allTablesAreSelectable() | Can all the tables returned by getTable be SELECTed by the current user? | False |
| dataDefinitionCausesTransactionCommit() | Does a data definition statement within a transaction force the transaction to commit? | False |
| dataDefinitionIgnoredInTransactions () | Is a data definition statement within a transaction ignored? | False |
| doesMaxRowSizeIncludeBlobs() | Did getMaxRowSize() include LONGVARCHAR and LONGVARBINARY blobs? | False |
| getBestRowIdentifier(String, String, String, int, boolean) | Get a description of a table’s optimal set of columns that uniquely identifies a row. | (result set) |
| getCatalogs() | Get the catalog names available in this database. | "Driver not capable" |
| getCatalogSeparator() | What’s the separator between catalog and table name? | “” (blank) |
| getCatalogTerm() | What’s the database vendor’s preferred term for “catalog”? | “” (blank) |
| getColumnPrivileges(String, String, String, String) | Get a description of the access rights for a table’s columns. | (result set) |
| getColumns(String, String, String, String) | Get a description of table columns available in a catalog. | (result set) |
| getCrossReference(String, String, String, String, String, String) | Get a description of the foreign key columns in the foreign key table that reference the primary key columns of the primary key table (describe how one table imports another’s key.) This should normally return a single foreign key/primary key pair (most tables only import a foreign key from a table once.) They are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ. | (result set) |
| getDatabaseProductName() | What’s the name of this database product? | “FairCom DB SQL” |
| getDatabaseProductVersion() | What’s the version of this database product? | “14.00..00.0000” |
| getDefaultTransactionIsolation() | What’s the database’s default transaction isolation level? The values are defined in java.sql.Connection. | TRANSACTION_SERIALIZABLE |
| getDriverMajorVersion() | What’s this JDBC driver’s major version number? | 14 |
| getDriverMinorVersion() | What’s this JDBC driver's minor version number? | 0 |
| getDriverName() | What’s the name of this JDBC driver? | “ctree.jdbc.ctreeDriver” |
| getDriverVersion() | What’s the version of this JDBC driver? | “14.00.00.0000” |
| getExportedKeys(String, String, String) | Get a description of the foreign key columns that reference a table’s primary key columns (the foreign keys exported by a table). | (result set) |
| getExtraNameCharacters() | Get all the “extra” characters that can be used in unquoted identifier names (those beyond a-z, A-Z, 0-9 and _). | null |
| getIdentifierQuoteString () | What’s the string used to quote SQL identifiers? This returns a space “ ” if identifier quoting isn’t supported. | “"” |
| getImportedKeys(String, String, String) | Get a description of the primary key columns that are referenced by a table’s foreign key columns (the primary keys imported by a table). | (result set) |
| getIndexInfo(String, String, String, boolean, boolean) | Get a description of a table’s indexes and statistics. | (result set) |
| getMaxBinaryLiteralLength() | How many hex characters can you have in an inline binary literal? | 32000 |
| getMaxCatalogNameLength() | What’s the maximum length of a catalog name? | 64 |
| getMaxCharLiteralLength() | What’s the max length for a character literal? | 8192 |
| getMaxColumnNameLength() | What’s the limit on column name length? | 64 |
| getMaxColumnsInGroupBy() | What’s the maximum number of columns in a “GROUP BY” clause? | 0 (no limit) |
| getMaxColumnsInIndex() | What’s the maximum number of columns allowed in an index? | 100 |
| getMaxColumnsInOrderBy() | What’s the maximum number of columns in an “ORDER BY” clause? | 0 (no limit) |
| getMaxColumnsInSelect() | What’s the maximum number of columns in a “SELECT” list? | 0 (no limit) |
| getMaxColumnsInTable() | What’s the maximum number of columns in a table? | 500 |
| getMaxConnections() | How many active connections can we have at a time to this database? | 10 |
| getMaxCursorNameLength() | What’s the maximum cursor name length? | 64 |
| getMaxIndexLength() | What’s the maximum length of an index (in bytes)? | 0 (no limit) |
| getMaxProcedureNameLength() | What’s the maximum length of a procedure name? | 64 |
| getMaxRowSize() | What’s the maximum length of a single row? | 0 (no limit) |
| getMaxSchemaNameLength() | What’s the maximum length allowed for a schema name? | 64 |
| getMaxStatementLength() | What’s the maximum length of a SQL statement? | 32MB (35000 prior to V10.3) |
| getMaxStatements() | How many active statements can we have open at one time to this database? | 0 (no limit) |
| getMaxTableNameLength() | What’s the maximum length of a table name? | 64 |
| getMaxTablesInSelect() | What’s the maximum number of tables in a SELECT? | 250 |
| getMaxUserNameLength() | What’s the maximum length of a user name? | 64 |
| getNumericFunctions() | Get a comma separated list of math functions. |
ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, DATALENGTH, DEGREES, EXP, FLOOR, ISNUMERIC, LOG, LOG10, MOD, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, TAN, TRUNCATE |
| getPrimaryKeys(String, String, String) | Get a description of a table’s primary key columns. | (result set) |
| getProcedureColumns(String, String, String, String) | Get a description of a catalog’s stored procedure parameters and result columns. | (result set) |
| getProcedures(String, String, String) | Get a description of stored procedures available in a catalog. | (result set) |
| getProcedureTerm() | What’s the database vendor’s preferred term for “procedure”? | “procedure” |
| getSchemas() | Get the schema names available in this database. | (result set) |
| getSchemaTerm() | What’s the database vendor’s preferred term for “schema”? | “Owner” |
| getSearchStringEscape() | This is the string that can be used to escape ‘_’ or ‘%’ in the string pattern style catalog search parameters. | “\” |
| getSQLKeywords() | Get a comma separated list of all a database’s SQL keywords that are NOT also SQL92 keywords. | null |
| getStringFunctions() | Get a comma separated list of string functions. | ASCII, , CHAR, CHAR_LENGTH, CHARACTER_LENGTH, CONCAT, DIFFERENCE, INSERT, LCASE, LEFT, LENGTH, LOCATE, LTRIM, OCTECT_LENGTH, OVERLAY, POSITION, REPEAT, REPLACE, RIGHT, RTRIM, SOUNDEX, SPACE, SUBSTRING, SUBSTRING, UCASE, |
| getSystemFunctions() | Get a comma separated list of system functions. | USERNAME, IFNULL, USER |
| getTablePrivileges(String, String, String) | Get a description of the access rights for each table available in a catalog. | (result set) |
| getTables(String, String, String, String []) | Get a description of tables available in a catalog. | (result set) |
| getTableTypes() | Get the table types available in this database. | SYNONYM, SYSTEM TABLE, TABLE, VIEW |
| getTimeDateFunctions() | Get a comma separated list of time and date functions. | CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURDATE, CURTIME, DATEADD, DATEDIFF, DAYNAME, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, EXTRACT, HOUR, MINUTE, MONTH, MONTHNAME, NOW, QUARTER, SECOND, SYSDATE, SYSTIME, SYSTIMESTAMP, TIMESTAMPADD, TIMESTAMPDIFF, WEEK, YEAR |
| getTypeInfo() | Get a description of all the standard SQL types supported by this database. | (result set) |
| getURL() | What’s the url for this database? | (the URL) |
| getUserName() | What’s our user name as known to the database? | (the userid) |
| getVersionColumns(String, String, String) | Get a description of a table’s columns that are automatically updated when any value in a row is updated. | (result set) |
| isCatalogAtStart() | Does a catalog appear at the start of a qualified table name? (Otherwise it appears at the end) | True |
| isReadOnly() | Is the database in read-only mode? | False |
| nullPlusNonNullIsNull() | Are concatenations between NULL and non-NULL values NULL? A JDBC-Compliant driver always returns true. | True |
| nullsAreSortedAtEnd() | Are NULL values sorted at the end regardless of sort order? | False |
| nullsAreSortedAtStart() | Are NULL values sorted at the start regardless of sort order? | False |
| nullsAreSortedHigh() | Are NULL values sorted high? | False |
| nullsAreSortedLow() | Are NULL values sorted low? | True |
| storesLowerCaseIdentifiers() | Does the database treat mixed case unquoted SQL identifiers as case insensitive and store them in lower case? |
True or False Depends on the identifier case specified during the creation of database. |
| storesLowerCaseQuotedIdentifiers() | Does the database treat mixed case quoted SQL identifiers as case insensitive and store them in lower case? |
True or False Depends on the identifier case specified during the creation of database. |
| storesMixedCaseIdentifiers() | Does the database treat mixed case unquoted SQL identifiers as case insensitive and store them in mixed case? | False |
| storesMixedCaseQuotedIdentifiers() | Does the database treat mixed case quoted SQL identifiers as case insensitive and store them in mixed case? | False |
| storesUpperCaseIdentifiers() | Does the database treat mixed case unquoted SQL identifiers as case insensitive and store them in upper case? |
True or False Depends on the identifier case specified during the creation of database. |
| storesUpperCaseQuotedIdentifiers() | Does the database treat mixed case quoted SQL identifiers as case insensitive and store them in upper case? | False |
| supportsAlterTableWithAddColumn() | Is “ALTER TABLE” with add column supported? | True |
| supportsAlterTableWithDropColumn() | Is “ALTER TABLE” with drop column supported? | True |
| supportsANSI92EntryLevelSQL() | Is the ANSI92 entry level SQL grammar supported? All JDBC-Compliant drivers must return true. | True |
| supportsANSI92FullSQL() | Is the ANSI92 full SQL grammar supported? | False |
| supportsANSI92IntermediateSQL() | Is the ANSI92 intermediate SQL grammar supported? | False |
| supportsCatalogsInDataManipulation() | Can a catalog name be used in a data manipulation statement? | False |
| supportsCatalogsInIndexDefinitions() | Can a catalog name be used in an index definition statement? | False |
| supportsCatalogsInPrivilegeDefinitions() | Can a catalog name be used in a privilege definition statement? | False |
| supportsCatalogsInProcedureCalls() | Can a catalog name be used in a procedure call statement? | False |
| supportsCatalogsInTableDefinitions() | Can a catalog name be used in a table definition statement? | False |
| supportsColumnAliasing() | Is column aliasing supported? If so, the SQL AS clause can be used to provide names for computed columns or to provide alias names for columns as required. | True |
| supportsConvert() | Is the CONVERT function between SQL types supported? | True |
| supportsConvert(int, int) | Is CONVERT between the given SQL types supported? |
True or False Depends on the types being converted |
| supportsCoreSQLGrammar() | Is the ODBC Core SQL grammar supported? | True |
| supportsCorrelatedSubqueries() | Are correlated subqueries supported? A JDBC-Compliant driver always returns true. | True |
| supportsDataDefinitionAndDataManipulationTransactions () | Are both data definition and data manipulation statements within a transaction supported? | True |
| supportsDataManipulationTransactionsOnly() | Are only data manipulation statements within a transaction supported? | False |
| supportsDifferentTableCorrelationNames() | If table correlation names are supported, are they restricted to be different from the names of the tables? | False |
| supportsExpressionsInOrderBy() | Are expressions in “ORDER BY” lists supported? | True |
| supportsExtendedSQLGrammar() | Is the ODBC Extended SQL grammar supported? | True |
| supportsFullOuterJoins() | Are full nested outer joins supported? | False |
| supportsGroupBy() | Is some form of “GROUP BY” clause supported? | True |
| supportsGroupByBeyondSelect() | Can a “GROUP BY” clause add columns not in the SELECT provided it specifies all the columns in the SELECT? | True |
| supportsGroupByUnrelated() | Can a “GROUP BY” clause use columns not in the SELECT? | False |
| supportsIntegrityEnhancementFacility() | Is the SQL Integrity Enhancement Facility supported? | True |
| supportsLikeEscapeClause() | Is the escape character in “LIKE” clauses supported? A JDBC-Compliant driver always returns true. | True |
| supportsLimitedOuterJoins() | Is there limited support for outer joins? (This will be true if supportFullOuterJoins is true.) | True |
| supportsMinimumSQLGrammar() | Is the ODBC Minimum SQL grammar supported? All JDBC-Compliant drivers must return true. | True |
| supportsMixedCaseIdentifiers() | Does the database treat mixed case unquoted SQL identifiers as case sensitive and as a result store them in mixed case? A JDBC-Compliant driver will always return false. | False |
| supportsMixedCaseQuotedIdentifiers() | Does the database treat mixed case quoted SQL identifiers as case sensitive and as a result store them in mixed case? A JDBC-Compliant driver will always return true. | True |
| supportsMultipleResultSets() | Are multiple ResultSets from a single execute supported? | False |
| supportsMultipleTransactions () | Can we have multiple transactions open at once (on different connections)? | True |
| supportsNonNullableColumns() | Can columns be defined as non-nullable? A JDBC-Compliant driver always returns true. | True |
| supportsOpenCursorsAcrossCommit() | Can cursors remain open across commits? | False |
| supportsOpenCursorsAcrossRollback() | Can cursors remain open across rollbacks? | False |
| supportsOpenStatementsAcrossCommit() | Can statements remain open across commits? | True |
| supportsOpenStatementsAcrossRollback() | Can statements remain open across rollbacks? | True |
| supportsOrderByUnrelated() | Can an “ORDER BY” clause use columns not in the SELECT? | True |
| supportsOuterJoins() | Is some form of outer join supported? | True |
| supportsPositionedDelete() | Is positioned DELETE supported? | True |
| supportsPositionedUpdate() | Is positioned UPDATE supported? | True |
| supportsSchemasInDataManipulation() | Can a schema name be used in a data manipulation statement? | True |
| supportsSchemasInIndexDefinitions() | Can a schema name be used in an index definition statement? | True |
| supportsSchemasInPrivilegeDefinitions() | Can a schema name be used in a privilege definition statement? | True |
| supportsSchemasInProcedureCalls() | Can a schema name be used in a procedure call statement? | True |
| supportsSchemasInTableDefinitions() | Can a schema name be used in a table definition statement? | True |
| supportsSelectForUpdate() | Is SELECT for UPDATE supported? | True |
| supportsStoredProcedures() | Are stored procedure calls using the stored procedure escape syntax supported? | True |
| supportsSubqueriesInComparisons() | Are subqueries in comparison expressions supported? A JDBC-Compliant driver always returns true. | True |
| supportsSubqueriesInExists() | Are subqueries in ‘exists’ expressions supported? A JDBC-Compliant driver always returns true. | True |
| supportsSubqueriesInIns() | Are subqueries in ‘in’ statements supported? A JDBC-Compliant driver always returns true. | True |
| supportsSubqueriesInQuantifieds() | Are subqueries in quantified expressions supported? A JDBC-Compliant driver always returns true. | True |
| supportsTableCorrelationNames() | Are table correlation names supported? A JDBC-Compliant driver always returns true. | True |
| supportsTransactionIsolationLevel(int) | Does the database support the given transaction isolation level? | True |
| supportsTransactions () | Are transactions supported? If not, commit is a no-op and the isolation level is TRANSACTION_NONE. | True |
| supportsUnion() | Is SQL UNION supported? | True |
| supportsUnionAll() | Is SQL UNION ALL supported? | True |
| usesLocalFilePerTable() | Does the database use a file for each table? | False |
| usesLocalFiles() | Does the database store tables in a local file? | False |
| JDBC 2.0 | ||
| deletesAreDetected(int) | Indicates whether or not a visible row delete can be detected by calling ResultSet.rowDeleted(). | False |
| getConnection() | Retrieves the connection that produced this metadata object. | The connection that produced this metadata object |
| getUDTs(String, String, String, int[]) | Gets a description of the userdefined types defined in a particular schema. | Empty ResultSet object |
| insertsAreDetected(int) | Indicates whether or not a visible row insert can be detected by calling ResultSet.rowInserted(). | False |
| othersDeletesAreVisible(int) | Indicates whether deletes made by others are visible. | False |
| othersUpdatesAreVisible(int) | Indicates whether updates made by others are visible. | False |
| ownDeletesAreVisible(int) | Indicates whether a result set’s own deletes are visible. | False |
| ownInsertsAreVisible(int) | Indicates whether inserts made by others are visible. | False |
| ownUpdatesAreVisible(int) | Indicates whether a result set’s own updates are visible. | False |
| supportsBatchUpdates() | Indicates whether the driver supports batch updates. | True |
| supportsResultSetType(int) | Does the database support the given result set type? |
True if result set type is FORWARD_ONLY or SCROLL_INSENSITIVE |
| supportsResultSetConcurrency(int, int) | Does the database support the concurrency type in combination with the given result set type? | True if result set type is FORWARD_ONLY and if concurrency type is CONCUR_READ_ONLY |
| updatesAreDetected(int) | Indicates whether or not a visible row update can be detected by calling the method ResultSet.rowUpdated. | False |
| JDBC 3.0 | ||
| supportsSavepoints() | Does the database support savepoints | False |
| supportsNamedParameters() | Does the database support named parameters to callable statements | False |
| supportsMultipleOpenResults() | Indicates whether it is possible to have multiple ResultSet objects returned from a Callable Statement object simultaneously | False |
| supportsGetGeneratedKeys() | Indicates whether auto-generated keys can be retrieved after a statement has been executed | False |
| getSuperTypes(String, String, String) | Gets a description of user-defined type hierarchies defined in a particular schema in this database | Empty ResultSet object |
| getSuperTables(String, String, String) | Gets a description of tables defined in a particular schema in this database | Empty ResultSet object |
| getAttributes(String, String, String, String) | Gets a description of the given attribute of the given type for a user-defined type that is available in the given schema and catalog | Empty ResultSet object |
| supportsResultSetHoldability(int) | Does the database support the given result set holdability | True if result set holdability is ResultSet .CLOSE_CURSORS_AT_COMMIT, otherwise False |
| getResultSetHoldability() | Gets the default holdability of this ResultSet object | Always returns ResultSet.CLOSE_CURSORS_AT_COMMIT |
| getDatabaseMajorVersion() | Gets the major version number of the underlying database | returns 4 |
| getDatabaseMinorVersion() | Gets the minor version number of the underlying database | returns 0 |
| getJDBCMajorVersion() | Gets the major JDBC version number of this driver | returns 4 |
| getJDBCMinorVersion() | Gets the minor JDBC version number of this driver | returns 0 |
| getSQLStateType() | Indicates whether the SQLStates returned by SQLException.getSQLState is X/Open SQL CLI or SQL99 | returns sqlState99 |
| locatorsUpdateCopy() | Indicates whether updates made to LOB are made on a copy or directly to the LOB | “Driver does not support this”Exception |
| supportsStatementPooling() | Does the database support statement pooling | False |
JDBC 4.0
| autoCommitFailureClosesAllResultSets() | Retrieves whether a SQLException while autoCommit is true indicates that all open ResultSets are closed, even ones that are holdable. | False |
| getFunctions(String, String, String) | Retrieves a description of the system (built-in) and user defined functions available in the database. | (result set) |
|
getFunctionColumns(String, String, String, String) |
Retrieves a description of the system (built-in) or user defined function parameters and return type. | (result set) |
| getClientInfoProperties() | Retrieves a list of the client info properties that the driver supports. | (result set) |
|
supportsStoredFunctionsUsing- CallSyntax() |
Retrieves whether this database supports invoking user-defined or vendor functions using the stored procedure escape syntax. | False |
| getSchemas.(String, String) | Retrieves the owner names available in this database. | (result set) |
JDBC 4.1
| generatedKeyAlwaysReturned() | Retrieves whether a generated key will always be returned for auto generated key columns | False |
| getPsuedoColumns() | Retrieves a description of the pseudo or hidden columns available in a given table | (result set) |
Error Messages
The error messages generated by the driver, along with associated SQLSTATE and FairCom DB SQL error code values, are documented in the Errors.