How to provide correct arguments to setAsciiStream method?

Question

This is my FULL test code with the main method:

public class TestSetAscii {

    public static void main(String[] args) throws SQLException, FileNotFoundException {
        String dataFile = "FastLoad1.csv";
        String insertTable = "INSERT INTO " + "myTableName" + " VALUES(?,?,?)";
        Connection conStd = DriverManager.getConnection("jdbc:xxxxx", "xxxxxx", "xxxxx");
        InputStream dataStream = new FileInputStream(new File(dataFile));
        PreparedStatement pstmtFld  = conStd.prepareStatement(insertTable);
            // Until this line everything is awesome

        pstmtFld.setAsciiStream(1, dataStream, -1); // This line fails
        System.out.println("works");
    }
}

I get the "cbColDef value out of range" error

Exception in thread "main" java.sql.SQLException: [Teradata][ODBC Teradata Driver] Invalid precision: cbColDef value out of range
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.SQLBindInParameterAtExec(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setStream(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setAsciiStream(Unknown Source)
    at file.TestSetAscii.main(TestSetAscii.java:21)

Here is the link to my FastLoad1.csv file. I guess that setAsciiStream fails because of the FastLoad1.csv file , but I am not sure

(In my previous question I was not able to narrow down the problem that I had. Now I have shortened the code.)

epinuj · Accepted Answer · 2013-11-24T00:50:49.057

2

It would depend on the table schema, but the third parameter of setAsciiStream is length. So

pstmtFld.setAsciiStream(1, dataStream, 4);

would work for a field of length 4 bytes.

But I dont think it would work as you expect it in the code. For each bind you should have separate stream.

This function setAsciiStream() is designed to be used for large data values BLOBS or long VARCHARS. It is not designed to read csv file line by line and split them into separate values.

Basicly it just binds one of the question marks with the inputStream.

After looking into the provided example it looks like teradata could handle csv but you have to explicitly tell that with:

String urlFld = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8,TYPE=FASTLOADCSV";

edited Nov 24 '13 at 00:50

answered Nov 24 '13 at 00:32

epinuj

142
2

+1 Thanks. This actually works. But I thought that "-1" would imply a field of any length (unlimited). how come "-1" does not work – Borat Sagddiev Nov 24 '13 at 00:37
When you say "bind" do you mean column? The question mark of the prepared statement? In here http://developer.teradata.com/doc/connectivity/jdbc/reference/current/samp/T20208JD.java.txt there is only one stream – Borat Sagddiev Nov 24 '13 at 00:41

score 1 · Answer 2 · answered Apr 11 '14 at 20:38

I don't have enough reputation to comment, but I feel that this info can be valuable to those navigating fast load via JDBC for the first time.

This code will get the full stack trace and is very helpful for diagnosing problems with fast load:

catch (SQLException ex){
    for ( ; ex != null ; ex = ex.getNextException ())
        ex.printStackTrace () ;
}

In the case of the code above, it works if you specify TYPE=FASTLOADCSV in the connection string, but when run multiple times will fail due to the creation of the error tables _ERR_1 and _ERR_2. Drop these tables and clear out the destination tables to run again.

How to provide correct arguments to setAsciiStream method?

2 Answers2