change escape character `translate_sql()` from ` to '

The default escape character for column names when using translate_sql() is ` I would like for it to be '. I cannot figure out how to accomplish this without casting to a character vector and manually replacing.

Currenty behavior:

translate_sql(var > 100)

#>  <SQL> `var` > 100.0


#> <SQL> 'var' > 100.0`

Is using the following valid in your case?

dbplyr::translate_sql("var" > 10)

That will return:

<SQL> 'var' > 10.0

Unfortunately not. :confused:
I'm trying to implement a filter.myclass that does basic translation for a where clause. It's not a real database back end but rather a list of metadata that will be used to send a query to a rest api.

A filter method like

filter.myclass <- function(x, ...) {
   dbplyr::partial_eval(rlang::expr(...), vars = x[["cols"]][["names"]])

get's 99% of the way there.

This will get all the way there for my purposes, so I'll settle on this.

filter.FeatureLayer <- function(x, ...) {
  dbplyr::partial_eval(rlang::expr(...), vars = x[["cols"]][["names"]])
  gsub("`", "", dbplyr::translate_sql(...))

I tried doing something like sql_escape_ident.myclass <- function(con, x) sql_quote(x, "") but that didn't work like I anticipated.

You were nearly there. You just have to use the con argument of translate_sql(). Either you supply the actual connection to a database or if there is none you can create a dummy connection that inherits from:

  • a custom class so that you can overwrite sql_escape_ident()
  • from DBIConnection so that the translation actually works



sql_escape_ident.myclass <- function(con, x) sql_quote(x, "'")

dummy_connection <- structure(
  class = c("myclass", "DBIConnection")
translate_sql(var > 100, con = dummy_connection)
#> Warning: <myclass> uses an old dbplyr interface
#> ℹ Please install a newer version of the package or contact the maintainer
#> This warning is displayed once every 8 hours.
#> <SQL> 'var' > 100.0

Created on 2022-12-13 with reprex v2.0.2

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.