The following batches 5 rows of data, 2 of them with errors. The JDBC FastLoad PreparedStatement.executeBatch attempts to INSERT the batched rows into a database table but throws a JDBC SQLException. Here is how to capture the chain of JDBC FastLoad SQLException messages and stack trace from JDBC FastLoad PreparedStatement.executeBatch and the chain of JDBC FastLoad SQLWarning messages and stack trace from JDBC FastLoad Connection.rollback:

drv = JDBC("com.teradata.jdbc.TeraDriver","c:\\terajdbc\\terajdbc4.jar;c:\\terajdbc\\tdgssconfig.jar")
con = .jcall("java/sql/DriverManager","Ljava/sql/Connection;","getConnection", "jdbc:teradata://system/TYPE=FASTLOAD","user","password")
s = .jcall(con, "Ljava/sql/Statement;", "createStatement")

.jcall(s, "I", "executeUpdate", "drop table foo")
.jcall(s, "I", "executeUpdate", "create table foo(c1 integer check(c1 between 10 and 20),c2 varchar(100)) unique primary index (c1)")

.jcall(con, "V", "setAutoCommit",FALSE)
ps = .jcall(con, "Ljava/sql/PreparedStatement;", "prepareStatement","insert into foo values(?,?)")

.jcall(ps,"V","setInt",as.integer(1),as.integer(12))
.jcall(ps,"V","setString",as.integer(2),"bar1")
.jcall(ps,"V","addBatch") # row 1

.jcall(ps,"V","setInt",as.integer(1),as.integer(23)) # inject constraint violation error
.jcall(ps,"V","setString",as.integer(2),"bar2")
.jcall(ps,"V","addBatch") # row 2

.jcall(ps,"V","setInt",as.integer(1),as.integer(14))
.jcall(ps,"V","setString",as.integer(2),"bar3")
.jcall(ps,"V","addBatch") # row 3

.jcall(ps,"V","setInt",as.integer(1),as.integer(25)) # inject constraint violation error
.jcall(ps,"V","setString",as.integer(2),"bar4")
.jcall(ps,"V","addBatch") # row 4

.jcall(ps,"V","setInt",as.integer(1),as.integer(16))
.jcall(ps,"V","setString",as.integer(2),"bar5")
.jcall(ps,"V","addBatch") # row 5

# capture chained JDBC SQLException messages and stack trace from PreparedStatement.executeBatch()
.jcall(ps,"[I","executeBatch", check=FALSE) # disable the default jcall exception handling with check=FALSE
ex = .jgetEx() # save exceptions from PreparedStatement.executeBatch()
.jclear() # clear all pending exceptions
if (!is.jnull(ex)) {
    while (!is.jnull(ex)) { # loop thru chained exceptions
        sw = .jnew("java/io/StringWriter")
        pw = .jnew("java/io/PrintWriter",.jcast(sw, "java/io/Writer"),TRUE)
        .jcall(ex,"V","printStackTrace",pw) # redirect printStackTrace to a Java PrintWriter so it can be printed in Rterm AND Rgui
        if (ex %instanceof% "java.sql.BatchUpdateException") {
            print(.jcall(ex,"[I","getUpdateCounts")) # print int[] update count showing 3 rows inserted successfully (1) and 2 rows failed to insert (-3)
        }
        cat(.jcall(sw,"Ljava/lang/String;","toString")) # print the error message and stack trace
        if (ex %instanceof% "java.sql.SQLException") {
            ex = ex$getNextException()
        } else {
            ex = ex$getCause()
        }
    }

    # capture chained JDBC SQLWarning messages and stack trace from Connection.rollback()
    .jcall(con, "V", "rollback")
    w = .jcall(con, "Ljava/sql/SQLWarning;", "getWarnings") # save warnings from Connection.rollback()
    while (!is.jnull(w)) { # loop thru chained warnings
        sw = .jnew("java/io/StringWriter")
        pw = .jnew("java/io/PrintWriter",.jcast(sw, "java/io/Writer"),TRUE)
        .jcall(w,"V","printStackTrace",pw) # redirect printStackTrace to a Java PrintWriter so it can be printed in Rterm AND Rgui
        cat(.jcall(sw,"Ljava/lang/String;","toString")) # print the warning message and stack trace
        w = w$getNextWarning()
    }
} else {
    .jcall(con, "V", "commit")
}

.jcall(ps,"V","close")
.jcall(con, "V", "setAutoCommit",TRUE)

rs = .jcall(s, "Ljava/sql/ResultSet;", "executeQuery", "select * from foo order by 1")
md = .jcall(rs, "Ljava/sql/ResultSetMetaData;", "getMetaData")
jr = new("JDBCResult", jr=rs, md=md, stat=s, pull=.jnull())
fetch(jr, -1) # 0 rows are selected
.jcall(rs,"V","close")

.jcall(s,"V","close")
.jcall(con,"V","close")

A sample Java program that illustrates the use of JDBC FastLoad can be found here.

Discussion
ulrich 51 comments Joined 09/09
04 Feb 2014

Hi Amarek,
thanks for that! Was a great help. I would not figured this out myself.
in line 33 it seems need to be 

ex = .jgetEx() # save exceptions from PreparedStatement.executeBatch()

Where is this stuff documented?
And how could I add nulls to  integers columns for example?
Regards Ulrich
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

amarek 1 comment Joined 04/09
06 Feb 2014

Hi Ulrich.

 
RE: line 33
Thanks for catching that. The example is now fixed.

 
RE: Where is this stuff documented?
Documentation is sparse to non-existent when it comes to using R as I show in my example. I know how to code Java using the JDBC Driver, so I downloaded the rJava and rJDBC source code and documention to find the R functions required to execute the R version of a JDBC PreparedStatement batch INSERT written in Java and manage some exceptions. I use the basic R functions because they give the most flexibility for what I needed.

 
RE: add nulls to integers columns
The following effectively calls JDBC PreparedStatement.setNull(int parameterIndex, int sqlType) with parameterIndex=1 and sqlType=4 (java.sql.Types.INTEGER):
    .jcall(ps,"V","setNull",as.integer(1),as.integer(4))

 
Regards,
Andreas
 

You must sign in to leave a comment.