[R] Getting data from Table in RStudio

Nordlund, Dan (DSHS/RDA) NordlDJ at dshs.wa.gov
Tue Jul 1 02:03:31 CEST 2014

> -----Original Message-----
> From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-
> project.org] On Behalf Of Shanae Clarke
> Sent: Monday, June 30, 2014 1:58 PM
> To: r-help at r-project.org
> Subject: [R] Getting data from Table in RStudio
> Hello,
> I am new to R progaming and have just started using this program since
> last week. What i want to achieve is to use R to determine patterns in
> sales/ customer complaint etc. information located in a mysql database.
> I am not sure how to approach this or which technique i should use to
> do so. However, i had proceeded to add a dataset to RStudio using the
> following code:
> library(RODBC)
> dsn.name <- "MySQLlocal"
> user.name <- "orange"
> pwd <- ""
> ch <- odbcConnect(dsn=dsn.name,uid=user.name,pwd = pwd)
> res <- sqlFetch(ch, "<my_table_name>")
> odbcQuery(ch, "Select * from <my_table_name>")
> odbcClose(ch)
> When the code is run all that is returned is
> > library (RODBC)
> > dsn.name <- "MySQLlocal"
>  > user.name <- "orange"
> > pwd <- ""
> > ch <- odbcConnect(dsn=dsn.name,uid=user.name,pwd = pwd)
> > res <- sqlFetch(ch, "<my_table_name>")
> > odbcQuery(ch, "Select * from <my_table_name>")
>  [1] 1
> > odbcClose(ch)
> No other results.
> I was expecting to see the table values or some other data. Not that.
> Maybe I am misunderstanding the whole concept of how R works.
> If so could someone please help me to clarify what the problem is and
> perhaps point me to a tutorial or somewhere i can get a information and
> get a better understanding.
> Thank you. Your Help will be greatly appreciated.
> Gayon Clarke
> 	[[alternative HTML version deleted]]

1. For future reference, you should read the posting guide linked at the bottom of each email, and post only in plain text (no HTML).  

2. You should read the help files for each of the functions you are trying use.

3. Did you try to look at the object, res, that you created?  That should have contained the table that you fetched from the MySQL database.  You could have typed res and the table would have printed out.  You also could have used head(res) to look at the first few records, or used str(res) to examine the structure of res.

There is also an R-sig-DB list where you can get more specific help with database connectivity questions.

Hope this is helpful,


Daniel J. Nordlund
Research and Data Analysis Division
Services & Enterprise Support Administration
Washington State Department of Social and Health Services

More information about the R-help mailing list