2

It seems when I pass a comment to dbGetQuery() it doesn't send the comment to our database. Is there a way to preserve comments? I'm trying track which apps on Posit Connect are sending which queries and trying to do something like this:

library(odbc)
library(glue)

glue_guid <- function(x) {
  glue(
    "/*platform: rsc, id: {guid}*/ {x}",
    guid = Sys.getenv('CONNECT_CONTENT_GUID') # exists on RStudio/Posit Connect
  )
}

conn <- dbConnect(...)
sql <- glue_guid("select * from my_table")
# /*platform: rsc, id: */ select * from my_table

dbGetQuery(conn, sql)

So the questions are:

  • Is there some better way to track SQL queries to apps from connect to our database?
  • If not, is this approach ^^^ even possible?
  • If not, could/should I make an issue and if so, which package? DBI or odbc? I think DBI is doing it here
yake84
  • 3,004
  • 2
  • 19
  • 35
  • Which DB server are you using? Do you expect the DB server to receive the full query ` /*platform: rsc, id: */ select * from my_table`? How do you determine that this is not the case? – Ralf Stubner Jun 05 '23 at 10:08
  • Thanks. We are using Netezza and I do not see the comments in the query history database (`histdb`) when I send it via `dbGetQuery()`. However, I do see the comments if I run it from DBeaver. I expect Netezza to receive the full query or some better way to match SQL statements to the content on Connect that generated the query. – yake84 Jun 05 '23 at 19:52
  • OK, can you set-up a DSN on the Connect server and test if the comment makes it to the DB when using `isql DSN`? This way we can determine whether it is the ODBC driver or any of the R packages that strips the comments. – Ralf Stubner Jun 06 '23 at 20:24
  • Finally got to test it and the comments get stripped out :( – yake84 Jun 20 '23 at 15:58
  • That's to bad :-( If the comments are already stripped out by the driver, your best bet is to use client side logging. – Ralf Stubner Jun 23 '23 at 07:24

0 Answers0