Name and schema in DBI::Id

[ghost]

@troelsy commented on Aug 9, 2018, 8:03 AM UTC:

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

This issue was moved by krlmlr from r-dbi/DBI#255.

[krlmlr]

Thanks. The preferred syntax is Id(schema = "...", table = "..."), but indeed the names should be respected so that Id(table = "...", schema = "...") works and Id(name = "...", schema = "...") either works or fails consistently. @jimhester: Does this ring a bell?

[wibeasley]

Regarding name vs table, is this related to @danielwo's post in #191 (comment)?

[jimhester]

So what @krlmrl, what is the desired resolution here? I don't really want to put Id validating code throughout all the odbc methods...

[krlmlr]

I think we need to access the components of an Id by name. What identifier components does ODBC support, does the interface specify any restrictions? Can we go with (a subset of) "database", "catalog", "schema", "table", is this the correct order?

[jimhester]

Yeah, that order and naming is right

[jimhester]

This seems now to be resolved, we now use the names of the Id rather than the position.