0
DBI::dbListFields(con, "ibin_acq")
#>  [1] "MANDT"           "IN_RECNO"        "VALFR"           "VALTO"          
#>  [5] "AENNR"           "DELFLAG"         "IN_STANCE"       "MLANG"          
#>  [9] "SORTF"           "OBJNR"           "IBASE"           "AMOUNT"         
#> [13] "UNIT"            "DATUV"           "TECHS"           "IN_OBJNR"       
#> [17] "IN_GUID"         "OBJECTTYP"       "VOID"            "CSTATUS"        
#> [21] "CUCOCNT"         "EXPERT"          "MAT_VARIANT"     "ATAUT"          
#> [25] "KLART"           "CRNAM"           "CRTIM"           "UPNAM"          
#> [29] "UPTIM"           "SOURCE_ID"       "EXTRACTION_DATE"

My R command above utlizing the DBI package works fine (querying a Teradata SQL table). When I try and look at the first ten rows of this table I get the following error:

DBI::dbGetQuery(con, "select top 10 * from ibin_acq")
#> Error in new_result(connection@ptr, statement) : 
#>   nanodbc/nanodbc.cpp:1344: 42S02: [Teradata][ODBC Teradata Driver]
#> [Teradata Database](-3807)Object 'ibin_acq' does not exist.

The error states that the ibin_acq object does not exist, yet my first code chunk (above) proves this object does exist. No?

DBI::dbReadTable(con, "ibin_acq")
#> Error in new_result(connection@ptr, statement) : 
#>   nanodbc/nanodbc.cpp:1344: 42S02: [Teradata][ODBC Teradata Driver]
#> [Teradata Database](-3807)Object 'ibin_acq' does not exist.

This code chunk above does not work either. Why can I dbListFields(con, "ibin_acq") yet I can't dbReadTable(con, "ibin_acq")?

Edit for @Fred - here's that command and the output:

DBI::dbGetQuery(con, "SELECT CURRENT_USER, DATABASE, DatabaseName, 
                TableName FROM DBC.TablesV WHERE TableName='ibin_acq'")
#>   Current_User         Database    DataBaseName TableName
#> 1      6841794 P0_IM_DL_QCM02_V P0_IM_ACQ_GCS_T  IBIN_ACQ
#> 2      6841794 P0_IM_DL_QCM02_V        DBA_DATA  ibin_acq
#> 3      6841794 P0_IM_DL_QCM02_V P0_IM_ACQ_GCS_V  IBIN_ACQ
Display name
  • 4,153
  • 5
  • 27
  • 75
  • 1
    It looks like there's issues with Teradata and that package (https://github.com/r-dbi/odbc/issues/55). I'd try using the more verbose syntax as shown in section 4.2 here and see if it works... https://downloads.teradata.com/blog/odbcteam/2016/02/r-with-teradata-odbc – cory Dec 11 '19 at 20:16
  • 1
    might be default schema/database specification issue? where does the table exist? – EJJ Dec 11 '19 at 20:18
  • 1
    Without knowing what's going on behind the curtain with thedbListFields method, it's hard to be sure. Try qualifying your select with the database name. – Andrew Dec 11 '19 at 20:32
  • To answer the comments: @cory `DBI::dbGetQuery(con, paste('select top 10 * from "ibin_acq"'))` unfortunately did not work. @EJJ The table exists from the `con` connection I've already made to Teradata. @Andrew I'm not quite sure how to qualify my select with a database name, how do I do that? – Display name Dec 11 '19 at 20:37
  • 1
    `select top 10 * from .ibin_acq`. – Andrew Dec 11 '19 at 20:41
  • @Andrew unfortunately qualifying the select with the database name did not work, and yields the same "object does not exist error". – Display name Dec 11 '19 at 21:03
  • 1
    Don't know if using [Teradata SQL Driver for R](https://github.com/Teradata/r-driver) instead of ODBC connection would make a difference. Are you able to find the table in the results of `dbListTables` or `SELECT databasename, tablename FROM DBC.TablesV WHERE tablename='ibin_acq'` – Fred Dec 11 '19 at 23:21
  • @Fred yes `dbListTables` does indeed show the `ibin_acq` table. It looks like you're a Teradata employee. Is there a way to contact you direct? My company does have a Teradata support contract should it matter. – Display name Dec 12 '19 at 14:55
  • 1
    Try this query: `SELECT CURRENT_USER, DATABASE, DatabaseName, TableName FROM DBC.TablesV WHERE TableName='ibin_acq'`. Does the returned value for DatabaseName match that returned for DATABASE? Do either of them match CURRENT_USER? – Fred Dec 12 '19 at 23:47
  • 1
    Just a shot-in-the-dark, maybe the package is suppressing double quoted objects. What if you try putting double quotes around `bin_acq`? – ravioli Dec 13 '19 at 09:29
  • @Fred I tried your latest query unsuccessfully but I may be doing something wrong. I'm populating the DATABASE as `P0_IM_DL_QCM02_V` and the DatabaseName (DBCName) as `00.11.22.3333`, the actual IP being different. Hidden for privacy reasons. I don't really know how to match them to CURRENT_USER. I'm not exactly sure what that even means. @ravioli The double quotes didn't work either :( – Display name Dec 13 '19 at 15:04
  • 1
    What error do you get from this query? `DBI::dbGetQuery(con, "SELECT CURRENT_USER, DATABASE, DatabaseName, TableName FROM DBC.TablesV WHERE TableName='ibin_acq'")` Or does it just return no data? – Fred Dec 13 '19 at 18:56
  • @Fred not sure if I got syntax right but here is the command`dbGetQuery(con, "SELECT 6841794, P0_IM_DL_QCM02_V, 18.44.74.174, ibin_acq FROM P0_IM_DL_QCM02_V.ibin_acq WHERE TableName='ibin_acq'")` and here's the output: `Error in new_result(connection@ptr, statement) : nanodbc/nanodbc.cpp:1344: 42000: [Teradata][ODBC Teradata Driver][Teradata Database](-3707)Syntax error, expected something like a name or a Unicode delimited identifier between a decimal number and the number '.74'.` – Display name Dec 13 '19 at 21:40
  • 1
    My query doesn't have any "variables" for you to substitute values. It's literally the exact text I wanted you to run. – Fred Dec 13 '19 at 22:04
  • @Fred just appended the original question with that exact command and it's output. Please see and let me know your thoughts. – Display name Dec 13 '19 at 22:18
  • 1
    It looks like the Teradata SQL Engine is using **P0_IM_DL_QCM02_V** as the default qualifier but it likely needs to be **P0_IM_ACQ_GCS_V** for this database object. (I presume dbListFields is doing some sort of search like my query, where the schema / DatabaseName qualifier is ignored.) Try specifying the latter value for `schema` in your dbGetQuery or dbReadTable. – Fred Dec 13 '19 at 22:48
  • @Fred That worked. If I establish the connection with the `P0_IM_ACQ_GCS_V` DataBaseName instead of the `P0_IM_DL_QCM02_V` Database everything works as intended. Both the `dbListFields(con, "ibin_acq")` and `dbReadTable(con, "ibin_acq")` commands function properly. How do I know whether to use the "Database" or the "DataBaseName" when I run these queries? Do you mind writing up an answer and I can mark this question solved? – Display name Dec 16 '19 at 16:54

2 Answers2

1

The "DATABASE" P0_IM_DL_QCM02_V returned by the metadata query against DBC.TablesV is the session default qualifier (schema). But the view you intended to access appears to be in P0_IM_ACQ_GCS_V DatabaseName.

Either change the default "DATABASE" setting for the connection or explicitly specify a schema for the query.

Fred
  • 1,916
  • 1
  • 8
  • 16
  • 1
    It appears that dbListFields does not apply the default schema, though dbGetQuery and dbReadTable both do. – Fred Dec 16 '19 at 17:55
0

Thats surely an access issue. Give access to those objects you want by something similar to Grant n then use it.

Himanshu
  • 3,830
  • 2
  • 10
  • 29
  • Does that mean I don't have permission to view that object? I'm not sure I understand. Can you expand on your answer a little bit? Thank you. – Display name Dec 11 '19 at 20:21
  • 1
    Unless R is wrapping Teradata errors inside it's own messages, this is not an access issue. Teradata will return something like `the user does not have select access to `. – Andrew Dec 11 '19 at 20:26