[R-sig-DB] how to?

[Jim Burke]

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, XFM 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 using 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.
>>>
>>>> _______________________________________________
>>>> R-sig-DB mailing list -- R Special Interest Group
>>>> R-sig-DB using stat.math.ethz.ch
>>>> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>>>>
>>>
>>> -- 
>>> Brian D. Ripley,                  ripley using 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
>>
>> _______________________________________________
>> R-sig-DB mailing list -- R Special Interest Group
>> R-sig-DB using stat.math.ethz.ch
>> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>