Name and schema in DBI::Id

[troelsy]

I think I found a bug when working with DBI::Id. DBI::Id requires that you specify 'name' and 'schema', which makes sense, but the order of the arguments must be schema-name and not name-schema. It seems like it doesn't actually use the name of the arguments.

I made this example, to show the error. You can try with both 'table_id'

library(odbc)
library(DBI)

con <- dbConnect(odbc(),
                   Driver = "PostgreSQL Unicode",
                   Server = "...",
                   Database = "...",
                   UID = "...",
                   PWD = "...",
                   Port = 5432)

data <- data.frame("x" = c(1, 2), "y" = c(2,1))
print(data)


# table_id <- DBI::Id(name = "my_table", schema = "my_schema")
table_id <- DBI::Id(schema="my_schema", name = "my_table")
print(table_id)

DBI::dbWriteTable(con, table_id, data, overwrite = TRUE)

If you run it as is, you'll get:

$ Rscript test.R
Loading required package: methods
  x y
1 1 2
2 2 1
<Id> name = my_schema, schema = my_table

If you use the other definition of 'table_id', you'll get:

$ Rscript test.R
Loading required package: methods
  x y
1 1 2
2 2 1
<Id> name = my_table, schema = my_schema
Error: <SQL> 'CREATE TABLE "my_table"."my_schema" (
  "x" DOUBLE PRECISION,
  "y" DOUBLE PRECISION
)
'
  nanodbc/nanodbc.cpp:1587: 3F000: ERROR: schema "my_table" does not exist;
Error while executing the query
Execution halted

Notice the error message saying the schema is "my_table". I'm using Ubuntu 16.04.4 LTS with PostgreSQL 9.5.12

[ghost]

This issue was moved by krlmlr to r-dbi/odbc#214.