how to?

Below is what I use to connect to MySQL. Along
with a moderate query. ?Perhaps Xavier might adapt
this for his use with SQLServer.

I am relatively new to R and find most of this to
be quite easy to do. Especially after reading the
help files in R.

And I use PremiumSoft's Navicat to provide a nice
GUI interface to MySQL.

Notice a couple of things in the code below.
`Candidate Name` wants a kind of quote like
below the tilde on the left side of my
keyboard. This is necessary if there are
spaces in your table's column name. Might
be MySQL specific.

library(RMySQL)
mycon <- dbConnect(MySQL(), user='root', dbname="mysql", password='pass')

rs <- dbSendQuery(mycon,
? "SELECT
? ? ? SUBSTR(`Precinct-Ballot Style`,1,4) AS 'PCT',
? ? ? SUM(`Total Count/Registered Voter`) AS 'D_TOT', ? ? ? `Candidate Name` AS D_CANDIDATE_NAME
? ?FROM precinct
? ?WHERE
? ? ? ?`Contest Title` LIKE '%108'
? ? GROUP BY SUBSTR(`Precinct-Ballot Style`,1,4);"
? ? )
data_dem <- fetch(rs, n = -1)
dbClearResult(rs)

Good luck,
Jim Burke


Prof Brian Ripley wrote:

On Wed, 22 Apr 2009,  wrote:

Hello,

I use windows XP, I have RGui with R version 2.8.1
I try to connect to a microsoft SQL database with is on a remote shared disk.
I have downloaded RODBC and DBI that I would like to use after if its simple.

For the moment I just try to connect with the DB, using

odbcConnect(dsn, uid = "", pwd = "", ...)
Actually I donnot understand what is dsn. The doc says

character string. A registered data source name.

But seriously it is not enough...

If you are using ODBC, it is. ?Did you read the README in the package?
And you ODBC documentation?

Whats that? The path to the DB?
I need a more detailed example than the one which is in the doc.

Like the ones in the installed tests.R file?

Thank you

xavier

On Fri, Apr 17, 2009 at 5:03 PM, Prof Brian Ripley
<ripley at stats.ox.ac.uk> wrote:

On Fri, 17 Apr 2009, wrote:

Hello,

I have downloaded R and I would like to do some sql queries. But I have
difficulties to find information. Do I need to download an additional

Have you read the 'R Data Import/Export Manual' that is installed with R? ?That covers this.

package? And then, what is the procedure?

All the information requested in the R posting guide is missing. ?We have no idea what platform you are using, what DBMS you are intending to use ....

See http://www.r-project.org/posting-guide.html

Thank you very much for your help.

Xavier

? ? ? [[alternative HTML version deleted]]

Please also note that the R posting guide asked you not to send HTML mail.

--
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

------------------------------------------------------------------------