How to change the quotation character(s) for glue_sql?

[kbzsl]

It looks that some database types/SQL dialects are not recognized correctly (like MS SQL Server via odbc driver, …).

In the result of the qlue_sql() the column names are marked with the apostrophe and not with quotation mark.

<SQL> SELECT 'ReportName', 'ExecutionResult', 'StartTime', 'EndTime'
FROM
WHERE 'ReportName' = 'ContentPerWeek'

but the following syntax is accepted by the SQL server:

SELECT "ReportName", "ExecutionResult", "StartTime", "EndTime"
FROM
WHERE "ReportName" = 'ContentPerWeek'

Is it possible to overwrite the connection object based settings or specify manually the characters used quote (or to delimit the column names)?

[jdb]

Without a reproducible example (reprex), it's hard to be as helpful as possible. FAQ: What's a reproducible example (`reprex`) and how do I do one?

However, maybe the quoting helper functions found here might help. https://glue.tidyverse.org/reference/quoting.html

[kbzsl]

Sorry, I am afraid that I not able to produce a fully reproducible example, because I cannot provide public access to the SQL server. I can share some code snippets only.

On the other hand, the I was choosing the glue_sql() because it does the quotation automatically after identifying the SQL dialect based on the connection object (.con). But it looks that the SQL dialect was not recognized correctly.

[kbzsl]

According to the issue #120, the quoting is done by the DBI::dbQuoteString().

So, I have to continue my investigation with DBI. But if you know how to overwrite the SQL dialect and/or the quotation character in the DBI::dbQuoteString(), please share your solutions.

By the way I am using the following odbc connector:

con <- 
  dbConnect(odbc(),
            # Microsoft ODBC Driver for SQL Server on Windows
            driver = "SQL Server",
            ...)

[yan_lyesin]

Could you please show your glue_sql call that produced this output, if possible

[yan_lyesin]

SELECT "sepal_width" FROM "iris" WHERE "iris".sepal_length > 2 AND "iris".species = 'setosa'

This is a perfect SQL server call that was adapted from glue_sql example

var <- "sepal_width"
tbl <- "iris"
num <- 2
val <- "setosa"
glue_sql("
  SELECT {`var`}
  FROM {`tbl`}
  WHERE {`tbl`}.sepal_length > {num}
    AND {`tbl`}.species = {val}
  ", .con = con2)

I only made a change to create con2 from ODBC connection to SQL Server, but your way of creating connection fine as well.
Is it possible that you are using val instead of var, when naming columns?

[kbzsl]

Sorry, it was my mistake. You’re right, I mixed the var and val. Thank you for highlighting it.