坑爹的RJDBC和Teradata

我实在是忍不住要抱怨了，哪有这样的半成品就给我们用的？RJDBC连TD是没有问题，可以读数据，但是dbWriteTable()写不回去！

dbWriteTable(jdbc.conn, name = "testdf", value =test_df)
Error in .verify.JDBC.result(s, "Unable to execute JDBC prepared statement ",  : 
  Unable to execute JDBC prepared statement INSERT INTO testdf VALUES(?,?) ([Teradata Database] 
[TeraJDBC 14.10.00.26] [Error 3932] [SQLState 25000] Only an ET or null statement is legal after a DDL Statement.)

[2016.1.12 更新]

亲爱的同事写了一个很方便用的r函数来解决这个问题，大家猛戳 -> github

以前的一些...

无奈之下，开始各种搜...除了大家都在抱怨的，有用的大概是两篇。抄过来。

第一篇讲dbWriteTable()的机制。

I have been able to reproduce the problem and look at the sourcecode of
RJDBC. The problem is that in dbWriteTable RJDBC disables autocommit
(and enables it again at the end), creates the table and then tries to
insert into it in the same transaction. Firebird does not allow inserts
into a table that is created in the same transaction.

As I see it, there are are two potential solutions:
1) Manually create the table and then use:
dbWriteTable(conn, "Tab_0", Tab, overwrite=FALSE, append=TRUE)

2) First execute dbWriteTable as normal to create the database (+ get
the error) and then append the data
dbWriteTable(conn, "Tab_0", Tab)
dbWriteTable(conn, "Tab_0", Tab, overwrite=FALSE, append=TRUE)

Other than that, I suggest you file a bug with the creators of RJDBC
that they should commit after the create table.

兴致冲冲的去试了一下，还是不行。没见过配合这么扭的，我建好了表去append吧，说我的表不存在；去建表吧，又说已经在了。坑爹啊！

dbWriteTable(jdbc.conn, name = "testdf", value =test_df, overwrite =F, row.names=F,append=T)
Error in .local(conn, name, value, ...) : 
  Cannot append to a non-existing table `testdf'
dbWriteTable(jdbc.conn, name = "testdf", value =test_df,  row.names=F,append=T)
Error in .local(conn, statement, ...) : 
  execute JDBC update query failed in dbSendUpdate ([Teradata Database] 
[TeraJDBC 14.10.00.26] [Error 3803] [SQLState 42S01] Table 'testdf' already exists.)

然后又一阵苦搜，看遍了Teradata JDBC driver的文档之类的，最后觉得唯一靠谱的是这篇：

library(RJDBC)
################
#def functions
################
myinsert <- function(arg1,arg2){
  .jcall(ps,"V","setInt",as.integer(1),as.integer(arg1))
  .jcall(ps,"V","setString",as.integer(2),arg2)
  .jcall(ps,"V","addBatch")
}

MHmakeRandomString <- function(n=1, lenght=12)
{
  randomString <- c(1:n)                  # initialize vector
  for (i in 1:n)
  {
    randomString[i] <- paste(sample(c(0:9, letters, LETTERS),
                                    lenght, replace=TRUE),
                             collapse="")
  }
  return(randomString)
}

################
#DB Connect
################
.jaddClassPath("/MyPath/terajdbc4.jar")
.jaddClassPath("/MyPath/tdgssconfig.jar")
drv = JDBC("com.teradata.jdbc.TeraDriver","/MyPath/tdgssconfig.jar","/MyPath/terajdbc4.jar")
conn = dbConnect(drv,"jdbc:teradata://MyServer/CHARSET=UTF8,LOG=ERROR,DBS_PORT=1025,TYPE=FASTLOAD,TMODE=TERA,SESSIONS=1","user","password")

################
#main
################

##gen test data
dim = 1000000
i = 1:dim
s = MHmakeRandomString(dim,12)

## set up table
dbSendUpdate(conn,"drop table foo;")
dbSendUpdate(conn,"create table foo (a int, b varchar(100));")

#set autocommit false
.jcall(conn@jc,"V","setAutoCommit",FALSE)
##prepare
ps = .jcall(conn@jc,"Ljava/sql/PreparedStatement;","prepareStatement","insert into foo values(?,?)")

#start time
ptm <- proc.time()

## batch insert
for(n in 1:dim){
  myinsert(i[[n]],s[[n]])
}
#run time
proc.time() - ptm

#apply & commit
.jcall(ps,"[I","executeBatch")
dbCommit(conn)
.jcall(ps,"V","close")
.jcall(conn@jc,"V","setAutoCommit",TRUE)

#get some sample results
dbGetQuery(conn,"select top 100 * from foo")
dbGetQuery(conn,"select count(*) from foo")

#disconnect
dbDisconnect(conn)

等心情好的时候我去试试....大坑的感觉 T_T ODBC用起来怎么就没有那么多事儿呢？

update Jan 13 2015

居然teradataR可以用...

http://enoriver.net/index.php/2014/12/15/i-wrote-an-r-data-frame-to-a-teradata-table-on-a-mac/

Here's how I did it:

1. On a new Mac running Mavericks and R 3.1.2 with devtools, I installed Java for Mac.
2. I installed the RJDBC package from CRAN (which depends on the DBI package also from CRAN) and the teradataR package from GitHub.
3. I downloaded the Teradata JDBC driver, unpacked it, and moved tdgssconfig.jar and terajdbc4.jar to /System/Library/Java/Extensions.

After that, writing the data frame foo to the table DATABASE.BAR was as simple as:

conn <- teradataR::tdConnect(dsn='datamart.mycompany.com',
                             uid='user',pwd='pass', 
                             dType='jdbc')
teradataR::tdWriteTable(databasename='DATABASE', 
                        tablename='BAR', df=foo)
teradataR::tdClose(conn)

I had to do this because DBI::dbWriteTable() now fails on Teradata as explained here.