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
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)))
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
Do the recent Teradata JDBC drivers fix this issue at all?
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
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