[R] RMySQL valid field names

[David James]

Luis,

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.

--
David

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 
> apply).
> 
> 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
> 
> -- 
> 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
> https://www.stat.math.ethz.ch/mailman/listinfo/r-help

-- 
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