Teradata R - create table based on a data frame using JDBC

Extensibility
Enthusiast

Teradata R - create table based on a data frame using JDBC

Hi All,



I am trying a solve an ssue where creating a new table and inserting data that results in error such as this: [Error 3932] [SQLState 25000] Only an ET or null statement is legal after a DDL Statement.  



The operation is very simple, we basically look at a set of data, massage it using R and try to insert back into a table.



Here are the sample script I modified from TD Exchange:



library(RJDBC)

library(teradataR)

tdConnect("123.123.123.123","hlim","password","sys_calendar","jdbc")

tdf <- td.data.frame("calendar")

my.stats <- td.stats(tdf,"year_of_calendar", c("cnt","min","max","mean"))



dbWriteTable(conn = tdConnection, name = "DWSP_ETL_STG.testme", value = my.stats, row.names = F, overwrite = T, append = T)



The last line is the problem where Error 3932 occurs.



We tried the normal dbConnect method and put in TMODE=ANSI or TERA but that does not help either.

For example, 

drv = JDBC("com.teradata.jdbc.TeraDriver","c:/teradatajdbc/terajdbc4.jar") 

conn = dbConnect(drv,"jdbc:teradata://111.111.111.111/TMODE=TERA",user="hlim",password="1234",dbname="hlim")

dbWriteTable(conn = tdConnection, name = "DWSP_ETL_STG.testme", value = my.stats, row.names = F, overwrite = T, append = T)

Many thanks for your help!!!!



Hock

Tags (2)
5 REPLIES
Senior Supporter

Re: Teradata R - create table based on a data frame using JDBC

I was also not able to do it.

DBQL states that both operations dbWriteTable and as.td.data.frame start with an BT; before the CREATE table is executed.

And in this case the TMODE=TERA does not help as in explizit transaction was started.

So question is if someone ever had been able to load data via as.td.data.frame and JDBC???

Enthusiast

Re: Teradata R - create table based on a data frame using JDBC

Thank you Ulrich!  We will go with the sqlSave RODBC route for now.  The fast=true option does speed up the insertion due to parameterized inserts but does have some datatype issue such as Date.  But my colleage figured out a way to do so by using varTypes:  

Test <- sqlQuery (channel = channel, query = "select * from sys_calendar.calendar where calendar_date<= (date - 10)")

sqlDrop(channel = channel, "DWSP_ETL_STG.testme")

sqlSave(channel = channel, dat = Test[1:10000,], tablename = "DWSP_ETL_STG.testme", append = F, rownames = F, colnames = FALSE, verbose = F, safer = TRUE, addPK = F, fast = T, test = FALSE, nastring = NULL, varTypes = unlist(lapply(Test, class)))

Senior Supporter

Re: Teradata R - create table based on a data frame using JDBC

Yes, looks like ODBC will do the trick but I would be also very interested in a JDBC solution...

Enthusiast

Re: Teradata R - create table based on a data frame using JDBC

Do the recent Teradata JDBC drivers fix this issue at all?

Teradata Employee

Re: Teradata R - create table based on a data frame using JDBC

Error 3932 is a Teradata Database error, not a Teradata JDBC Driver problem.

Please read Andreas Marek's blog for information about how to use the Teradata JDBC Driver with R.

http://developer.teradata.com/blog/amarek