I've discovered that the problem in this command is the use of 'GROUP' as a column name. Within mysql, we get the same error with:
mysql> create table tmp ( group varchar(20) ); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'group varchar(20) )' at line 1 This page documents the reserved words in MySQL: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html It is possible to use the word 'group' as a column name, but it must be quoted with back-ticks (all other quotes fail), eg: mysql> create table tmp ( `group` varchar(20) ); Query OK, 0 rows affected (0.10 sec) mysql> desc tmp; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | group | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 1 row in set (0.00 sec) Sorry for my ignorance of this issue, the RODBC library is working fine, maybe it could be revised to handle these exceptions. Best, Darren On 1/1/06, Darren Weber <[EMAIL PROTECTED]> wrote: > > > Dear Prof. Ripley et al., > > I have a data frame with some variable names that contain the underscore > character ('_'). It seems that RODBC is not converting them to a suitable > variable name for MySQL. > > > sqlSave(channel, zz, addPK = TRUE, verbose = TRUE) > Query: CREATE TABLE zz (rownames varchar(255) PRIMARY KEY, NAME > varchar(255), ID varchar(255), GROUP varchar(255), OAC_RT double, OUC_RT > double, OAT_RT double, OUT_RT double, OFN double, OFP double, OFP_RT double, > TAC_RT double, TUC_RT double, TAT_RT double, TUT_RT double, TAD_RT double, > TUD_RT double, TFN double, TFP double, TFP_RT double, OFP_PERC double, > OFN_PERC double, OTD_PERC double, TFP_PERC double, TFN_PERC double, TTD_PERC > double, SEX varchar(255), AGE double, EDUC double, EMP_STAT double, MAR_STAT > varchar(255), NART_IQ double, BDI double, SANX_ERP double, TRAIT double, GHQ > double, IES_A double, IES_I double, IES_TOT double, CAPS_BF double, CAPS_BI > double, CAPS_B double, CAPS_CF double, CAPS_CI double, CAPS_C double, > CAPS_DF double, CAPS_DI double, CAPS_D double, CAPS_F double, CAPS_I double, > CAPS_TOT double) > Error in sqlSave(channel, zz, addPK = TRUE, verbose = TRUE) : > [RODBC] ERROR: Could not SQLExecDirect37000 1064 > [unixODBC][MySQL][ODBC 3.51 Driver][mysqld-4.0.24_Debian-10sarge1-log]You > have an error in your SQL syntax. Check the manual that corresponds to your > MySQL server version for the right syntax to use near 'GROUP varchar(255), > OAC_RT double, OUC_RT double, OAT_RT double > > How can I avoid this error? Is there an option to sqlSave or should I > modify the names in the data frame? > > TIA and take care, Darren > > [[alternative HTML version deleted]] ______________________________________________ [email protected] mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
