dbWriteTable() is renaming the 'end' column
I presume this is about dbWriteTable in package DBI, and also RSQLite, but neither are mentioned and the 'example' fails to run as written.
On Tue, 29 Sep 2009, Herv? Pag?s wrote:
Gabor Grothendieck wrote:
end is a reserved SQL keyword.
Ok, maybe so what? This works: dbSendQuery(con, "CREATE TABLE mydata (start INTEGER, end INTEGER)") and if it didn't, I should get an error (or at least a warning). Then I could try again with: dbSendQuery(con, "CREATE TABLE mydata (`start` INTEGER, `end` INTEGER)") Using backquotes allows me to use whatever name I want for my columns, even SQL keywords. This is ANSI SQL.
Without telling us *which* ANSI SQL standard this is unclear, but I believe that it is a false claim. It seems so do the authors of MySQL, who have an ANSI_QUOTE mode using " to quote identifiers. As I know from RODBC, this is one of those areas of differences between actual SQL engines, and one which ODBC does not attempt to paper over. See also http://www.mail-archive.com/sqlite-users at sqlite.org/msg09583.html -- so it looks like backquotes were added to SQLite in 2005, long after the design of DBI/RSQLite, and as a non-ANSI extension. RODBC successfully uses double quotes for identifiers with SQLite, and the RODBC version of your example works as you expected.
IMO, it would be much better if dbWriteTable() was: - not trying to be too smart and just send the CREATE statement, leaving the DB backend decide whether it's valid or not, and eventually return an error, - if dbWriteTable() really wants to be smart, then it could try a little bit harder to create the table I want with the column names I want by using backquotes.
But DBI::dbWriteTable() does neither of these: it calls the method in the backend, which in the case of RSQLite is sqliteWriteTable(). That calls RSQLite::dbBuildTableDefinition() to make the CREATE TABLE query, and that calls DBI::make.db.names(), which is a generic with a method in RSQLite. So the correct subject of your scorn would seem to be RSQLite::dbBuildTableDefinition() and its lack of documentation. As I have argued earlier, I think that the solution has to be specific to the backend.
Just my 2 c.
Cheers, H.
2009/9/29 Herv? Pag?s <hpages at fhcrc.org>:
Hi,
Consider the following data:
mydata <- data.frame(start=1:5, end=11:15)
Then storing and retrieving the data shows that the 'end'
column was renamed:
drv <- dbDriver("SQLite")
con <- dbConnect(drv, dbname=":memory:")
> dbWriteTable(con, "mydata", mydata)
[1] TRUE
> dbReadTable(con, "mydata")
start end__1 1 1 11 2 2 12 3 3 13 4 4 14 5 5 15 The culprit doesn't seem to be dbReadTable():
> dbGetQuery(con, "SELECT * FROM mydata")
row_names start end__1 1 1 1 11 2 2 2 12 3 3 3 13 4 4 4 14 5 5 5 15 and using the sqlite3 client from outside R confirms this. Looks like a bug in dbWriteTable(). Cheers, H. -- Herv? Pag?s Program in Computational Biology Division of Public Health Sciences Fred Hutchinson Cancer Research Center 1100 Fairview Ave. N, M2-B876 P.O. Box 19024 Seattle, WA 98109-1024 E-mail: hpages at fhcrc.org Phone: (206) 667-5791 Fax: (206) 667-1319
_______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB at stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-db
-- Herv? Pag?s Program in Computational Biology Division of Public Health Sciences Fred Hutchinson Cancer Research Center 1100 Fairview Ave. N, M2-B876 P.O. Box 19024 Seattle, WA 98109-1024 E-mail: hpages at fhcrc.org Phone: (206) 667-5791 Fax: (206) 667-1319
_______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB at stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-db
Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595