Search code examples
rdplyrodbcdbplyr

Using ODBC::dbConnect and dplyr to connect to Sybase IQ database - table name not found

I can connect to my Sybase IQ 16 database using a connection string such as:

myDB_conn <- dbConnect(odbc(), "MyDSN_Name")

When I run this command the connections view shows a list of databases and corresponding tables/views. However when I try to use the logic laid out here specifically naming the view I keep receiving an error about my view not being found.

test <- tbl(myDB_conn, "OFFSHORE_BOB.SOME_VIEW_OR_TABLE_NAME")

In the connection window I can see the database and view but when I use the code above I receive a table name not found.

nanodbc/nanodbc.cpp:1374: 42S02: [Sybase][ODBC Driver][Sybase IQ]Table 'OFFSHORE_BOB.SOME_VIEW_OR_TABLE_NAME' not found

I have also looked at listing the tables in the OFFSHORE_BOB schema and I can see the table of interest in there:

y <- dbListTables(gopher_conn, schema = 'OFFSHORE_BOB')

I then searched for the view of interest and I can see it there so I am stumped as to why I can not use this code

test <- tbl(myDB_conn, "OFFSHORE_BOB.SOME_VIEW_OR_TABLE_NAME")

enter image description here

Any suggestions would be greatly appreciated.

Solution

  • You most likely want the in_schema command from dbplyr. For example:

    schema = "OFFSHORE_BOB"
tbl_name = "SOME_VIEW_OR_TABLE_NAME"

remote_table <- tbl(db_connection, from = in_schema(schema, tbl_name))

    Then show_query(remote_table) should return something like:

    SELECT *
FROM "OFFSHORE_BOB"."SOME_VIEW_OR_TABLE_NAME"

    (The quotation marks are inserted by dbplyr in case your table or schema name contains non-standard characters. E.g. abc-123 and 3-4 are not acceptable table names by themselves, but quoted "abc-123" and "3-4" they can be table or schema names.)

    Note that you can also include a database name in with the schema name should you need to:

    remote_table <- tbl(db_connection, from = in_schema(paste0(db,".",schema), table_name))