Wrong SQL in the examples section of glue_sql()

[yutannihilation]

I really don't know there's some SQL engine to support this INSERT statement, but it seems the result SQL of the example added by #122 is wrong at least as a SQLite query (c.f. https://www.sqlite.org/lang_insert.html).

glue/R/sql.R

Lines 95 to 96 in 995e13f

	#' values <- c(1, 2, 'Setosa')
	#' glue_sql("INSERT ({values*}) INTO ({`col_ids`*})", .con=con)

library(glue)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
colnames(iris) <- gsub("[.]", "_", tolower(colnames(iris)))
DBI::dbWriteTable(con, "iris", iris)

cols <- c("Sepal_Width", "Sepal_Length", "Species")
col_ids <- lapply(cols, function(x) DBI::Id(table="iris", column = x))
values <- c(1, 2, 'Setosa')
query1 <- glue_sql("INSERT ({values*}) INTO ({`col_ids`*})", .con=con)
query1
#> <SQL> INSERT ('1', '2', 'Setosa') INTO (`iris`.`Sepal_Width`, `iris`.`Sepal_Length`, `iris`.`Species`)

DBI::dbExecute(con, query1)
#> Error: near "(": syntax error

I can tweak this to a correct INSERT statement (as well as replacing col_ids with cols),

query2 <- glue_sql("INSERT INTO iris ({`cols`*}) VALUES ({values*})", .con=con)
query2
#> <SQL> INSERT INTO iris (`Sepal_Width`, `Sepal_Length`, `Species`) VALUES ('1', '2', 'Setosa')

DBI::dbExecute(con, query2)
#> [1] 1

but it's still incorrect because string literals are specified for double columns. I guess values should be list(1, 2, 'Setosa') instead of c(1, 2, 'Setosa'). (edit: sorry, I don't know how to fix this...)

tail(DBI::dbGetQuery(con, "select * from iris"), 1)
#>     sepal_length sepal_width petal_length petal_width species
#> 151            2           1           NA          NA  Setosa

[jimhester]

Yes I agree with you, the example is not correct, I fixed the issue with vectors of different types and completely rewrote the example of using DBI::Id().

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
values <- list(1, 2, 'Setosa')
glue::glue_sql("x = ({values*})", .con = con)
#> <SQL> x = (1, 2, 'Setosa')

^{Created on 2019-11-07 by the reprex package (v0.3.0)}

[yutannihilation]

Oh, cool. Thanks for fixing this so quickly. The new example of JOIN totally makes sense!