How to use JDBC FastExport 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 select rows from a database table using JDBC FastExport, which only works with JDBC PreparedStatement.

If the SELECT statement has no '?' parameter markers, then...

drv = JDBC("com.teradata.jdbc.TeraDriver","c:\terajdbc\terajdbc4.jar;c:\terajdbc\tdgssconfig.jar")
con = dbConnect(drv,"jdbc:teradata://system/TYPE=FASTEXPORT","user","password")

# initialize table foo
dbSendUpdate(con,"drop table foo")
dbSendUpdate(con,"create table foo(a int,b varchar(100))")
dbSendUpdate(con,"insert into foo values(?,?)", 42, "bar1")
dbSendUpdate(con,"insert into foo values(?,?)", 43, "bar2")

# select * from table foo
ps = .jcall(con@jc, "Ljava/sql/PreparedStatement;", "prepareStatement","select * from foo")
rs = .jcall(ps, "Ljava/sql/ResultSet;", "executeQuery")
md = .jcall(rs, "Ljava/sql/ResultSetMetaData;", "getMetaData")
jr = new("JDBCResult", jr=rs, md=md, stat=ps, pull=.jnull())
fetch(jr, -1)
.jcall(rs,"V","close")
.jcall(ps,"V","close")

dbDisconnect(con)

If the SELECT statement has '?' parameter markers, then...

drv = JDBC("com.teradata.jdbc.TeraDriver","c:\terajdbc\terajdbc4.jar;c:\terajdbc\tdgssconfig.jar")
con = dbConnect(drv,"jdbc:teradata://system/TYPE=FASTEXPORT","user","password")

# initialize table foo
dbSendUpdate(con,"drop table foo")
dbSendUpdate(con,"create table foo(a int,b varchar(100))")
dbSendUpdate(con,"insert into foo values(?,?)", 42, "bar1")
dbSendUpdate(con,"insert into foo values(?,?)", 43, "bar2")

# select * from table foo with '?' parameter marker
dbGetQuery(con,"select * from foo where a=?",as.integer(43))

dbDisconnect(con)