CODEWITHSUNDEEP
sqlrr-dbi
radiskull
radiskull

Reputation: 23

dbReadTable won't pull data but dbListFields will see correct fields

I am trying to pull data from a SQL database that I have access to. I can connect to the database, see the tables and get the fields associated with a given table, but cannot read a table into an R variable.

I'm working in R Studio, in case this makes a difference.

I have tried using online code snippets (new to R) and these work, except for the dbReadTable() examples. I have used both "Payments" and name="Payments" as the second argument, and both with and without "" quotes.

library(DBI)
con<-(dbConnect(odbc::odbc(), .connection_string="Driver={SQL Server},
Server=example_1234
Database=exampleDB
TrustedConnection=TRUE")

testing123 <- dbListFields(con,"Payments")
testing456 <- dbReadTable(con,"Payments")

I expect a connection to the database which is now named con. This works. I expect testing123 to contain all the fields in "Payments". This also works. I expect testing456 to be a data.frame copy of Payments. This produces: Error: 'SELECT * FROM "Payments" nanodbc/nanobdc.cpp:1587 42s02 [Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]Invalid pbject name 'Payments'.

It's slightly different without "Payments" as the argument - simply saying "Object "Payments" not found".

Any help much appreciated.

Upvotes: 1

Views: 853

Answers (2)

r2evans
r2evans

Reputation: 160792

I suspect that it's because your table is in a different catalog or schema.

Rationale: DBI::dbListFields is doing select * from ... limit 0 (which is not correct syntax for sql server), but odbc::dbListFields is really calling a C++ function connection_sql_columns that is SQL Server specific. It might be permitting you to be a touch sloppy in that it will find the table even if you do not specify the catalog and/or schema. This is why your dbListFields is working. However, DBI::dbReadTable is really doing select * from ... under the hood (and odbc:: is not overriding it), so it is not allowing you to omit the schema (and/or catalog).

First, find the specific table information for your case:

DBI::dbGetQuery(con, "select top 1 table_catalog, table_schema, table_name, column_name from information_schema.columns where table_name='events'")
#   table_catalog table_schema table_name column_name
# 1    my_catalog          dbo   Payments          Id

(I'm projecting what you'll find.)

From here, try one of the following until it works:

x <- DBI::dbReadTable(con, DBI::SQL("[Payments]")) # equivalent to the original
x <- DBI::dbReadTable(con, DBI::SQL("[dbo].[Payments]"))
x <- DBI::dbReadTable(con, DBI::SQL("[my_catalog].[dbo].[Payments]"))

My guess is that DBI::dbGetQuery(con, "select top 1 * from Payments") will not work, so for "regular queries" you'll need to use the same hierarchy of catalog.schema.table, such as one of

DBI::dbGetQuery(con, "select top 1 * from dbo.Payments")
DBI::dbGetQuery(con, "select top 1 * from [dbo].[Payments]")
DBI::dbGetQuery(con, "select top 1 * from [my_catalog].[dbo].[Payments]")

(The use of the [ and ] quoted-identifier brackets are often a personal preference, strictly required in only some corner cases.)

Upvotes: 2

Kris
Kris

Reputation: 420

Try changing your con argument just slightly: 

con <- DBI::dbConnect(odbc::odbc(),
    Driver = "SQL Server",
    Server = "example_1234",
    Database = "exampleDB",
    TrustedConnection = TRUE)

# read table to df
testing456 <- dbReadTable(con,"Payments")

# you can also use SQL queries directly, such as:
testing789 <- dbGetQuery(con, statement = "SELECT * FROM Payments WHERE ...")

Upvotes: 0

Related Questions