Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Escaping odd table names in SQLite not working #926

Closed
pschmied opened this issue Jan 27, 2015 · 4 comments
Closed

Escaping odd table names in SQLite not working #926

pschmied opened this issue Jan 27, 2015 · 4 comments
Labels
Milestone

Comments

@pschmied
Copy link

@pschmied pschmied commented Jan 27, 2015

This report is reproduced from this Stack Overflow question:
http://stackoverflow.com/questions/28132697/bracket-escaped-table-names-with-dplyr?noredirect=1#comment44719663_28132697

I'm programmatically fetching a bunch of datasets, many of them having silly names that begin with numbers and have special characters like minus signs in them. Because none of the datasets are particularly large, and I wanted the benefit R making its best guess about data types, I'm (ab)using dplyr to dump these tables into SQLite.

I am using square brackets to escape the horrible table names, but this doesn't seem to work. For example:

data(iris)
foo.db <- src_sqlite("foo.sqlite3", create = TRUE)
copy_to(foo.db, df=iris, name="[14m3-n4m3]")

This results in the error message:

Error in sqliteSendQuery(conn, statement, bind.data) : error in statement: no such table: 14m3-n4m3

This works if I choose a sensible name. However, due to a variety of reasons, I'd really like to keep the cumbersome names. I am also able to create such a badly-named table directly from sqlite:

sqlite> create table [14m3-n4m3](foo,bar,baz);
sqlite> .tables
14m3-n4m3

Without cracking into things too deeply, this looks like dplyr is handling the square brackets in some way that I cannot figure out. My suspicion is that this is a bug, but I wanted to check here first to make sure I wasn't missing something.

EDIT: I forgot to mention the case where I just pass the janky name directly to dplyr. This errors out as follows:

library(dplyr)

data(iris)
foo.db <- src_sqlite("foo.sqlite3", create = TRUE)
copy_to(foo.db, df=iris, name="14M3-N4M3")

Error in sqliteSendQuery(conn, statement, bind.data) : 
  error in statement: unrecognized token: "14M3"
@pschmied pschmied changed the title Escaping odd table names not working Escaping odd table names in SQLite not working Jan 29, 2015
@pschmied
Copy link
Author

@pschmied pschmied commented Jan 29, 2015

I've started trying to dig into this myself a bit. Just for fun, I decided to install / test against a Postgres database. This appears to work correctly without escaping:

library(dplyr)
library("RPostgreSQL")

foo.db <- src_postgres("foo")

foo <- copy_to(foo.db, df=iris, name="14M3-N4M3")

@FvD
Copy link
Contributor

@FvD FvD commented Apr 9, 2015

Apparently the issue is with the first character being a number in "14M3". It tried to save a sequence of databases with a UUID and copy_to will fail as soon as there is a table name with a number as the first character (this is copying to a sqlite database).

Reports are added nicely as long as there is a letter in front:

> reportdb
src:  sqlite 3.8.6 [reportdata.sqlite3]
tbls: a325db39, a54088fb, b25fdcd4, b45adc99, b68946ab, d135c554, e59bdbe7, ef2ae5ff, result_view, sqlite_stat1

Error as soon as there is a number in front:

> resultsDB <- copy_to(reportdata, result_view, name="7b1ee1d2", temporary=FALSE)
Error in sqliteSendQuery(conn, statement, bind.data) : 
  error in statement: unrecognized token: "7b1ee1d2"

Fixed by adding a letter (to verify):

> resultsDB <- copy_to(reportdata, result_view, name="e7b1ee1d2", temporary=FALSE)
> reportdb
src:  sqlite 3.8.6 [reportdata.sqlite3]
tbls: a325db39, a54088fb, b25fdcd4, b45adc99, b68946ab, d135c554, e59bdbe7, e7b1ee1d2, ef2ae5ff, result_view,
  sqlite_stat1

@hadley hadley added this to the 0.5 milestone May 19, 2015
NikNakk pushed a commit to NikNakk/dplyr that referenced this issue Jun 14, 2015
…unction for db_insert_into that supports escaping (unlike the current DBI::dbWriteTable that was used) and using build_sql rather than paste0 within db_query_fields.
@NikNakk
Copy link

@NikNakk NikNakk commented Jun 14, 2015

I've just made a couple of changes to src-sqlite.r which I think fix the problem. I've put in a pull request (from NikNakk/dplyr).

NikNakk pushed a commit to NikNakk/dplyr that referenced this issue Jul 10, 2015
…unction for db_insert_into that supports escaping (unlike the current DBI::dbWriteTable that was used) and using build_sql rather than paste0 within db_query_fields.
NikNakk pushed a commit to NikNakk/dplyr that referenced this issue Jul 22, 2015
Keeps - Uses build_sql rather than paste0 within db_query_fields
Restores - DBI::dbWriteTable (fixes have occurred upstream in RSQLite)
NikNakk pushed a commit to NikNakk/dplyr that referenced this issue Jul 22, 2015
@hadley
Copy link
Member

@hadley hadley commented Mar 9, 2016

This is fixed in the development version of RSQLite

@hadley hadley closed this Mar 9, 2016
@lock lock bot locked as resolved and limited conversation to collaborators Jun 9, 2018
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

4 participants