3

I am wondering if there is a way to retrieve the SQL query which dbWriteTable sends to the DBMS. For example, for the following example. Is there a way to get the query?

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars[1:10, ])
dbDisconnect(con)

EDIT: 2016-11-11

As a Postgres user, I would be most interested in the commands sent using RPostegreSQL. Following @krlmlr, I figured that there is a function postgresqlCopyInDataframewhich itself calls the C function RS_PostgreSQL_CopyInDataframe. So I hit a dead-end here as C is beyond my skills. Any ideas welcome...

Mark Heckmann
  • 10,943
  • 4
  • 56
  • 88

1 Answers1

4

The development version of RSQLite, which will soon hit CRAN, uses DBI::sqlCreateTable(). This function returns SQL that creates a particular table:

con <- dbConnect(RSQLite::SQLite(), ":memory:")
sqlCreateTable(con, "mtcars", mtcars[1:10, ])
dbDisconnect(con)

For other drivers you could look up the method definition in the driver's source code, e.g., via showMethods("dbWriteTable", includeDefs = TRUE) and proceed from there.

krlmlr
  • 25,056
  • 14
  • 120
  • 217