fastexport with JDBC

Connectivity
N/A

fastexport with JDBC

type=fastload will allow to use multi session fastload with JDBC driver, does anyone know if there is a way to use fastexport with JDBC?

Thanks,

Vadim
5 REPLIES
Teradata Employee

Re: fastexport with JDBC

Yes, the Teradata JDBC Driver also supports the FastExport wire protocol -- specify the TYPE=FASTEXPORT connection parameter.

More information is available in the Teradata JDBC Driver Reference.

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BGBFBBEG

Re: fastexport with JDBC

Hi ,

I am using teradata 13.0. I am trying to export a table and write it in to a csv file using JDBC driver fast export.

I am facing Datatype not supported issue. I used all the JDBC driver version (JDBC 14.10,15.0,16.10) . But no luck.

Please assist me . The table contains below data types.

SyntaxEditor Code Snippet

CREATE MULTISET TABLE teradata_demo_table2 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT     (
      id INTEGER,
      col_date DATE FORMAT 'yyyy-mm-dd',
      col_time TIME(4),
      col_timestamp TIMESTAMP(5),
      col_timestamp_tz TIMESTAMP(4),
      col_time_tz TIME(6) ,
      col_interval_yr_month INTERVAL YEAR(4) TO MONTH,
      col_interval_yr INTERVAL YEAR(4),
      col_interval_month INTERVAL MONTH(4),
      col_interval_day INTERVAL DAY(2),
      col_interval_day_hr INTERVAL DAY(2) TO HOUR,
      col_period_date PERIOD(DATE) FORMAT 'YYYY-MM-DD',
      col_period_timestamp PERIOD(TIMESTAMP(6)),
      col_byteint BYTEINT FORMAT 'Z9',
      col_smallint SMALLINT FORMAT '999',
      col_integer INTEGER,
      col_bigint BIGINT,
      col_decimal DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99',
      col_float FLOAT,
      col_char CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC,
      col_varchar VARCHAR(16384) CHARACTER SET LATIN NOT CASESPECIFIC)PRIMARY INDEX ( id );

 Below is the Java program .

 

package com.teradata.tableoffload;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import com.opencsv.CSVWriter;
public class Tera13DataOffload {
// Name of the user able to select a table
public static String user = "tduser";
public static String password = "tduser";
public static void main(String args[])
throws ClassNotFoundException, IOException
{
// URL to be passed to the JDBC driver
String url ="jdbc:teradata://XX.**bleep**.**bleep**.**bleep**/TMODE=ANSI,CHARSET=ASCII,TYPE=FASTEXPORT";
// Name of sample table
String tableName = "XXXXX";
// SELECT statement
String selectTable =
"SELECT * FROM " + tableName;
try {
System.out.println(
"Sample progName Empexp starting: " + new java.util.Date());
System.out.println(" Looking for the Teradata JDBC driver...");
Class.forName("com.teradata.jdbc.TeraDriver");
System.out.println(" Teradata JDBC driver loaded.");
System.out.println(" Attempting to connect to Teradata...");
Connection con = DriverManager.getConnection(url, user, password);
System.out.println(" Connection to Teradata established.");
try {

con.clearWarnings();
System.out.println(
" Creating a PreparedStatement object for SELECT...");
PreparedStatement pstmt2 = con.prepareStatement(selectTable);
System.out.println(
" Created a PreparedStatement object for SELECT...");
try {
SQLWarning w = con.getWarnings();
// Note that there could be multiple SQLWarning objects
// chained together!
while (w != null) {
System.out.println("*** SQLWarning caught ***");
StringWriter sw = new StringWriter();
w.printStackTrace(new PrintWriter(sw, true));
System.out.println(
"SQL State = " + w.getSQLState()
+ ", Error Code = " + w.getErrorCode()
+ "\n" + sw.toString());
w = w.getNextWarning();
}
System.out.println(" Selecting data rows...");
ResultSet rs = pstmt2.executeQuery();
try{

CSVWriter writer = new CSVWriter(new FileWriter("yourfile.csv"), '|');
boolean includeHeaders = true;
writer.writeAll(rs, includeHeaders);
writer.close();
} finally { rs.close(); }
w = pstmt2.getWarnings();
// Note that there could be multiple SQLWarning objects
// chained together!
while (w != null) {
System.out.println("*** SQLWarning caught ***");
StringWriter sw = new StringWriter();
w.printStackTrace(new PrintWriter(sw, true));
System.out.println("SQL State = " + w.getSQLState()
+ ", Error Code = " + w.getErrorCode()
+ "\n" + sw.toString());
w = w.getNextWarning();
}
} finally {
System.out.println(
" Closing PreparedStatement object for SELECT...");
pstmt2.close();
System.out.println(
" PreparedStatement object for SELECT closed.");
}

} finally {
System.out.println(" Closing Connection to Teradata...");
con.close();
System.out.println(" Connection to Teradata closed.");
}
System.out.println(
"Sample progName Empexp finished. " + new java.util.Date());
}
catch (SQLException ex)
{
// A SQLException was generated. Catch it and display
// the error information.
// Note that there could be multiple error objects chained
// together.
System.out.println();
System.out.println("*** SQLException caught ***");
while (ex != null)
{
System.out.println(" Error code: " + ex.getErrorCode());
System.out.println(" SQL State: " + ex.getSQLState());
System.out.println(" Message: " + ex.getMessage());
ex.printStackTrace();
System.out.println();
ex = ex.getNextException();
}
throw new IllegalStateException ("Sample failed.") ;
}
}
}

 

Please find below the error log :

Message: [Teradata JDBC Driver] [TeraJDBC 14.10.00.44] [Error 1006] [SQLState HY000] Unrecognized data type: 833
java.sql.SQLException: [Teradata JDBC Driver] [TeraJDBC 14.10.00.44] [Error 1006] [SQLState HY000] Unrecognized data type: 833
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:94)
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:69)
at com.teradata.jdbc.jdbc_4.parcel.PrepInfoParcel.createPrepInfoItem(PrepInfoParcel.java:226)
at com.teradata.jdbc.jdbc_4.parcel.StatementInfoParcel.translateMetadataItems(StatementInfoParcel.java:163)
at com.teradata.jdbc.jdbc_4.parcel.StatementInfoParcel.translateSIPintoPrepInfoItem(StatementInfoParcel.java:148)
at com.teradata.jdbc.jdbc_4.parcel.StatementInfoParcel.<init>(StatementInfoParcel.java:117)

at com.teradata.jdbc.jdbc_4.parcel.ParcelFactory.nextParcel(ParcelFactory.java:354)
at com.teradata.jdbc.jdbc_4.io.TDPacket.nextParcel(TDPacket.java:135)
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.getNextParcel(StatementReceiveState.java:295)
at com.teradata.jdbc.jdbc_4.statemachine.ReceiveSuccessSubState.action(ReceiveSuccessSubState.java:69)
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:307)
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:196)
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:123)
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.run(StatementController.java:114)
at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:386)
at com.teradata.jdbc.jdbc_4.TDStatement.prepareRequest(TDStatement.java:573)
at com.teradata.jdbc.jdbc_4.TDPreparedStatement.<init>(TDPreparedStatement.java:117)
at com.teradata.jdbc.jdk6.JDK6_SQL_PreparedStatement.<init>(JDK6_SQL_PreparedStatement.java:29)
at com.teradata.jdbc.jdk6.JDK6_SQL_Connection.constructPreparedStatement(JDK6_SQL_Connection.java:81)
at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1357)
at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1401)
at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1387)
at com.teradata.jdbc.jdbc.ManagerConnectionBase.prepareStatement(ManagerConnectionBase.java:281)
at com.teradata.tableoffload.Tera13DataOffload.main(Tera13DataOffload.java:52)
Exception in thread "main" java.lang.IllegalStateException: Sample failed.
at com.teradata.tableoffload.Tera13DataOffload.main(Tera13DataOffload.java:128)

Re: fastexport with JDBC

Also .Please tell me what are all the data type which are not supported by the Teradata JDBC.

I checked in

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BGBFBBEG

But it is not mentioned clearly.

 

Teradata Employee

Re: fastexport with JDBC

You say that you are "using teradata 13.0" and I see that your teradata_demo_table2 declares a couple of PERIOD data types. This is not supported.

Our Teradata JDBC Driver documentation, in section Period Data Types says that "Beginning with Teradata Database 13.10 and Teradata JDBC Driver 13.00.00.18, Period data types can be used with java.sql.Struct."
http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#CCHFADJ2

At minimum, you will need to use Teradata Database 13.10 and Teradata JDBC Driver 14.10 (when JDBC FastExport began support for PERIOD data types).

 

Teradata Employee

Re: fastexport with JDBC

The Teradata JDBC Driver support for FastExport is limited by the Teradata Database FastExport protocol. A good source of what data types the FastExport protocol does support is the Teradata FastExport Reference (B035-2410): http://www.info.teradata.com/download.cfm?ItemID=1004458 (Release 13.10).