r-dbi / odbc

Connect to ODBC databases (using the DBI interface)
https://odbc.r-dbi.org/
Other
392 stars 107 forks source link

A failing query using `DBI::dbGetQuery()` yields a useless error message #859

Open lschneiderbauer opened 3 weeks ago

lschneiderbauer commented 3 weeks ago

When a query fails in DBI::dbGetQuery() a useless error message is shown instead of quoting the error message that is issued by the remote data base:

Error in `fun(..., .envir = .envir)`:
! Could not parse cli `{}` expression: `100.96.8.10:55372 -…`.
Caused by error in `parse(text = code, keep.source = FALSE)`:
! <text>:1:7: unerwartete numerische Konstante
1: 100.96.8
          ^
Type .Last.error to see the more details.

Since the error depends on the remote system (HANA DB) and a failing query the issue is hard to reproduce, so I cannot share a useful reprex.

It looks like some problems when parsing the remote error message (see https://rlang.r-lib.org/reference/topic-condition-formatting.html#transitioning-from-abort-to-cli-abort-) in this code piece: https://github.com/r-dbi/odbc/blob/bd6686766a503dc69040d19e99c03a9ad3a73967/R/utils.R#L162-L178

Stack trace

11. DBI::dbGetQuery(remote_con(x), get_sql(x, cte, header, hints))
12. DBI::dbGetQuery(remote_con(x), get_sql(x, cte, header, hints))
13. local .local(conn, statement, ...)
14. DBI::dbSendQuery(conn, statement, params = params, immediate = immediate, …
15. odbc::dbSendQuery(conn, statement, params = params, immediate = immediate, …
16. local .local(conn, statement, ...)
17. odbc:::OdbcResult(connection = conn, statement = statement, params = params, …
18. odbc:::new_result(p = connection@ptr, sql = statement, immediate = immediate)
19. (function (msg, call = trace_back()$call[[1]]) …
20. cli::cli_abort(c(`!` = "ODBC failed with error {res$cnd_context_code} from \\\n   …
21. cli:::vcapply(message, format_inline, .envir = .envir)
22. base::vapply(X, FUN, FUN.VALUE = character(1), ..., USE.NAMES = USE.NAMES)
23. local FUN(X[[i]], ...)
24. cli::cli_fmt(fun(..., .envir = .envir), collapse = collapse, strip_newline = TRUE)
25. cli:::cli__rec(expr)
26. local fun(..., .envir = .envir)
27. cli:::cli__message("inline_text", list(text = glue_cmd(..., .envir = .envir, …
28. "id" %in% names(args)
29. cli:::glue_cmd(..., .envir = .envir, .call = sys.call(), .trim = FALSE)
30. cli:::glue(str, .envir = .envir, .transformer = transformer, .cli = TRUE, …
31. (function (expr) …
32. .transformer(expr, .envi…
33. local .transformer(expr, .envi…
34. parse(text = code, keep.…
35. cli:::chain_error(expr, err, s…
36. | base::withCallingHandlers({ …
37. base::parse(text = code, keep.…
38. base::.handleSimpleError(funct…
39. | local h(simpleError(msg, call))
40. | cli:::throw_error(err, parent …
simonpcouch commented 3 weeks ago

Huh. Thanks for the thorough issue description!

Triple checking the cli inline markup here (and that it's tested thoroughly) and not seeing anything that jumps out to me. I'm wondering if there's some nonstandard formatting in the driver message from Hana DB that introduces issues with cli. Trying to inline some numbers in different substitutions to trigger that "unexpected numeric constant" error but no dice.

Could you please run the following code before your code that triggers the error and then paste your console output here?

library(odbc)
testthat::local_mocked_bindings(
  rethrow_database_error = function(msg, call = "") {
    print(dput(msg))
    print(call)
    invisible()
  },
  .package = "odbc"
)
lschneiderbauer commented 2 days ago

Hi, my apologies for the delayed response.

I managed to get a sample, I censored IP adresses, session IDs and the SQL statement itself in the hope that those parts don't contain the crucial bits.

"nanodbc/nanodbc.cpp:1722: S1000\n[SAP AG][LIBODBCHDB DLL][HDBODBC] General error;403 internal error: Error opening the cursor for the remote database <***.***> [SAP AG][LIBODBCHDB SO][HDBODBC] Connection not open;-10807 Connection down: [89013] Socket closed by peer {***.**.*.**:***** -> ***.**.***.**:***** TenantName:(none) SiteVolumeID:1:3 SiteType:PRIMARY ConnectionID:****** SessionID:************}\n \n<SQL> 'SELECT DISTINCT\n  \"po_id\", ***CENSORED*** \n'"
[1] "nanodbc/nanodbc.cpp:1722: S1000\n[SAP AG][LIBODBCHDB DLL][HDBODBC] General error;403 internal error: Error opening the cursor for the remote database <***.***> [SAP AG][LIBODBCHDB SO][HDBODBC] Connection not open;-10807 Connection down: [89013] Socket closed by peer {***.**.*.**:***** -> ***.**.***.**:***** TenantName:(none) SiteVolumeID:1:3 SiteType:PRIMARY ConnectionID:****** SessionID:************}\n \n<SQL> 'SELECT DISTINCT\n  \"po_id\",\n  ***CENSORED*** \n'"
[1] ""