Overview
This chapter describes Java Database Connectivity (JDBC), the Teradata JDBC Driver, and the two-tier architecture that connects client system Java programs to the database. The section titles include:
What's New
What's New in Teradata JDBC Driver version 20.0
- TLS certificate revocation checking (JDBC-191874, JDBC-191875)
- Requires JDK/JRE 8 or later
- Does not work with JDK/JRE 7 and earlier
What's New in Teradata JDBC Driver version 17.20
- STRICT_NAMES=OFF connection parameter for Apache Spark interoperability (JDBC-191813)
- OIDC_SCOPE and OIDC_TOKEN connection parameters for configuration flexibility with OpenID Connect (JDBC-191823)
- BROWSER_TIMEOUT connection parameter (JDBC-191811)
- Improved details for Client System Information (JDBC-191828)
- Reconnect interoperability with VPN disconnect/reconnect (JDBC-191829)
What's New in Teradata JDBC Driver version 17.10
- HTTPS/TLS connections available with Teradata Advanced SQL Engine 16.20.53.30 and later (JDBC-190758)
- Browser Authentication available with Teradata Advanced SQL Engine 17.10 and later (JDBC-191772)
What's New in Teradata JDBC Driver version 17.0
- Eliminate tdgssconfig.jar (JDBC-191761)
- JWT authentication available with Teradata Advanced SQL Engine 16.20 and later (JDBC-187068)
- Compatibility with JDK 13 (JDBC-191770)
What's New in Teradata JDBC Driver version 16.20
- New data type DATASET STORAGE FORMAT CSV with Teradata Advanced SQL Engine 16.20 and later (JDBC-184941)
- Kerberos authentication available on AIX, HP-UX, MacOS, and Solaris with Teradata Database 16.10 and later (JDBC-184179)
What's New in Teradata JDBC Driver version 16.10
- Stored Password Protection (JDBC-154936)
- Support for Teradata Database multiple hash maps with Teradata Database 16.10 and later (JDBC-174213)
- Java Stored Procedure parameters as UDTs, Periods, XML, ST_Geometry, and Array with Teradata Database 16.10 and later (JDBC-124424)
- Connection parameters MAYBENULL and COLUMN_NAME to tailor ResultSetMetaData behavior (JDBC-183490, JDBC-185106)
- JDBC FastLoad and JDBC FastLoad CSV connection parameters for error tables (JDBC-171880)
What's New in Teradata JDBC Driver version 16.0
- Larger rows up to 1MB in size with Teradata Database 16.0 and later (JDBC-115666, JDBC-177265, JDBC-177267)
- Larger message sizes for improved data transfer performance with Teradata Database 16.0 and later (JDBC-177261)
- Unicode Pass Through with Teradata Database 16.0 and later (JDBC-174886)
- New data type DATASET STORAGE FORMAT AVRO with Teradata Database 16.0 and later (JDBC-176193)
- LOGMECH=TDNEGO connection parameter for automatic selection of logon mechanism with Teradata Database 15.10 and later (JDBC-154836)
- Account string now supported with LDAP and KRB5 logon mechanisms (JDBC-181560)
- JDBC 4.0 APIs DatabaseMetaData getFunctions, getFunctionColumns, getSchemas(String,String) (JDBC-163135)
- JDBC 4.0 API behavior for getPrecision method of ParameterMetaData and ResultSetMetaData (JDBC-156332)
- DatabaseMetaData getColumn method avoids HELP commands with Teradata Database 16.0 and later (JDBC-154823)
- DatabaseMetaData methods use views DBC.UDTInfoV and DBC.UDTTransformV with Teradata Database 16.0 and later (JDBC-143102, JDBC-170532)
- Improved performance for DatabaseMetaData methods with LITERAL_UNDERSCORE=ON connection parameter (JDBC-179996)
- Improved performance for ResultSet cursor movement methods (JDBC-182658)
- Improved logon performance through reducing DNS lookups (JDBC-177757, JDBC-182168)
What's New in Teradata JDBC Driver version 15.10
- Improved performance for small LOB values with Teradata Database 15.10 and later (JDBC-163202)
- New escape syntax scalar functions from the JDBC 4.0 API Specification (JDBC-163170)
- Connection isValid method for SQL connections (JDBC-164061)
- Support Teradata QueryGrid connector for MongoDB (JDBC-173903)
- CHATTER connection parameter for JDBC FastLoad and JDBC FastLoad CSV (JDBC-171241)
- Support profile query band values with Teradata Database 15.10 and later (JDBC-170226)
- Support for JDK 8
What's New in Teradata JDBC Driver version 15.0
- Support for the JSON data type with Teradata Database 15.0 and later (JDBC-162376)
- Centralized administration for data encryption with Teradata Database 14.10 and later (JDBC-148040)
- Avoid the "Slow Logon on Linux" problem (JDBC-165341)
- STRICT_ENCODE connection parameter (JDBC-144415)
- Support for the PERIOD data type with JDBC FastLoad (JDBC-160027) and JDBC FastExport (JDBC-160024)
- Support Struct values for TIME WITH TIME ZONE, TIMESTAMP WITH TIME ZONE (JDBC-144698), and Interval data types (JDBC-110776)
- Automatic reconnect after database communication failure with Teradata Database 14.10 and later (JDBC-154833)
- Redrive SQL requests interrupted by database restart with Teradata Database 14.10 and later (JDBC-154881)
What's New in Teradata JDBC Driver version 14.10
- java.sql.Driver getPropertyInfo method implemented (JDBC-55968)
- Support for 64-bit activity counts with Teradata Database 14.10 and later (JDBC-68722)
- ResultSet holdability CLOSE_CURSORS_AT_COMMIT (JDBC-99266)
- DatabaseMetaData getIndexInfo method supports arguments containing double quotes (JDBC-100184)
- Fixed-width BYTE data values as PreparedStatement parameters (JDBC-127422)
- JDBC 4.0 SQLXML data type with Teradata Database 14.10 and later (JDBC-134645)
- JDBC FastLoad and JDBC FastExport GOVERN=OFF fail-fast with Teradata Database 14.10 and later (JDBC-153117)
- Console partition connection support for UTF8 session character set with Teradata Database 14.10 and later (JDBC-156715)
- DatabaseMetaData getIndexInfo method provides index database name with Teradata Database 14.10 and later (JDBC-159277)
- PreparedStatement setObject method with scaleOrLength argument for Raw connections (JDBC-160029)
- Support for the SHOW IN XML command (JDBC-160209)
- JDBC 4.0 API methods for non-SQL connections (JDBC-160380)
- Support for JDK 7 (JDBC-162129)
What's New in Teradata JDBC Driver version 14.0
- JDBC 4.0 API Specification interface support (JDBC-107402)
- Support for the Array data type with Teradata Database 14.0 and later (JDBC-138098)
- Support for the SQL NUMBER data type with Teradata Database 14.0 and later (JDBC-143362)
- JDBC FastLoad CSV connections enable efficient loading of CSV (comma-separated value) data into an empty destination table (JDBC-143576)
- Input and output java.sql.Struct values for Structured and Internal UDT values with Teradata Database 13.10 and later (JDBC-115639)
- Support for Period Data Types as java.sql.Struct with Teradata Database 13.10 and later (JDBC-117048)
- Support for application custom type mapping for Distinct and Structured UDT values with Teradata Database 13.10 and later (JDBC-115641)
- Support for Mandatory Access Control (row-level security) with Teradata Database 14.0 and later (JDBC-129622)
- Support for Client Attributes with Teradata Database 14.0 and later (JDBC-122378)
- Monitor connection support for UTF8 and UTF16 session character sets with Teradata Database 14.0 and later (JDBC-109963)
- Support for Teradata session reconnect after database communication failure (JDBC-107800)
- DDSTATS connection parameter (JDBC-143408)
What's New in Teradata JDBC Driver version 13.10
- PreparedStatement batch per-row success/error conditions with Teradata Database 13.10 and later (JDBC-124457)
- Support for Teradata Database 13.10 TASM workload management (JDBC-123191)
- Support FastExport direct export without spooling with Teradata Database 13.10 and later (JDBC-122317)
- Improved support for TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE (JDBC-106222)
- Support for Geospatial data types with Teradata Database 13.0 and later (JDBC-132370)
- CONNECT_FAILURE_TTL connection parameter (JDBC-113453)
- TCP connection parameter for TCP socket options (JDBC-131226, RFC 131929)
- Support for the STARTUP string specified by CREATE/MODIFY USER (JDBC-130385)
- COP connection parameter to control COP hostname discovery (JDBC-92048)
- Support for tables with no primary index with Teradata Database 13.0 and later (JDBC-121210)
What's New in Teradata JDBC Driver version 13.0
- The tdgssjava.jar file is no longer required by the Teradata JDBC Driver (JDBC-112569)
- Support for Java User-Defined Functions (UDFs) with Teradata Database 13.0 and later (JDBC-115627)
- Support for returning dynamic result sets from a Java Stored Procedure with Teradata Database 13.0 and later (JDBC-108348)
- Login timeout functionality with DriverManager setLoginTimeout or DataSource setLoginTimeout (JDBC-107027)
- JDBC FastExport enables efficient export of large amounts of data from tables and views (JDBC-111264)
- Better implicit data type conversions for DATE, TIME, and TIMESTAMP values sent to Teradata Database 12.0 and later (JDBC-69205)
- Y2K-compliant implicit conversion for DATE values sent to destination CHAR/VARCHAR with Teradata Database 12.0 and later (JDBC-69205)
- Support for Trusted Sessions (QueryBand PROXYUSER user impersonation) with Teradata Database 13.0 and later (JDBC-114956)
- Support getConnection parameters username and password for LDAP authentication with Teradata Database V2R6.2 and later (JDBC-116276)
- Support for a literal IP address as a Teradata Database hostname (JDBC-120378)
What's New in Teradata JDBC Driver version 12.0
- Support for Java Stored Procedures with Teradata Database 12.0 and later (JDBC-101800)
- User Defined Functions (UDFs), User Defined Methods (UDMs), Java Stored Procedures, and other External Stored Procedures (XSPs) can be created from resources on the client classpath (JDBC-101277)
- Support for Dynamic Result Sets returned from SQL Stored Procedures with Teradata Database 12.0 and later (JDBC-102453)
- Support for Updatable Result Sets with Teradata Database V2R6.2 and later (JDBC-51544)
- Support for the JDBC 3.0 Specification Blob and Clob update methods (JDBC-58075)
- JDBC FastLoad connections enable efficient loading of large amounts of data into an empty destination table (JDBC-104893)
- JDBC Monitor connections provide access to the database's performance monitoring and production control functions (JDBC-100351)
- Fractional seconds of TIME values are returned from the database (JDBC-104020)
- Support for DatabaseMetaData queries against the Data Dictionary X views with the USEXVIEWS connection parameter (JDBC-92937)
- Support for database password expiration with the NEW_PASSWORD connection parameter (JDBC-92927)
- Support for the SET QUERY_BAND statement with Teradata Database 12.0 and later (JDBC-102732)
- Support getMoreResults(KEEP_CURRENT_RESULT) for multi-statement request cursor positioning with Teradata Database 12.0 and later (JDBC-94241)
- Support for select-list parameter markers with Teradata Database 12.0 and later (JDBC-107900)
Planning for Software Upgrades
Change applications to expect the JDBC specification features and behavior listed, in anticipation of support for these JDBC specification features and behavior.The JDBC 3.0 specification requires JDBC drivers and data sources to validate the SQL requests that are passed to the executeQuery, executeUpdate, and executeBatch APIs; and requires an SQLException to be thrown from the executeQuery, executeUpdate, and executeBatch APIs if the SQL request is inappropriate for the API.
- If executeQuery is used on an SQL statement that does not return a result set or returns multiple result sets then an SQLException is thrown.
- If executeUpdate is used on an SQL statement that returns a result set or returns multiple update counts then an SQLException is thrown.
- If executeBatch is used where one or more of the SQL statements return a result set, then an SQLException is thrown.
Determining the Current Version of the Teradata JDBC Driver
Windows
To determine the currently installed version of the Teradata JDBC Driver on Windows, open a Command Prompt window, change to the directory containing the Teradata JDBC Driver, and use the following commands:
jar xvf terajdbc4.jar META-INF/MANIFEST.MF
type META-INF\MANIFEST.MF
UNIX and Linux
To determine the currently installed version of the Teradata JDBC Driver on UNIX and Linux, change to the directory containing the Teradata JDBC Driver, and use the following commands.
jar xvf terajdbc4.jar META-INF/MANIFEST.MF
cat META-INF/MANIFEST.MF
JDBC Interface Description
JDBC is a specification for an application programming interface (API). This API allows platform-independent Java applications to access database management systems using SQL.
The JDBC API provides a standard set of interfaces for:
- Opening connections to databases
- Executing SQL statements
- Processing results
These interfaces are shown Figure 1 and listed in Table 2.
Figure 1: JDBC Interfaces
Table 2: JDBC Interfaces
|
|
|
Provides support for Binary Large Objects (BLOB)
|
|
Provides support for Character Large Objects (CLOB)
|
|
Represents a connection to a database
|
java.sql.DatabaseMetaData
|
Accesses a variety of information for the selected Database
|
|
Provides support for creating/obtaining database connections
|
|
Handles driver loading and supports creating new database connections
|
java.sql.ParameterMetaData
|
Accesses the metadata information for the parameters
|
java.sql.PreparedStatement
|
Acts as a container for executing a prepared SQL statement on a given connection
|
|
Controls access to the row results of a given statement
|
java.sql.ResultSetMetaData
|
Accesses the metadata information for the result set
|
|
Acts as a container for executing an SQL statement on a given connection
|
Java.sql.CallableStatement
|
Acts as a container for executing a stored procedure on the database for a given connection
|
javax.sql.ConnectionEventListener
|
Acts as an object that registers to receive events generated by a PooledConnection
|
javax.sql.ConnectionPoolDataSource
|
Is a factory for PooledConnection objects. An object that implements this interface typically is registered with a Java Naming and Directory Interface (JNDI) service.
|
javax.sql.PooledConnection
|
Is a connection object that provides hooks for connection pool management. A PooledConnection object represents a physical connection to a data source.
|
Teradata JDBC Driver Description
The Teradata JDBC Driver is a set of Java classes that work with the JDBC interface, enabling access to Teradata Vantage™ using the Java language. As illustrated in Figure 2, Teradata currently supports the Type 4 JDBC driver.
Figure 2: JDBC Interface
Type 4 JDBC Driver
The Type 4 JDBC driver communicates directly with the database.
The JDBC Type 4 Architecture
Description
The Teradata JDBC Driver uses a two-tier architecture to access the database as shown in Figure 3.
Figure 3: JDBC Type 4 Architecture
How It Works
The Teradata JDBC Driver is platform-independent and can be used on any system that has a supported Java Virtual Machine (JVM) installed.
Java classes of the Teradata JDBC Driver connect directly to the database using a TCP socket.
Benefits
The two-tier access architecture offers the following benefits:
- Improved performance over the JDBC Type 3 driver
- All-Java driver allows it to run anywhere
Support for Internationalization
Java Virtual Machine Locale
The Teradata JDBC Driver provides support for the Japanese locale, such that the message text of Teradata JDBC Driver exceptions is provided in Japanese, when the Japanese locale is used for the JVM.
java -Duser.language=ja -Duser.country=JP
This applies only to Teradata JDBC Driver exception conditions. Exception message text for database error conditions is provided by the database, and is not controlled by the JVM locale.
The message text of Teradata JDBC Driver exceptions is provided in English for all locales other than the Japanese locale.
Data Flow From a Java Application to Teradata JDBC Driver
Almost all Java APIs, including the JDBC APIs, assume the use of java.lang.String objects, which contain Unicode characters.
Character data is typically passed from a Java application into the Teradata JDBC Driver as java.lang.String objects. This occurs when an application:
- Includes character data as SQL string literals in an SQL statement, and uses one of the JDBC APIs such as Statement.execute, Statement.executeUpdate, Statement.executeQuery, or Connection.prepareStatement
- Uses a prepared statement, and binds character data to a ? parameter marker using the JDBC API method PreparedStatement.setString or PreparedStatement.setObject(String)
Once data passes into the Teradata JDBC Driver as java.lang.String objects, the Teradata JDBC Driver does not convert from non-Unicode to Unicode characters, because java.lang.String objects always contain Unicode characters.
There are two unusual scenarios in which a Java application might obtain non-Unicode characters, and subsequently need to store those characters in the database. These are not normal application development scenarios, since normal Java application development is entirely Unicode-based:
- Scenario 1: The Java application obtains non-Unicode character data in some way; for example, by reading from a TCP socket, and stores the non-Unicode character data as bytes in a Java byte array
- Scenario 2: The Java application must read non-Unicode character data from a file on the file system
For Scenario 1, with the non-Unicode character data stored as bytes in a Java byte array, the Java application uses the following java.lang.String constructor:
String(byte[] bytes, String charsetName)
This constructs a new String by decoding the specified array of bytes using the specified charset. After constructing the java.lang.String object, the Java application can manipulate this object like any other Java String object, and pass it into the Teradata JDBC Driver.
For Scenario 2, with the non-Unicode character data stored in a file on the file system, the Java application uses a PreparedStatement object with a ? parameter marker to represent the input data from the file, and the Java application uses one of the following JDBC APIs:
- PreparedStatement.setCharacterStream–can be used for data stored in a file, in any character set. The JDBC API PreparedStatement.setCharacterStream requires the Java application to provide a Reader object as one of the arguments. With PreparedStatement.setCharacterStream, it is the Java application's responsibility to use the appropriate Java I/O APIs to convert non-Unicode input data to Unicode and ensure that the conversion does not result in any loss of any data.
The Java application constructs an InputStreamReader object on a FileInputStream object, and then supplies the InputStreamReader object as an argument to the JDBC API PreparedStatement.setCharacterStream.
When constructing the InputStreamReader object, the Java application uses one of the InputStreamReader constructors that have a charset parameter, and the Java application specifies the correct character set of the data stored in the file.
For example:
prepstmt.setCharacterStream(columnIndex, new InputStreamReader(new FileInputStream("myfile.dat"),"ISO-8859-8"))
- PreparedStatement.setAsciiStream–can be used for data stored in a file, only in the American Standard Code for Information (ASCII) character set. The JDBC API PreparedStatement.setAsciiStream requires the Java application to provide an InputStream object as one of the arguments.
The Java application constructs a FileInputStream object to read from the ASCII file, and then supplies that FileInputStream object as an argument to the JDBC API PreparedStatement.setAsciiStream.
For example:
prepstmt.setAsciiStream(columnIndex, new FileInputStream("ascii.txt"))
Data Flow Between the Teradata JDBC Driver and Database
The Teradata JDBC Driver provides a CHARSET connection parameter for the Java application to specify the session character set for the database session. The CHARSET connection parameter's description is located in the Database Connection Parameters table in Using the Teradata JDBC Driver. If the Java application does not specify a CHARSET connection parameter, then the default setting is CHARSET=ASCII.
It is strongly recommended that Java applications store character data in Unicode columns in the database, and use the UTF8 session character set (connection parameter CHARSET=UTF8). This avoids conversions between character sets, and ensures end-to-end fidelity of character data.
The Teradata JDBC Driver provides a fixed mapping of Teradata session character sets to Java character sets, shown in Table 3. For a given Teradata session character set, the corresponding Java character set is used to encode bytes sent to the database, and to decode bytes received from the database.
Table 3: Character Set Mapping
Teradata Session Character Set
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Although the CLIENT_CHARSET connection parameter can be used to override the Teradata JDBC Driver's normal mapping of
Teradata session character sets to Java character sets, the CLIENT_CHARSET connection parameter is not intended for use in new Teradata deployments.
It is a legacy support feature intended to assist transition away from the unsupported use of the database in terms of storing non-Latin characters in a Latin column,
and the subsequent unsupported access of those non-ASCII characters using an ASCII session character set.
Data corruption will occur if the wrong Java character set is specified with the CLIENT_CHARSET connection parameter.
Teradata cannot provide any guarantees of data fidelity or quality when the CLIENT_CHARSET connection parameter is used.
Character Mapping Differences
Table 4 shows how certain character sets differ in specific character code points.
- The Java application avoids using characters that cannot be represented in the Java character set corresponding to the Teradata session character set
- When the Java application uses the PreparedStatement.setCharacterStream technique described above, the Java application ensures that the Java character set specified as an argument to the InputStreamReader constructor provides a mapping to Unicode for the non-Unicode characters stored in the file
Table 4: Internal Unicode Mapping
|
|
|
|
|
|
|
Teradata Internal Unicode
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
If the Java application attempts to store WAVE DASH or MINUS SIGN characters in a CHAR or VARCHAR column, the database might not be able to process the request correctly, since there are multiple Unicode character translations for the SJIS, EUC, and EBCDIC code points for the WAVE DASH and MINUS SIGN characters.
The database maps U+FF5E to the following external code points for each session character set. A database map file is required for the following mappings.
- KANJIEBCDIC 0x43A1
- KANJIEUC 0xA1C1
- KANJISJIS 0x8160 1-
- in mapeuc: UNICODE_2_UPC_CS123 0xFF5E 0xA1C1 # FULLWIDTH TILDE 2-
- in mapsjis: UNICODE_2_SJIS_MBC 0xFF5E 0x8160 # FULLWIDTH TILDE 3-
- in mapsosi: UNICODE_2_SOSI 0xFF5E 0x43A1 # FULLWIDTH TILDE
Data Flow From the Teradata JDBC Driver to a Java Application
After executing a query, the Java application typically gets character data from the Teradata JDBC Driver as java.lang.String objects by using one of the following JDBC APIs:
- ResultSet.getString
- ResultSet.getObject for a CHAR or VARCHAR column returns a String object
Alternatively, the Java application can get character data from the Teradata JDBC Driver by using one the following JDBC APIs:
- ResultSet.getCharacterStream–returns a Reader object that provides Unicode characters
- ResultSet.getAsciiStream–returns an InputStream object that can only provide ASCII characters. The Java application should not attempt to use this JDBC API to get non-ASCII character data.
Modifying SQL Statements
Teradata JDBC Driver Release 3.1 and earlier modified SQL statements, replacing all occurrences of ? with IS NULL whenever the application called setNull() for the ? in a where clause.
For example, if the SQL statement was:
SELECT * from table1 where colid = ?
and the application called setNull(1), the Teradata JDBC Driver changed the SQL statement to
SELECT * from table1 where colid is null
This was an incorrect procedure to use, since the SQL statement returned all rows where colid was null. The correct SQL statement is:
SELECT * from table1 where colid = null
Note that this statement never returns any rows because null is never equal to anything including itself.
The problem was fixed in the Teradata JDBC Driver Release 3.2, but the fix might potentially change the output of some applications.
Null Expressions Policy
In SQL Functions, Operators, Expressions, and Predicates, the following policy is stated for the result of a comparison (including the = equality operator) involving null values.
If any expression in a comparison is null, the result of the comparison is unknown.
For a comparison to provide a TRUE result when comparing fields that might result in nulls, the statement must include the IS [NOT] NULL operator.
Correcting the SQL Statements
To modify an SQL statement that is incorrect and stops working with the Release 3.3 driver, use the following information as an example:
If the application uses an SQL statement such as:
SELECT * from table1 where colid = ?
and the application may bind either a null value argument, or bind a non-null value argument to the ? parameter, then the application may have expected the = comparison operator to return true in either case, since the Teradata JDBC Driver Release 3.1 and earlier modified the SQL statement to provide that behavior.
If the application expected that behavior, then change the SQL statement as follows:
SELECT * from table1 where colid = ? or ? is null and colid is null
and change the application to bind the argument twice to both ? parameters.