Using the Teradata JDBC Driver

This chapter describes using the Teradata JDBC Driver software in Java programs, and guides you through the process of getting the Teradata JDBC Driver running on site:

• Importing the SQL Package and Loading the Teradata JDBC Driver
• Database Connection Parameters
• COP Discovery
• Proxy Server Support
• Stored Password Protection
• Encryption, Authentication, and Authorization
• Client System Information
• User STARTUP SQL Request
• Teradata Session Reconnect
• Transaction Mode
• Auto-Commit
• SQL Literals and SQL Injection Attacks
• JDBC Escape Clauses
• Query Banding
• Trusted SQL and Query Band Impersonation
• Date, Time, Timestamp Values and Time Zones
• Session DateForm
• Session Time Zone
• Stored Procedure TIME and TIMESTAMP INOUT Parameters
• Multi-Statement Requests
• Database Macros
• Creating User-Defined Functions and External Stored Procedures
• User-Defined Types
• Period Data Types
• ARRAY Data Type
• XML Data Type
• NUMBER Data Type
• Interval Data Types
• JSON Data Type
• DATASET Data Type
• Updatable LOBs
• Row Numbers and Update Counts Exceeding Integer.MAX_VALUE
• Multi-Threading Issues
• Data Dictionary Access by DatabaseMetaData Methods
• Using the Sun JDK 5.0 Implementation of JDBC RowSet Interface
• Generated Keys
• ParameterMetaData and Ambiguous Types
• Java External Stored Procedures
• Updatable Result Sets
• Stored Procedure Dynamic Result Sets
• Special Floating Point Values
• PreparedStatement Batch
• PreparedStatement BatchUpdateException Handling
• JDBC FastLoad
• JDBC FastLoad CSV
• JDBC FastExport
• JDBC Monitor
• Raw Connection

Importing the SQL Package and Loading the Teradata JDBC Driver

This section lists the necessary steps for a standalone Java application to use the Teradata JDBC Driver.

This information does not apply to a J2EE application deployed to an application server environment such as WebSphere or WebLogic. Application servers provide their own mechanisms for defining the classpath, and application servers are responsible for loading the JDBC driver classes.

1. The Teradata JDBC Driver jar file(s) must be downloaded to, or copied to, the development system.
   
   Beginning with Teradata JDBC Driver 16.20.00.11, one jar file is required:
   • terajdbc4.jar
   
   With older versions of the Teradata JDBC Driver, two jar files are required:
   • terajdbc4.jar
   • tdgssconfig.jar
   
   
2. Beginning with Teradata JDBC Driver 16.20.00.11, one jar file must be listed on the CLASSPATH:
   • terajdbc4.jar
   
   With older versions of the Teradata JDBC Driver, two jar files must be listed on the CLASSPATH:
   • terajdbc4.jar
   • tdgssconfig.jar
   
   
3. Place the following line near the top of the Java program:

   import java.sql.*;

4. Use the following statement to load and register the Teradata JDBC Driver:

   Class.forName("com.teradata.jdbc.TeraDriver");

Database Connection Parameters

To access a database from a Java program, use the java.sql.DriverManager.getConnection method to obtain a new java.sql.Connection object from the DriverManager.

The java.sql.DriverManager.getConnection method takes a URL string as an argument. The URL string identifies a database, and the DriverManager uses the URL prefix jdbc:teradata:// to select the Teradata JDBC Driver for the connection.

The following table lists the Teradata JDBC Driver connection URL parameters and values. Beginning with Teradata JDBC Driver 16.00.00.28, the Teradata JDBC Driver validates URL connection parameters and throws SQLException for an invalid parameter name and/or invalid value.

Data Source Interface

When using an application server, the preferred way to obtain a java.sql.Connection is to use the java.sql.DataSource interface.

DataSource allows the encapsulation all of the parameters associated with obtaining a database connection in one object that has a logical name. You can then just ask for a connection to that name (for example, Teradata1) and not have to be aware of the numerous parameters that are required to fulfill this request.

A DataSource must be created before it can be used. This job is normally done by a system administrator. The work involved amounts to setting the parameters associated with a Driver Manager URL into the DataSource object and saving it as a file or network‑addressable resource.

The names of these parameters must be identical to their names used in the URL, including capitalization. For example, if CHARSET is used to set the character set property, then CharSet, CHARSet or CHARSEt would not set it. Additional properties dataSourceName, description, user and password are only available at the DataSource level. Beginning with Teradata JDBC Driver 16.00.00.28, the Teradata JDBC Driver validates Data Source property values and throws SQLException for an invalid property value.

Refer to TeraDataSource Class for additional information.

DataSources are accessed with the Java Naming and Directory Interface (JNDI). For more information, visit http://www.oracle.com/technetwork/java/jndi/

With pooled connections, it is very important that the connection be closed when the user no longer needs it. Otherwise, it is not returned to the connection pool. When using pooled connections, it is advisable to have a finally block after try/catch blocks to ensure that connections are closed.

Session Attributes Warning

The database does not provide a facility to reset a session's attributes. Therefore, the user of a connection pool data source must be aware that any commands that affect session attributes must not be used. Any changes to session attributes continue to be in effect for the next unsuspecting user of that connection.

Session attributes that MUST NOT BE CHANGED include:

• Database (DATABASE command or SET SESSION DATABASE command)
• Collation (SET SESSION COLLATION command)
• Dateform (SET SESSION DATEFORM command)
• Timezone (SET TIME ZONE command)
• Default date format
• Session Query Band (SET QUERY_BAND ... FOR SESSION command, introduced with Teradata Database 12.00.00)

  Note:  The SET QUERY_BAND ... FOR TRANSACTION command is recommended as an alternative to SET QUERY_BAND ... FOR SESSION because SET QUERY_BAND ... FOR TRANSACTION is limited in scope to the current transaction.

COP Discovery

The Teradata JDBC Driver provides Communications Processor (COP) discovery behavior when the COP=ON connection parameter is specified, or when the COP connection parameter is omitted.

A database system can be composed of multiple database nodes. One or more of the database nodes can be configured to run the database Gateway process. Each database node that runs the database Gateway process is termed a Communications Processor, or COP. COP Discovery refers to the procedure of identifying all the available COP hostnames and their IP addresses. COP hostnames can be defined in DNS, or can be defined in the client system's hosts file. Teradata strongly recommends that COP hostnames be defined in DNS, rather than the client system's hosts file. Defining COP hostnames in DNS provides centralized administration, and enables centralized changes to COP hostnames if and when the database is reconfigured.

Beginning with Teradata JDBC Driver 16.00.00.28, the COPLAST connection parameter specifies how COP Discovery determines the last cop hostname. When the COPLAST=OFF connection parameter is specified, or the COPLAST connection parameter is omitted, or COP Discovery is disabled via the COP=OFF connection parameter, then the Teradata JDBC Driver will not perform a DNS lookup for the coplast hostname.

Beginning with Teradata JDBC Driver 16.00.00.28, when the COPLAST=ON connection parameter is specified, and COP Discovery is enabled, then the Teradata JDBC Driver will first perform a DNS lookup for a coplast hostname to obtain the IP address of the last COP hostname before performing COP Discovery. Subsequently, during COP Discovery, the Teradata JDBC Driver will stop searching for COP hostnames when either an unknown COP hostname is encountered, or a COP hostname is encountered whose IP address matches the IP address of the coplast hostname.

• COPLAST=ON can improve performance with DNS that is slow to respond for DNS lookup failures.
• COPLAST=ON is necessary for DNS that never returns a DNS lookup failure.

When performing COP Discovery, the Teradata JDBC Driver starts with cop1, which is appended to the database hostname, and then proceeds with cop2, cop3, ..., copN. The Teradata JDBC Driver supports domain-name qualification for COP Discovery and the coplast hostname. Domain-name qualification is recommended, because it can improve performance by avoiding unnecessary DNS lookups for DNS search suffixes.

The following table illustrates the DNS lookups performed for a hypothetical three-node database system named "whomooz".

The Teradata JDBC Driver supports the definition of multiple IP addresses for COP hostnames and non-COP hostnames. The Teradata JDBC Driver calls the Java API method InetAddress.getAllByName to obtain all the IP addresses defined for each hostname.

For the first connection to a particular database system, the Teradata JDBC Driver generates a random number to index into the list of COPs. For each subsequent connection, the Teradata JDBC Driver increments the saved index until it wraps around to the first position. This behavior provides load distribution across all discovered COPs.

The Teradata JDBC Driver masks connection failures to down COPs, thereby hiding most connection failures from the client application. An exception is thrown to the application only when all the COPs are down for that database. If a COP is down, the next COP in the sequence (including a wrap-around to the first COP) receives extra connections that were originally destined for the down COP. When multiple IP addresses are defined in DNS for a COP, the Teradata JDBC Driver will attempt to connect to each of the COP's IP addresses, and the COP is considered down only when connection attempts fail to all of the COP's IP addresses.

If COP Discovery is turned off, or no COP hostnames are defined in DNS, the Teradata JDBC Driver connects directly to the database hostname provided in the connection URL. This permits load distribution schemes other than the COP Discovery approach. For example, round-robin DNS or a TCP/IP load distribution product can be used. COP Discovery takes precedence over simple database hostname lookup. To use an alternative load distribution scheme, either ensure that no COP hostnames are defined in DNS, or specify the COP=OFF connection parameter.

InetAddress Caching

The JVM caches DNS lookups, and the Teradata JDBC Driver does not maintain its own cache of DNS name resolutions. The administrator can use the standard JVM system properties for cache control as defined in the javadoc for the InetAddress class.

The InetAddress class caches both successful and unsuccessful host name resolutions. The positive caching guards against DNS spoofing attacks, and the negative caching improves performance.

By default, the result of positive host name resolutions are cached forever, because there is no general rule to decide when it is safe to remove cache entries. The result of an unsuccessful host name resolution is cached for a very short period of time (10 seconds) to improve performance.

Under certain circumstances where it can be determined that DNS spoofing attacks are not possible, a Java security property can be set to a different Time-to-live (TTL) value for positive caching. Likewise, a system administrator can configure a different negative caching TTL value when needed. Two Java security properties control the TTL values used for positive and negative host name resolution caching:

indicates the caching policy for successful name lookups from the name service. The value is specified as an integer to indicate the number of seconds to cache the successful lookup.

A value of -1 indicates cache forever.

indicates the caching policy for unsuccessful name lookups from the name service. The value is specified as an integer to indicate the number of seconds to cache the failure for unsuccessful lookups.

A value of 0 indicates never cache.

A value of -1 indicates cache forever.

Proxy Server Support

Proxy server support is available beginning with Teradata JDBC Driver 20.00.00.12.

The following table lists each kind of network connection made by the driver, and indicates the available options for specifying a proxy server.

Highest priority

Connection parameters are available to specify the proxy server for HTTPS connections. These connection parameters have the highest priority in determining proxy server settings for HTTPS connections.

In particular, connection parameters to specify the proxy server have higher priority than Java System proxy server settings.

If connection parameters for proxy server are specified and are applicable to the kind of connection, then the JDBC Driver will use the specified connection parameter proxy server settings for the connection.

Otherwise, if connection parameters for proxy server are not specified or are not applicable to the kind of connection, then the JDBC Driver will use lower-priority proxy server settings for the connection.

For example, PROXY_BYPASS_HOSTS can specify a database COP hostname wildcard to bypass the proxy server for HTTPS connections to the database, while using the proxy server for other HTTPS connections.

Second priority

Java System proxy server settings are second priority, and are lower priority than connection parameter proxy server settings.

For HTTPS connections to database and for HTTPS connections to Identity Provider endpoints:

• Java System property https.proxyHost specifies the proxy server hostname.
• Java System property https.proxyPort specifies the proxy server port number.

For HTTP connections to Identity Provider endpoints and for HTTP connections for CRL and OCSP certificate revocation checking:

• Java System property http.proxyHost specifies the proxy server hostname.
• Java System property http.proxyPort specifies the proxy server port number.

The HTTPS and HTTP Java System proxy server settings can be specified together.

Java System property http.nonProxyHosts optionally specifies a matching pattern for hostnames and addresses to bypass the proxy server identified by the Java System properties https.proxyHost and http.proxyHost. The same property http.nonProxyHosts is used for both HTTPS and HTTP.

• Separate multiple hostnames and addresses with a vertical bar | character. Specify an asterisk * as a wildcard character.
• When this parameter is omitted, the default pattern is localhost|127.*|[::1] which bypasses the proxy server identified by https.proxyHost and/or http.proxyHost for common variations of the loopback address.

For example, http.nonProxyHosts can specify a database COP hostname wildcard to bypass the proxy server for HTTPS connections to the database, while using the proxy server for other kinds of connections.

Third priority

If the Java System property java.net.useSystemProxies=true is specified, without specifying other Java System proxy server settings, then the operating system proxy server settings are used.

The Java System property java.net.useSystemProxies is only available for Windows and macOS.

Java proxy precedence behavior is documented in OpenJDK file src/java.base/share/conf/net.properties: "Note that the system properties that do explicitly set proxies (like http.proxyHost) do take precedence over the system settings even if java.net.useSystemProxies is set to true."

Lowest priority

If Java System proxy server settings are not specified or are not applicable to the kind of connection, then a direct connection is made and no proxy server is used.

Stored Password Protection

Overview

Teradata JDBC Driver Stored Password Protection enables an application to provide a JDBC connection password in encrypted form to the Teradata JDBC Driver, and also enables an application to provide the NEW_PASSWORD connection parameter's value in encrypted form.

Stored Password Protection is available beginning with Teradata JDBC Driver 16.00.00.24.

There are several different ways that an application may specify a password to the Teradata JDBC Driver, all of which may use an encrypted password:

1. A login password specified as the third argument to the DriverManager.getConnection(String,String,String) method.
2. A login password specified as the "password" property to the DriverManager.getConnection(String,Properties) method.
3. A login password specified as the PASSWORD connection URL parameter with the DriverManager.getConnection(String) method.
4. A login password specified within the LOGDATA connection URL parameter with any variant of the DriverManager.getConnection method.
5. A login password specified as the DataSource or ConnectionPoolDataSource password parameter.
6. A login password specified within the DataSource or ConnectionPoolDataSource LOGDATA parameter.
7. A new password specified as the NEW_PASSWORD connection URL parameter with any variant of the DriverManager.getConnection method.
8. A new password specified as the DataSource or ConnectionPoolDataSource NEW_PASSWORD parameter.
9. A new password specified as the SSLTRUSTSTORE_PASSWORD connection URL parameter with any variant of the DriverManager.getConnection method.
10. A new password specified as the DataSource or ConnectionPoolDataSource SSLTRUSTSTORE_PASSWORD parameter.

If the password, however specified, begins with the prefix "ENCRYPTED_PASSWORD(" then the specified password must follow this format:

   ENCRYPTED_PASSWORD(PasswordEncryptionKeyResourceName,EncryptedPasswordResourceName)

The PasswordEncryptionKeyResourceName must be separated from the EncryptedPasswordResourceName by a single comma.

The PasswordEncryptionKeyResourceName specifies the name of a resource that contains the password encryption key and associated information. The EncryptedPasswordResourceName specifies the name of a resource that contains the encrypted password and associated information. The two resources are described below.

When an encrypted password is specified for the PASSWORD, NEW_PASSWORD, SSLTRUSTSTORE_PASSWORD, and/or LOGDATA connection URL parameters, the value must be enclosed in single quotes, to enclose the "ENCRYPTED_PASSWORD(" syntax's comma separator for the resource names, otherwise that comma would be interpreted as a separator for the next connection URL parameter.

Program TJEncryptPassword

TJEncryptPassword.java is a sample program to create encrypted password files for use with Teradata JDBC Driver Stored Password Protection.

This program works in conjunction with Teradata JDBC Driver Stored Password Protection. This program creates the files containing the password encryption key and encrypted password, which can be subsequently specified to the Teradata JDBC Driver via the "ENCRYPTED_PASSWORD(" syntax.

You are not required to use this program to create the files containing the password encryption key and encrypted password. You can develop your own software to create the necessary files. The only requirement is that the files must match the format expected by the Teradata JDBC Driver, which is documented below.

This program encrypts the password and then immediately decrypts the password, in order to verify that the password can be successfully decrypted. This program mimics the implementation of the Teradata JDBC Driver's password decryption, and is intended to openly illustrate its operation and enable scrutiny by the community.

The encrypted password is only as safe as the two files. You are responsible for restricting access to the files containing the password encryption key and encrypted password. If an attacker obtains both files, the password can be decrypted. The operating system file permissions for the two files should be as limited and restrictive as possible, to ensure that only the intended operating system userid has access to the files.

The two files can be kept on separate physical volumes, to reduce the risk that both files might be lost at the same time. If either or both of the files are located on a network volume, then an encrypted wire protocol can be used to access the network volume, such as sshfs, encrypted NFSv4, or encrypted SMB 3.0.

• How to use Stored Password Protection with sshfs
• How to use Stored Password Protection with encrypted NFSv4
• How to use Stored Password Protection with encrypted SMB

This program accepts eight command-line arguments:

Prerequisites for using TJEncryptPassword

Complete instructions for how to download, compile, and run the Teradata JDBC Driver sample programs are available here.

The following list is a brief summary of the necessary steps to prepare for using the TJEncryptPassword program:

• Download and install a supported JDK on your development system.
• Download the Teradata JDBC Driver.
• Download samples.jar
• Extract all the files: jar xvf samples.jar
• Compile: javac TJEncryptPassword.java

Example Commands

The following commands assume that the files extracted from samples.jar, the compiled class files, and the Teradata JDBC Driver jar files are all located in the current directory. The current directory must be specified on the classpath.

The TJEncryptPassword program uses the Teradata JDBC Driver to log on to the specified database using the encrypted password, so the TJEncryptPassword program must have access to the Teradata JDBC Driver jar files on the classpath.

Beginning with Teradata JDBC Driver 16.20.00.11, the classpath must list the current directory and terajdbc4.jar

• On Windows, the classpath must be specified with semicolon separators: java -cp .;terajdbc4.jar
• On all other platforms, the classpath must be specified with colon separators: java -cp .:terajdbc4.jar

With older versions of the Teradata JDBC Driver, the classpath must list the current directory, terajdbc4.jar, and tdgssconfig.jar

• On Windows, the classpath must be specified with semicolon separators: java -cp .;terajdbc4.jar;tdgssconfig.jar
• On all other platforms, the classpath must be specified with colon separators: java -cp .:terajdbc4.jar:tdgssconfig.jar

The following example command illustrates running the TJEncryptPassword program on Windows, using a 256-bit AES key available with the JCE Unlimited Strength Jurisdiction Policy Files, and using the HmacSHA256 algorithm available beginning with JDK 5.

The following example command illustrates running the TJEncryptPassword program on Windows, using the default AES key size due to lacking the JCE Unlimited Strength Jurisdiction Policy Files, and using the HmacSHA256 algorithm available beginning with JDK 5.

The following example command illustrates running the TJEncryptPassword program on Windows, using the HmacSHA1 algorithm available with JDK 1.4.2.

Password Encryption Key File Format

You are not required to use the TJEncryptPassword program to create the files containing the password encryption key and encrypted password. You can develop your own software to create the necessary files, but the files must match the format expected by the Teradata JDBC Driver.

The password encryption key file is a text file in Java Properties file format, using the ISO 8859-1 character encoding.

The file must contain the following string properties:

Encrypted Password File Format

The encrypted password file is a text file in Java Properties file format, using the ISO 8859-1 character encoding.

The file must contain the following string properties:

Transformation, Key Size, and MAC

A transformation is a string that describes the set of operations to be performed on the given input, to produce transformed output.

A transformation always includes the name of a cryptographic algorithm such as DES or AES, and may optionally be followed by a feedback mode and padding scheme.

The JDK 7 javadoc for javax.crypto.Cipher indicates that every Java implementation must support the following transformations:

Teradata JDBC Driver Stored Password Protection uses a symmetric encryption algorithm such as DES or AES, in which the same secret key is used for encryption and decryption of the password. Teradata JDBC Driver Stored Password Protection does not use an asymmetric encryption algorithm such as RSA, with separate public and private keys.

• ECB (Electronic Codebook) is the simplest block cipher encryption mode. The input is divided into blocks, and each block is encrypted separately. ECB is unsuitable for encrypting data whose total byte count exceeds the algorithm's block size, and is therefore unsuitable for use with Teradata JDBC Driver Stored Password Protection.
• CBC (Cipher Block Chaining) is a more complex block cipher encryption mode. With CBC, each ciphertext block is dependent on all plaintext blocks processed up to that point. CBC is suitable for encrypting data whose total byte count exceeds the algorithm's block size, and is therefore suitable for use with Teradata JDBC Driver Stored Password Protection.

Teradata JDBC Driver Stored Password Protection hides the password length in the encrypted password file by extending the length of the UTF8-encoded password with trailing null bytes. The length is extended to the next 512-byte boundary.

• A block cipher with no padding, such as AES/CBC/NoPadding, may only be used to encrypt data whose byte count after extension is a multiple of the algorithm's block size. The 512-byte boundary is compatible with many block ciphers. AES, for example, has a block size of 128 bits (16 bytes), and is therefore compatible with the 512-byte boundary.
• A block cipher with padding, such as AES/CBC/PKCS5Padding, can be used to encrypt data of any length. However, CBC with padding is vulnerable to a "padding oracle attack", so Teradata JDBC Driver Stored Password Protection performs Encrypt-then-MAC for protection from a padding oracle attack. MAC algorithm HmacSHA256 is available with JDK 5 and later. MAC algorithm HmacSHA1 is available with JDK 1.4.2.
• A block cipher can encrypt data in units smaller than the cipher's actual block size when using a mode such as CFB (Cipher Feedback) or OFB (Output Feedback). A block cipher can be used as a byte-oriented cipher by specifying an 8-bit mode such as CFB8 or OFB8, so a transformation such as AES/CFB8/NoPadding can be used to encrypt data of any length.

The strength of the encryption depends on your choice of cipher algorithm and key size.

• AES uses a 128-bit (16 byte), 192-bit (24 byte), or 256-bit (32 byte) key. Specify 128, 192, or 256 as the keysize argument for the KeyGenerator.init method. To use AES with a 192-bit or 256-bit key, the Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files must be downloaded from Oracle and installed in your JRE.
• DESede uses a 192-bit (24 byte) key that is effectively either a 112-bit or 168-bit key. Specify 112 or 168 as the keysize argument for the KeyGenerator.init method.

Resource Names

The TJEncryptPassword program has command-line arguments PasswordEncryptionKeyFileName and EncryptedPasswordFileName to specify filenames.

In contrast, the Teradata JDBC Driver's "ENCRYPTED_PASSWORD(" syntax uses resource names, rather than filenames, in order to offer more flexibility for file storage location and access.

   ENCRYPTED_PASSWORD(PasswordEncryptionKeyResourceName,EncryptedPasswordResourceName)

Files created by the TJEncryptPassword program are subsequently accessed as resources by the Teradata JDBC Driver. The resource names include a prefix to indicate how the resource must be accessed. If the resource name begins with the "classpath:" prefix, then the Teradata JDBC Driver loads the resource from the classpath. If you specify a resource name with the "classpath:" prefix, then you must ensure the resource is available on the classpath for the Teradata JDBC Driver.

For security, classpath resources are required to have specific resource name prefixes. The PasswordEncryptionKeyResourceName must begin with "PassKey" and the EncryptedPasswordResourceName must begin with "EncPass".

Example:

   ENCRYPTED_PASSWORD(classpath:PassKeyJohnDoe.properties,classpath:EncPassJohnDoe.properties)

If the resource name begins with a prefix other than "classpath:", then the Teradata JDBC Driver loads the resource via the new URL(resourcename).openStream() method. Non-classpath resources are not required to have specific resource name prefixes. You must ensure that non-classpath resources are accessible by the Teradata JDBC Driver.

The resource name can begin with the "file:" prefix and specify a relative pathname for the Teradata JDBC Driver to load the resource from a relative-pathname file.

Example with files in current directory:

   ENCRYPTED_PASSWORD(file:JohnDoeKey.properties,file:JohnDoePass.properties)

Example with relative paths:

   ENCRYPTED_PASSWORD(file:../dir1/JohnDoeKey.properties,file:../dir2/JohnDoePass.properties)

The resource name can begin with the "file:" prefix and specify an absolute pathname for the Teradata JDBC Driver to load the resource from an absolute-pathname file.

Example with absolute paths on Windows:

   ENCRYPTED_PASSWORD(file:c:/dir1/JohnDoeKey.properties,file:c:/dir2/JohnDoePass.properties)

Example with absolute paths on Linux:

   ENCRYPTED_PASSWORD(file:/dir1/JohnDoeKey.properties,file:/dir2/JohnDoePass.properties)

Teradata JDBC Driver Actions

The two resource names specified for an encrypted password must be accessible to the Teradata JDBC Driver and must conform to the properties file formats described above. The Teradata JDBC Driver throws SQLException if the resource name begins with the "classpath:" prefix, but the resource is not available on the classpath. The Teradata JDBC Driver will also throw SQLException if a non-classpath resource is not accessible. The Teradata JDBC Driver throws SQLException if the resources do not conform to the required properties file formats.

The Teradata JDBC Driver verifies that the match values in the two resources are present, and match each other. The Teradata JDBC Driver throws SQLException if the match values differ from each other. The match values are compared to ensure that the two specified resources are related to each other, serving as a "sanity check" to help avoid configuration errors. The TJEncryptPassword program uses a timestamp as a shared match value, but a timestamp is not required. Any shared string can serve as a match value. The timestamp is not related in any way to the encryption of the password, and the timestamp cannot be used to decrypt the password.

Before decryption, the Teradata JDBC Driver calculates the MAC using the ciphertext, transformation name, and algorithm parameters if any, and verifies that the calculated MAC matches the expected MAC. The Teradata JDBC Driver throws SQLException if the calculated MAC differs from the expected MAC, to indicate that either or both of the resources may have been tampered with.

For a logon password, the Teradata JDBC Driver uses the decrypted password string to log on to the database. For a new password, the Teradata JDBC Driver uses the decrypted password string with the MODIFY USER command to update an expired password.

Encryption, Authentication, and Authorization

Table 14 describes URL and data source parameters for authentication and encryption. Descriptions of methods that allow the getting and setting of the parameters are listed in TeraDataSource Class.

Please refer to the Teradata Vantage™ Security Administration reference, chapter "Network Security Policy", section "Configuring a Confidentiality QOP Policy" for how to configure an LDAP directory to require message traffic encryption for particular users, for non-HTTPS connections.

In that same chapter, please refer to section "Requiring Confidentiality" for how to use the gtwcontrol command to require message traffic encryption for all database users, for non-HTTPS connections.

Logon Mechanisms

The following table describes the logon mechanisms selected by the LOGMECH connection parameter.

Meeting Kerberos Prerequisites

Prior to operating with Kerberos, the system administrator must perform the following prerequisites:

• Set up a Kerberos Domain and Realm
• Define users within the Active Directory
• Verify Kerberos client support
• Ensure that each system has a krb5.ini or krb5.conf file
• Ensure that each user can run the kinit program
• Verify that the login configuration file is correct
• Windows Registry setting
• Specify the Java VM option
• Enable Kerberos SSO

Set up a Kerberos Domain and Realm

A Kerberos domain and realm must be set up for the machines that are to be in the domain. A system administrator must perform this work. See Security Administration.

Define Users within Active Directory

All Kerberos users must be defined within the Active Directory in the Windows domain. Kerberos usernames are case-sensitive. It is important that users log on to the their machines exactly as they are defined within Active Directory. Although a user defined in an Active Directory as DR818999 could successfully log onto the domain as dr818999 (notice the case change), for Kerberos to work, the user must log in to the domain as DR818999.

Note:  Ensure that the definition of the user within the Active Directory has the "Do not require Kerberos preauthentication" checked.

Verify Kerberos client support

Kerberos authentication is supported on the following client systems.

• Windows client systems
• Linux client systems beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.0
• AIX, HP-UX, MacOS, and Solaris client systems beginning with Teradata Database 16.10 and Teradata JDBC Driver 16.0

Refer to the Teradata Vantage™ Security Administration reference for information about configuring supported client systems to use an Active Directory or Linux Kerberos Key Distribution Center (KDC).

Ensure that each system has a krb5.ini or krb5.conf file

The essential Kerberos configuration information is specified in the krb5.ini file on Windows or the krb5.conf file on other platforms. Java searches for krb5.ini or krb5.conf in the following order.

• If the Java system property java.security.krb5.conf is set, that property value specifies the path and file name.
• If the Java system property java.security.krb5.conf is not set, then Java looks for krb5.ini or krb5.conf in the JRE's lib/security directory.
• Finally, Java looks for c:\winnt\krb5.ini on Windows, /etc/krb5/krb5.conf on Solaris, or /etc/krb5.conf on other platforms.

The krb5.ini file on Windows is the equivalent of the krb5.conf file that is the standard for both MIT and Heimdal Kerberos. The details of the various settings can be found in the MIT Kerberos documentation.

The following file is an example and must be modified to reflect your actual domain, realm, and Key Distribution Center (KDC).

[libdefaults]
ticket_lifetime = 6000
default_realm = ESROOTDOM.ESDEV.TDAT
clockskew = 13000
checksum_type=2
[realms]
ESROOTDOM.ESDEV.TDAT = {
    kdc = esroot.esrootdom.esdev.tdat:88
    default_domain = esrootdom
}
[domain_realm]
esrootdom = {
    .esrootdom = ESROOTDOM.ESDEV.TDAT
    esrootdom = ESROOTDOM.ESDEV.TDAT
}

For a Linux client, add the following lines to the krb5.conf file under the [libdefaults] entry:

    default_tkt_enctypes = arcfour-hmac rc4-hmac
    default_tgs_enctypes = arcfour-hmac rc4-hmac

Naming Conventions for Realm Names and Hostnames

DNS domain names and hostnames are case-insensitive and, by convention, are lowercase.

In contrast, Kerberos realm names are uppercase and are case-sensitive. The Kerberos realm name is the uppercase version of the domain name.

Kerberos authentication will not work unless the Kerberos realm name is specified in uppercase in the krb5.ini or krb5.conf file.

Run kinit

For SSO, users must be able to run the kinit program, which obtains and caches a Kerberos Ticket-Granting-Ticket. This program can be found in the jre/bin directory of the Java JDK. An example follows:

C:\j2sdk1.4.2_04\jre\bin>kinit
Password for DR818999@ESROOTDOM.ESDEV.TDAT:Mypassword
New ticket is stored in cache file C:\Documents and Settings\DR818999\krb5cc_DR818999

Credential Delegation and Teradata Unity Director

When using Kerberos and Teradata Unity Director, Credential Delegation must be enabled. To use Credential Delegation, obtain a Ticket-Granting-Ticket that is forwardable. This is done using the forwardable option of kinit, for example:

kinit -f

This option must be used in order for Teradata Unity Director to route authentication requests to the database.

Set the forwardable=true option in the krb5.ini (Windows) or krb5.conf (Linux) file under the [libdefaults] section. The previous krb5.ini or krb5.conf file modified to use credential delegation is similar to:

[libdefaults]
forwardable = true
ticket_lifetime = 6000
default_realm = ESROOTDOM.ESDEV.TDAT
clockskew = 13000
checksum_type=2
[realms]
ESROOTDOM.ESDEV.TDAT = {
    kdc = esroot.esrootdom.esdev.tdat:88
    default_domain = esrootdom
}
[domain_realm]
esrootdom = {
    .esrootdom = ESROOTDOM.ESDEV.TDAT
    esrootdom = ESROOTDOM.ESDEV.TDAT
}

Verify Login Configuration Information

Kerberos authentication requires the following Login Configuration file, which can be stored in a directory that you choose.

com.sun.security.jgss.initiate
{
  com.sun.security.auth.module.Krb5LoginModule sufficient useTicketCache=true;
};
other
{
  com.sun.security.auth.module.Krb5LoginModule required;
};

You can specify the Login Configuration filename with the java.security.auth.login.config Java system property.

For example, if the Login Configuration file is named TeraJDBC.config and is located in the current directory, specify the following JVM command-line option.

-Djava.security.auth.login.config=TeraJDBC.config

Alternatively, the Login Configuration file can be specified as a system-wide setting. Edit the java.security file located in the JRE's lib/security directory, and add a login.config.url.N property.

Examine the java.security file to determine whether you have any existing login.config.url.N properties. You must choose a value for N that does not conflict with any of your existing properties, and the values of N must be consecutively numbered.

The following example shows a single login.config.url.N value named login.config.url.1. In this example, the configuration file is located at C:\dmr\TeraJDBC.config.

login.config.url.1=file:C:/dmr/TeraJDBC.config

Note that the property value URL must be specified with forward slashes. On Windows, substitute backslashes with forward slashes.

Windows Registry setting

The normal configuration for Microsoft Windows does not permit the export of a session key for a Kerberos Ticket-Granting Ticket (TGT). In order to use Kerberos SSO, you must change a Windows registry setting to enable the export of a session key for a Kerberos TGT.

  HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters
  Value Name: AllowTgtSessionKey
  Value Type: REG_DWORD
  Value:      0x01  (default is 0)


When an Active Directory account belongs to the local Administrators group on the client PC, Windows will not export a session key for a Kerberos TGT, even when the AllowTgtSessionKey registry value is set to 0x1. Therefore, it is not possible to use Kerberos SSO for an account that belongs to the local Administrators group on the client PC.

Specify the JVM Option

To enable SSO, the JVM option must be supplied:

-Djavax.security.auth.useSubjectCredsOnly=false

Enable Kerberos SSO

To enable the Kerberos SSO logon, the user defined in the database must have the same password as the user's logon password and support SSO. To support SSO, the DDL statement

grant logon with null password

is required.

For example, to provide the needed permission for user dr818999, use the following grant logon:

grant logon on all to dr818999 with null password;

More detail on this statement can be found in the Teradata Vantage™ SQL Data Definition Language reference.

Server-Side Default Authentication Mechanism

The Teradata Security Administrator can change the default authentication mechanism from TD2 to a different mechanism on the server side (Teradata Database). However, the Administrator must be aware that certain mechanisms are only supported on certain platforms. For example, with Teradata Database 14.0 and earlier, Kerberos support is limited to Windows clients only.

Extra preparation is needed when changing the server-side default mechanism to a mechanism that is not supported on all the client platforms in use at the site. Before changing the server-side default mechanism, all Java applications on client platforms that don't support the planned server-side default mechanism must be configured to explicitly specify a supported authentication mechanism using the LOGMECH= connection parameter.

For example, consider a site using Teradata Database 14.0 or earlier with Java applications deployed to both Windows and Linux clients. If the Administrator decides to change the server-side default mechanism from TD2 to Kerberos, the Administrator must first verify that the Java applications deployed on Linux specify the LOGMECH=TD2 connection parameter.

Client System Information

When the Teradata JDBC Driver establishes a connection to the database, the Teradata JDBC Driver transmits information about the client system and client software to the database. The database records this information in Data Dictionary system tables, to enable analysis of client system demographics by database administrators. The following sections describe the Client Attributes feature and the LogonSource column.

Client Attributes

Beginning with Teradata Database 14.0 and Teradata JDBC Driver 13.10.00.21, the Client Attributes feature records a variety of information about the client system and client software in the system tables DBC.SessionTbl and DBC.EventLog. The Client Attributes feature is intended to be a replacement for the information recorded in the LogonSource column of the system tables DBC.SessionTbl and DBC.EventLog.

The Client Attributes are recorded at session logon time. Subsequently, the system views DBC.SessionInfoV and DBC.LogOnOffV can be queried to obtain information about the client system and client software on a per-session basis. Client Attribute values may be recorded in the database in either mixed-case or in uppercase, depending on the session character set and other factors. Analysis of recorded Client Attributes must flexibly accommodate either mixed-case or uppercase values.

LogonSource Column

Beginning with Teradata Database 14.0, the LogonSource column is considered obsolete and has been superseded by the Client Attributes feature. The LogonSource column may be deprecated and subsequently removed in future releases of the database.

When the Teradata JDBC Driver establishes a connection to the database, the Teradata JDBC Driver composes a string value that is stored in the LogonSource column of the system tables DBC.SessionTbl and DBC.EventLog. The LogonSource column is included in system views such as DBC.SessionInfoV and DBC.LogOnOffV. All LogonSource values provided by Teradata JDBC Driver and other clients are recorded in the database in uppercase.

The Teradata JDBC Driver follows the format documented in the Teradata Data Dictionary, section "System Views Columns Reference", for network-attached LogonSource values. Network-attached LogonSource values have eight fields, separated by whitespace. The database composes fields 1 through 3; the Teradata JDBC Driver composes fields 4 through 8.

1. The literal "(TCP/IP)", to indicate the connection type
2. TCP port number on the client system, in hexadecimal
3. IP address of the client system
4. Database hostname, known as the Teradata Directory Program Identifier "TDPID"
5. Client process/thread identifier
6. Client system user ID
7. Program used on the client system
8. The literal "01 LSS", to indicate the LogonSource string version 01

Fields 4 through 8 are described in detail in the following sections.

All client information refers to the system running JVM containing the Teradata JDBC Driver. Typically, this is an application server. Client information does not refer to any other clients, such as a web browser, that may be communicating with the application server.

Example LogonSource value

                                                                                                   1         1         1

         1         2         3         4         5         6         7         8         9         0         1         2

12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678

<--set by Teradata Database--><-------------------------------------------set by client---------------------------------------->

(TCP/IP) 11AB 153.64.135.140  CHARON;CHARONCOP1/192.168.16.144:1025     CID=C2A132   ROOTUSER JDBC03.02.00.00;1.4.2_01   01 LSS

(TCP/IP) 11AB 153.64.135.140  CHARON.SD.TERADATA.COM;CHARONCOP1.SD.TERA CID=C2A132   ROOTUSER JDBC03.02.00.00;1.4.2_01   01 LSS

----------------------------- ----------------------------------------- ------------ -------- -------------------------- ------

                                       1         2         3         4           1                     1         2

                              12345678901234567890123456789012345678901 123456789012 12345678 12345678901234567890123456 123456

                                       Truncated to the space            Will be     Trunc'ed    Trunc'ed to 26 chars    Always

                                  remaining in the 97 chars, after       12 chars   to 20 chars      (may be less)       6 chars

                                 the subsequent fields are composed      or less   (may be less)

Fields are separated from each other by exactly one space character.

Field 4 - TDPID (target database hostname) Field

The TDPID field is composed of:

• Original database hostname specified by the application (without a COPnnn suffix). For example, CHARON.
• followed by a semicolon: ";"
• followed by the COP-suffixed hostname and/or IP address of the database node. For example, CHARONCOP1/192.168.16.144
• followed by a colon: ":"
• followed by the destination port number of the TCP connection to the database node. For example, 1025.

This TPID field is truncated to the space remaining in the 97 chars, after all the other fields are composed.

An example value for this field when an application specifies the database hostname "CHARON" is: CHARON;CHARONCOP1/192.168.16.144:1025

An example truncated value for this field when an application specifies a fully-qualified database hostname of "CHARON.SD.TERADATA.COM" is: CHARON.SD.TERADATA.COM;CHARONCOP1.SD.TERA

Field 5 - Client Process ID/Thread ID Field

Note:  The Teradata JDBC Driver does not provide the Java Thread ID for this field.

In an application server environment, threads are not tied to particular database connections, so any particular thread can execute requests on a connection originally created by a different thread.

To avoid potential confusion, the Teradata JDBC Driver provides a connection ID for field 5 containing the LogonSource value. The connection ID is also provided in exception and log messages, which enable connection ID values to be correlated between LogonSource values and exception and log messages. The Teradata JDBC Driver always prefixes connection ID values with the prefix "cid=", to make it easy to distinguish connection ID values from other values.

The connection ID is the hash code of a private object used by the connection. It provides a simple unique identifier for a particular connection to the database.

The Client Process/Thread ID field is composed of:

• CID=
• followed by the Connection ID

An example value for this field is: CID=1E51060

Field 6 - Client Process User Field

The Client Process User field is composed of System.getProperty("user.name")

This field is truncated to 20 chars, but can be shorter.

An example value for this field is: ROOTUSER

Field 7 - Client Program Name Field

The Client Program Name field is composed of:

• JDBC
• followed by the JDBC driver version, for example, 03.02.00.00
• followed by a semicolon ";"
• then the System.getProperty("java.version"), for example, 1.4.2_04

This field is truncated to 26 chars, but can be shorter.

An example value for this field is: JDBC03.02.00.00;1.4.2_04

Field 8 - LogonSource string version 1

This field is composed of 01 LSS.

User STARTUP SQL Request

CREATE USER and MODIFY USER commands provide STARTUP clauses for specifying certain initial session settings. In addition, the Teradata JDBC Driver provides connection parameters and corresponding DataSource properties for specifying certain initial session settings.

Some session settings are specified only by executing an SQL DDL session command. The following table lists many of the session settings, and indicates which initial settings are specified with a CREATE/MODIFY USER clause or a JDBC Connection parameter. The complete list of SET SESSION commands is available in the reference Teradata Vantage™ SQL Data Definition Language Syntax and Examples.

Unicode Pass Through support is available beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.08.

The user's STARTUP SQL request can be used to establish initial session settings that cannot be set with a CREATE/MODIFY USER clause or a JDBC Connection parameter. For example, the following command sets a STARTUP SQL request for user "susan" to change the transaction isolation to read-uncommitted.

The Teradata JDBC Driver RUNSTARTUP=ON connection parameter must be specified to execute the user's STARTUP SQL request after logon. The default behavior is RUNSTARTUP=OFF. If the RUNSTARTUP connection parameter is omitted, then the user's STARTUP SQL request will not be executed.

Teradata Session Reconnect

A Java application uses a JDBC java.sql.Connection object to interact with a database session. The JDBC connection can be disconnected from the database session in various ways outside the control of the Teradata JDBC Driver, such as:

• a network cable being unplugged
• a network communication failure
• an administrator terminating the session with the Monitor partition command Abort Session
• an administrator terminating the session with the Gateway command Kill Session
• a database restart

Teradata Session Reconnect is available beginning with Teradata JDBC Driver 13.10.00.24. When Teradata Session Reconnect is enabled, the Teradata JDBC Driver will attempt to reconnect the JDBC connection to the database session after a communication failure. Teradata Session Reconnect is enabled when one or more of the following conditions is satisfied:

• Recoverable Network Protocol is in effect
• and/or connection parameter RECONNECT_COUNT=number is specified (if omitted, the default is 11 attempts)
• and/or connection parameter RECONNECT_INTERVAL=seconds is specified (if omitted, the default is 30 seconds)

The maximum possible elapsed time for reconnect attempts is:

    (ReconnectCount - 1) * ReconnectInterval

Beginning with Teradata Database 14.10 and Teradata JDBC Driver 15.00.00.12, Teradata Session Reconnect can be augmented with the Recoverable Network Protocol so that reconnection is supported for a variety of failure events, including transient network failures. Prior to Teradata Database 14.10 and Teradata JDBC Driver 15.00.00.12, Teradata Session Reconnect only supports reconnection after a database restart; it does not support reconnection after other events, such as transient network failures.

Recoverable Network Protocol is enabled or disabled through a combination of database and Teradata JDBC Driver configuration parameters; specifically, the database dbscontrol fields RedriveProtection (67), RedriveDefaultParticipation (68), and DisableRecoverableNetProtocol (77), and the Teradata JDBC Driver connection parameter REDRIVE with level 2 or higher.

When Teradata Session Reconnect is disabled, and a communication failure occurs, the operation in progress fails, the Teradata JDBC Driver closes the JDBC connection and throws a SQLException with SQLState 08S01.

When Teradata Session Reconnect is enabled, but Recoverable Network Protocol is not in effect, and a communication failure occurs, the operation in progress fails, the Teradata JDBC Driver attempts to reconnect and throws a SQLException with one of the following error codes to indicate the outcome of the reconnect attempt:

• 1278 - Reconnected to the Teradata Database after a communication failure.
• 1279 - Reconnected to the Teradata Database after a communication failure. A failure occurred after reconnecting to the Teradata Database.
• 1280 - Unable to reconnect to the Teradata Database.

Reconnect is never attempted if a communication failure occurs while a JDBC connection is being closed.

The database enforces a limited time period for reconnecting to a session after a database restart. The amount of time is set using the database utility program "gtwcontrol". The standard value is 20 minutes. The database will reject all reconnect attempts after the time period expires.

When Teradata Session Reconnect is enabled, but Recoverable Network Protocol is not in effect, a significant part of each session's state is discarded when a database restart occurs.

• The current transaction is rolled back.
• All open result sets are discarded.
• All open BLOB and CLOB values are discarded.
• All volatile tables are discarded.
• All materialized global temp tables are discarded.

Applications that use Teradata Session Reconnect without Recoverable Network Protocol must be designed to accommodate the possible loss of session state at any point in time. Teradata Session Reconnect without Recoverable Network Protocol is not recommended for use with JDBC connection pools.

When Teradata Session Reconnect, Recoverable Network Protocol, and automatic Redrive of SQL requests are all in effect, and a communication failure occurs, the operation in progress fails, but is redriven automatically, the session's state is preserved, the Teradata JDBC Driver attempts to reconnect and does not throw an exception if the reconnect was successful.

Transaction Mode

The TMODE connection parameter enables an application to specify the transaction mode for the connection.

• TMODE=ANSI provides American National Standards Institute (ANSI) transaction semantics. This mode is recommended.
• TMODE=TERA provides legacy Teradata transaction semantics. This mode is only recommended for legacy applications that require Teradata transaction semantics.
• TMODE=DEFAULT provides the default transaction mode configured for the database, which may be either ANSI or TERA mode. TMODE=DEFAULT is the default when the TMODE connection parameter is omitted.

While ANSI mode is generally recommended, please note that every application is different, and some applications may need to use TERA mode. The following differences between ANSI and TERA mode might affect a typical user or application:

1. Silent truncation of inserted data occurs in TERA mode, but not ANSI mode. In ANSI mode, the database returns an error instead of truncating data.
2. Tables created in ANSI mode are MULTISET by default. Tables created in TERA mode are SET tables by default.
3. For tables created in ANSI mode, character columns are CASESPECIFIC by default. For tables created in TERA mode, character columns are NOT CASESPECIFIC by default.
4. In ANSI mode, character literals are CASESPECIFIC. In TERA mode, character literals are NOT CASESPECIFIC.

The last two behavior differences, taken together, may cause character data comparisons (such as in WHERE clause conditions) to be case-insensitive in TERA mode, but case-sensitive in ANSI mode. This, in turn, can produce different query results in ANSI mode versus TERA mode. Comparing two NOT CASESPECIFIC expressions is case-insensitive regardless of mode, and comparing a CASESPECIFIC expression to another expression of any kind is case-sensitive regardless of mode. You may explicitly CAST an expression to be CASESPECIFIC or NOT CASESPECIFIC to obtain the character data comparison required by your application.

The reference Teradata Vantage™ SQL Request and Transaction Processing recommends that ANSI mode be used for all new applications. The primary benefit of using ANSI mode is that inadvertent data truncation is avoided. In contrast, when using TERA mode, silent data truncation can occur when data is inserted, because silent data truncation is a feature of TERA mode.

A drawback of using ANSI mode is that you can only call stored procedures that were created using ANSI mode, and you cannot call stored procedures that were created using TERA mode. It may not be possible to switch over to ANSI mode exclusively, because you may have some legacy applications that require TERA mode to work properly. You can work around this drawback by creating your stored procedures twice, in two different users/databases, once using ANSI mode, and once using TERA mode.

Refer to Teradata Vantage™ SQL Request and Transaction Processing for complete information regarding the differences between ANSI and TERA transaction modes.

Auto-Commit

Auto-commit is a basic feature of the JDBC API Specification, and the Teradata JDBC Driver appropriately implements auto-commit on and off functionality for both ANSI and TERA mode.

When a connection is first established, it begins with the default JDBC auto-commit setting, which is on (true). When auto-commit is on, the JDBC Driver is solely responsible for managing transactions, and the JDBC Driver commits each SQL request that is successfully executed. An application should not execute any transaction management SQL commands when auto-commit is on. An application should not call the Connection.commit method or the Connection.rollback method when auto-commit is on.

An application can manage transactions itself by calling the Connection.setAutoCommit method with a false argument to turn off auto-commit. When auto-commit is off, the JDBC Driver leaves the current transaction open after each SQL request is executed, and the application is responsible for committing or rolling back the transaction by calling the Connection.commit or the Connection.rollback method, respectively.

Best practices recommend that an application avoid executing database-vendor-specific transaction management commands such as BT, ET, ABORT, COMMIT, or ROLLBACK, because such commands differ from one vendor to another. (They even differ between Teradata's two modes ANSI and TERA.) Instead, best practices recommend that an application only call the standard JDBC API methods Connection.setAutoCommit, Connection.commit and Connection.rollback for transaction management.

1. When auto-commit is on in ANSI mode, the Teradata JDBC Driver automatically executes COMMIT WORK after every successful SQL request.
2. When auto-commit is off in ANSI mode, the Teradata JDBC Driver does not automatically execute COMMIT WORK. When the application calls the Connection.commit method, then the Teradata JDBC Driver executes COMMIT WORK.
3. When auto-commit is on in TERA mode, the Teradata JDBC Driver does not execute BT or ET, unless the application explicitly executes BT or ET commands itself, which is not recommended.
4. When auto-commit is off in TERA mode, the Teradata JDBC Driver executes BT before submitting the application's first SQL request of a new transaction. When the application calls the Connection.commit method, then the Teradata JDBC Driver executes ET until the transaction is complete.

As part of the wire protocol between the database and Teradata client interface software (such as the Teradata JDBC Driver), each message transmitted from the database to the client has a bit designated to indicate whether the session has a transaction in progress or not. Thus, the client interface software is kept informed as to whether the session has a transaction in progress or not.

In TERA mode with auto-commit off, when the application uses the Teradata JDBC Driver to execute a SQL request, if the session does not have a transaction in progress, then the Teradata JDBC Driver automatically executes BT before executing the application's SQL request. Subsequently, in TERA mode with auto-commit off, when the application uses the Teradata JDBC Driver to execute another SQL request, and the session already has a transaction in progress, then the Teradata JDBC Driver has no need to execute BT before executing the application's SQL request.

In TERA mode, BT and ET pairs can be nested, and the database keeps track of the nesting level. The outermost BT/ET pair defines the transaction scope; inner BT/ET pairs have no effect on the transaction because the database does not provide actual transaction nesting. To commit the transaction, ET commands must be repeatedly executed until the nesting is unwound. The Teradata wire protocol bit (mentioned earlier) indicates when the nesting is unwound and the transaction is complete. When the application calls the Connection.commit method in TERA mode, the Teradata JDBC Driver repeatedly executes ET commands until the nesting is unwound and the transaction is complete.

In rare cases, an application may not follow best practices and may explicitly execute transaction management commands. Such an application must turn off auto-commit before executing transaction management commands such as BT, ET, ABORT, COMMIT, or ROLLBACK. The application is responsible for executing the appropriate commands for the transaction mode in effect. TERA mode commands are BT, ET, and ABORT. ANSI mode commands are COMMIT and ROLLBACK. An application must take special care when opening a transaction in TERA mode with auto-commit off. In TERA mode with auto-commit off, when the application executes a SQL request, if the session does not have a transaction in progress, then the Teradata JDBC Driver automatically executes BT before executing the application's SQL request. Therefore, the application should not begin a transaction by executing BT.

// TERA mode example showing undesirable BT/ET nesting

con.setAutoCommit(false);

stmt.execute("BT"); // BT automatically executed by the JDBC Driver before this, and produces a nested BT

stmt.execute("insert into mytable1 values(1, 2)");

stmt.execute("insert into mytable2 values(3, 4)");

stmt.execute("ET"); // unwind nesting

stmt.execute("ET"); // complete transaction

// TERA mode example showing how to avoid BT/ET nesting

con.setAutoCommit(false);

stmt.execute("insert into mytable1 values(1, 2)"); // BT automatically executed by the JDBC Driver before this

stmt.execute("insert into mytable2 values(3, 4)");

stmt.execute("ET"); // complete transaction

Please note that neither previous example shows best practices. Best practices recommend that an application only call the standard JDBC API methods Connection.setAutoCommit, Connection.commit and Connection.rollback for transaction management.

// Example showing best practice

con.setAutoCommit(false);

stmt.execute("insert into mytable1 values(1, 2)");

stmt.execute("insert into mytable2 values(3, 4)");

con.commit();

SQL Literals and SQL Injection Attacks

Applications that compose SQL requests containing SQL string literals must take care to properly escape single-quote characters. An SQL string literal is enclosed by single-quotes, for example: 'New York'. To use a single-quote character in an SQL string literal, the single-quote character must be repeated, for example: 'Joe"s Diner'.

Applications that compose SQL requests based on user input, such that the user input is directly substituted into the SQL text string, may be vulnerable to an SQL Injection attack.

For example, an application prompts the user to enter a person's last name, and then composes a query to search for all people with that last name:

In this example, notice that the hardcoded part of the SQL text includes the single‑quote characters to be used on each side of the SQL string literal. This technique works only if the user's input value never includes any single quotes.

However, if the user specifies "O'Malley" as a last name, then the application will erroneously compose the following query, which is then rejected by the database as a syntax error:

An SQL Injection attack takes advantage of this kind of defect in the application code to do something malicious. For example, the malicious input value would be:

which would cause the application to compose the SQL text string:

In this example, the malicious user carefully chose a value that works with the hardcoded single‑quote characters on each side of the input value. The database would successfully execute the SQL string and, assuming that the malicious user had write-access to the important table, would delete all the rows from the important table, with a result being a denial of service.

Applications must be coded to handle single-quote characters, and to correctly compose SQL requests.

The most common recommendation to protect against SQL Injection attack is to use prepared statements so that users cannot modify the SQL text; the user's input values are only used as bind parameter values for prepared statements.

If it is not possible for the application to use prepared statements, such as for composing Data Definition Language (DDL) commands, then another common recommendation is to validate and escape the user's input values. For example:

• If the user's input is a number, then the application must verify that the user's input value consists of numeric digits only, before the user's input value is concatenated into the SQL text string
• If the user's input value is used inside an SQL string literal, then the user's input value must be scanned for any single-quote characters, and each single-quote character must be repeated, before the user's input value is concatenated into the SQL text string. On the database side, the database unescapes each pair of single-quote characters in the SQL string literal, to be one single-quote character.

JDBC Escape Clauses

When JDBC escape clause processing is enabled, the Teradata JDBC Driver looks for any escape syntax and translates it into native SQL syntax for the database. This makes escape syntax independent of any database.

The default for JDBC escape clause processing is ENABLED. Escape clause processing can be disabled for the Classes Statement and RowSet by calling the methods:

• Statement.setEscapeProcessing(false)
• RowSet.setEscapeProcessing(false)

The following is the generic syntax for escape clauses:

Teradata JDBC Driver supports the following types of escape clauses:

• Date, time, and timestamp literals
• Scalar functions
• LIKE predicate escape characters
• Outer joins
• Calls to stored procedures

Date and Time Literals

Scalar Functions

The following tables list the JDBC Escape Syntax scalar functions that are supported by the Teradata JDBC Driver.

Conversion Functions

Escape clauses for data type conversion use the following syntax:

where SQLtype is one of the data types listed in the following table.

LIKE Predicate Escape Characters

Teradata JDBC Driver supports ESCAPE characters for LIKE SQL statements with the following syntax:

This escape clause specifies the escape character so wildcards such as %, _ can be interpreted literally in an SQL LIKE statement.

Outer Joins

The Teradata JDBC Driver supports escape clauses for outer joins. The following is the syntax for an outer join:

In this language rule, outer-join structure is:

table {LEFT|RIGHT|FULL} OUTER JOIN {table | outer-join}

ON search-condition

Calls to Stored Procedures

The escape clause syntax for a call to a stored procedure is the following:

The following are true in Escape clauses:

• A missing parenthesis () is only added when calling a stored procedure with a JDBC escape clause
• It is invalid to execute a macro inside of a JDBC escape clause

Effective with Teradata Tools and Utilities 08.01.00 and the Teradata JDBC Driver Release 03.03.00, parentheses are no longer added to an SQL CALL statement that doesn't use JDBC escape syntax. This behavior change allows an application's SQL statements to be passed to the database unmodified when JDBC escape syntax is not used.

It is strongly recommended that applications always use standard vendor-independent JDBC Escape Syntax to call stored procedures. When JDBC Escape Syntax is used to call a stored procedure, as in:

then the Teradata JDBC Driver modifies the SQL statement as needed to satisfy the database's precise syntax requirements. JDBC Escape Syntax functionality for calling stored procedures has not changed with this release of the Teradata JDBC Driver.

Applications that inadvertently relied on the behavior of previous Teradata JDBC Driver releases to modify SQL CALL statements that did not use JDBC Escape Syntax may need to be modified either:

• To use standard JDBC Escape Syntax for calling a stored procedure (recommended)
• To provide the empty parentheses after the stored procedure name

Connection Functions

The following table lists the JDBC Escape Syntax functions that are intended for use with the Connection.nativeSQL method. These functions provide information about the connection, or control the behavior of the connection. Functions that provide information return locally-cached information and avoid a round-trip to the database.

Request-Scope Functions

The following table lists the JDBC Escape Syntax functions that are intended for use with the Connection.createStatement, Connection.prepareStatement, or Connection.prepareCall method. These functions control the behavior of the corresponding Statement, PreparedStatement, or CallableStatement, and are limited in scope to the particular SQL request in which they are specified.

Query Banding

Teradata Database 12.0 introduced Query Banding. A query band is a set of name-value pairs that can be set on a session or a transaction to identify an SQL request's originating source.

Why is Query Banding Needed?

• Many J2EE applications that access a database make use of connection pooling mechanisms that consolidate the activity of several end-users onto database connections using the same database userID. Especially with a multi-tiered application, there may be no straightforward way on the database tier to identify the end-user, application, and so forth, that originated the SQL request.
• J2EE applications may desire to identify or group together all the SQL requests submitted on behalf of a particular HTTP request for rendering a web page
• For charge back, accounting, troubleshooting, and other types of system management, administrators need to know what end-user, application, report, or part of an application issued a request

Use Case Scenario

In a web-based Java application, connection pools exist on the web servers. All the connections in the pools are for the same Teradata user; for example, appuser. This is commonly known as a robot user. The application uses browser cookies to identify the end-users who are using the web browsers.

Every time the user clicks a link on a web page, the user's web browser sends a request to the web server and transmits the browser cookie as part of the request. The web server invokes the application to process the request. The application does something; for example, submits a query to Teradata. Immediately before submitting the query to Teradata, the application first submits the following statements:

PreparedStatement pstmt = conn.prepareStatement("Set QUERY_BAND=? FOR TRANSACTION");

pstmt.setString (1, "custIdFromCookie=46734832");

pstmt.executeUpdate ();

Then, the application can submit its query:

After the query returns a result set, the application outputs an HTML page to the web browser.

Use Case Scenario

• A web service, implemented in Java, responds to an HTTP/SOAP request
• A web service uses the Java Transaction API (JTA) to begin a User-Managed Transaction
• A web service obtains a JDBC connection from a connection pool managed by the application server
• A web service invokes multiple Enterprise Java Beans (EJB) to do some useful work. For example, the first EJB inserts a row into Table A and the second EJB updates some rows in Table B. All calls to the EJBs participate in the same transaction and therefore are identified with the same query band.
• A web service commits the transaction. The transaction's query band value is cleared automatically, so any subsequent SQL requests submitted on the pooled connections have a different query band value.

Syntax

The query band is passed to the database as a list of name=value pairs in a string. The application defines both the names and the values. A query band can be set for the transaction and/or for the session. Beginning with Teradata Database 15.10, a default query band can be set for a profile.

• SET QUERY_BAND FOR TRANSACTION is a DML command. The query band may be specified with a SQL string literal enclosed within single quotes or with a question-mark parameter marker. It is strongly recommended to specify the query band using a question-mark parameter marker and the PreparedStatement.setString method.
• SET QUERY_BAND FOR SESSION is a DDL command. The query band must be specified with a SQL string literal enclosed within single quotes. A question-mark parameter marker is not permitted.
• CREATE PROFILE AS QUERY_BAND and MODIFY PROFILE AS QUERY_BAND are DDL commands. The query band must be specified with a SQL string literal enclosed within single quotes. A question-mark parameter marker is not permitted. These commands are available beginning with Teradata Database 15.10.

For more information on these SQL commands, refer to the Teradata Vantage™ SQL Data Definition Language reference.

The query band syntax rules are as follows:

• Each name-value pair, including the last one, must terminate with a semicolon
• Each query band name must be 1-128 characters in length
• Each query band value must be 0-256 characters in length
• Query band names and values cannot contain the following characters: equal sign, semicolon, or null character (\u0000)
• Each query band name must be specified only once in a query band string. (If the query band name is specified more than once in a query band string, then the database returns an error.)
• The maximum length of the entire query band string is limited to 2048 characters. All characters, including white-space characters, are counted when determining the length. White-space characters are permitted at (and removed from) the beginning and ending of the query band string, before and after each equal sign, and before and after each semicolon.

Example

To set a query band value in a transaction using an SQL string literal with a non‑PreparedStatement:

stmt = conn.createStatement();

stmt.executeUpdate("SET QUERY_BAND='Org=Finance; report=EndOfYear; universe=west;' FOR TRANSACTION");

Example

To set a query band value in a transaction using a PreparedStatement:

pstmt=conn.prepareStatement("SET QUERY_BAND=? FOR TRANSACTION");

pstmt.setString (1, "Org = Finance; report = EndOfYear; universe=west;);

pstmt.executeUpdate ();

Example

To clear a query band value in a transaction:

stmt = conn.CreateStatement();

stmt.executeUpdate("SET QUERY_BAND = NONE for TRANSACTION");

Retrieving Query Band Values

Beginning with Teradata JDBC Driver 14.00.00.33, applications running in a JDK 6.0 or later environment can retrieve query band values with the following methods:

• Properties java.sql.Connection.getClientInfo()
• String java.sql.Connection.getClientInfo(String name)

The first method returns a Properties object containing all active query band name/value pairs. The second method returns the query band value for the specified name.

If the same query band name is active for multiple contexts, only one of the corresponding query band values will be returned by these methods. Transaction query band values take precedence over session query band values, which take precedence over profile query band values. Profile query band values are supported beginning with Teradata Database 15.10 and Teradata JDBC Driver 15.00.00.23.

Recommended Query Band Names

The following are standard Client Info property names defined by the JDBC 4.0 specification:

• ApplicationName–the name of the application currently utilizing the connection
• ClientUser–the name of the user for which the application using the connection is performing work. (This might not be the same as the user name that was used in establishing the connection.)
• ClientHostname–the hostname of the computer on which the application using the connection is running

For applications that need to use query band name-value pairs that correspond to the standard Client Info properties, Teradata recommends using standard Client Info property names as QueryBand names.

An application is not limited to using only standard Client Info properties. For QueryBand name-value pairs that do not correspond to the standard Client Info properties, the application is free to use any legal query band name that is supported by the database.

Uses for the Query Band

• The query band is logged as a field in the Database Query Log (DBQL) detail logging table. DBQL reports can be created using the query band name-value pairs to provide additional refinement for accounting and resource allocation purposes. For more information about DBQL, refer to Teradata Vantage™ - Database Administration.
• The query band name-value pairs can be associated with Teradata workload management filter and throttle rules. For directory-based connections and applications that utilize a web-based application server that all connect to Teradata with a single logon, the originating source of requests can be distinguished using the query band to permit resource control using the workload management rules.
• The query band name-value pairs can be defined in workload classification criteria. This enables requests all coming from a single logon to be classified into different workloads based on the query band set by the originating application. It also enables an application to set different priorities for different requests. For example, a GUI application may have dialogs that require quick responses and other dialogs that submit long-running reports that run in the background. The application can set a different QueryBand for each type of job, causing the requests to be classified into different workloads and thus running at different priorities.

Trusted SQL and Query Band Impersonation

Beginning with Teradata Database 13.10, Trusted Sessions Enhanced Security prevents the use of the SET QUERY_BAND SQL to set or remove the current proxy user. This is accomplished by categorizing all SQL requests as trusted or untrusted.

Applications that compose all their own SQL requests and trust all their own SQL requests, do not use this feature. This feature is used by applications working with both trusted and untrusted SQL requests. An example of an untrusted SQL request is a SQL request obtained from a user. An untrusted SQL request might contain an SQL Injection attempt by a malicious user.

Applications working with both trusted and untrusted SQL requests use this feature by following this process:

• The DBA uses the WITH TRUST_ONLY option for all GRANT CONNECT THROUGH grants done for the application.
• The application uses Teradata JDBC Driver connections with the TRUSTED_SQL=ON connection parameter.
• All points throughout the application code that process user-input SQL are identified.
• The application is changed to prepend the {fn teradata_untrusted} escape function to all user-input SQL text before the application passes the SQL request to the Teradata JDBC Driver.

This escape function downgrades an SQL request from trusted to untrusted. No mechanism is provided to upgrade an SQL request from untrusted to trusted because it might be exploited by an SQL Injection attack.

Date, Time, Timestamp Values and Time Zones

java.sql.Date, Time, and Timestamp Objects

The following table describes the behavior of the valueOf and toString methods of the java.sql.Date, Time, and Timestamp classes.

The Teradata JDBC Driver's PreparedStatement and CallableStatement setter methods setDate, setTime, and setTimestamp send to the Teradata Database the java.sql.Date, Time, or Timestamp value that matches what the java.sql.Date, Time, or Timestamp object's toString method would print at the time that the setter method is called, meaning the value is relative to the JVM default timezone at the time that the setter method is called.

Therefore, the application should ensure that the JVM default timezone in effect when the java.sql.Date, Time, or Timestamp object is created, is also in effect when application calls setDate, setTime, or setTimestamp.

Sending Date Values

Because the Teradata Database does not provide a DATE WITH TIME ZONE data type, it is recommended that applications call setDate without Calendar only, and avoid calling setDate with Calendar.

Sending Time Values

Because Time values do not have an associated date, Daylight Savings Time is not applicable for Time values, and Daylight Savings Time is ignored for the Calendar argument's timezone. The TIME ZONE field is sent to the database as the time zone's raw offset from UTC, and is not affected by Daylight Savings Time.

Sending Timestamp Values

Daylight Savings Time is not used for Timestamp values. The database stores TIMESTAMP WITH TIME ZONE values as a raw offset from UTC. For consistency with the database, the Teradata JDBC Driver ignores Daylight Savings Time for the Calendar argument's timezone. The TIME ZONE field is sent to the database as the time zone's raw offset from UTC, and is not affected by Daylight Savings Time.

An application that requires TIMESTAMP WITH TIME ZONE values to reflect Daylight Savings Time must specify a Calendar argument using a custom timezone.

For example, to indicate Eastern Standard Time, an application may specify a Calendar argument as follows:

prepstmt.setTimestamp(index, timestamp,
  Calendar.getInstance(TimeZone.getTimeZone("GMT-05:00")));

To indicate Eastern Daylight Time, an application may specify a Calendar argument as follows:

prepstmt.setTimestamp(index, timestamp,
  Calendar.getInstance(TimeZone.getTimeZone("GMT-04:00")));

Using setObject to Send Time With Time Zone and Timestamp With Time Zone Values

Beginning with Teradata JDBC Driver 14.10.00.26, an application can use the PreparedStatement or CallableStatement setObject method to bind a Struct value to a question-mark parameter marker as a TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE data type. Prior to Teradata JDBC Driver 14.10.00.26, the setTime(Time, Calendar) method was required to bind a TIME WITH TIME ZONE value, and the setTimestamp(Timestamp, Calendar) method was required to bind a TIMESTAMP WITH TIME ZONE value. Those methods continue to work as before. Applications only need to use the new functionality for situations that the setTime/setTimestamp methods do not support; in particular, binding TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE attribute values of UDTs and Period Data Types.

The application must compose each data value as a java.sql.Struct value with two attributes. The first attribute of the Struct must be a java.sql.Time or a java.sql.Timestamp value. The second attribute of the Struct must be a Calendar value whose TimeZone attribute specifies the desired time zone. A Struct composed in this way is treated the same as the setTime/setTimestamp methods with Calendar argument. Refer the text above for a description of the behavior of the setTime/setTimestamp methods with Calendar argument.

The application must provide a class that implements the java.sql.Struct interface.

public class MyStruct implements java.sql.Struct

{

  private String m_typeName ;

  private Object [] m_attributes ;

  public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }

  public String getSQLTypeName() { return m_typeName ; }

  public Object [] getAttributes() { return m_attributes ; }

  public Object [] getAttributes(java.util.Map map) { return m_attributes ; }

}

The application uses instances of that class to compose TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE values.

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT+08:00")) ;

Time time = Time.valueOf( ...

Timestamp ts = Timestamp.valueOf( ...

// Assuming a table with two columns: TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE

PreparedStatement ps = con.prepareStatement ("INSERT INTO MyTable VALUES(?,?)") ;

ps.setObject(1, new MyStruct("TIME WITH TIME ZONE", new Object [] {time, cal})) ;

ps.setObject(2, new MyStruct("TIMESTAMP WITH TIME ZONE", new Object [] {ts, cal})) ;

Receiving DATE, TIME, and TIMESTAMP Values

Beginning with Teradata JDBC Driver 13.00.00.09, the ResultSet and CallableStatement interfaces' getDate, getTime, and getTimestamp methods with Calendar argument will modify the Calendar argument's TimeZone attribute to provide the time zone portion of TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE data values received from the database.

Also beginning with Teradata JDBC Driver 13.00.00.09, the ResultSet and CallableStatement interfaces' getDate, getTime, and getTimestamp methods provide implicit data type conversions for DATE, TIME, and TIMESTAMP data values received from the database.

While database TIME values may contain up to 6 digits of fractional seconds, java.sql.Time values are limited to milliseconds precision - only 3 digits of fractional seconds. An application can work around the limitation of java.sql.Time values by retrieving database TIME values using the getTimestamp method, which will provide an implicit data type conversion from TIME to java.sql.Timestamp, and will preserve all the digits of the TIME value's fractional seconds.

The following table describes the behavior of the ResultSet and CallableStatement interfaces' getDate, getTime, and getTimestamp methods. Avoid combinations designated as (Not intended use) in order to ensure proper round-trip exchange of data values with the database.

Session DateForm

The session's current DateForm primarily dictates how DATE values are transmitted from the database to the Teradata JDBC Driver. The session's current DateForm also dictates how the database performs implicit conversions from a PreparedStatement/CallableStatement setString value to a destination DATE column or parameter.

ANSIDate is the recommended DateForm. When the session's current DateForm is ANSIDate, then the database transmits DATE values to the Teradata JDBC Driver as 10-character values in the form "YYYY-MM-DD", and the database rejects non-Y2K-compliant implicit conversions from a PreparedStatement setString value to a destination DATE column or parameter. (Teradata Database error 2666 is returned in this situation.)

The IntegerDate DateForm is provided for legacy applications. When the session's current DateForm is IntegerDate, then the database transmits DATE values to the Teradata JDBC Driver as 4-byte binary values, and the database accepts non-Y2K-compliant implicit conversions from a PreparedStatement setString value to a destination DATE column or parameter. (The database uses 19 as the century digits for the year in this situation.)

It is possible to specify a DateForm attribute for a Teradata Database user with the CREATE USER command or the MODIFY USER command. When a session is first established, the session's current DateForm defaults to the user's DateForm, if available; or to the system default defined by the DATEFORM parameter in the DBSControl record.

The HELP SESSION command shows the session's current DateForm.

The session's current DateForm can be changed by executing the SQL commands SET SESSION DATEFORM=ANSIDATE or SET SESSION DATEFORM=INTEGERDATE. It is not recommended for a Java application to execute the SET SESSION DATEFORM commands using a pooled connection within an application server.

Receiving DATE Values from the Teradata Database

With TTU 8.1 and earlier releases of the Teradata JDBC Driver, less accurate information was provided for DATE values when the session's current DateForm is ANSIDate. For a DATE value received from the database, the ResultSetMetadata getColumnType method returned java.sql.Types.CHAR, the ResultSetMetadata getColumn ClassName method returned java.lang.String, and the ResultSet getObject method returned a String value.

Beginning with TTU 8.2 Teradata JDBC Driver 3.4, more accurate information is provided for DATE values when the session's current DateForm is ANSIDate. For a DATE value received from the database, the ResultSetMetadata getColumnType method returns java.sql.Types.DATE, the ResultSetMetadata getColumnClassName method returns java.sql.Date, and the ResultSet getObject method returns a java.sql.Date value.

Sending DATE Values to the Teradata Database

Beginning with Teradata JDBC Driver 12.0 and Teradata Database 12.0, the Teradata JDBC Driver sends java.sql.Date values to the database as DATE values using the ANSIDate DateForm. This provides Y2K-compliant implicit conversion for java.sql.Date values that are specified with the PreparedStatement/CallableStatement setDate or setObject methods, and sent to destination CHAR/VARCHAR columns and parameters.

A legacy application requiring non-Y2K-compliant behavior can use Teradata-specific Escape Syntax functions introduced in Teradata JDBC Driver 12.0. The DateForm in effect at the time that an application calls the PreparedStatement/CallableStatement setDate or setObject method is the DateForm that is used for the corresponding DATE value sent to the database.

// DateForm=IntegerDate provides non-Y2K-compliant implicit

// conversions from java.sql.Date to CHAR/VARCHAR

connection.nativeSQL("{fn teradata_useintegerdate}");

// DateForm=ANSIDate provides Y2K-compliant implicit conversion 

// from java.sql.Date to CHAR/VARCHAR (the default behavior)

connection.nativeSQL("{fn teradata_useansidate}");

Teradata Database releases 12.0 through 12.0.1.1 only accept DATE values whose DateForm matches the session's current DateForm. When the session's current DateForm is:

• IntegerDate and a DATE value with ANSIDate DateForm is sent to the database, then the database returns error 2665 ("Invalid date")
• ANSIDate and a DATE value with IntegerDate DateForm is sent to the database, then the database returns error 3610 ("Internal error: Please do not resubmit the last request. SubCode, CrashCode")

This restriction was removed beginning with Teradata Database 12.0.1.2.

Session Time Zone

All TIME and TIMESTAMP data is associated with time zones explicitly or implicitly. The user's default time zone is in effect initially when a connection (session) is established with the database. If no default time zone is defined for the user, then the system default time zone is in effect initially for a connection. The connection's time zone can be changed by the SET TIME ZONE statement. For more information, see SET TIME ZONE in SQL Data Definition Language.

The PreparedStatement interface defines setTime and setTimestamp methods both with and without a Calendar argument:

• When an application uses the PreparedStatement setTime and setTimestamp methods without a Calendar argument, then the database implicitly associates the connection's time zone with the TIME or TIMESTAMP value;
• When an application uses the PreparedStatement setTime and setTimestamp methods with a Calendar argument, then the database explicitly associates the specified Calendar's time zone with the TIME or TIMESTAMP value.

TIME and TIMESTAMP table columns and stored procedure parameters can be declared with or without a time zone field: TIME, TIME WITH TIME ZONE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE:

• TIME and TIMESTAMP table columns and stored procedure parameters declared without a time zone field contain values relative to UTC;
• TIME and TIMESTAMP table columns and stored procedure parameters declared with a time zone field contain values relative to the value's time zone.

Table 12 illustrates how the database uses the combination of the input data value and its implicitly or explicitly associated time zone, and whether the destination is declared with or without a time zone field, to determine the resulting data value that is stored in a TIME or TIMESTAMP destination.

Stored Procedure TIME and TIMESTAMP INOUT Parameters

The database forces the output value for an INOUT parameter, as set by a stored procedure, to conform exactly to the same data type attributes as the bound input value for the INOUT parameter.

When calling a stored procedure having INOUT parameters of type TIME WITH TIME ZONE and or TIMESTAMP WITH TIME ZONE, the application must bind values using the CallableStatement methods setTime or setTimestamp, respectively, with a Calendar argument. If the Calendar argument is not used, the database returns error 3996 (Right truncation of string data). Also, the setNull method cannot be used to bind a NULL value to an INOUT parameter of type TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE. You must instead specify a null data value, using the setTime or setTimestamp method with a Calendar argument.

The database does not currently provide or permit implicit or explicit data type conversions for TIME and TIMESTAMP values that reduce the number of fractional digits of the value.

The Teradata JDBC Driver connection parameters TNANO and TSNANO exist to work around this database limitation. The TNANO and TSNANO connection parameters are awkward to use, however, because they force all TIME data values to have the same number of fractional digits (as specified by TNANO), and or force all TIMESTAMP data values to the same data type attributes as the bound input value for the INOUT parameter.

This limitation particularly affects stored procedure TIME and TIMESTAMP INOUT parameters, because of how the database forces the output value for an INOUT parameter, as set by a stored procedure, to conform exactly to the same data type attributes as the bound input value for the INOUT parameter.

• It is not possible to bind an input TIME or TIMESTAMP value to a stored procedure INOUT parameter when the input value has a larger number of fractional digits than used to declare the INOUT parameter. The Teradata Database will return error 5404 (Datetime field overflow) in this situation.
• It is possible to bind an input TIME or TIMESTAMP value to a stored procedure INOUT parameter when the input value has a smaller number of fractional digits than used to declare the INOUT parameter. Unfortunately, however, the stored procedure is restricted from returning an output value with more fractional digits than the input value, because the database returns error 5404 (Datetime field overflow). The stored procedure might not be able to return an output value with all the fractional digits used to declare the INOUT parameter.
• The net effect is that an application is effectively required to bind input TIME and TIMESTAMP values for stored procedure INOUT parameters with exactly the same number of fractional digits used to declare the INOUT parameters. Because the TNANO and TSNANO connection parameters force all data values to have the same number of fractional digits, an application might not be able to call stored procedures declared with INOUT parameters having differing numbers of fractional digits.

For example, the following stored procedure might encounter this problem:

Regarding NULL TIME and TIMESTAMP values bound to PreparedStatement or CallableStatement parameters, when the TNANO or TSNANO connection parameter is not specified, the Teradata JDBC Driver has default behavior that assumes that each NULL TIME or TIMESTAMP value, respectively, has zero fractional digits. This default behavior works with all possible INSERT and UPDATE destination TIME and TIMESTAMP columns, regardless of the number of fractional digits used in declaration. This default behavior does not work with stored procedure INOUT parameters declared with more than zero fractional digits.

Until the database is enhanced, applications that call stored procedures with TIME and TIMESTAMP INOUT parameters must follow these guidelines:

1. The TNANO and TSNANO connection parameters must be specified
2. The stored procedure TIME and TIMESTAMP INOUT parameters' fractional digits must all be declared to be the same and they must match the TNANO and TSNANO connection parameters, respectively.

Multi-Statement Requests

Multi-statement requests can be performed by executing one SQL statement consisting of multiple SQL commands, separated by semicolons. Multi-statement requests can be executed using Statement.execute() method. The application can retrieve the result using Statement.getResultSet() or Statement.getUpdateCount() methods and in case multiple results are returned, then the application must use Statement.getMoreResults() method to iterate through these results.

Database Macros

A database macro consists of one or more SQL statements. Macros can be executed using Statement.execute() method. The application can retrieve the result using Statement.getResultSet() or Statement.getUpdateCount() methods and in case multiple results are returned, then the application must use Statement.getMoreResults() method to iterate through these results.

Creating User-Defined Functions and External Stored Procedures

User-Defined Functions

Use the CREATE/REPLACE FUNCTION statement to create a User-Defined Function (UDF); it always returns a result set. The Statement.executeQuery() or Statement.execute() methods are used to execute this statement.

For more information regarding UDFs, refer to User-Defined Functions in SQL External Routine Programming.

External Stored Procedures

Use the CREATE/REPLACE PROCEDURE statement to create an External Stored Procedure (XSP). This can be executed using the Statement.execute() or Statement.executeUpdate() methods. The Statement.getWarnings() method can be used to retrieve the SQLWarning returned by the Statement object.

For more information regarding XSPs, refer to External Stored Procedures in SQL External Routine Programming.

For more information on Java XSPs, refer to the section in this chapter, Java External Stored Procedures.

Source File Locations for JDBC

The Teradata JDBC Driver can create UDFs, UDMs, or XSPs from source files that are stored on the server or the client. Source files stored on the client must be transferred from the client node to the server node.

For security purposes, the Teradata JDBC Driver uses the classpath to load all resources. This requires the source file on the client to be on the classpath. Once the classpath is set, the Teradata JDBC Driver can transfer the source file to the server node.

User-Defined Types

Creating User-Defined Types (UDTs)

Creating a UDT using Teradata JDBC Driver is done in a similar manner to creating other kinds of database objects. A Java application can call the Statement execute or executeUpdate method to issue the appropriate CREATE command.

The CREATE/REPLACE TYPE command is used to create a UDT. After the CREATE/REPLACE TYPE command is executed by the database, the output messages from the command can be obtained by the application as a chain of SQLWarning objects from the Statement getWarnings method.

The CREATE TYPE command must be followed by CREATE METHOD, CREATE FUNCTION, CREATE TRANSFORM, and CREATE ORDERING commands as needed to fully create the type.

When creating UDMs and UDFs, if the "CREATE METHOD" or "CREATE FUNCTION" statements indicate that the source file containing the method definition is located on the client, then the source file must be available as a resource on the classpath. Teradata JDBC Driver automatically loads the resource from classpath and transfers it to the database.

For more information on these SQL commands, refer to the Teradata Vantage™ SQL Data Definition Language reference.

Transferring UDT Values To and From the Database

Teradata JDBC Driver supports the following functionality for UDTs.

• UDT values can be composed using UDT constructors and UDT mutator methods.
• PreparedStatement parameter markers can correspond to individual UDT attribute values that represent arguments to UDT constructors and UDT mutator methods.
• Beginning with Teradata Database 13.10 and Teradata JDBC Driver 13.00.00.18, PreparedStatement parameter markers can correspond to entire UDT values using java.sql.Struct values. (With prior software versions, PreparedStatement parameter markers cannot correspond to entire UDT values using java.sql.Struct values.)
• Beginning with Teradata Database 13.10 and Teradata JDBC Driver 13.10.00.07, JDBC custom type mapping is supported, so PreparedStatement parameter markers can correspond to instances of an application class that implements java.sql.SQLData. (With prior software versions, custom type mapping is not supported.)
• Using the SELECT statement's ".ALL" syntax, UDT attribute values can be retrieved as ResultSet column values
• Beginning with Teradata Database 13.10 and Teradata JDBC Driver 13.00.00.18, UDT values can be retrieved from ResultSet columns as java.sql.Struct values. (With prior software versions, retrieving UDT values from ResultSet columns is not supported.)
• Beginning with Teradata Database 13.10 and Teradata JDBC Driver 13.10.00.07, JDBC custom type mapping is supported, so UDT values can be retrieved from ResultSet columns as instances of an application class according to the custom type mapping associated with the UDT. (With prior software versions, custom type mapping is not supported.)
• Beginning with Teradata Database 13.10 and Teradata JDBC Driver 13.00.00.18, output UDT values from stored procedure INOUT or OUT parameters can be obtained as java.sql.Struct values. (With prior software versions, retrieving output UDT values from stored procedure INOUT or OUT parameters is not supported.)
• Beginning with Teradata Database 13.10 and Teradata JDBC Driver 13.10.00.07, output UDT values from stored procedure INOUT or OUT parameters can be obtained as application class instances according to the custom mapping associated with the UDT. (With prior software versions, custom type mapping is not supported.)
• User-Defined Methods (UDM) can be created using source files that are client resources available on the classpath, or using source files located on the database server

UDT Metadata

UDT metadata is available from DatabaseMetaData, ResultSetMetaData, and ParameterMetaData methods.

UDT Limitations

The Geospatial data types cannot be used with java.sql.Struct.

The ResultSetMetaData getColumnDisplaySize method returns zero for Structured UDT column values.

When a UDT attribute is a LOB data type, the application cannot set the LOB attribute value using an InputStream. The application must set the LOB attribute value using a Blob or Clob value obtained from the getBlob or getClob method, respectively.

A Struct object created by the Connection createStruct method is not subject to JDBC custom type mapping. Its getAttributes method will return the attribute values specified at the time of creation.

Custom type mapping is not supported for stored procedure INOUT parameters that are DISTINCT UDTs.

Period Data Types

Beginning with Teradata Database 13.10 and Teradata JDBC Driver 13.00.00.18, Period data types can be used with java.sql.Struct.

• PERIOD(DATE)
• PERIOD(TIME)
• PERIOD(TIME WITH TIME ZONE)
• PERIOD(TIMESTAMP)
• PERIOD(TIMESTAMP WITH TIME ZONE)

The application must compose each Period data value as a java.sql.Struct value with two attributes. The first attribute corresponds to the start of the period, and the second attribute corresponds to the end of the period. For more information regarding Period data types, refer to Period Data Types in the Teradata Vantage™ Data Types and Literals reference.

• The application must compose each PERIOD(DATE) value as a java.sql.Struct value with two java.sql.Date attributes.
• The application must compose each PERIOD(TIME) value as a java.sql.Struct value with two java.sql.Time attributes.
• Beginning with Teradata JDBC Driver 14.10.00.26, the application may compose each PERIOD(TIME WITH TIME ZONE) value as a java.sql.Struct value with two java.sql.Struct attributes. Each of the two nested java.sql.Struct values must have two attributes: a java.sql.Time value, and a Calendar value whose TimeZone attribute specifies the desired time zone. Prior to Teradata JDBC Driver 14.10.00.26, time zone components are not supported within PERIOD values, and the application must compose each PERIOD(TIME WITH TIME ZONE) value as a java.sql.Struct value with two java.sql.Time attributes.
• The application must compose each PERIOD(TIMESTAMP) value as a java.sql.Struct value with two java.sql.Timestamp attributes.
• Beginning with Teradata JDBC Driver 14.10.00.26, the application may compose each PERIOD(TIMESTAMP WITH TIME ZONE) value as a java.sql.Struct value with two java.sql.Struct attributes. Each of the two nested java.sql.Struct values must have two attributes: a java.sql.Timestamp value, and a Calendar value whose TimeZone attribute specifies the desired time zone. Prior to Teradata JDBC Driver 14.10.00.26, time zone components are not supported within PERIOD values, and the application must compose each PERIOD(TIMESTAMP WITH TIME ZONE) value as a java.sql.Struct value with two java.sql.Timestamp attributes.

Example of Sending PERIOD(TIME WITH TIME ZONE) values

The application must provide a class that implements the java.sql.Struct interface.

public class MyStruct implements java.sql.Struct

{

  private String m_typeName ;

  private Object [] m_attributes ;

  public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }

  public String getSQLTypeName() { return m_typeName ; }

  public Object [] getAttributes() { return m_attributes ; }

  public Object [] getAttributes(java.util.Map map) { return m_attributes ; }

}

The application uses instances of that class to compose PERIOD(TIME WITH TIME ZONE) values.

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT+08:00")) ;

Time time1 = Time.valueOf( ...

Time time2 = Time.valueOf( ...

// Available beginning with Teradata JDBC Driver 14.10.00.26

Struct timetz1 = new MyStruct("TIME WITH TIME ZONE", new Object [] {time1, cal}) ;

Struct timetz2 = new MyStruct("TIME WITH TIME ZONE", new Object [] {time2, cal}) ;

// Assuming a table with two columns: INTEGER and PERIOD(TIME WITH TIME ZONE)

PreparedStatement ps = con.prepareStatement ("INSERT INTO MyTable VALUES(?,?)") ;

ps.setInt(1, id) ;

ps.setObject(2, new MyStruct("PERIOD(TIME WITH TIME ZONE)", new Object [] {timetz1, timetz2})) ;

ARRAY Data Type

Beginning with Teradata Database 14.0 and Teradata JDBC Driver 14.00.00.04, the java.sql.Array interface and the SQL ARRAY data type are supported. A Teradata SQL ARRAY is defined with one or more dimensions, and is used to store many values of the same data type sequentially or in a matrix-like format.

Array Rules and Limitations

An application can use the Connection createArrayOf method to compose a java.sql.Array value to send to the database. The createArrayOf method's Object[] argument can be a one-dimensional Object[] array or a multi-dimensional Object[][][]... array, and the array must follow these rules.

• Only innermost arrays are permitted to contain nulls.
• All non-null data values within innermost arrays must be the same data type.
• All non-innermost arrays of a multi-dimensional array must contain solely non-null Object[] values.
• The outermost array can range in length from zero to the maximum defined for the outermost dimension.
• A one-dimensional array is considered to be both the outermost array (so it can range in length from zero to the maximum defined for the array), and the innermost array (so it can contain nulls).
• For a multi-dimensional array, the non-last array(s) of each non-outermost dimension must have a length equal to the maximum defined for that dimension.
• For a multi-dimensional array, the last array of each non-outermost dimension can range in length from one to the maximum defined for that dimension.
• An Array object created by the Connection createArrayOf method is not subject to JDBC custom type mapping of UDT elements for Array methods getArray and getResultSet.
• The Array methods getArray(long index, int count) and getArray(long index, int count, Map map) are not supported for multi-dimensional arrays.
• None of the Array getResultSet methods are supported for multi-dimensional arrays.

For more information regarding the SQL ARRAY data type, refer to ARRAY/VARRAY Data Type in the Teradata Vantage™ Data Types and Literals reference.

XML Data Type

Beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.19, the java.sql.SQLXML interface and the SQL XML data type are supported. An application can use the Teradata JDBC Driver to send XML data to the database and retrieve XML data from the database. The java.sql.SQLXML interface is available in JDK 6.0 and later. SQLXML is not available in JDK 5.0 and earlier.

• An application can use the Connection createSQLXML method to construct an empty SQLXML object, then use one of the SQLXML methods setBinaryStream, setCharacterStream, setResult, or setString to specify the data for the SQLXML object. The application can subsequently bind the SQLXML object to a PreparedStatement or CallableStatement parameter marker.
• An application receives an XML value from the database as an SQLXML object. The application obtains the XML data from the SQLXML object through one of the SQLXML methods getBinaryStream, getCharacterStream, getSource, or getString.

SQLXML Functionality and Limitations

• JDK 6.0 or later is required to use SQLXML functionality.
• When calling the SQLXML getSource method, a SQLException will be thrown if the sourceClass parameter is null.
• When calling the SQLXML setResult method, a SQLException will be thrown if the resultClass parameter is null.
• After any of the SQLXML object's set or get methods are called, the SQLXML object becomes not writable and not readable.
• The SQLXML setBinaryStream method requires the OutputStream argument to contain UTF8-encoded characters.
• The SQLXML getCharacterStream method returns a UTF8-encoded character stream.

XML External Entity (XXE) Processing

Java applications using XML libraries are vulnerable to XXE attacks because the default settings for most Java XML parsers is process XML external entities. The Teradata JDBC Driver uses DocumentBuilderFactory to parse XML values returned from the database. Beginning with Teradata JDBC Driver 16.20.00.10, Document Type Definition (DTD) processing is disabled for the DocumentBuilderFactory, to prevent most XML entity attacks. If your application requires this functionality, specify the XXE_PROCESSING=ON connection parameter.

Early builds of JDK 6 and JDK 7 are affected by Java bug JDK-7157610 which causes a NullPointerException when DTD processing is disabled. The best solution is to upgrade to a JDK version with the fix for Java bug JDK-7157610. As a workaround to avoid Java bug JDK-7157610, specify the XXE_PROCESSING=ON connection parameter.

For more information regarding the SQL XML data type, refer to XML Data Type in the Teradata Vantage™ Data Types and Literals reference.

NUMBER Data Type

Beginning with Teradata Database 14.0 and Teradata JDBC Driver 14.00.00.09, the SQL NUMBER data type is supported. The JDBC data type identifier Types.NUMERIC corresponds to the SQL NUMBER data type.

Beginning with Teradata Database 14.0 and Teradata JDBC Driver 14.00.00.09, when an application binds a BigDecimal value to a PreparedStatement parameter marker, the Teradata JDBC Driver's default behavior is to send the BigDecimal value to the database as the SQL NUMBER data type. An application can override this behavior to force the Teradata JDBC Driver to send a BigDecimal value to the database as the SQL DECIMAL data type. Correspondingly, a SQL NUMBER value received from the database will be presented to the application as Types.NUMERIC, and as a BigDecimal value.

With prior software versions, the Teradata JDBC Driver sends a BigDecimal value to the database as the SQL DECIMAL data type, and the SQL NUMBER data type is not supported.

The database requires all data values in a PreparedStatement batch bound to a particular parameter marker to be of the same data type. The application must bind null and non-null values appropriately to ensure that all values bound to a parameter marker are either NUMBER or DECIMAL, not a combination of the two.

For more information regarding the SQL NUMBER data type, refer to Numeric Data Types in the Teradata Vantage™ Data Types and Literals reference.

Interval Data Types

Beginning with Teradata JDBC Driver 14.10.00.26, an application can use the PreparedStatement or CallableStatement setObject method to bind a Struct value to a question-mark parameter marker as an Interval data type. Prior to Teradata JDBC Driver 14.10.00.26, the setString method was required to bind an Interval value. The setString method continues to work as before for Interval values. Applications only need to use the new functionality for situations that the setString method does not support. These situations are shown below in the examples.

The application must compose each Interval data value as a java.sql.Struct value with one attribute. The one and only attribute of the Struct must be a String value, containing the character representation of the Interval value.

The application is responsible for ensuring that the number of Interval digits in the String matches the default number of interval digits for the Interval data type. For INTERVAL...SECOND data types, the application is also responsible for ensuring that the number of fractional digits in the String matches the default number of fractional digits for the Interval data type. The following table lists the SQL type name and the required number of digits for each Interval data type.

The application must provide a class that implements the java.sql.Struct interface.

public class MyStruct implements java.sql.Struct

{

  private String m_typeName ;

  private Object [] m_attributes ;

  public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }

  public String getSQLTypeName() { return m_typeName ; }

  public Object [] getAttributes() { return m_attributes ; }

  public Object [] getAttributes(java.util.Map map) { return m_attributes ; }

}

The application uses instances of that class to compose Interval values.

Example of Invoking the Database Built-In EXTRACT Function

This example sends an INTERVAL YEAR TO MONTH value of 56 years and 3 months, and the EXTRACT YEAR FROM expression will extract the value 56. The WHERE clause condition tests whether the number of years is more than 50. In this case, a single-row ResultSet will be returned, containing the value 'Y'.

  PreparedStatement ps = con.prepareStatement("SELECT 'Y' WHERE EXTRACT(YEAR FROM ?) > 50") ;

  ps.setObject(1, new MyStruct("INTERVAL YEAR TO MONTH", new Object [] {"56-03"})) ;

  ResultSet rs = ps.executeQuery() ;

Example of Implicit Type Conversion for an Interval Value

This example inserts an INTERVAL YEAR value into an INTERVAL YEAR TO MONTH destination column, relying on the database to perform an implicit type conversion from INTERVAL YEAR to INTERVAL YEAR TO MONTH.

  // Assuming a table with a single INTERVAL YEAR TO MONTH column

  PreparedStatement ps = con.prepareStatement("INSERT INTO MyTable VALUES(?)") ;

  ps.setObject(1, new MyStruct("INTERVAL YEAR", new Object [] {"56"})) ;

  ps.executeUpdate() ;

Example of Inserting Both NULL and non-NULL Interval Values in a PreparedStatement Batch

This example shows a PreparedStatement batch insert of NULL and non-NULL INTERVAL YEAR values into an INTERVAL YEAR destination column.

  // Assuming a table with a single INTERVAL YEAR column

  PreparedStatement ps = con.prepareStatement("INSERT INTO MyTable VALUES(?)") ;

  ps.setObject(1, new MyStruct("INTERVAL YEAR", new Object [] {"56"})) ;

  ps.addBatch() ;

  ps.setObject(1, new MyStruct("INTERVAL YEAR", new Object [] {null})) ;

  ps.addBatch() ;

  ps.executeBatch() ;

JSON Data Type

Beginning with Teradata Database 15.0 and Teradata JDBC Driver 15.00.00.11, the JSON data type is supported. The JDBC API does not yet define a standard JSON data type, so the Teradata JDBC Driver offers Teradata-specific functionality for an application to use the PreparedStatement or CallableStatement setObject method to bind a Struct value to a question-mark parameter marker as a JSON data type. Applications can also insert VARCHAR and CLOB values into JSON destination columns. In order for the application to fully take advantage of the JSON data type's built-in functions, the JSON question-mark parameter marker should be set using a Struct value. This can be seen in the example shown below.

The database returns a JSON value as a CLOB value. An application can use the following metadata methods to differentiate between a JSON value and an actual CLOB value. These methods return "JSON" to indicate a JSON value, and return "CLOB" to indicate a CLOB value.

• ResultSetMetaData getColumnTypeName
• ParameterMetaData getParameterTypeName

When an application uses the Teradata-specific functionality of specifying a JSON value as a Struct value, the Struct value must contain one of the following attributes.

• String
• java.io.Reader
• java.sql.Clob
• null

Furthermore, when the Struct contains a Reader attribute, the Struct must also contain a second attribute that is an Integer type specifying the number of characters in the stream.

When an application uses the Teradata-specific functionality of specifying a JSON value as a Struct value, the application must provide a class that implements the java.sql.Struct interface.

public class MyStruct implements java.sql.Struct

{

  private String m_typeName ;

  private Object [] m_attributes ;

  public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }

  public String getSQLTypeName() { return m_typeName ; }

  public Object [] getAttributes() { return m_attributes ; }

  public Object [] getAttributes(java.util.Map map) { return m_attributes ; }

}

The application uses instances of that class to compose JSON values.

Example of Invoking the JSON Data Type's Combine Method

This example combines two JSON values resulting in a single JSON object. In this case, a single-row ResultSet will be returned, containing the value {"name" : "Jim","name" : "Joe"}.

  PreparedStatement ps = con.prepareStatement("SELECT CAST(? AS JSON).combine(?)") ;

  ps.setObject(1, new MyStruct("JSON", new Object [] {"{\"name\" : \"Jim\"}"})) ;

  ps.setObject(2, new MyStruct("JSON", new Object [] {"{\"name\" : \"Joe\"}"})) ;

  ResultSet rs = ps.executeQuery() ;

Example of Inserting Both NULL and non-NULL JSON Values in a PreparedStatement Batch

This example shows a PreparedStatement batch insert of NULL and non-NULL JSON values into a JSON destination column.

  // Assuming a table with an INTEGER column and a JSON column

  PreparedStatement ps = con.prepareStatement("INSERT INTO MyTable VALUES(?, ?)") ;

  ps.setInt(1, 123) ;

  ps.setObject(2, new MyStruct("JSON", new Object [] {"{\"name\" : \"Kimberly\"})) ;

  ps.addBatch() ;

  ps.setInt(1, 456) ;

  ps.setObject(2, new MyStruct("JSON", new Object [] {null})) ;

  ps.addBatch() ;

  ps.executeBatch() ;

JSON Data Type Incompatibility Errors

If an application attempts to use the Teradata-specific functionality of specifying a JSON value as a Struct value with Teradata Database 15.0 or later, in conjunction with an old version of the Teradata JDBC Driver that does not support the JSON data type, then the following exception may be thrown. The solution is to upgrade to a newer version of the Teradata JDBC Driver that supports the JSON data type.

If an application attempts to use the Teradata-specific functionality of specifying a JSON value as a Struct value with Teradata Database 14.10 or earlier, in conjunction with a version of the Teradata JDBC Driver that supports the JSON data type, then the following exception may be thrown. The solution is to upgrade to Teradata Database 15.0 or later.

If an application executes a query that returns a JSON data value from the database while using an old version of the Teradata JDBC Driver that does not support the JSON data type, then the following exception may be thrown. The solution is to upgrade to a newer version of the Teradata JDBC Driver that supports the JSON data type.

DATASET Data Type

The DATASET data type is a complex data type in which each data value corresponds to an entire file or document. The kind of files or documents that can be stored is dictated by the DATASET data type's associated "Storage Format".

DATASET Storage Format Avro

Beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.23, the DATASET data type offers the Avro storage format. The JDBC API does not yet define a standard DATASET data type, so the Teradata JDBC Driver offers Teradata-specific functionality for an application to use the PreparedStatement or CallableStatement setObject method to bind a Struct value to a question-mark parameter marker as an Avro format DATASET data type. Applications can also insert VARBYTE and BLOB values into Avro format DATASET destination columns. In order for the application to fully take advantage of the DATASET data type's built-in functions, the DATASET question-mark parameter marker should be set using a Struct value. This can be seen in the example shown below.

The database returns an Avro-formatted DATASET value as a BLOB value. An application can use the following metadata methods to differentiate between an Avro-formatted DATASET value and an actual BLOB value. These methods return "DATASET STORAGE FORMAT AVRO" to indicate an Avro-formatted DATASET value, and return "BLOB" to indicate a BLOB value.

• ResultSetMetaData getColumnTypeName
• ParameterMetaData getParameterTypeName

When an application uses the Teradata-specific functionality of specifying an Avro-formatted value as a Struct value, the Struct value must contain one of the following attributes.

• byte array
• java.io.InputStream
• java.sql.Blob
• null

Furthermore, when the Struct contains an InputStream attribute, the Struct must also contain a second attribute that is an Integer type specifying the number of bytes in the stream.

To use the Teradata-specific functionality of specifying an Avro-formatted DATASET value as a Struct value, the application must provide a class that implements the java.sql.Struct interface.

public class MyStruct implements java.sql.Struct

{

  private String m_typeName ;

  private Object [] m_attributes ;

  public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }

  public String getSQLTypeName() { return m_typeName ; }

  public Object [] getAttributes() { return m_attributes ; }

  public Object [] getAttributes(java.util.Map map) { return m_attributes ; }

}

The application then uses instances of that class to compose Avro-formatted DATASET values.

Example of Invoking the AVRO_CHECK Function

This example validates the Avro-formatted DATASET value. In this case, a single-row ResultSet will be returned containing the value "OK".

  PreparedStatement ps = con.prepareStatement("SELECT AVRO_CHECK(?)") ;

  ps.setObject(1, new MyStruct("DATASET STORAGE FORMAT AVRO", new Object [] {avroInputStream, nLength})) ;

  ResultSet rs = ps.executeQuery() ;

Example of Inserting Both NULL and non-NULL Avro-formatted DATASET Values in a PreparedStatement Batch

This example shows a PreparedStatement batch insert of NULL and non-NULL Avro-formatted DATASET values into an Avro format DATASET destination column.

  // Assuming a table with an INTEGER column and a DATASET STORAGE FROMAT AVRO column

  PreparedStatement ps = con.prepareStatement("INSERT INTO MyTable VALUES(?, ?)") ;

  ps.setInt(1, 123) ;

  ps.setObject(2, new MyStruct("DATASET STORAGE FORMAT AVRO", new Object [] {avroInputStream, nAvroLength})) ;

  ps.addBatch() ;

  ps.setInt(1, 456) ;

  ps.setObject(2, new MyStruct("DATASET STORAGE FORMAT AVRO", new Object [] {null})) ;

  ps.addBatch() ;

  ps.executeBatch() ;

DATASET Storage Format CSV

Beginning with Teradata Advanced SQL Engine 16.20 and Teradata JDBC Driver 16.20.00.01, the DATASET data type offers the Comma Separated Value (CSV) storage format. The JDBC API does not yet define a standard DATASET data type, so the Teradata JDBC Driver offers Teradata-specific functionality for an application to use the PreparedStatement or CallableStatement setObject method to bind a Struct value to a question-mark parameter marker as a CSV format DATASET data type. Applications can also insert VARCHAR and CLOB values into CSV format DATASET destination columns. In order for the application to fully take advantage of the DATASET data type's built-in functions, the DATASET question-mark parameter marker should be set using a Struct value. This can be seen in the example shown below.

The database returns a CSV-formatted DATASET value as a CLOB value. An application can use the following metadata methods to differentiate between a CSV-formatted DATASET value and an actual CLOB value. These methods return "DATASET STORAGE FORMAT CSV" to indicate a CSV-formatted DATASET value, and return "CLOB" to indicate a CLOB value.

• ResultSetMetaData getColumnTypeName
• ParameterMetaData getParameterTypeName

When an application uses the Teradata-specific functionality of specifying a CSV-formatted value as a Struct value, the Struct value must contain one of the following attributes.

• String
• java.io.Reader
• java.sql.Clob
• null

Furthermore, when the Struct contains a Reader attribute, the Struct must also contain a second attribute that is an Integer type specifying the number of characters in the reader.

To use the Teradata-specific functionality of specifying a CSV-formatted DATASET value as a Struct value, the application must provide a class that implements the java.sql.Struct interface.

public class MyStruct implements java.sql.Struct

{

  private String m_typeName ;

  private Object [] m_attributes ;

  public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }

  public String getSQLTypeName() { return m_typeName ; }

  public Object [] getAttributes() { return m_attributes ; }

  public Object [] getAttributes(java.util.Map map) { return m_attributes ; }

}

The application then uses instances of that class to compose CSV-formatted DATASET values.

Example of Invoking the validate Method

This example validates the CSV-formatted DATASET value. In this case, a single-row ResultSet will be returned containing the integer value 1 if it valid or 0 if it is invalid.

  PreparedStatement ps = con.prepareStatement("SELECT CAST (? as DATASET STORAGE FORMAT CSV).validate ()") ;

  ps.setObject(1, new MyStruct("DATASET STORAGE FORMAT CSV", new Object [] {csvReader, nLength})) ;

  ResultSet rs = ps.executeQuery() ;

Example of Inserting Both NULL and non-NULL CSV-formatted DATASET Values in a PreparedStatement Batch

This example shows a PreparedStatement batch insert of NULL and non-NULL CSV-formatted DATASET values into a CSV format DATASET destination column.

  // Assuming a table with an INTEGER column and a DATASET STORAGE FROMAT CSV column

  PreparedStatement ps = con.prepareStatement("INSERT INTO MyTable VALUES(?, ?)") ;

  ps.setInt(1, 123) ;

  ps.setObject(2, new MyStruct("DATASET STORAGE FORMAT CSV", new Object [] {csvReader, nCSVLength})) ;

  ps.addBatch() ;

  ps.setInt(1, 456) ;

  ps.setObject(2, new MyStruct("DATASET STORAGE FORMAT CSV", new Object [] {null})) ;

  ps.addBatch() ;

  ps.executeBatch() ;

DATASET Data Type Incompatibility Errors

If an application executes a query that returns an Avro-formatted DATASET data value from the database while using an old version of the Teradata JDBC Driver that does not support the DATASET data type, then the data type will be returned as a BLOB.

If an application attempts to use the Teradata-specific functionality of binding an Avro-formatted DATASET value as a Struct value with Teradata Database 16.0 or later, in conjunction with an old version of the Teradata JDBC Driver that does not support the DATASET data type, then the following exception may be thrown. The solution is to upgrade to a newer version of the Teradata JDBC Driver that supports the DATASET data type.

If an application attempts to use the Teradata-specific functionality of binding an Avro-formatted DATASET value as a Struct value with Teradata Database 15.10 or earlier, in conjunction with a version of the Teradata JDBC Driver that supports the Avro format DATASET data type, then the following exception may be thrown. The solution is to upgrade to Teradata Database 16.0 or later.

If an application executes a query that returns a CSV-formatted DATASET data value from the database while using an old version of the Teradata JDBC Driver that does not support the DATASET data type, then the data type will be returned as a CLOB.

If an application attempts to use the Teradata-specific functionality of binding a CSV-formatted DATASET value as a Struct value with Teradata Advanced SQL Engine 16.20 or later, in conjunction with an old version of the Teradata JDBC Driver that does not support the DATASET data type, then the following exception may be thrown. The solution is to upgrade to a newer version of the Teradata JDBC Driver that supports the DATASET data type.

If an application attempts to use the Teradata-specific functionality of binding a CSV-formatted DATASET value as a Struct value with Teradata Database 16.10 or earlier, in conjunction with a version of the Teradata JDBC Driver that supports the CSV format DATASET data type, then the following exception may be thrown. The solution is to upgrade to Teradata Advanced SQL Engine 16.20 or later.

Updatable LOBs

Temporary Table

Before LOB updates can be used with the Teradata JDBC Driver, a table must be created with the following columns:

• id INTEGER
• bval BLOB
• cval CLOB

For normal application usage, the database administrator creates the table as a Global Temporary Table (GTT). However, the table could be a regular table for special usage cases, such as for debugging.

The id integer column is intended to be the primary index. If desired, it could be specified as a unique primary index.

When the GTT is created with CREATE TABLE, the ON COMMIT PRESERVE ROWS clause must be specified, so that the Teradata JDBC Driver can manipulate LOBs across transactions.

In the example that follows, the table name JdbcLobUpdate is just a suggestion; any name can be chosen for the table:

create global temporary table JdbcLobUpdate(

    id integer not null,

    bval blob,

    cval clob character set unicode)

  unique primary index upi_JdbcLobUpdate(id)

  on commit preserve rows

Connection Parameter

The Connection parameter LOB_TEMP_TABLE must be set to the name chosen for the temporary table.

LOB_TEMP_TABLE=tableName

A database name can be optionally specified:

LOB_TEMP_TABLE=databaseName.tableName

Update LOB

If an application wants to update an existing LOB value in a table, then after calling any LOB update methods, the application must also execute an UPDATE statement to put the modified LOB value back into the original row.

Teradata JDBC Driver returns true from DatabaseMetaData locatorsUpdateCopy to indicate that the implementation updates a copy of the LOB.

ResultSet rs = stmt.executeQuery("SELECT id,data FROM datatab");

rs.next();

int id = rs.getInt(1);

Blob data = rs.getBlob(2);

int numWritten = data.setBytes(1, val);

if (dbmd.locatorsUpdateCopy() == true){

  PreparedStatement ps = conn.prepareStatement(

   "UPDATE datatab SET data = ? WHERE id = ?");

  try {

    ps.setBlob(1, data);

    ps.setInt(2, id);

    ps.executeUpdate();

  } finally {

    ps.close();

  }

}

Calling free

The free method releases the resources held by a Blob or Clob object. After free has been called, the object cannot be used.

The free method is available beginning with Teradata JDBC Driver 14.00.00.08. If the application updates a Blob or Clob object, then the application must call free when the application is done with the object; otherwise, database Error 3130 (Response Limit Exceeded) may occur.

With JDK 6.0 and later, the free method is defined in the java.sql.Blob and java.sql.Clob interfaces, and the free method can be called directly by the application. With JDK 5.0 and earlier, reflection must be used to call the free method.

Row Numbers and Update Counts Exceeding Integer.MAX_VALUE

While the database can accommodate tables containing many billions of rows, the JDBC API methods that work with row numbers or update counts use a signed 32-bit integer (a Java int) to represent a row number or update count. The largest positive value that a signed 32-bit integer can hold is approximately two billion, and is denoted by the constant Integer.MAX_VALUE.

Row Numbers

Some ResultSet methods accept a row number argument or return a row number. While a large result set may contain more than two billion rows, the ResultSet methods are limited to accessing only the initial number of rows, such that the row number is less than Integer.MAX_VALUE.

Update Counts

Beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.25, for row count values received from the database that are too large to fit into a signed 32-bit integer, the Teradata JDBC Driver will return an update count of Integer.MAX_VALUE to the application, and will provide a SQLWarning with error code 1474 that lists the actual row count in the message text.

The following JDBC API methods provide this behavior.

Multi-Threading Issues

Using multi-threading can improve an application's performance. Determine which requests can run at the same time and then using multiple concurrent sessions, submit a request on each session. It is strongly discouraged to try and submit more than one concurrent request per session.

Note:  The database does not support more than one active request per session. If the application attempts to do this, then the Teradata JDBC Driver blocks until the previous request has returned. This may negatively impact performance.

Table 13 contains JDBC Object Thread safety information.

Data Dictionary Access by DatabaseMetaData Methods

Several of the Teradata JDBC Driver DatabaseMetaData methods submit queries to the database on behalf of the application that calls the DatabaseMetaData methods. The application designer must ensure that the application has sufficient privileges to access the necessary Data Dictionary tables and views.

The following table lists each of the DatabaseMetaData methods that query Data Dictionary tables and/or views, and lists the necessary access for each method.

Beginning with Teradata JDBC Driver 13.00.00.25, Data Dictionary V views are used when connected to Teradata Database 12.0 or later. In the table below, the [V] suffix indicates whether the V view will be conditionally used depending on the database version.

The USEXVIEWS connection parameter controls whether the normal views or the X views are used. In the table below, the [X] suffix indicates whether the X view will be conditionally used depending on the setting of the USEXVIEWS connection parameter.

Using the Sun JDK 5.0 Implementation of JDBC RowSet Interface

The Teradata JDBC Driver works with the Sun JDK 5.0 implementation of JDBC RowSet Interface.

When using com.sun.rowset.JdbcRowSetImpl(java.sql.Connection connection) and com.sun.rowset.JdbcRowSetImpl(String url, String user, String password), it calls Connection.prepareStatement(String sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE). However, for database versions prior to 12.0, CONCUR_UPDATABLE is not supported.

The Teradata JDBC Driver downgrades the unsupported ResultSet concurrency (CONCUR_UPDATABLE) to the supported concurrency (CONCUR_READ_ONLY) for database versions prior to 12.0 or when the fetched result set is not updatable for Teradata Database 12.0 and newer versions. Refer to Making a Result Set Updatable for more details about how to use updatable result set.

Generated Keys

In version 3.4 of the Teradata JDBC Driver, the following methods were added or enabled to support generated keys:

• Statement.getGeneratedKeys()
• Statement.executeUpdate(String sql, int autoGeneratedKeys)
• Statement.executeUpdate(String sql, int[] columnIndexes)
• Statement.executeUpdate(String sql, String[] columnNames)
• Statement.execute(String sql, int autoGeneratedKeys)
• Statement.execute(String sql, int[] columnIndexes)
• Statement.execute(String sql, String[] columnNames)
• Connection.prepareStatement(String sql, int autoGeneratedKeys)
• Connection.prepareStatement(String sql, int[] columnIndexes)
• Connection.prepareStatement(String sql, String[] columnNames)

Multi-Statement Requests

If generated keys are being requested for a multi-statement request, then the application can retrieve the generated keys for the first statement by calling getGeneratedKeys(). It is then necessary to call getMoreResults() before each additional call to getGeneratedKeys(). If the statement is not an INSERT statement, then an empty result set is returned.

The JDBC spec does not state how an application would obtain multiple generated key result sets from a multi-statement request. The JDBC spec does not mandate or prohibit using getMoreResults() to advance to the next generated key result set. This is a design choice for the Teradata JDBC Driver that seemed to be the most obvious and intuitive choice.

PreparedStatement Batch Requests

If generated keys are being retrieved for a PreparedStatement batch request, then the rows are coalesced into a single auto-generated key result set, which is returned from getGeneratedKeys(). The maximum number of inserts in a batch request is limited to 1024.

INSERT ... SELECT Statement

If the request is an INSERT ... SELECT statement, then multiple rows are returned in the result set. The rows are not in any specific order.

Exceptions

Error 1125

One or more of the generated keys specified do not match a column name in the table. Change the list of column names to match the column names in the table where the row is being inserted may be returned for:

• Statement.executeUpdate(String sql, String[] columnNames)
• Statement.execute(String sql, String[] columnNames)
• Connection.prepareStatement(String sql, String[] columnNames)

Error 1126

One or more of the generated key indexes are invalid. Change the indexes so that they are greater than 0 and less than or equal to the number of columns in the table where the row is being inserted may be returned for:

• Statement.executeUpdate(String sql, int[] columnIndexes)
• Statement.execute(String sql, int[] columnIndexes)
• Connection.prepareStatement(String sql, int[] columnIndexes)

Error 1127

Column names array or column index array cannot be null may be returned if any of the methods are called that contain a null value for the column names array or the column index array.

Error 1128

AutoGenerated Keys are not supported with this release of database software. The database must be running V2R6.2 or higher may be returned if any of the methods are called that request Auto Generated Keys.

Statement.executeUpdate()

The following exceptions are currently being used for PreparedStatement.executeUpdate(), but are now used for Statement.executeUpdate() where auto-generated keys are being requested.

• executeUpdate() cannot be used when a result set is expected; use executeQuery() or execute()
• executeUpdate() cannot be used when the request contains multiple statements; use execute()

Upsert Statements are not Supported

UPSERT statements are not supported with getGenerated Keys. If generatedKeys are requested after an UPSERT statement, an empty result set is returned.

ParameterMetaData and Ambiguous Types

When using ParameterMetaData, there are cases where the data type of a parameter may be ambiguous. This can happen when a ? parameter is used in certain expressions or used in the invocation of an overloaded UDF or UDM. Consider the following examples:

        Simple INSERT Operation:

This is a straightforward operation in which the three parameters, indicated by (?, ?, ?), are inserted into the table T1. In this instance, there is a simple assignment of each of the parameters to a column or field in the table. The parameter metadata returned is clear; it is the metadata describing each of the columns that the parameter data populates.

        INSERT Operation with Expressions:

In this example, each of the columns of the target table is to be assigned the results of expressions: ? * 3, ? + 1, and ? MOD 3. The metadata associated with the three parameters has become ambiguous. since it could map to more than one SQL type. In this case, the data type of the parameter is considered unknown, and the method java.sql.ParameterMetaData.getParameterType() returns java.sql.Types.NULL.

Table 16 outlines what is returned in cases where the data type of a parameter is ambiguous, and is considered an unknown data type.

When a ? parameter is specified in the select list of a SELECT statement, the ResultSetMetaData may be ambiguous, in addition to the ParameterMetaData.

Example

Note:  Question-mark parameter markers may be used in a select-list within a SELECT statement beginning with Teradata Database V2R6.2.0.19.

Table 17 outlines what is returned in cases where the data type of a parameter is ambiguous, and is considered an unknown data type.

Java External Stored Procedures

The Java External Stored Procedure (XSP) portion of the ANSI SQL standard is provided by Teradata Database 12.0 or later, when used in conjunction with the Teradata JDBC Driver 12.0 or later. This includes the SQLJ database and tables, jar file installation, Java XSP definition, and Java XSP access to the JDBC default connection.

For more information, refer to SQL External Routine Programming.

Use Java XSPs in the following manner:

• Compile the Java source outside of the database and place the resulting class or classes (the byte code) in a jar file

  Note:  Teradata Database 12.0 and 13.0 do not support Java Stored Procedures compiled with JDK 6.0. Only Java Stored Procedures compiled with JDK 1.4.2 or JDK 5.0 are supported.

• Register the resulting jar file with the database by calling an XSP named SQLJ.INSTALL_JAR
• Create the JAVA XSP with its EXTERNAL NAME clause specifying the jar file and associated Java class

Once created, access the Java routine in the same manner as any XSP. Java XSPs can execute SQL code using the standard JDBC driver interface. Since the stored procedure is running on the database and is invoked from within a logged-on session, a connection URL of jdbc:default:connection should be used.

JAR Files

A jar file contains a collection of Java classes. The classes (compiled Java bytecodes) are referenced when an external Java routine is created using the EXTERNAL NAME clause. The jar files are created outside of the database. Before the classes can be referenced, they must be registered and copied into the SQL environment. Once a jar is installed onto the database, its content can't be changed in any way–it can only be deleted or replaced in its entirety.

A jar file is not global but is only available to the user that installed it using a call to SQLJ.INSTALL_JAR(). Like the infrastructure for C/C++ UDFs and XSPs, a directory is created on the server for each database that contains a jar. A C/C++ DLL created for one or more UDFs or XSPs in a given database is not accessible to other users or databases; the same is true for jar files. In connection with this, no new access rights are created for jar files. Therefore, user‑database A cannot create an XSP that references a jar installed in user‑database B. However, user‑database A can be granted access to a Java XSP that has been created in user‑database B by using the same access rights designed for C/C++ UDFs/XSPs. A model that could optionally be followed for Java XSPs is to install all jars and create all Java XSPs in the same database, and then grant access to these Java XSPs to all users who will need to execute them.

The jar files are installed, replaced, deleted, or path-specified by the following XSPs:

• SQLJ.INSTALL_JAR
• SQLJ.REPLACE_JAR
• SQLJ.REMOVE_JAR
• SQLJ.ALTER_JAVA_PATH

Transferring Java XSP From the Client to the DBS Server

If using JDBC, use jar files for XSPs that are stored on the server or the client. A jar file for a Java XSP that is on the client must be transferred from the client node to the server node. For security purposes, the Teradata JDBC Driver uses the classpath to load all resources. The jar file that contains Java XSPs must not be on the classpath itself. Instead, the container of the jar file must be on the classpath. For example, if the jar file is located in a directory on the file system, then the directory name must be present in the classpath. As another example, the jar file may be located inside a war file or an ear file, because the application server will automatically make the contents of the war file or ear file available on the classpath. Once the class path is set, the Teradata JDBC Driver can transfer the source file to the server node.

The following is code from a JDBC sample class using SQLJ to install and transfer a jar file from the client to the DBS server:

stmt.executeUpdate("{call sqlj.install_jar('cj!SampleXJSP.jar', 'SampleXJSP',0)}");

This example gives the location of the jar file using the locspec parameter. The <locspec> specifies where the originating jar file is located. If the <location designator> specifies 'CJ!' then the jar is located on the client in the client-interpreted location specified by the class path for the Teradata JDBC Driver. If the <location designator> specifies 'SJ!' then the jar is located on the database server using the <server jar path>.

Defining the SQL Routines

After the jar file is installed, the next step is to create the Java class procedure DeptJobInfo. For example:

REPLACE PROCEDURE getDeptJobInfo

(IN name VARCHAR(30), OUT dept VARCHAR(50), OUT job VARCHAR(300))

LANGUAGE JAVA MODIFIES SQL DATA

PARAMETER STYLE JAVA

EXTERNAL NAME 'SampleXJSP:DeptJobInfo.getDeptJobInfo';

Parameter Usage Example

The following example shows a procedure definition for various parameter types. SQL statement:

REPLACE PROCEDURE getEmpInfo

(IN name VARCHAR(30), OUT id INTEGER, OUT dept VARCHAR(50),

OUT job VARCHAR(300), OUT res CLOB)

LANGUAGE JAVA MODIFIES SQL DATA

PARAMETER STYLE JAVA

EXTERNAL NAME 'SampleXJSP:EmpInfo.getEmpInfo(

java.lang.String,

java.lang.Integer[],

java.lang.String[],

java.lang.String[],

java.sql.Clob[])';

Source code for the Java stored procedure defined above:

public class EmpInfo

{

  public static void getEmpInfo(String name,

                        java.lang.Integer[] id,

                        String[] dept,

                        String[] job,

                        java.sql.Clob[] res) throws SQLException

  {

    /* Establish default connection.*/

    Connection con =     DriverManager.getConnection("jdbc:default:connection");

    String query = "SELECT empID, empDept, empJob, empResume" +

             "FROM employee 2" +

             "WHERE empName = ?;";

    /* Executing the command */

    PreparedStatement pStmt = con.prepareStatement(query);

    try

    {

      pStmt.setString(1, name);

      ResultSet rs = pStmt.executeQuery();

      boolean more = rs.next();

      if(more)

      {

        id[0] = new java.lang.Integer(rs.getInt(1));

        dept[0] = rs.getString(2);

        job[0] = rs.getString(3);

        res[0] = rs.getClob(4);

      }

    }

    finally

    {

      pStmt.close();

    }

}

The parameters in the Java XSP provided here are explicitly mapped from the SQL types to the Java types. These mappings also can be implicit. The mappings for external Java XSP parameters from SQL types to Java types are defined in SQL Data Types Mapping.

Default Connection

The Java XSP in the previous example is running on the database and is invoked from within a logged-on session. As a result, the connection URL being used is jdbc:default:connection. This creates a default connection that participates in the caller's session and current transaction. No logoff occurs when the connection's close method is called since the default connection uses the same session as the caller. The default connection is only accessible from one thread; specifically, the thread that invoked the Java XSP.

Invoking a Java XSP from JDBC

Calling a Java XSP from a JDBC client is the same as invoking any stored procedure. The following example uses a CallableStatement:

String sCall = "{call getEmpInfo(?,?,?,?,?)}";

String sName = "Brian Lee";

// Creating a CallableStatement object, representing

// a precompiled SQL statement and preparing the callable

// statement for execution.

CallableStatement cStmt = con.prepareCall(sCall);

// Setting up input parameter value

cStmt.setString(1, sName);

// Setting up output parameters for data retrieval by

// declaring parameter types.

cStmt.registerOutParameter(2, Types.INTEGER);

cStmt.registerOutParameter(3, Types.VARCHAR);

cStmt.registerOutParameter(4, Types.VARCHAR);

cStmt.registerOutParameter(5, Types.CLOB);

System.out.printIn("\n Calling the procedure with '"

           + sName + '"...");

// Making a procedure call

cStmt.executeUpdate();

// Displaying procedure call result

System.out.printIn(" Call successful.");

System.out.printIn("\n Displaying output of the call to"

           + "getEmpInfo(...):");

System.outprintIn("\n" + sName);

System.out.printIn("---------------");

int id = cStmt.getInt(2);

System.out.printIn(" Employee ID : " + id);

System.out.printIn(" Department : " + cStmt.getString(3));

System.out.printIn(" Job Description : " + cStmt.getString(4));

System.out.print(" Resume: ");

// Writing CLOB data out to a file for review

createClobFile(cStmt.getClob(5),(id + "resumeT20604.txt"));

Transaction Semantics and Java XSPs

Transaction semantics (ANSI or Teradata) are set when a session is logged on, and cannot be subsequently changed. When using the Teradata JDBC Driver, the transaction semantics are specified using the TMODE connection parameter. The default connection used by a Java XSP always inherits the transaction semantics used to establish the caller's session.

• If the caller's session has ANSI transaction semantics, then the Java XSP's default connection Auto-Commit setting will be false.
• If the caller's session has Teradata transaction semantics, then the Java XSP's default connection Auto-Commit setting will depend on whether the caller's session is within an existing transaction (for example, BT was executed). In this case, the Auto-Commit setting will be false if BT was executed, and the Auto-Commit setting will be true if BT was not executed.

Limitations

The following SQL statements are not supported in a Java stored procedure when being used to generate a dynamic result set. This also means that they cannot be mixed with other SQL statements in a multi-statement request when some of these other SQL statements are used to generate a dynamic result set:

• HELP CONSTRAINT
• HELP [TEMPORARY] INDEX
• HELP REPLICATION GROUP
• HELP SESSION
• HELP STATISTICS
• HELP TRANSFORM
• HELP TRIGGER
• HELP VOLATILE TABLE
• SHOW FUNCTION
• SHOW PROCEDURE
• SHOW REPLICATION GROUP
• SHOW SPECIFIC METHOD
• SHOW TYPE

The following restrictions apply when returning auto-generated keys result sets from a Java stored procedure:

• If the Java stored procedure specified RETURN_GENERATED_KEYS, then the client application will receive a one-column ResultSet containing just the identity column value(s)
• If the Java stored procedure specified an array of columns, then the client application will receive a ResultSet containing all the columns of the destination table
• Returning a PreparedStatement batch's generated keys result set from a Java stored procedure is not supported

A Java stored procedure can never directly or indirectly call another Java stored procedure.

An IN or INOUT LOB parameter cannot be updated in a Java Stored Procedure.

The LOB_TEMP_TABLE used in a Java Stored Procedure must be separate from the one used by the JDBC connection calling the Java Stored Procedure.

Teradata Database 12.0 and 13.0 support Java Stored Procedures compiled with JDK 1.4.2 or JDK 5.0 only.

Java Stored Procedures compiled with JDK 6.0 are supported beginning with Teradata Database 13.10.

Updatable Result Sets

Making a Result Set Updatable

A default ResultSet object is returned when calling the following methods:

• Connection.createStatement()
• Connection.prepareStatement(String query)
• Connection.prepareCall(String query)

This default ResultSet object is not updatable and has a cursor that moves forward only.

It is possible to produce ResultSet objects that are scrollable and updatable by calling the following methods:

• Connection.createStatement(int type, ResultSet.CONCUR_UPDATABLE)
• Connection.prepareStatement(String sql, int type, ResultSet.CONCUR_UPDATABLE)
• Connection.prepareCall(String sql, int type, ResultSet.CONCUR_UPDATABLE)

To make the ResultSet objects from the above methods updatable, the following requirements need to be satisfied:

• Database version requirement: Teradata Database V2R6.2 or later
• Connection parameter LOB_SUPPORT must be ON or omitted (the default is ON)
• Unique index requirement: The fetched result set must contain a column that is the only member of a unique index, or a column that is a member of one or more unique indexes on the table, and all the columns of at least one unique index have been selected in the result set.

Non-updatable Result Set

The Teradata JDBC Driver attempts to satisfy updating, inserting, and deleting requests from the result set fetched from the single table or multiple joined tables. However, there are several cases where the returned result set from database is not updatable, including:

• When the result set is fetched from self-joined tables, the columns in the result set fetched from self-joined tables have the same original table name and column names, so the UPDATE, INSERT, and DELETE queries applied to this result set might be ambiguous.
• When ResultSet.insertRow() is used with the result set fetched from either a single table or multiple joined tables, all the columns with NOT NULL constraints from the single table or all the joined tables must be contained in the fetched result set. Otherwise, method insertRow fails and the database returns an error message.

Inner Joins

There are no issues with using updatable result set with inner joins since only matched rows are selected from the inner-joined tables without NULL values padded for unmatched rows.

However, if the result set fetched from multiple inner-joined tables doesn't meet the following unique index requirement for all tables with columns contained in the result set, the methods updateRow and deleteRow fail and the Teradata JDBC Driver throws an SQLException, and method insertRow returns an error message from database.

The fetched result set must contain a column that is the only member of a unique index or a column that is a member of one or more unique indexes on the table, and all the columns of at least one unique index have been selected in the result set.

Outer Joins

If the result set fetched from multiple outer-joined tables doesn't meet the following unique index requirement for all tables with columns contained in the result set, the methods updateRow and deleteRow fail and the Teradata JDBC Driver throws a SQLException, and method insertRow returns an error message from the database.

The fetched result set must contain a column that is the only member of a unique index, or a column that is a member of one or more unique indexes on the table, and all the columns of at least one unique index have been selected in the result set.

Also, the Teradata JDBC Driver is only able to permit a result set row from a join to be updated if the unique index column(s) selected from the above unique index requirement is(are) not NULL. However, outer joins could involve NULL values for these unique index columns for one or more joined table(s). In this case, updateRow() and deleteRow() operations fail and the Teradata JDBC Driver throws a SQLException.

Using an Updatable Result Set

The following are some common scenarios for using an updatable result set with the Teradata JDBC Driver:

• Update the current row's column values in the database

  The following code fragment updates the STATE column in the third row of the ResultSet object rs and then uses the method updateRow to update the data source table from which rs was derived.

  rs.absolute(3); // moves the cursor to the third row of rs

  rs.updateString("STATE", "CALIFORNIA"); // updates the

     // STATE column of row 3 to be CALIFORNIA

  rs.updateRow(); // updates the row in the data source

• Insert a new row into the database

  An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a three-column row, and inserts it into rs and into the data source table, using the method insertRow.

  rs.moveToInsertRow(); // moves cursor to the insert row

  rs.updateString(1, "Michael"); // updates the

     // first column of the insert row to be Michael

  rs.updateInt(2, 35); // updates the second column to be 35

  rs.updateBoolean(3, true); // updates the third column to true

  rs.insertRow(); // inserts the row in the data source

  rs.moveToCurrentRow(); // moves back to current row

• Delete the current row from the database

  rs.absolute(3); // moves the cursor to the third row of rs

  rs.deleteRow(); // deletes the current row 3 in the data source

• Refresh the current row

  The Teradata JDBC Driver implements the method refreshRow to clear up the column values updated by a set of updater methods for the current row in the result set. If refreshRow is called after calling the updater methods, but before calling the method updateRow, then the updates made to the row are lost. However, the Teradata JDBC Driver doesn't refetch the latest value of the current row from the database to refresh the current row.

  The following code fragment updates the STATE and AMOUNT columns in the third row of the ResultSet object rs and then uses the method refreshRow to clear up the column values updated by the updater methods for the current row in the result set. The method updateRow does not update the data source table from which rs was derived since the update column values are lost.

  rs.absolute(3); // moves the cursor to the third row of rs

  rs.updateString("STATE", "CALIFORNIA"); // updates the

     // STATE column of row 3 to be CALIFORNIA

  rs.updateInt("AMOUNT", 58); // updates the second column to be 35

  rs.refreshRow(); // clears up the update column

     // values for the current row

  rs.updateRow(); // no UPDATE in the data source since

     // update values are lost

Result Set Type and Concurrency Upgrading and Downgrading

There are some scenarios where the result set type and concurrency need to be upgraded or downgraded.

Scenario 1: Type Upgrading

The Teradata JDBC Driver implements updatable result set as result set type ResultSet.TYPE_SCROLL_INSENSITIVE.

When users attempt to use result set type

ResultSet.TYPE_FORWARD_ONLY and concurrency mode

ResultSet.CONCUR_UPDATABLE in the following methods:

• Connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)
• Connection.prepareStatement(String sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)
• Connection.prepareCall(String sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)

and the following requirements are satisfied:

• Teradata Database V2R6.2 or later
• Connection parameter LOB_SUPPORT is ON or omitted (the default is ON)

the fetched result set type is upgraded to ResultSet.TYPE_SCROLL_INSENSITIVE, and an SQLWarning is added to the connection object.

Scenario 2: Concurrency Downgrading

When users attempt to use result set concurrency

ResultSet.CONCUR_UPDATABLE in the following methods:

• Connection.createStatement(int type, ResultSet.CONCUR_UPDATABLE)
• Connection.prepareStatement(String sql, int type, ResultSet.CONCUR_UPDATABLE)
• Connection.prepareCall(String sql, int type, ResultSet.CONCUR_UPDATABLE)

and the following requirements are not satisfied:

• Database version requirement: Teradata Database V2R6.2 or later
• Connection parameter LOB_SUPPORT is ON or omitted (the default is ON)
• Unique index requirement: the fetched result set contains a column that is the only member of a unique index, or a column that is a member of one or more unique indexes on the table, and all the columns of at least one unique index have been selected in the result set.

the fetched result set concurrent mode is downgraded to ResultSet.READ_ONLY, and an SQLWarning is added to the connection object.

Exceptions

The following are Teradata JDBC Driver exception scenarios when using updatable result set:

• It is ambiguous and unpredictable to apply methods updateRow, insertRow, and deleteRow to the result set fetched from self-joined tables
• If the result set fetched from single- or multiple-joined tables doesn't meet the above unique index requirement for the table(s) with columns contained in the result set:
  • Methods updateRow and deleteRow: The Teradata JDBC Driver throws a SQLException.

    Error 1197: No unique primary index (UPI) or key columns are fetched in this result set from the table

  • Method insertRow: The Teradata JDBC Driver throws a SQLException for the following conditions:

    Error 1198: Not all of non-nullable columns in the insert row have been given a value for the table

    Error 1199: Not all of non-nullable columns in the insert row have been given a non-null value for the table

• The Teradata JDBC Driver throws a SQLException if the unique index column(s) selected from the above unique index requirement contain(s) NULL values from outer-joined tables when calling methods updateRow and deleteRow.

  Error 1195: Unique Primary Index (UPI) columns are NULL for the table

  Error 1196: Primary key columns are NULL for the table

• The Teradata JDBC Driver throws a SQLException with Error 1190 if an update method is called for an unsupported data type
• The Teradata JDBC Driver throws a SQLException if methods cancelRowUpdates, updateRow, deleteRow, and refreshRow are called when the cursor is on the insert row.

  Error 1191: Function should not be called since the cursor is on the insert row

Stored Procedure Dynamic Result Sets

A stored procedure that returns dynamic result sets is similar to any multi-statement request:

• CallableStatement.execute()–use if more than one result set is expected or if it is unknown whether a stored procedure will return any dynamic result set(s)
• CallableStatement.executeQuery()–use if a single result set is expected

The result sets are dynamic; therefore, it is not possible to look at the metadata for the results until after the statement is executed.

Special Floating Point Values

The Teradata Database does not provide complete support for the special floating point values positive infinity, negative infinity, and Not a Number (NaN). It is possible for a Java application to use a PreparedStatement to bind and insert special floating point values into a FLOAT column in the database. However, because special floating point values are not fully supported by the database, incorrect results and database errors might occur for SELECT statements with WHERE clause conditions that reference special floating point values.

PreparedStatement Batch

A PreparedStatement batch provides efficient inserting, updating, or deleting data where the SQL statement remains the same and only the data values differ for each submission.

Insert performance depends on many factors, such as the number of columns, the column data types, the data value sizes, and so on. Table 18 provides general comparisons of different insert techniques, ordered from slowest to fastest.

The JDBC PreparedStatement and CallableStatement batch feature is implemented by the Teradata JDBC Driver using the Teradata Database's "iterated request" feature. Improved performance can be expected when using a PreparedStatement batch with a SQL request that is compatible with an iterated request, such as INSERT, UPDATE, or DELETE.

Multi-statement requests, and CALLs to stored procedures, are not compatible with iterated requests, due to a Teradata Database limitation. A multi-statement request cannot be used with a PreparedStatement batch.

The Teradata JDBC Driver offers special-case support for a CALL to a stored procedure with a PreparedStatement (or CallableStatement) batch. Because the Teradata Database does not support iterated requests for a CALL to a stored procedure, the Teradata JDBC Driver cannot transmit a batch as an iterated request when the SQL request is a CALL to a stored procedure. Instead, the Teradata JDBC Driver executes the CALL to the stored procedure repeatedly, once per each set of bound parameter values. No performance improvement is expected when a batch is used for a CALL to a stored procedure.

PreparedStatement BatchUpdateException Handling

Beginning with Teradata Database 13.10 and Teradata JDBC Driver 13.00.00.16, PreparedStatement batch execution can return individual success and error conditions for each parameter set.

An application using the PreparedStatement executeBatch method must have a catch-block for BatchUpdateException and the application must examine the error code returned by the BatchUpdateException getErrorCode method.

When an application encounters a BatchUpdateException, the application iterates over the integer updateCount array returned by the BatchUpdateException getUpdateCounts method. Each integer in the updateCount array sequentially corresponds to a parameter set in the PreparedStatement batch.

The parameter set was executed successfully when the integer is greater than or equal to zero, or if the integer is equal to Success.SUCCESS_NO_INFO. The application takes no additional actions.

When the integer is equal to Statement.EXECUTE_FAILED, the parameter set failed to process. The application must handle each parameter set with an integer equal to Statement.EXECUTE_FAILED.

JDBC FastLoad

JDBC FastLoad provides a method for quickly loading large amounts of data into an empty destination table in a database. The actual performance of JDBC FastLoad varies, depending on the application and database configuration.

For example, given an unconstrained network, JDBC FastLoad may be three to 10 times faster than the corresponding SQL PreparedStatement batched insert. In other words, JDBC FastLoad may take only 10% to 33% of the time for the equivalent SQL PreparedStatement batch insert.

Enabling JDBC FastLoad

JDBC FastLoad is enabled with TYPE=FASTLOAD in the URL connection string. When enabled, the FastLoad protocol is used with the database for FastLoad‑capable SQL INSERT statements. For all other SQL statements, including SQL INSERT statements not FastLoad capable, the standard protocol is used with the database.

In order to qualify for JDBC FastLoad, the SQL INSERT statement must meet the following criteria:

• JDBC FastLoad must be used with a PreparedStatement
• The PreparedStatement must be a single SQL INSERT statement
• The PreparedStatement must be used without returning auto-generated keys
• The Teradata session character set must be ASCII, UTF8, or UTF16. Beginning with Teradata JDBC Driver 13.00.00.20, Teradata session character sets KANJISJIS_0S and KANJIEUC_0U are also supported.
• All Teradata data types declared in the destination table must be supported by JDBC FastLoad