RODBC help

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

RODBC help

Darren Weber-2
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]]

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Reply | Threaded
Open this post in threaded view
|

Re: RODBC help

Darren Weber-2
I would like to make one of the columns in a data frame the primary key.
Using RODBC it seems that it always specifies a numerical index 'rownames'
and you only have a choice of renaming that variable (rownames = "char") and
setting it as the primary key (or not).  I would like to not have this
variable in the db table and just use another variable as the primary key
(something like 'patient name' or 'id').  Is this possible?


On 1/1/06, Darren Weber <[hidden email]> 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]]

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Reply | Threaded
Open this post in threaded view
|

Re: RODBC help

Darren Weber-2
In reply to this post by Darren Weber-2
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 <[hidden email]> 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]]

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html