[R] RMySQL valid field names
dj at research.bell-labs.com
Tue Nov 25 15:30:12 CET 2003
Thanks for your thoughtful comments. Indeed you've uncovered
a bug/problem in that there's no way for users to control the
"allow.keywords=" argument in calls to dbWriteTable() -- this
needs to be fixed. Regarding the default value for allow.keywords,
I'm not sure it is wise to set it to TRUE by default (despite the
fact that MySQL does not explicitly prohibit keywords as column names)
since problems can arise when MySQL runs in ANSI mode (see section
6.1.7 in the manual). Perhaps the default value should depend
on whether MySQL is running in ANSI mode or not, I'll check whether
that's easy to determine from R at runtime.
Again, thanks for reporting the problem.
Luis Torgo wrote:
> I'm having some problems with valid field names when using RMySQL to interface
> R (version 1.7.0, under RedHat9.0), to MySQL (4.1.0-alpha). I think I've
> spotted the problem and a solution (which is working for me), but I wanted to
> share this with you as I may be missing something.
> (Note: I'm aware that this is an old R version, but I've checked the code of
> the lastest version of the RMySQL package at CRAN, and my comments still
> I have a data frame which has among others three columns with names
> ('DateTime', 'Open' and 'Close'). When I use dbWriteTable to dump the data
> frame into a MySQL database everything works fine except the names of these
> three columns which are slightly different (e.g. 'DateTime_9').
> This only occurs with these 3 columns because their names are reserved words
> of MySQL. The change of names is occurring in the function mysqlWriteTable,
> namely in the call:
> names(field.types) <- make.db.names(con,names(field.types),allow.keywords=F)
> If I change the parameter allow.keywords into T, everything works fine for me.
> Given that MySQL allows all characters to be part of field names (section
> 6.1.2 of MySQL reference manual), I do not understand what is the reason for
> calling make.db.names with this value of parameter allow.keywords.
> In resume, my question is: Is there any reason for this that I am missing, or
> the change I've done is pretty safe and could possibly be done in future
> versions of the package?
> Thank you for any help.
> Luis Torgo
> FEP/LIACC, University of Porto Phone : (+351) 22 607 88 30
> Machine Learning Group Fax : (+351) 22 600 36 54
> R. Campo Alegre, 823 email : ltorgo at liacc.up.pt
> 4150 PORTO - PORTUGAL WWW : http://www.liacc.up.pt/~ltorgo
> R-help at stat.math.ethz.ch mailing list
David A. James
Statistics Research, Room 2C-253 Phone: (908) 582-3082
Bell Labs, Lucent Technologies Fax: (908) 582-3340
Murray Hill, NJ 09794-0636
More information about the R-help