back to first page [..][[BR]]
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 [[BR]]
click on add a connection and select new ODBC ANSI [[BR]]
'' note ANSI will do for most windows database, but if your database is UTF8, select Unicode ''  [[BR]][[BR]]
[[BR]][[BR]][[Image(source:data/Docs/trac/RODBC.jpg)]][[BR]][[BR]]
[[Image(source:data/Docs/trac/RODBC2.jpg)]][[BR]][[BR]]
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