All Forums Extensibility
hock 4 posts Joined 08/08
29 Nov 2012
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

ulrich 816 posts Joined 09/09
30 Nov 2012

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???

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

hock 4 posts Joined 08/08
30 Nov 2012

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)))

ulrich 816 posts Joined 09/09
01 Dec 2012

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

sundarvenkata 7 posts Joined 11/12
23 Jun 2014

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

tomnolan 594 posts Joined 01/08
24 Jun 2014

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

You must sign in to leave a comment.