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