John Smith John Smith - 3 months ago 54
R Question

Connecting R To Teradata VOLATILE TABLE

I am using R to try and connect to a teradata database and am running into difficulties
The steps in the process are below

1) Create Connection
2) Create a VOLATILE TABLE
3) Load information from a data frame into the Volatile table

Here is where it fails, giving me an error message

Error in sqlSave(conn, mydata, tablename = "TEMP", rownames = FALSE, :
first argument is not an open RODBC channel


The code is below

# Import Data From Text File and remove duplicates
mydata = read.table("Keys.txt")
mydata.unique = unique(mydata)

strSQL.TempTable = "CREATE VOLATILE TABLE TEMP………[Table Details]"
"UNIQUE PRIMARY INDEX(index)"
"ON COMMIT PRESERVE ROWS;"

# Connect To Database
conn <- tdConnect('Teradata')

# Execute Temp Table
tdQuery(strSQL.TempTable)
sqlSave(conn, mydata, tablename = "TEMP ",rownames = FALSE, append = TRUE)


Can anyone help, Is it closing off the connection before I can upload the information into the Table?

Answer

My Mistake, I have been confusing libraries

Basically the lines

 # Connect To Database
   conn <- tdConnect('Teradata')

 # Execute Temp Table
   tdQuery(strSQL.TempTable)
   sqlSave(conn, mydata, tablename = "TEMP ",rownames = FALSE, append = TRUE)

can all be replaced by this

# Connect To Database
channel <- odbcConnect('Teradata')
# Execute Temp Table
sqlQuery(channel, paste(strSQL.TempTable))

sqlSave(channel, mydata, table = "TEMP",rownames = FALSE, append = TRUE)

Now I'm being told, i don't have access to do this but this is another question for another forum

Thanks