If you use Windows for your stats computing and your data is in a database (probably true for almost all corporate business analysts) R 2.12 has provided a unique procedural hitch for you NO BINARIES for packages used till now to read from these databases.
The Readme notes of the release say-
Packages related to many database system must be linked to the exact
version of the database system the user has installed, hence it does
not make sense to provide binaries for packages
RMySQL, ROracle, ROracleUI, RPostgreSQL
although it is possible to install such packages from sources by
install.packages('packagename', type='source')
after reading the manual 'R Installation and Administration'.
So how to connect to Databases if the Windows Binary is not available-
So how to connect to PostgreSQL and MySQL databases.
For Postgres databases-
You can update your PostgreSQL databases here-
http://www.postgresql.org/download/windows
Fortunately the RpgSQL package is still available for PostgreSQL
- Using the RpgSQL package
library(RpgSQL) #creating a connection con <- dbConnect(pgSQL(), user = "postgres", password = "XXXX",dbname="postgres") #writing a table from a R Dataset dbWriteTable(con, "BOD", BOD) # table names are lower cased unless double quoted. Here we write a Select SQL query dbGetQuery(con, 'select * from "BOD"') #disconnecting the connection dbDisconnect(con)
You can also use RODBC package for connecting to your PostgreSQL database but you need to configure your ODBC connections in
Windows Start Panel-
Settings-Control Panel-
Administrative Tools-Data Sources (ODBC)
You should probably see something like this screenshot.
Coming back to R and noting the name of my PostgreSQL DSN from above screenshot-( If not there just click on add-scroll to appropriate database -here PostgreSQL and click on Finish- add in the default values for your database or your own created database values-see screenshot for help with other configuring- and remember to click Test below to check if username and password are working, port is correct etc.
so once the DSN is probably setup in the ODBC (frightening terminology is part of databases)- you can go to R to connect using RODBC package
#loading RODBC
library(RODBC)
#creating a Database connection
# for username,password,database name and DSN name
chan=odbcConnect("PostgreSQL35W","postgres;Password=X;Database=postgres")
#to list all table names
sqlTables(chan)
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS 1 postgres public bod TABLE 2 postgres public database1 TABLE 3 postgres public tt TABLE
Now for MySQL databases it is exactly the same code except we download and install the ODBC driver from http://www.mysql.com/downloads/connector/odbc/
and then we run the same configuring DSN as we did for postgreSQL.
After that we use RODBC in pretty much the same way except changing for the default username and password for MySQL and changing the DSN name for the previous step.
channel <- odbcConnect("mysql","jasperdb;Password=XXX;Database=Test")
test2=sqlQuery(channel,"select * from jiuser")
test2
id username tenantId fullname emailAddress password externallyDefined enabled previousPasswordChangeTime1 1 jasperadmin 1 Jasper Administrator NA 349AFAADD5C5A2BD477309618DC NA 01
2 2 joe1ser 1 Joe User NA 4DD8128D07A NA 01
odbcClose(channel)While using RODBC for all databases is a welcome step, perhaps the change release notes for Window Users of R may need to be more substantiative than one given for R 2.12.2
Related Articles
- Q&A with PG West Presenter Josh Berkus about PostgreSQL and “Neat Widgets” (blogs.enterprisedb.com)
- Oracle MySQL Rival PostgreSQL Updated (pcworld.com)
- Postgres folks, consider the 2011 MySQL conference (xaprb.com)
- O’Reilly MySQL Conference CfP ends today (xaprb.com)
- EnterpriseDB Announces Support for PostgreSQL 9.0; The Best Leverage Against Exploding Enterprise Relational Database Costs (eon.businesswire.com)
- PostgreSQL Conference West: 2010 lands in San Francisco November 2nd through 4th (prweb.com)
- New Community version: GreenSQL FW: 1.3.0 released (greensql.com)
- RPostgreSQL 0.1-7 (dirk.eddelbuettel.com)
