Re: [R] RODBC, sqlSave and sqlAppend

From: roger bos <roger.bos_at_gmail.com>
Date: Thu 17 Mar 2005 - 01:37:52 EST

ODBC also doesn't like the name 'index' for a column name. It also doesn't like any '.' periods in the column name. You can use a name like 'from' as a column name, but then when you query it you have to put [] around it because from is a sql key word. Most of these issues are sql issues rather than R issues, but still good to know and share.

On Wed, 16 Mar 2005 13:54:35 -0000, Matthew W Nash <spjgmwn@iop.kcl.ac.uk> wrote:
> If anyone is interested, I found out that something (Access or ODBC?) didn't
> like the column name 'Left', after changing it to something else, both
> sqlSave and sqlUpdate are working as expected.
>
> Thanks to those that replied.
>
> Matthew Nash,
>
> Post-Doctoral Research Worker,
> GENDEP study,
> SGDP, Institute of Psychiatry,
> PO82, Room CB.15,
> 16 De Crespigny Park,
> London, SE5 8AF,
> United Kingdom
>
> Phone: (+44) 207 848 0805
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> ~
> Announcing the 6th MRC SGDP SUMMER SCHOOL 25 - 29 July 2005 with courses in
> a) Twin model fitting, Mx
> b) Microarrays (Affymetrix), gene expression, SNPs
> c) Linkage, association and allied methods
> http://sgdp.iop.kcl.ac.uk/summerschool/
>
> -----Original Message-----
> From: roger bos [mailto:roger.bos@gmail.com]
> Sent: 15 March 2005 19:40
> To: spjgmwn@iop.kcl.ac.uk
> Cc: r-help@stat.math.ethz.ch
> Subject: Re: [R] RODBC, sqlSave and sqlAppend
>
> I use RODBC all day every day and while I am pretty happy with it, I
> was never able to make a table separately and append to it using
> sqlSave. Nevertheless, maybe my observations will help.
>
> I always let sqlSave make the table for me. Make sure the table
> doesn't exist and it will make it. I never use sqlAppend, I use
> sqlSave(..., append=TRUE). Also, how do you know you are not getting
> an error. Try 'go <- sqlSave(...)' and then type go and if it returns
> 'character(0)' then you have no error, otherwise you will see an
> extremely brief hint of what went wrong. Finally, I always have my
> database open when I sqlSave and that is never a problem, so you don't
> need to close MS access AFAIK. Its probably easier to use sqlQuery
> right after sqlSave to verify that it worked.
>
> Once sqlSave works, then you can use standard SQL statements to alter
> the table and alter to columns to make the datatypes whatever you want
> and then use 'insert into' statements to move the data when you want.
> So basically, because I save to a temporary table, alter the columns
> to change the datatypes, and then copy that to my final table. Sounds
> like a lot of work, but it can be automated once you write the command
> in R.
>
> Maybe someone will provide you with a better answer, but this might
> get you started.
>
> Thanks,
>
> Roger
>
> On Tue, 15 Mar 2005 15:19:26 -0000, Matthew W Nash
> <spjgmwn@iop.kcl.ac.uk> wrote:
> > Hi all,
> >
> > I am currently trying to read, write and append data between R and MS
> access
> > using the RODBC library functions. I have no problems reading in the data
> > but when using sqlSave and sqlAppend it doesn't seem to work. I have made
> > sure that all the column names are sensible and there are no gaps etc in
> the
> > variables. My call looks like this:
> >
> > sqlSave(channel,treatlist,test=F)
> >
> > I've played with various options but what consistently happens is that R
> > writes a new table (column names a written and sensible variable types are
> > assigned), but it doesn't actually write any data into it. When I run
> > sqlAppend there are no error messages, but when I look at the MS access
> > database nothing has been written. (I always have the database closed when
> > doing this.)
> >
> > What am I doing wrong?
> >
> > Matthew Nash,
> >
> > Post-Doctoral Research Worker,
> > GENDEP study,
> > SGDP, Institute of Psychiatry,
> > PO82, Room CB.15,
> > 16 De Crespigny Park,
> > London, SE5 8AF,
> > United Kingdom
> >
> > Phone: (+44) 207 848 0805
> >
> >
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > ~
> > Announcing the 6th MRC SGDP SUMMER SCHOOL 25 - 29 July 2005 with courses
> in
> > a) Twin model fitting, Mx
> > b) Microarrays (Affymetrix), gene expression, SNPs
> > c) Linkage, association and allied methods
> > http://sgdp.iop.kcl.ac.uk/summerschool/
> >
> > ______________________________________________
> > R-help@stat.math.ethz.ch mailing list
> > https://stat.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
> >
>
> ______________________________________________
> R-help@stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
>



R-help@stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html Received on Thu Mar 17 01:49:55 2005

This archive was generated by hypermail 2.1.8 : Fri 03 Mar 2006 - 03:30:49 EST