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 (the one used in the xml file) 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