R help - May 2004 - sqlSave with underscores in table fieldname

Hi group,

I try to write a frame to a table (RODBC). I use
	colnames(temp6) <-
c("ind_id","ser_id","period_id","year","calc","mean")
	sqlSave(channel, temp6, tablename = "series_indices_test",append=
TRUE, rownames=FALSE, verbose = FALSE, test = FALSE, nastring = -999999, fast =
FALSE)

This is giving me an error:
	Error in sqlSave(channel, temp6, tablename = "series_indices_test",
append = TRUE,  :
        	unable to append to table %sseries_indices_test

My table definition is:
ind_id smallint(3) 
ser_id smallint(4)
period_id tinyint(2)
year smallint(4)
calc mediumint(6)
mean mediumint(6)

This error is related to the underscores I use in the table. Because, when I
change the table definition to (removing underscores):

indid smallint(3) 
serid smallint(4)
periodid tinyint(2)
year smallint(4)
calc mediumint(6)
mean mediumint(6)

and execute
	colnames(temp6) <-
c("indid","serid","periodid","year","calc","mean")
	sqlSave(channel, temp6, tablename = "series_indices_test",append=
TRUE, rownames=FALSE, verbose = FALSE, test = FALSE, nastring = -999999, fast =
FALSE)


it goes well.

Not using the underscores is not really prefered, as this table with this
underscores in the definition is used on a lot of places elsewhere. But if
removing the underscores is the only solution, let me know as well.


Any clue?

Using R 1.9.0, RODBC-package 1.4.0 (underlying database MySQL 4.0.16-standard)

Thanks,
Maarten


+-------------------------------
| Maarten van der Hoeven
| KNMI, De Bilt
| +31-30-2206 402
| maarten.van.der.hoeven at knmi.nl
+-------------------------------
-------------------------------------------------------------- 

Zie ook/see also: http://www.knmi.nl/maildisclaimer.html

Hi,

resending this question; guess someone missed it (?)

Thanks,
Maarten
> -----Original Message-----
> From: r-help-bounces at stat.math.ethz.ch
> [mailto:r-help-bounces at stat.math.ethz.ch]On Behalf Of Hoeven, Maarten
> van der
> Sent: Monday, May 10, 2004 12:34 PM
> To: r-help at stat.math.ethz.ch
> Subject: [R] sqlSave with underscores in table fieldname
> 
> 
> Hi group,
> 
> I try to write a frame to a table (RODBC). I use
> 	colnames(temp6) <- 
>
c("ind_id","ser_id","period_id","year","calc","mean")
> 	sqlSave(channel, temp6, tablename = 
> "series_indices_test",append= TRUE, rownames=FALSE, verbose = 
> FALSE, test = FALSE, nastring = -999999, fast = FALSE)
> 
> This is giving me an error:
> 	Error in sqlSave(channel, temp6, tablename = 
> "series_indices_test", append = TRUE,  : 
>         	unable to append to table %sseries_indices_test
> 
> My table definition is:
> ind_id smallint(3) 
> ser_id smallint(4)
> period_id tinyint(2)
> year smallint(4)
> calc mediumint(6)
> mean mediumint(6)
> 
> This error is related to the underscores I use in the table. 
> Because, when I change the table definition to (removing underscores):
> 
> indid smallint(3) 
> serid smallint(4)
> periodid tinyint(2)
> year smallint(4)
> calc mediumint(6)
> mean mediumint(6)
> 
> and execute
> 	colnames(temp6) <- 
>
c("indid","serid","periodid","year","calc","mean")
> 	sqlSave(channel, temp6, tablename = 
> "series_indices_test",append= TRUE, rownames=FALSE, verbose = 
> FALSE, test = FALSE, nastring = -999999, fast = FALSE)
> 
> 
> it goes well.
> 
> Not using the underscores is not really prefered, as this 
> table with this underscores in the definition is used on a 
> lot of places elsewhere. But if removing the underscores is 
> the only solution, let me know as well.
> 
> 
> Any clue?
> 
> Using R 1.9.0, RODBC-package 1.4.0 (underlying database MySQL 
> 4.0.16-standard)
> 
> Thanks,
> Maarten
> 
> 
> +-------------------------------
> | Maarten van der Hoeven
> | KNMI, De Bilt
> | +31-30-2206 402
> | maarten.van.der.hoeven at knmi.nl
> +-------------------------------
> -------------------------------------------------------------- 
> 
> Zie ook/see also: http://www.knmi.nl/maildisclaimer.html
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://www.stat.math.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! 
> http://www.R-project.org/posting-guide.html
>
-------------------------------------------------------------- 

Zie ook/see also: http://www.knmi.nl/maildisclaimer.html

Don't understand... What patch do you expect from me? And, I thought my
analysis is quite reproducable and thorough. It is im my situation, though.

I'm not patching something, or writing/issueing R-code. I'm
'just' an R-user with a problem/question.

In short: using underscores in SQL-tablefields  doesnt work, not using
underscores in SQL-tablefields does work. See below.

Question: is my observation correct, that RODBC cannot handle underscores in
Mysql-tablefield? If not correct, what is the solution I need to apply?


Thanks,
Maarten
> -----Original Message-----
> From: Prof Brian Ripley [mailto:ripley at stats.ox.ac.uk]
> Sent: Wednesday, May 12, 2004 2:50 PM
> To: Hoeven, Maarten van der
> Subject: RE: [R] sqlSave with underscores in table fieldname
> 
> 
> No, we saw it.
> 
> What I missed was the analysis and patch I was expecting from you.
> 
> On Wed, 12 May 2004, Hoeven, Maarten van der wrote:
> 
> > Hi,
> > 
> > resending this question; guess someone missed it (?)
> > 
> > Thanks,
> > Maarten
> > 
> > > -----Original Message-----
> > > From: r-help-bounces at stat.math.ethz.ch
> > > [mailto:r-help-bounces at stat.math.ethz.ch]On Behalf Of 
> Hoeven, Maarten
> > > van der
> > > Sent: Monday, May 10, 2004 12:34 PM
> > > To: r-help at stat.math.ethz.ch
> > > Subject: [R] sqlSave with underscores in table fieldname
> > > 
> > > 
> > > Hi group,
> > > 
> > > I try to write a frame to a table (RODBC). I use
> > > 	colnames(temp6) <- 
> > >
c("ind_id","ser_id","period_id","year","calc","mean")
> > > 	sqlSave(channel, temp6, tablename = 
> > > "series_indices_test",append= TRUE, rownames=FALSE,
verbose =
> > > FALSE, test = FALSE, nastring = -999999, fast = FALSE)
> > > 
> > > This is giving me an error:
> > > 	Error in sqlSave(channel, temp6, tablename = 
> > > "series_indices_test", append = TRUE,  : 
> > >         	unable to append to table %sseries_indices_test
> > > 
> > > My table definition is:
> > > ind_id smallint(3) 
> > > ser_id smallint(4)
> > > period_id tinyint(2)
> > > year smallint(4)
> > > calc mediumint(6)
> > > mean mediumint(6)
> > > 
> > > This error is related to the underscores I use in the table. 
> > > Because, when I change the table definition to (removing 
> underscores):
> > > 
> > > indid smallint(3) 
> > > serid smallint(4)
> > > periodid tinyint(2)
> > > year smallint(4)
> > > calc mediumint(6)
> > > mean mediumint(6)
> > > 
> > > and execute
> > > 	colnames(temp6) <- 
> > >
c("indid","serid","periodid","year","calc","mean")
> > > 	sqlSave(channel, temp6, tablename = 
> > > "series_indices_test",append= TRUE, rownames=FALSE,
verbose =
> > > FALSE, test = FALSE, nastring = -999999, fast = FALSE)
> > > 
> > > 
> > > it goes well.
> > > 
> > > Not using the underscores is not really prefered, as this 
> > > table with this underscores in the definition is used on a 
> > > lot of places elsewhere. But if removing the underscores is 
> > > the only solution, let me know as well.
> > > 
> > > 
> > > Any clue?
> > > 
> > > Using R 1.9.0, RODBC-package 1.4.0 (underlying database MySQL 
> > > 4.0.16-standard)
> > > 
> > > Thanks,
> > > Maarten
> > > 
> > > 
> > > +-------------------------------
> > > | Maarten van der Hoeven
> > > | KNMI, De Bilt
> > > | +31-30-2206 402
> > > | maarten.van.der.hoeven at knmi.nl
> > > +-------------------------------
> > > -------------------------------------------------------------- 
> > > 
> > > Zie ook/see also: http://www.knmi.nl/maildisclaimer.html
> > > 
> > > ______________________________________________
> > > R-help at stat.math.ethz.ch mailing list
> > > https://www.stat.math.ethz.ch/mailman/listinfo/r-help
> > > PLEASE do read the posting guide! 
> > > http://www.R-project.org/posting-guide.html
> > >
> > -------------------------------------------------------------- 
> > 
> > Zie ook/see also: http://www.knmi.nl/maildisclaimer.html
> > 
> > ______________________________________________
> > R-help at stat.math.ethz.ch mailing list
> > https://www.stat.math.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide! 
> http://www.R-project.org/posting-guide.html
> > 
> > 
> 
> -- 
> Brian D. Ripley,                  ripley at stats.ox.ac.uk
> Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
> University of Oxford,             Tel:  +44 1865 272861 (self)
> 1 South Parks Road,                     +44 1865 272866 (PA)
> Oxford OX1 3TG, UK                Fax:  +44 1865 272595
> 
>
-------------------------------------------------------------- 

Zie ook/see also: http://www.knmi.nl/maildisclaimer.html

my apologies; this messages shouldnt have been send to the list. I did reply to
Brian directly.

Sorry for your time.
> -----Original Message-----
> From: r-help-bounces at stat.math.ethz.ch
> [mailto:r-help-bounces at stat.math.ethz.ch]On Behalf Of Hoeven, Maarten
> van der
> Sent: Wednesday, May 12, 2004 3:04 PM
> To: r-help at stat.math.ethz.ch
> Subject: RE: [R] sqlSave with underscores in table fieldname
> 
> 
> Don't understand... What patch do you expect from me? And, I 
> thought my analysis is quite reproducable and thorough. It is 
> im my situation, though.
> 
> I'm not patching something, or writing/issueing R-code. I'm 
> 'just' an R-user with a problem/question. 
> 
> In short: using underscores in SQL-tablefields  doesnt work, 
> not using underscores in SQL-tablefields does work. See below. 
> 
> Question: is my observation correct, that RODBC cannot handle 
> underscores in Mysql-tablefield? If not correct, what is the 
> solution I need to apply?
> 
> 
> Thanks,
> Maarten
> 
> > -----Original Message-----
> > From: Prof Brian Ripley [mailto:ripley at stats.ox.ac.uk]
> > Sent: Wednesday, May 12, 2004 2:50 PM
> > To: Hoeven, Maarten van der
> > Subject: RE: [R] sqlSave with underscores in table fieldname
> > 
> > 
> > No, we saw it.
> > 
> > What I missed was the analysis and patch I was expecting from you.
> > 
> > On Wed, 12 May 2004, Hoeven, Maarten van der wrote:
> > 
> > > Hi,
> > > 
> > > resending this question; guess someone missed it (?)
> > > 
> > > Thanks,
> > > Maarten
> > > 
> > > > -----Original Message-----
> > > > From: r-help-bounces at stat.math.ethz.ch
> > > > [mailto:r-help-bounces at stat.math.ethz.ch]On Behalf Of 
> > Hoeven, Maarten
> > > > van der
> > > > Sent: Monday, May 10, 2004 12:34 PM
> > > > To: r-help at stat.math.ethz.ch
> > > > Subject: [R] sqlSave with underscores in table fieldname
> > > > 
> > > > 
> > > > Hi group,
> > > > 
> > > > I try to write a frame to a table (RODBC). I use
> > > > 	colnames(temp6) <- 
> > > >
c("ind_id","ser_id","period_id","year","calc","mean")
> > > > 	sqlSave(channel, temp6, tablename = 
> > > > "series_indices_test",append= TRUE,
rownames=FALSE, verbose =
> > > > FALSE, test = FALSE, nastring = -999999, fast = FALSE)
> > > > 
> > > > This is giving me an error:
> > > > 	Error in sqlSave(channel, temp6, tablename = 
> > > > "series_indices_test", append = TRUE,  : 
> > > >         	unable to append to table %sseries_indices_test
> > > > 
> > > > My table definition is:
> > > > ind_id smallint(3) 
> > > > ser_id smallint(4)
> > > > period_id tinyint(2)
> > > > year smallint(4)
> > > > calc mediumint(6)
> > > > mean mediumint(6)
> > > > 
> > > > This error is related to the underscores I use in the table.
> > > > Because, when I change the table definition to (removing 
> > underscores):
> > > > 
> > > > indid smallint(3) 
> > > > serid smallint(4)
> > > > periodid tinyint(2)
> > > > year smallint(4)
> > > > calc mediumint(6)
> > > > mean mediumint(6)
> > > > 
> > > > and execute
> > > > 	colnames(temp6) <- 
> > > >
c("indid","serid","periodid","year","calc","mean")
> > > > 	sqlSave(channel, temp6, tablename = 
> > > > "series_indices_test",append= TRUE,
rownames=FALSE, verbose =
> > > > FALSE, test = FALSE, nastring = -999999, fast = FALSE)
> > > > 
> > > > 
> > > > it goes well.
> > > > 
> > > > Not using the underscores is not really prefered, as this 
> > > > table with this underscores in the definition is used on a 
> > > > lot of places elsewhere. But if removing the underscores is 
> > > > the only solution, let me know as well.
> > > > 
> > > > 
> > > > Any clue?
> > > > 
> > > > Using R 1.9.0, RODBC-package 1.4.0 (underlying database
MySQL
> > > > 4.0.16-standard)
> > > > 
> > > > Thanks,
> > > > Maarten
> > > > 
> > > > 
> > > > +-------------------------------
> > > > | Maarten van der Hoeven
> > > > | KNMI, De Bilt
> > > > | +31-30-2206 402
> > > > | maarten.van.der.hoeven at knmi.nl
> > > > +-------------------------------
> > > >
--------------------------------------------------------------
> > > > 
> > > > Zie ook/see also: http://www.knmi.nl/maildisclaimer.html
> > > > 
> > > > ______________________________________________
> > > > R-help at stat.math.ethz.ch mailing list
> > > > https://www.stat.math.ethz.ch/mailman/listinfo/r-help
> > > > PLEASE do read the posting guide! 
> > > > http://www.R-project.org/posting-guide.html
> > > >
> > > -------------------------------------------------------------- 
> > > 
> > > Zie ook/see also: http://www.knmi.nl/maildisclaimer.html
> > > 
> > > ______________________________________________
> > > R-help at stat.math.ethz.ch mailing list
> > > https://www.stat.math.ethz.ch/mailman/listinfo/r-help
> > > PLEASE do read the posting guide! 
> > http://www.R-project.org/posting-guide.html
> > > 
> > > 
> > 
> > -- 
> > Brian D. Ripley,                  ripley at stats.ox.ac.uk
> > Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
> > University of Oxford,             Tel:  +44 1865 272861 (self)
> > 1 South Parks Road,                     +44 1865 272866 (PA)
> > Oxford OX1 3TG, UK                Fax:  +44 1865 272595
> > 
> >
> -------------------------------------------------------------- 
> 
> Zie ook/see also: http://www.knmi.nl/maildisclaimer.html
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://www.stat.math.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! 
> http://www.R-project.org/posting-guide.html
>
-------------------------------------------------------------- 

Zie ook/see also: http://www.knmi.nl/maildisclaimer.html