How to use JDBC PreparedStatement batch INSERT with R

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

The following shows how to batch 2 rows of data, then invoke JDBC PreparedStatement.executeBatch to INSERT them into a database table.

JDBC PreparedStatement.executeBatch returns an array of integers that must be checked to confirm all batched rows have been inserted successfully.

JDBC FastLoad can be used by changing con = dbConnect(drv,"jdbc:teradata://system/TYPE=FASTLOAD","user","password").

drv = JDBC("com.teradata.jdbc.TeraDriver","c:\terajdbc\terajdbc4.jar;c:\terajdbc\tdgssconfig.jar")
con = dbConnect(drv,"jdbc:teradata://system","user","password")
dbSendUpdate(con,"drop table foo")
dbSendUpdate(con,"create table foo(a int,b varchar(100))")
ps = .jcall(con@jc, "Ljava/sql/PreparedStatement;", "prepareStatement","insert into foo values(?,?)")
.jcall(ps,"V","setInt",as.integer(1),as.integer(42))
.jcall(ps,"V","setString",as.integer(2),"bar1")
.jcall(ps,"V","addBatch")
.jcall(ps,"V","setInt",as.integer(1),as.integer(43))
.jcall(ps,"V","setString",as.integer(2),"bar2")
.jcall(ps,"V","addBatch")
.jcall(ps,"[I","executeBatch")
.jcall(ps,"V","close")
dbReadTable(con,"foo")
dbDisconnect(con)

JDBC PreparedStatement.executeBatch may be called more than once. JDBC FastLoad then requires that auto-commit be false and one explicit commit be executed after the last JDBC PreparedStatement.executeBatch.

drv = JDBC("com.teradata.jdbc.TeraDriver","c:\terajdbc\terajdbc4.jar;c:\terajdbc\tdgssconfig.jar")
con = dbConnect(drv,"jdbc:teradata://system/TYPE=FASTLOAD","user","password")
dbSendUpdate(con,"drop table foo")
dbSendUpdate(con,"create table foo(a int,b varchar(100))")
.jcall(con@jc, "V", "setAutoCommit",FALSE)
ps = .jcall(con@jc, "Ljava/sql/PreparedStatement;", "prepareStatement","insert into foo values(?,?)")
.jcall(ps,"V","setInt",as.integer(1),as.integer(42))
.jcall(ps,"V","setString",as.integer(2),"bar1")
.jcall(ps,"V","addBatch")
.jcall(ps,"[I","executeBatch")
.jcall(ps,"V","setInt",as.integer(1),as.integer(43))
.jcall(ps,"V","setString",as.integer(2),"bar2")
.jcall(ps,"V","addBatch")
.jcall(ps,"[I","executeBatch")
dbCommit(con)
.jcall(ps,"V","close")
.jcall(con@jc, "V", "setAutoCommit",TRUE)
dbReadTable(con,"foo")
dbDisconnect(con)

2 Comments
Senior Supporter

Hi Amarek,

good post. But it would be even nicer of the code section could be copied and would not require a complete retyping for the validation ;-).

Ulrich

Teradata Employee

There is a problem currently with Developer Exchange that prevents posting certain SQL statements. Posting code as an image is a temporary workaround.

After the Developer Exchange problem is resolved, we will repost the code as text.