[R-sig-DB] dbWriteTable() is renaming the 'end' column
Hervé Pagès
hp@ge@ @end|ng |rom |hcrc@org
Wed Sep 30 01:12:40 CEST 2009
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.
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.
Just my 2 c.
Cheers,
H.
>
> 2009/9/29 Hervé Pagès <hpages using 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 using fhcrc.org
>> Phone: (206) 667-5791
>> Fax: (206) 667-1319
>>
>> _______________________________________________
>> R-sig-DB mailing list -- R Special Interest Group
>> R-sig-DB using 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 using fhcrc.org
Phone: (206) 667-5791
Fax: (206) 667-1319
More information about the R-sig-DB
mailing list