[R] RODBC, sqlSave and sqlAppend

[Matthew W Nash]

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
>