How to create, test and use the RODBC connector in R

back to Recipes for EDA CookBook Eda

1. Create the ODBC connector

If you plan to connect to postgres or Oracle database, check that you have the odbc connector working for those bases. The following is for postgres database start> configuration panel> administration tools> RODBC
click on add a connection and select new ODBC ANSI
note ANSI will do for most windows database, but if your database is UTF8, select Unicode







The Data Source name will indicate your ODBC connection used in R (in the xml file as shown below). Test your connector, it should return connection successfull. If not check that your port is 5432 (it can be different if you have installled several postgres servers). Check also that your usernames and passwords are correct.

2. Connect from R to your database

So far on windows RODBC is still the best way to handle a connection. There is also a Direct R_postgres driver which so far I find less satisfying ​http://rpgsql.sourceforge.net/

At the root of EDAload there must be a EDAload.xml file which allows to load the password, name, and ODBC connector name of the local machine. Edit it (with notepad++ ​http://notepad-plus.sourceforge.net/fr/site.htm), and write down the appropriate ODBCnames, user, and passwords. It looks like the following

<?xml version="1.0" encoding="windows-1252" standalone="yes"?>
<EDA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<Utilisateur>
		<lienODBCOracle>mapopale1</lienODBCOracle>
		<uidOracle>bdmap</uidOracle>
		<pwdOracle>bdmap</pwdOracle>
		<lienODBCBar>ROE</lienODBCBar>
		<uidBar>postgres</uidBar>
		<pwdBar>postgres</pwdBar>
		<pgwd>C:/Documents and Settings/cedric/Mes documents/Migrateur/programmes/workspace3.5/EDA</pgwd>
		<datawd>C:/Documents and Settings/cedric/Mes documents/Migrateur/programmes/workspace3.5/EDAdata</datawd>
	</Utilisateur>
</EDA>

Load init.r note : you are supposed to put the xml file where the R program can directly load it. In the case of EDA you will be at the root of the EDA project. In Eclipse, a variable working directory allows to start R directly at the right location. If not within eda, just keep the 28 first lines, ie delete the source that point to the EDA project to test your connection write

library(RODBC)
odbcConnect(dsn=baseODBC[1],uid=baseODBC[2],pwd=baseODBC[3])
> RODBC Connection 4
> Details:
> case=tolower
> ...

The possible commands for RODBC are

help.search("ODBC") #returns the following
RODBC::odbcUpdate       Internal RODBC functions
RODBC::RODBC            ODBC Database Connectivity
RODBC::odbcQuery        Low-level ODBC functions
RODBC::odbcClose        ODBC Close Connections
RODBC::odbcConnect      ODBC Open Connections
RODBC::odbcDataSources
                        List ODBC Data Sources
RODBC::odbcGetInfo      Request Information on an ODBC Connection
RODBC::odbcSetAutoCommit
                        ODBC Set Auto-Commit Mode
RODBC::sqlPrimaryKeys   Query Column Structure in ODBC Tables
RODBC::sqlCopy          ODBC Copy
RODBC::sqlDrop          Deletion Operations on Tables in ODBC databases
RODBC::sqlFetch         Reading Tables from ODBC Databases
RODBC::sqlQuery         Query an ODBC Database
RODBC::sqlSave          Write a Data Frame to a Table in an ODBC
                        Database
RODBC::sqlTables        List Tables on an ODBC Connection
RODBC::sqlTypeInfo      Request Information about Data Types in an ODBC

Now let's try to get some data from a table "my_table"

con<-odbcConnect(dsn=baseODBC[1],uid=baseODBC[2],pwd=baseODBC[3])
sqlQuery(channel=con,query=sqlQuery(channel, query=select * from my_table limit 100)

3 - Some hopefully usefull functions

Attached to this files are classes used to handle the connection and queries. They are developped and maintained for the stacomi project. Download them and in the following change your path accordingly. Here I assume that you have loaded the ccm database. The backup for the CCM France is in EDAdata/EDACommun/dataEDAccm/CCM_2003.backup. Create the CCM database following instructions in

library(RODBC)
baseODBC=c("CCM",uid="postgres",pwd="eeldata3$$")
setGeneric("connect",def=function(objet,...) standardGeneric("connect"))
source("EDAcommun/prg/outils/ConnexionODBC.r")
source("EDAcommun/prg/outils/RequeteODBC.r")
source("EDAcommun/prg/outils/RequeteODBCwhere.r")
source("EDAcommun/prg/outils/RequeteODBCwheredate.r")
requete=new("RequeteODBC")
requete@baseODBC=baseODBC
requete@sql=paste("SELECT * FROM riversegments limit 100")
requete<-connect(requete)	
data<-requete@query