0

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.

TheGoat
  • 2,587
  • 3
  • 25
  • 58
  • 1
    What is `OFFSHORE_BOB`? The database or schema? If database, connections usually connect directly to databases, so this period qualifying name is not necessary. If schema, look into `in_schema()`. – Parfait Jul 04 '20 at 00:26
  • Try `tbl(myDB_conn, "SOME_VIEW_OR_TABLE_NAME", schema("OFFSHORE_BOB"))` – r2evans Jul 04 '20 at 00:41
  • @r2evans I tried your suggestion but I am getting the same error. Thanks for the suggestion. – TheGoat Jul 04 '20 at 10:21

1 Answers1

1

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))
Simon.S.A.
  • 6,240
  • 7
  • 22
  • 41