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

What's New in Teradata JDBC Driver version 17.20

What's New in Teradata JDBC Driver version 17.10

What's New in Teradata JDBC Driver version 17.0

What's New in Teradata JDBC Driver version 16.20

What's New in Teradata JDBC Driver version 16.10

What's New in Teradata JDBC Driver version 16.0

What's New in Teradata JDBC Driver version 15.10

What's New in Teradata JDBC Driver version 15.0

What's New in Teradata JDBC Driver version 14.10

What's New in Teradata JDBC Driver version 14.0

What's New in Teradata JDBC Driver version 13.10

What's New in Teradata JDBC Driver version 13.0

What's New in Teradata JDBC Driver version 12.0

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.

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:

These interfaces are shown Figure 1 and listed in Table 2.

Figure 1: JDBC Interfaces

Table 2: JDBC Interfaces

Interface Name

Description

java.sql.Blob

Provides support for Binary Large Objects (BLOB)

java.sql.Clob

Provides support for Character Large Objects (CLOB)

java.sql.Connection

Represents a connection to a database

java.sql.DatabaseMetaData

Accesses a variety of information for the selected Database

javax.sql.DataSource

Provides support for creating/obtaining database connections

java.sql.DriverManager

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

java.sql.ResultSet

Controls access to the row results of a given statement

java.sql.ResultSetMetaData

Accesses the metadata information for the result set

java.sql.Statement

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:

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:

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:

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:

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

Java Character Set

ASCII

ASCII

UTF8

UTF8

UTF16

UnicodeBigUnmarked

EBCDIC037_0E

Cp037

EBCDIC273_0E

Cp273

EBCDIC277_0E

Cp277

HANGULEBCDIC933_1II

Cp933

HANGULKSC5601_2R4

MS949

KANJIEBCDIC5026_0I

Cp930

KANJIEBCDIC5035_0I

Cp939

KANJIEUC_0U

EUC_JP

KANJISJIS_0S

MS932

LATIN1_0A

ISO8859_1

LATIN1252_0A

Cp1252

LATIN9_0A

ISO8859_15_FDIS

SCHEBCDIC935_2IJ

Cp935

SCHGB2312_1T0

EUC_CN

TCHBIG5_1R0

BIG5

TCHEBCDIC937_3IB

Cp937

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.

Table 4: Internal Unicode Mapping 

Encoding

Character Name

SJIS

MS932

Cp943

Cp943C

Teradata Internal Unicode

REVERSE SOLIDUS

0x5C

U+005C

–

U+005C

–

YEN SIGN

0x5C

–

U+00A5

–

U+00A5

WAVE DASH

0x8160

U+FF5E

U+301C

U+301C

U+301C

DOUBLE VERTICAL LINE

0x8161

U+2225

U+2016

U+2016

U+2016

MINUS SIGN

0x817C

U+FF0D

U+2212

U+2212

U+2212

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.

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:

Alternatively, the Java application can get character data from the Teradata JDBC Driver by using one the following JDBC APIs:

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.