back to first page ..
back to Recipes for EDA CookBook R

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

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. note : as far as I remember, this can be quite difficult for Oracle, or postgres see CookBook shptopostgres The following isntructions are for a postgres database but can be easily translated into Oracle. 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.

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 CookBook shptopostgres. Right clic on the database and choose restore (Restaurer) If you are not working in EDA, download the attached classes and in the following scripts change your path accordingly.

Using RequeteODBC

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

more on RequeteODBC

Some options @silent=FALSE can be used to obtain more verbose messages during the connection

requete=new("RequeteODBC")
requete@baseODBC=baseODBC
requete@silent=FALSE
requete@sql=paste("SELECT * FROM riversegments limit 100")
requete<-connect(requete)	
data<-requete@query

Getting the sql statement passed to the request can be handy when requests are used inside the program

assign("showmerequest",1,envir=.GlobalEnv)
requete=new("RequeteODBC")
requete@baseODBC=baseODBC
requete@sql=paste("SELECT * FROM riversegments limit 100")
requete<-connect(requete)
remove("showmerequest",envir=.GlobalEnv)

Finally, you might be using the connection in a context where you plan to leave the connection open to gain time In this case you can use a "RequeteODBC" object several times

requete=new("RequeteODBC")
requete@baseODBC=baseODBC
requete@silent=FALSE
requete@open=TRUE
requete@sql=paste("SELECT * FROM riversegments limit 100")
requete<-connect(requete)
requete<-connect(requete) # once more
odbcClose(requete@connexion)# don't forget to close the connection once finished (usually in a loop)

requeteODBCwhere

This class uses a class conversion setAs to buid the request and uses RequeteODBC, remember to use requete@select instead of requete@sql . The request@and is a character vector which can contain several statements. You can use request@order_by

assign("showmerequest",1,envir=.GlobalEnv)
requete=new("RequeteODBCwhere")
requete@baseODBC=baseODBC
requete@open=TRUE
requete@select="SELECT * FROM riversegments" # warning it is no longer called sql but only select
requete@where="WHERE drain_km2>5000"
requete@and=c("AND strahler in (1,2,3,4)","AND cum_len>100") # a character vector
requete@order_by="ORDER BY gid"
requete<-connect(requete)
data<-requete@query

requeteODBCwheredate

Similar to the previous but with a query over a period. Below an example taken from the stacomi project

requete=new("RequeteODBCwheredate")
requete@baseODBC<-baseODBC
requete@select<- "select * from t_operation_ope"
requete@datedebut=strptime("1996-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S")#must be a POSIXt
requete@datefin=strptime("2000-01-01 00:00:00",format="%Y-%m-%d %H:%M:%S")#must be a POSIXt
requete@colonnedebut="ope_date_debut" # the column name corresponding to the beginning
requete@colonnefin="ope_date_fin" # the column name corresponding to the end
requete@and<-c("AND ope_dic_identifiant=1","AND ope_dic_identifiant=2")
requete@order_by<-"ORDER BY ope_identifiant"
requete@silent=FALSE

ConnexionODBC

this class opens a connexion without closing it, and does not do any query. It should be used for testing purpose only, it is the mother class of all requeteODBC... classes