[R] RODBC, sqlSave and sqlAppend

[roger bos]

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 at 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 at gmail.com]
> Sent: 15 March 2005 19:40
> To: spjgmwn at iop.kcl.ac.uk
> Cc: r-help at 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 at 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 at 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 at 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
>