CODEWITHSUNDEEP
javaascii
Borat Sagddiev
Borat Sagddiev

Reputation: 837

How to provide correct arguments to setAsciiStream method?

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.)

Upvotes: 1

Views: 467

Answers (2)

sakurashinken
sakurashinken

Reputation: 4080

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.

Upvotes: 1

epinuj
epinuj

Reputation: 142

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";

Upvotes: 2

Related Questions