Clean up table creation approach

[colearendt]

copy_to is currently used for table creation. However, there are some issues related to the persistence of tables, schema references for temporary tables, etc. that makes it very challenging to clean up or use this in an interactive session.

This may be something that we try to take care of in our test suite by being "smart" about the different databases or it could be something that we push upstream to fixes in dplyr or DBI so that this use case is more easily tackled in R packages, generally.

For instance,

• On Postgres, temp tables are created in a random separate schema (pg_temp_1, for instance) and so cannot be dropped. In this case, dropping the connection and then creating a new one should work ok.
• On Oracle, something similar happens. I don't know enough about Oracle to know where the table "actually" lives. I presume they support temporary tables, so dropping the connection should be a fine workaround here, too
• DB2 does not support temporary tables. At least not in the syntax we are using

Error: <SQL> 'CREATE TEMPORARY TABLE "rqoshitjquoyizbfnanj" (
  "fld_factor" VARCHAR(255),
  "fld_datetime" VARCHAR(255),
  "fld_date" VARCHAR(255),
  "fld_time" VARCHAR(255),
  "fld_binary" INTEGER,
  "fld_integer" INTEGER,
  "fld_double" DOUBLE PRECISION,
  "fld_character" VARCHAR(255),
  "fld_logical" INTEGER
)
'
  nanodbc/nanodbc.cpp:1587: 42601: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "TEMPORARY" was found following "CREATE ".  Expected tokens may include:  "<space>".  SQLSTATE=42601 

• Impala does not support transactions:

 Error in connection_begin(conn@ptr) : 
  nanodbc/nanodbc.cpp:1128: HYC00: [RStudio][ODBC] (11470) Transactions are not supported.

[colearendt]

Information on DB2 temporary tables:

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/intro/src/tpc/db2z_creationoftemporarytables.html

[colearendt]

This is a constant thorn in my side. If you want to knit the README.md more than once, you have to change seeds... 😫