Don't encodeString() by default in dbQuoteString()

[gaborcsardi]

I use sqlInterpolate whenever I can, and it encodes the strings via dbQuoteString and encodeString by default, which is fine, but I don't think that there is an operation to decode a string that I am getting out of a database.

Maybe there is a standard way to decode such a string, but I cannot find it. I cannot even use capture.output with cat, because capture_output encodes the string again.

[krlmlr]

Could you please show an example?

[gaborcsardi]

Good point, sorry. Here is one:

library(RSQLite)
library(DBI)

db <- dbConnect(RSQLite::SQLite(), "my.db")
dbSendQuery(db, "CREATE TABLE expect (name TEXT PRIMARY KEY, chat TEXT)")

dbSendQuery(db,
  sqlInterpolate(ANSI(),
    "INSERT INTO expect VALUES (?name, ?chat)",
    name = "foo",
    chat = "Special \n, \\n, \\\\r"
  )
)

res <- dbGetQuery(db, "SELECT * FROM expect")
res$chat

## [1] "Special \\n, \\\\n, \\\\\\\\r"

devtools::session_info()
## Session info -------------------------------------------------------------------
##  setting  value
##  version  R version 3.3.1 (2016-06-21)
##  system   x86_64, darwin13.4.0
##  ui       X11
##  language (EN)
##  collate  en_US.UTF-8
##  tz       Europe/London
##  date     2016-07-09
## 
## Packages -----------------------------------------------------------------------
##  package    * version  date       source
##  clisymbols   1.0.0    2015-06-08 CRAN (R 3.2.0)
##  crayon       1.3.2    2016-06-28 local
##  DBI        * 0.4-1    2016-05-26 Github (rstats-db/DBI@a79dcc6)
##  devtools     1.12.0   2016-06-24 CRAN (R 3.2.5)
##  digest       0.6.9    2016-01-08 CRAN (R 3.2.3)
##  memoise      1.0.0    2016-01-29 CRAN (R 3.2.3)
##  memuse       2.5      2015-07-02 CRAN (R 3.2.0)
##  parr         3.3.0    2016-04-16 Github (gaborcsardi/parr@3a2564e)
##  prompt       1.0.0    2016-04-16 local (gaborcsardi/prompt@53e0550)
##  Rcpp         0.12.5   2016-05-14 CRAN (R 3.2.5)
##  RSQLite    * 1.0.9002 2016-05-26 Github (rstats-db/RSQLite@3098b59)
##  withr        1.0.2    2016-06-20 CRAN (R 3.2.5)

After this I updated DBI and RSQLite, and got the same with

devtools::session_info()
## Session info -------------------------------------------------------------------
##  setting  value
##  version  R version 3.3.1 (2016-06-21)
##  system   x86_64, darwin13.4.0
##  ui       X11
##  language (EN)
##  collate  en_US.UTF-8
##  tz       Europe/London
##  date     2016-07-09
## 
## Packages -----------------------------------------------------------------------
##  package    * version  date       source
##  clisymbols   1.0.0    2015-06-08 CRAN (R 3.2.0)
##  crayon       1.3.2    2016-06-28 local
##  DBI        * 0.4-2    2016-07-09 Github (rstats-db/DBI@a1367f7)
##  devtools     1.12.0   2016-06-24 CRAN (R 3.2.5)
##  digest       0.6.9    2016-01-08 CRAN (R 3.2.3)
##  memoise      1.0.0    2016-01-29 CRAN (R 3.2.3)
##  memuse       2.5      2015-07-02 CRAN (R 3.2.0)
##  parr         3.3.0    2016-04-16 Github (gaborcsardi/parr@3a2564e)
##  prompt       1.0.0    2016-04-16 local (gaborcsardi/prompt@53e0550)
##  Rcpp         0.12.5   2016-05-14 CRAN (R 3.2.5)
##  RSQLite    * 1.0.9004 2016-07-09 Github (rstats-db/RSQLite@e9d3c0f)
##  withr        1.0.2    2016-06-20 CRAN (R 3.2.5)

[krlmlr]

Thanks. No conversion should be necessary, sqlInterpolate() should just add the strings so that a 1:1 copy is returned when calling select.

SQLite doesn't seem to use \ as escaping character:

sqlite> SELECT length('\n');
2
sqlite> SELECT length('n');
1
sqlite> SELECT length('\');
1

You should call sqlInterpolate(db, ...), not sqlInterpolate(ANSI(), ...), but the behavior is the same when I do the former. Postgres has the same behavior, only MySQL knows \n (but also accepts an inline line feed character).

I'll change the default in DBI, and add a test for the roundtrip.

[gaborcsardi]

Thanks!

So even if encodeString is not called, the names and fields will be still properly escaped, right?

[krlmlr]

Everything should "just work" ;-) Could you please rephrase the question? I don't understand "names and fields".

[gaborcsardi]

Forget the names and fields, it was a stupid question. Thanks again!