How to capture chained JDBC SQLException error 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 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 SQLException error messages and stack trace:


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","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)")

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(12)) # inject duplicate key 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 error 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
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()
}
}

.jcall(ps,"V","close")

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) # 3 rows are selected (c1=12,14,16)
.jcall(rs,"V","close")

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