2

I am trying to copy a local dataframe from R to my db2 database. I have permissions to write to the table and I have verified the connection is working.

I am using:

copy_to(connection, data.frame, name = my_table_name)

I am getting the following error and it doesnt make sense to me. The object it says does not exist is the very object I am trying to create. What am I doing wrong?

Error in typeof(x) : object 'my_table_name' not found

creeperspeak
  • 5,403
  • 1
  • 17
  • 38
cowboy
  • 613
  • 5
  • 20
  • 1
    Try copy_to(connection, data.frame, name = "my_table_name") – davechilders Jan 10 '18 at 20:05
  • @DMC thanks for the reply. That seems to have bypassed the original error, however a new error has occurred: Error in new_result(connection@ptr, statement) : nanodbc/nanodbc.cpp:1344: 42000: [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token TEMPORARY was not valid. Valid tokens: SUMMARY. – cowboy Jan 10 '18 at 20:16
  • @DMC is this error generated because of the size of the table I am trying to copy? – cowboy Jan 10 '18 at 20:18
  • 1
    The fact that "Token" was mentioned indicates it is a permissions issue – davechilders Jan 10 '18 at 20:19

2 Answers2

6

by default, copy_to() tries to create a temporary table. An option is to add the argument temporary = FALSE, to overcome the TEMPORARY token error. A better solution is for the package that you are using to connect, which is hopefully the odbc package, is for it to add support for DB2 databases. In other words, odbc will need to know what is the specific command needed to properly create a temporary table. Once that's fixed, copy_to() will work. If it's indeed the odbc package that you are using, I would recommend that you open a GitHub issue in the package's repo.

edgararuiz
  • 625
  • 5
  • 9
1

This might be caused by an issue with DBI not dbplyr. See here. There is a work around that draws on the DBI package. you can more details on it here.

In summary: The DBI package has a write table command. It uses the same connection, but it also requires that the input table name is wrapped in SQL().

I tested the following and it worked as expected.

data(iris)
DBI::dbWriteTable(connection, SQL("database.schema.iris"), iris)
Simon.S.A.
  • 6,240
  • 7
  • 22
  • 41