Teradata JDBC Driver Sample Programs

The purpose of this documentation is to provide examples of programs that can be used as a guide for purposes of developing JDBC applications solely for use with the Teradata Database, and is provided as an illustration of how JDBC functions operate with the Teradata Database.

This documentation, including the sample programs, are provided on an "AS-IS" basis, and under no circumstances will Teradata be liable for any damages (including direct, indirect, consequential, special, punitive, incidental damages) resulting from any use even if notified of the possibility of any damages. Teradata reserves the right to change or withdraw any of the information provided in this documentation without notice.

The sample programs are grouped into distinct modules. Each module consists of one or more sample programs within a general theme, showing how to use a particular area of functionality.

ModuleDescription
0Sets up the environment, creating necessary tables and indexes

T20000JD.java Set up the example environment (users, databases, tables, indexes)
T20001JD.java Set up the example environment (users, databases, tables, indexes)
T20002JD.java Set up the example environment (users, databases, tables, indexes)
T20003JD.java Set up the example environment (users, databases, global temporary tables, indexes)
1Shows how to obtain result set meta data for a SQL statement

T20100JD.java Execute a SQL statement and obtain any result set column meta data
T20101JD.java Execute a SQL statement and obtain any result set column meta data
2Illustrates performing insertions into the sample tables

T20200JD.java Insert non LOB columns into a row in a table, without using parameter markers
T20201JD.java Insert non LOB columns into a row in a table, using parameter markers
T20202JD.java Insert LOB columns into a row in a table, without using parameter markers
T20203JD.java Insert LOB columns into a row in a table, using parameter markers
T20204JD.java Insert data into a table and display the generated keys
T20205JD.java Insert data into a table using FastLoad with auto-commit true
T20206JD.java Insert data into a table using FastLoad with auto-commit false
T20207JD.java Insert data into a table using FastLoad with auto-commit false and verify FastLoad sessions
T20208JD.java Insert data into a table using FastLoad CSV with one CSV file
T20209JD.java Insert data into a table using FastLoad CSV with two CSV files
3Illustrates performing selections from the sample tables

T20300JD.java Select non-LOB columns from a specific row, from a table, without using parameter markers for the predicate values. Also obtain column meta data.
T20301JD.java Select non-LOB columns from a specific row, from a table, using parameter markers for the predicate values. Also obtain column meta data.
T20302JD.java Select LOB columns from a specific row, from a table, without using parameter markers for the predicate values. Also obtain column meta data.
T20303JD.java Select LOB columns from a specific row, from a table, using parameter markers for the predicate values. Also obtain column meta data.
T20304JD.java Obtain result set metadata prior to executing the prepared statement
T20305JD.java Obtain parameter metadata prior to executing the prepared statement
T20306JD.java Select data from a table using FastExport without parameter marker
T20307JD.java Select data from a table using FastExport with parameter marker
T20308JD.java Select data from a table using FastExport with parameter marker and verify FastExport sessions
4Illustrates performing updates to the sample tables

T20400JD.java Update a non-LOB column from a specific row, from a table, without using parameter markers for the update and predicate values
T20401JD.java Update a non-LOB column from a specific row, from a table, using parameter markers for the update and predicate values
T20402JD.java Update a LOB column from a specific row, from a table, without using parameter markers for the update and predicate values
T20403JD.java Update a LOB column from a specific row, from a table, using parameter markers for the update and predicate values
T20404JD.java Update a LOB column from a specific row, from a table, using the set methods for the update and predicate values
5Illustrates performing deletions from the sample tables

T20500JD.java Delete a specific row, from a table, without using parameter markers for predicate values
T20501JD.java Delete a specific row, from a table, using parameter markers for the predicate values
6Illustrates creation and execution of stored procedures

T20600JD.java Create (if allowed) and execute a stored procedure and obtain its results (no LOB parameters)
T20601JD.java Create (if allowed) and execute a stored procedure and obtain its results (LOB parameters)
T20602JD.java Create (if allowed) and execute an external stored procedure and obtain its results
T20603JD.java Install (if allowed) a jar file for sample Java Stored Procedures
T20604JD.java Create (if allowed) and execute a Java Stored Procedure (no LOB parameters)
T20605JD.java Create (if allowed) and execute a Java Stored Procedure (with a LOB parameter)
T20606JD.java Create (if allowed) and execute a SQL stored procedure and retrieve its dynamic result set
T20607JD.java Create (if allowed) and execute a Java stored procedure and retrieve its dynamic result set
T20608JD.java Create (if allowed) and execute a Java stored procedure and retrieve its multiple dynamic result sets
7Illustrates execution of multi-statement requests

T20700JD.java Execute a multi-statement request that contains only non-SELECT statements, and obtain the results
T20701JD.java Execute a multi-statement request that contains only SELECT statements, and obtain the results
T20702JD.java Execute a multi-statement request that contains both SELECT and non-SELECT statements, and obtain the results
T20703JD.java Execute a multi-statement INSERT request and display the generated keys
T20704JD.java Execute a multi-statement batch INSERT request and display the generated keys
T20705JD.java Execute a multi-statement request and make all results available simultaneously
T20706JD.java Execute a multi-statement request using a PreparedStatement batch request and demonstrates the handling of the PreparedStatement BatchUpdateException
8Illustrates creation and execution of macros

T20800JD.java Execute a macro that contains only non-SELECT statements, and obtain the results
T20801JD.java Execute a macro that contains only SELECT statements, and obtain the results.
T20802JD.java Execute a macro that contains both SELECT and non-SELECT statements, and obtain the results.
9Illustrates obtaining various information about the database

T20900JD.java Obtain meta data about the client access product
T20901JD.java Obtain a list of reserved words
T20902JD.java Obtain a list of all supported SQL data types
T20903JD.java Obtain a list of all table types
T20904JD.java Obtain table index meta data
T20905JD.java Obtain the names of all databases
T20906JD.java Obtain the names of all tables in all databases
T20907JD.java Obtain the names of all columns in all tables in all databases
T20908JD.java Obtain the names of all procedures in all databases
T20909JD.java Obtain the names of all procedure columns in all databases
T20910JD.java Obtain a list of primary key columns for a specific table
T20911JD.java Obtain an optimal set of columns that uniquely identifies a row for a specific table
T20912JD.java Obtain a list of the user-defined types (UDTs) defined in a particular schema
T20913JD.java Obtain a description of the given attribute of the given type for a user-defined type (UDT) that is available in the given schema and catalog
T20914JD.java Demonstrate use of SET SESSION RATE and MONITOR SESSION.
10Illustrates searching for specific information about the database

T21000JD.java Search for a specific table type
T21001JD.java Search for specific databases by name
T21002JD.java Search for specific databases by wildcard characters
T21003JD.java Search for specific tables by name
T21004JD.java Search for specific tables by wildcard characters
T21005JD.java Search for specific columns by name
T21006JD.java Search for specific columns by wildcard characters
T21007JD.java Search for specific procedures by name
T21008JD.java Search for specific procedures by name by wildcard characters
T21009JD.java Search for specific procedure columns by name
T21010JD.java Search for specific procedure columns by wildcard characters
11Illustrates obtaining and modifying client access product options

T21100JD.java Getting and setting connection options
T21101JD.java Getting and setting statement options
T21102JD.java Use transaction isolation level TRANSACTION_READ_UNCOMMITTED
T21103JD.java Use ResultSet holdability CLOSE_CURSORS_AT_COMMIT
T21104JD.java Use URL/DataSource Parameter NEW_PASSWORD to automatically handle Database Password expiration
T21105JD.java Use URL/DataSource Parameter USEXVIEWS
12Illustrates using the JDK 5.0 JdbcRowSetImpl

T21200JD.java Create and use JdbcRowSetImpl, given a connection URL, user, and password
T21201JD.java Create and use JdbcRowSetImpl, given an existing connection
T21202JD.java Create and use JdbcRowSetImpl, given an existing result set
13Illustrates using DataSources

T21300JD.java Programmatically create a DataSource (see note below)
T21301JD.java Programmatically create a ConnectionPoolDataSource (see note below)
T21302JD.java Obtain a connection from a DataSource (see note below)
14Illustrates creation and execution of User-Defined Functions (UDFs)

T21400JD.java Create and execute a User-Defined Function (UDF)
T21401JD.java Install a jar file for sample Java User Defined Function (JUDF)
T21402JD.java Create and execute a Java User Defined Function (JUDF)
15Illustrates the use of data types

T21500JD.java Perform Teradata-specific data type conversions
T21501JD.java Use Geospatial data types
T21502J6.java Use the SQLXML type (requires JDK 6.0 or later)
T21503J6.java Use the SQLXML type with DOM and StAX (requires JDK 6.0 or later)
T21504JD.java Use java.sql.Struct values for TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE values
T21505JD.java Use java.sql.Struct values for Interval values, and use the EXTRACT function
T21506JD.java Use java.sql.Struct values for PERIOD(TIMESTAMP WITH TIME ZONE) values
T21507JD.java Use java.sql.Struct values for JSON values
T21509JD.java Use java.sql.Struct values for DATASET STORAGE FORMAT AVRO values
T21510JD.java Use java.sql.Struct values for DATASET STORAGE FORMAT CSV values
16Illustrates the use of JDBC Escape Syntax

T21600JD.java Use JDBC Escape Syntax
18Illustrates the use of JDBC Updatable Result Set

T21800JD.java Use JDBC Updatable Result Set from a non-prepared Statement
T21801JD.java Use JDBC Updatable Result Set from a PreparedStatement
T21802JD.java Use JDBC Updatable Result Set from a CallableStatement
19Illustrates the use of SET QUERY_BAND statement

T21900JD.java Use SET QUERY_BAND statement
T21901JD.java Use SET QUERY_BAND statement with PROXYUSER
20Illustrates creation and use of User-Defined Types (UDTs), and use of the Struct interface

T22000JD.java Create a non-nested User-Defined Type (UDT) with its associated methods and functions
T22001JD.java Create a nested User-Defined Type (UDT) with its associated methods and functions
T22003JD.java Create a table using the non-nested UDT, insert into the table and select the results.
T22005JD.java Create a table using the nested UDT, insert into the table and select the results.
T22007JD.java Create a table using Period data types, insert into the table and select the results.
T22008JD.java Create a table using the non-nested UDT, insert into the table and select the results using custom type mapping.
T22009JD.java Create a table using the nested UDT, insert into the table and select the results using custom type mapping.
T22010JD.java Create a table using Period data types, insert into the table and select the results using custom type mapping.
21Illustrates creation and use of Array Types, and use of the Array interface

T22100JD.java Create a single dimension array data type and a table that uses this array.
T22101JD.java Create a multiple dimension array data type and an SQL stored procedure that uses this array.
T22102JD.java Insert a single dimension array and select it demonstrating both Array.getArray and Array.getResultSet.
T22103JD.java Execute an SQL stored procedure with multiple dimension array parameters and retrieve the OUT and INOUT parameters.
99Cleans up the environment

T29900JD.java Clean up the example environment (user, database, tables, indexes, UDTs)


Utility Sample Programs

Some useful sample programs are provided separately from the themed modules.

sample1.java is a sample program to test a JDBC connection.

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



How to Run Sample Programs

Use the following procedure to run the Teradata JDBC Driver Sample Programs:

  1. Download and install a supported JDK on your development system.

  2. The sample programs are distributed in a platform-independent jar file, samples.jar, which contains the source code for the sample programs.


    Download samples.jar

    Download and copy samples.jar, terajdbc4.jar, and tdgssconfig.jar to your development platform. Note: tdgssjava.jar is also required for TTU 8.2/Teradata JDBC Driver 3.4 or earlier releases. The commands below assume that all the files are copied to the same directory on your development platform.

  3. Go to the directory where samples.jar is located and unjar the sample applications:

    jar xvf samples.jar

  4. On the z/OS USS platform, the .java files in samples.jar must be converted to EBCDIC after they are extracted from the jar file. The .java source files in samples.jar are encoded in ASCII, but z/OS USS requires EBCDIC files. The .java source files can be converted to EBCDIC using the "iconv" tool.

    For example, to convert sample1.java from ASCII to EBCDIC:

    iconv -f ISO8859-1 -t IBM-1047 sample1.java > sample1-ebcdic.java

  5. Modify the sample application sample1.java with your Teradata Database information.

    You need to change the following:


  6. Compile the modified sample application by entering the following:

    javac sample1.java

  7. Run the application on Windows by entering the following:

    java -classpath .;terajdbc4.jar;tdgssconfig.jar sample1

    Or run the application on UNIX by entering the following:

    java -classpath .:terajdbc4.jar:tdgssconfig.jar sample1

    With TTU 8.2 / Teradata JDBC Driver 3.4 or earlier releases, run the application on Windows by entering the following:

    java -classpath .;terajdbc4.jar;tdgssjava.jar;tdgssconfig.jar sample1

    With TTU 8.2 / Teradata JDBC Driver 3.4 or earlier releases, run the application on UNIX by entering the following:

    java -classpath .:terajdbc4.jar:tdgssjava.jar:tdgssconfig.jar sample1

Note: The "." entry, representing the current directory, on the classpath in the commands above is what enables Java to locate the sample1.class file.

Repeat steps 4 through the end for each of the other sample programs.

Note: The Java Stored Procedure samples in Module 6 must be compiled and placed into the SampleJXSP.jar file before they can be installed on the Teradata Database. The steps listed below assume that you have already unjarred the contents of samples.jar

  1. Compile the Java Stored Procedure samples by entering the following:

    javac -d . BookInfo.java DeptJobInfo.java EmpInfo.java

  2. Jar the Java Stored Procedure samples by entering the following:

    jar cvf SampleJXSP.jar com/teradata/sample/BookInfo.class com/teradata/sample/DeptJobInfo.class com/teradata/sample/EmpInfo.class

  3. Modify the sample application T20603JD.java with your Teradata Database information.

    You need to change the following:


  4. Compile the modified sample application by entering the following:

    javac T20603JD.java

  5. Run the application on Windows by entering the following:

    java -classpath .;terajdbc4.jar;tdgssconfig.jar T20603JD

    Or run the application on UNIX by entering the following:

    java -classpath .:terajdbc4.jar:tdgssconfig.jar T20603JD

    As shown above, tdgssjava.jar must also be listed on the classpath for TTU 8.2/Teradata JDBC Driver 3.4 or earlier releases.

Note: The Java User Defined Function sample in module 14 must be compiled and placed into the SampleJavaUDF.jar file before they are installed on the Teradata database. The steps listed below assume that you have already unjarred the contents of samples.jar

  1. Compile the Java User Defined Function sample by entering the following:

    javac -d . JavaUDFClass.java

  2. Jar the Java User Defined Function sample by entering the following:

    jar cvf SampleJavaUDF.jar com/teradata/sample/JavaUDFClass.class

  3. Modify the sample application T21401JD.java with your Teradata Database information.

    You need to change the following:


  4. Compile the modified sample application by entering the following:

    javac T21401JD.java

  5. Run the application on Windows by entering the following:

    java -classpath .;terajdbc4.jar;tdgssconfig.jar T21401JD

    Or run the application on UNIX by entering the following:

    java -classpath .:terajdbc4.jar:tdgssconfig.jar T21401JD

    As shown above, tdgssjava.jar must also be listed on the classpath for TTU 8.2/Teradata JDBC Driver 3.4 or earlier releases.

Note: The Module 13 DataSource Sample Programs require the com.sun.jndi.fscontext.RefFSContextFactory class, which is included in the fscontext.jar file of the Java Enterprise Edition SDK. In order to run the Module 13 DataSource Sample Programs, you need to download and install the Java EE SDK from Oracle and include fscontext.jar and providerutil.jar on your classpath.