Writing a data frame to a Teradata table using RJDBC

Question

After making the connection, as described here , I tried to write a very simple data frame ( trythis , shown below) into a table called gh_test_20141105 in a database called p_cia_t . First i tried

 > conn <- getTdConnection(vdm='vivaldi') > dbWriteTable(conn=conn,name=tbl,value=trythis) Error in .verify.JDBC.result(s, "Unable to execute JDBC prepared statement ", : Unable to execute JDBC prepared statement INSERT INTO p_cia_t.gh_test_20141005 VALUES(?,?,?,?) ([Teradata Database] [TeraJDBC 14.10.00.17] [Error 3932] [SQLState 25000] Only an ET or null statement is legal after a DDL Statement.) 

Then I created an empty table in Teradata:

create table p_cia_t.gh_test_20141105 (eenie integer, meenie integer, minie integer, moe integer);

My next two attempts to write this data frame occurred in conflicting ways:

  > dbWriteTable(conn=conn,name=tbl,value=trythis,append=T) Error in .local(conn, statement, ...) : execute JDBC update query failed in dbSendUpdate ([Teradata Database] [TeraJDBC 14.10.00.17] [Error 3803] [SQLState 42S01] Table 'gh_test_20141105' already exists.) > dbWriteTable(conn=conn,name=tbl,value=trythis,append=T,overwrite=F) Error in .local(conn, name, value, ...) : Cannot append to a non-existing table `p_cia_t.gh_test_20141105' > trythis eenie meenie minie moe 1 1 4 7 10 2 2 5 8 11 3 3 6 9 12 > conn An object of class "JDBCConnection" Slot "jc": [1] "Java-Object{ com.teradata.jdbc.jdk6.JDK6_SQL_Connection@2f242b11 }" Slot "identifier.quote": [1] NA > tbl [1] "p_cia_t.gh_test_20141105" 

Why does the append=T object, that the table already exists, and how to add overwrite=F change the objection to a "nonexistent table?"? It makes no sense.

I would really appreciate any working example of successfully writing a data frame to a Teradata table, whether previously or not, with or without adding.

 > sessionInfo() R version 3.1.2 (2014-10-31) Platform: x86_64-apple-darwin13.4.0 (64-bit) locale: [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8 attached base packages: [1] stats graphics grDevices utils datasets methods base other attached packages: [1] RJDBC_0.2-4 rJava_0.9-6 DBI_0.3.1 dplyr_0.3.0.2 scales_0.2.4 ggplot2_1.0.0 reshape2_1.4 [8] RODBC_1.3-10 data.table_1.9.4 loaded via a namespace (and not attached): [1] assertthat_0.1 chron_2.3-45 colorspace_1.2-4 digest_0.6.4 grid_3.1.2 gtable_0.1.2 magrittr_1.0.1 [8] MASS_7.3-35 munsell_0.4.2 parallel_3.1.2 plyr_1.8.1 proto_0.3-10 Rcpp_0.11.3 stringr_0.6.2 [15] tools_3.1.2 

Answer 1

I think this is due to a bug in the Teradata JDBC driver. As explained here , the reason is because

The problem is that in dbWriteTable, RJDBC disables autocommit (and enables it again at the end), creates a table and then tries to insert into it in the same transaction. Firebird does not allow insertion into a table created in the same transaction.

This page seems to offer a solution, but I think that, in principle, we may have to wait until TD fixes the error ...

My temporary solution is to access ODBC (a small table) or call fastload directly through the system("fastload < your_fastload_script") (large table) ...