NetezzaSQL connection viewer pane preview not working with cross catalog table

[meztez]

When you attempt to preview a table from a different catalog than the one used to create the initial connection, you will get a

Error in new_result(connection@ptr, statement, immediate) : 
  nanodbc/nanodbc.cpp:1412: 42S02: ERROR:  relation does not exist PWORK.ADMIN.MSAS_TBL_COT_COMMERCL 

In this case, the table is in catalog PDW.

Here is a break down of what happens.

The preview call will receive the right value for the catalog but a NULL value for the schema ~~since I think RStudio internal connection display pane will ignore schema objects (hypothesis).~~

This will get translated to something like

SELECT * FROM \"CATALOG\".MSAS_TBL_COT_COMMERCL

Internally, Netezza will think you are trying to use a schema and will default back to the catalog/schema you used for the connection.

Since the table does not exist there, it will fail.

~~This could just be due to the driver we use. I use the following workaround in our odbc fork. Rename schema to schemax, I'm fooling RStudio connection pane. It will then pass the schemax value to the preview call and the preview will work as intended.~~

~~It is my understanding than the > 1 schemas/catalogs condition was put there to avoid clutter in the pane. Alas, for Netezza, I switched it to > 0 since we had only one schema and it was not showing up.~~

~~Anyway, this is mostly for people who might face similar issues with Netezza. Hopefully you are able to move away from this DB.~~

# NetezzaSQL ---------------------------------------------------------

setMethod(
  "odbcListObjectTypes", signature("NetezzaSQL"),
  function(connection) {
    # slurp all the objects in the database so we can determine the correct
    # object hierarchy

    # all databases contain tables, at a minimum
    obj_types <- list(table = list(contains = "data"))

    # see if we have views too
    table_types <- string_values(connection_sql_tables(connection@ptr, "", "", "", "%")[["table_type"]])
    if (any(table_types == "VIEW")) {
      obj_types <- c(obj_types, list(view = list(contains = "data")))
    }

    # check for multiple schema or a named schema
    schemaxs <- string_values(connection_sql_tables(connection@ptr, "", "%", "", "")[["table_schema"]])
    if (length(schemaxs) > 0) {
      obj_types <- list(schemax = list(contains = obj_types))
    }

    # check for multiple catalogs
    catalogs <- string_values(connection_sql_tables(connection@ptr, "%", "", "", "")[["table_catalog"]])
    if (length(catalogs) > 0) {
      obj_types <- list(catalog = list(contains = obj_types))
    }

    obj_types
  })

setMethod(
  "odbcListObjects", signature("NetezzaSQL"),
  function(connection, catalog = NULL, schemax = NULL, name = NULL, type = NULL, ...) {
    # if no catalog was supplied but this database has catalogs, return a list of
    # catalogs
    if (is.null(catalog)) {
      catalogs <- string_values(connection_sql_tables(connection@ptr, catalog_name = catalog %||% "%", "", "", NULL)[["table_catalog"]])
      if (length(catalogs) > 0) {
        return(
          data.frame(
            name = catalogs,
            type = rep("catalog", times = length(catalogs)),
            stringsAsFactors = FALSE
          ))
      }
    }

    # if no schema was supplied but this database has schema, return a list of
    # schema
    if (is.null(schemax)) {
      schemaxs <- string_values(connection_sql_tables(connection@ptr, "", schema_name = schemax %||% "%", "", NULL)[["table_schema"]])
      if (length(schemaxs) > 0) {
        return(
          data.frame(
            name = schemaxs,
            type = rep("schema", times = length(schemaxs)),
            stringsAsFactors = FALSE
          ))
      }
    }

    objs <- tryCatch(connection_sql_tables(connection@ptr, catalog, schemax, name, table_type = type), error = function(e) NULL)
    # just return a list of the objects and their types, possibly filtered by the
    # options above
    data.frame(
      name = objs[["table_name"]],
      type = tolower(objs[["table_type"]]),
      stringsAsFactors = FALSE
    )
  })

[meztez]

~~The Schema name is "ADMIN",~~ something's fishy

[meztez]

Scratch that, it is just because there is only one schema in the database and the odbcListObjects function has a > 1 condition. I could not reproduce the shenanigan I was observing on my system Friday night. See #444 .

[meztez]

This has been resolved.