0

I have been trying to figure out how to write a data frame to a volatile table on a teradata server. I was trying to follow this example but am not having a lot of luck.

Here's my attempt:

pass = getPass(msg = "Network Password:")
edv = odbcConnect(dsn = "", uid = "", pwd = pass)
rm(pass)

# So I believe we'll first have to create a table:
quer1 = paste("CREATE VOLATILE TABLE testdat",
               "(op_sys INT, enter_final_icu VARCHAR(20), mrn VARCHAR(8))",
               "UNIQUE PRIMARY INDEX(op_sys)",
               "ON COMMIT PRESERVE ROWS;")
sqlQuery(edv, quer1)

# This returns a zero row table, so far so good:
quer2 = "select top 10 * from testdat"
sqlQuery(edv, quer2)

# See if we can save the data:
columnTypes <- list(op_sys = "INT", 
                    enter_final_icu = "VARCHAR(20)", 
                    mrn = "VARCHAR(8)")
# pop is a data.frame with 3 the same 3 columns as in the definition of testdat
sqlSave(channel = edv, 
        dat = pop, 
        tablename = 'testdat', 
        append = TRUE, 
        rownames = FALSE, 
        varTypes = columnTypes)


sqlUpdate(channel = edv, 
        dat = pop, 
        tablename = 'testdat')

The sqlSave statement tells me that 'table testdat already exists' which is what I thought I wanted to do. I thought update = TRUE would let me add data to the table...

The sqlUpdate statement tells me that 'testdat: table not found' which is pretty odd given that I'm able to query it.

Any suggestions would be appreciated, thanks!

makeyourownmaker
  • 1,558
  • 2
  • 13
  • 33
svenhalvorson
  • 1,090
  • 8
  • 21
  • It might be due to the temporary status of volatile tables. Try running your `sqlSave` directly after your `sqlQuery` that runs `CREATE`. – Parfait Feb 25 '19 at 19:46
  • In brief experiments with R and Teradata, I've never had much success with volatile tables. I hit the same error with sqlSave you do. Works fine with permanent tables. – Andrew Feb 25 '19 at 20:20
  • 1
    This is a bit of a guess since I don't know how R determines if the volatile table exists (in order to determine if the table needs to be created or not). It *COULD* be that R is relying on the default database of the ODBC connection in Teradata to qualify your unqualified table name. For fun, try setting your `tablename` of your `sqlsave` command to the username you are using to connect appending `.testdat` to it. So if your username in Teradata `bobmcbob` then `tablename = 'bobmcbob.testdat',` – JNevill Feb 25 '19 at 22:00
  • @JNevill - I've tried it with `my_id.volatile_table_name` and `volatile_table_name`. No difference in behavior, fails with the same error for both. – Andrew Feb 25 '19 at 22:26
  • Darn. Thought we may get lucky with that one. It's strange that it thinks it doesn't exist. I'd vlbe very curious to know how it's assessing that under the covers. – JNevill Feb 25 '19 at 22:36
  • BTW, looking at the link the OP posted, it looks like that was done using a package called teradataR, which doesn't seem to exist in the cran repositories. Looks like you can download it from GitHub, proceed at your own risk, your mileage may vary, etc... – Andrew Feb 25 '19 at 22:48

0 Answers0