How to capture chained JDBC FastLoad SQLException/SQLWarning messages and stack trace with R

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

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.

2 Comments
Senior Supporter

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

Teradata Employee

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