glue ≥ 1.7 : wrong format of Posixt datetime by glue_sql() #321
Comments
|
(reprex with my implementation of Tools.glue_sql.multi.fct <- function(sql_multi_vars, args, connexion_bdd = DBI::ANSI()) {
args <- unlist(list(sql_multi_vars, args, .con = connexion_bdd), recursive = FALSE)
unname(names(args)[1])
do.call(glue_sql, args)
}
Tools.glue_sql.multi.fct (
'{mydatetime_posixt} "mydatetime_sqlserver",{mydate} "mydate_sqlserver"',
datas
)
# glue 1.6.2
#> <SQL> '2023-11-16 14:52:45' "mydatetime_sqlserver",'2023-11-15' "mydate_sqlserver"
# glue 1.7.0
#> <SQL> '20231116145245' "mydatetime_sqlserver",'2023-11-15' "mydate_sqlserver") |
|
Yeah, quite possibly. |
|
With the caveat that I have not worked on the SQL-related features of glue, a quick peek at DBI suggests that, in our use of I'm relying on the other discussants to confirm this would produce the desired outcome. Update: Or, perhaps, maybe that code is the problem? Now that I read it more carefully, that looks like the formatting I see above. Should this be an issue in DBI? |
|
Yeah, I thnk it's a DBI issue. |
|
@philibe given your excellent reprex-ing skills 😀 I think you should be able to create a reprex that only uses DBI (not glue). If true, then you can close this an open an issue on DBI. |
|
You mean this test : Here is the results:
Therefore I debug step by step: Traceback:
I'm in the process of debug and it seems it maybe caused by the change of |
|
Here is
library(glue)
datas<-structure(
list(
mydatetime_posixt = structure(1700146365,
tzone = "UTC",
class = c("POSIXct", "POSIXt")
),
mydate = structure(19676, class = "Date")
),
row.names = c(NA,-1L),
class = c("tbl_df", "tbl", "data.frame")
)
sql_quote_transformer_162 <-
function (connection, .na)
{
if (is.null(.na)) {
.na <- DBI::SQL(NA)
}
function(text, envir) {
should_collapse <- grepl("[*][[:space:]]*$", text)
if (should_collapse) {
text <- sub("[*][[:space:]]*$", "", text)
}
m <- gregexpr("^`|`$", text)
is_quoted <- any(m[[1]] != -1)
if (is_quoted) {
regmatches(text, m) <- ""
res <- eval(parse(text = text, keep.source = FALSE),
envir)
if (length(res) == 1) {
res <- DBI::dbQuoteIdentifier(conn = connection,
res)
}
else {
res[] <- lapply(res, DBI::dbQuoteIdentifier,
conn = connection)
}
}
else {
res <- eval(parse(text = text, keep.source = FALSE),
envir)
if (inherits(res, "SQL")) {
if (should_collapse) {
res <- glue_collapse(res, ", ")
}
if (length(res) == 0L) {
res <- DBI::SQL("NULL")
}
return(res)
}
is_object <- is.object(res)
if (is_object) {
res <- as.character(res)
}
is_na <- is.na(res)
if (any(is_na)) {
res[is_na] <- rep(list(.na), sum(is_na))
}
is_char <- vapply(res, function(x) !is.na(x) && is.character(x),
logical(1))
res[is_char] <- lapply(res[is_char], function(x) DBI::dbQuoteLiteral(conn = connection,
x))
res[!is_char] <- lapply(res[!is_char], function(x) DBI::SQL(conn = connection,
x))
}
if (should_collapse) {
res <- glue_collapse(res, ", ")
}
if (length(res) == 0L) {
res <- DBI::SQL("NULL")
}
res
}
}
glue_sql_with_sql_quote_transformer_162<-
function (..., .con, .sep = "", .envir = parent.frame(), .open = "{",
.close = "}", .na = DBI::SQL("NULL"), .null = character(),
.comment = "#", .literal = FALSE, .trim = TRUE)
{
DBI::SQL(glue(..., .sep = .sep, .envir = .envir, .open = .open,
.close = .close, .na = .na, .null = .null, .comment = .comment,
.literal = .literal, .transformer = sql_quote_transformer_162(.con,
.na), .trim = .trim))
}
glue_sql_with_sql_quote_transformer_162 (
'{mydatetime_posixt} "mydatetime_sqlserver",{mydate} "mydate_sqlserver"',
mydatetime_posixt=datas$mydatetime_posixt,
mydate=datas$mydate,
.con=DBI::ANSI()
)
#> <SQL> '2023-11-16 14:52:45' "mydatetime_sqlserver",'2023-11-15' "mydate_sqlserver"Created on 2024-02-23 with reprex v2.1.0 |
|
The ball is in your court :) |
|
@philibe yes, glue now relies on DBI to supply the correct transformation from R literals to SQL literals. So please file an issue in whatever database package you are using. |
|
Can you explain your answer please ? : with connection I use |
|
Therefore in https://github.com/r-dbi/DBI/issues I suppose. |
|
@philibe you need to file an issue with the specific backend that you're using, unless you have evidence to suggest that's the wrong date-time format for ANSI SQL. |
glue_sql()for RDBMS :'2023-11-16 14:52:45'glue_sql()for RDBMS :'20231116145245'glue 1.6.2 : ok
'2023-11-16 14:52:45'Created on 2024-02-21 with reprex v2.0.2
Session info
glue 1.7.0 : issue
'20231116145245'Created on 2024-02-21 with reprex v2.1.0
Session info
temporary workaround in glue 1.7 : base::format(mydatetime_posixt, format = "%Y-%m-%d %H:%M:%S")
Created on 2024-02-21 with reprex v2.1.0
The text was updated successfully, but these errors were encountered: