Connecting from your application - ODBC Clients
ODBC (Open DataBase Connectivity) is a standard to access databases originally developed by Microsoft. ODBC provides an API to make the code independent of database systems and operating systems.
Denodo provides an ODBC interface but it requires the installation of the PostgreSQL ODBC driver. As any other ODBC drivers, you have to install it on the machine where the client application is running.
In this section you will learn how to access to the Denodo server using an ODBC client. This information is valid for any other ODBC connection. For the example, we will use MS Excel but feel free to use any other ODBC client.
Creating the DSN
The first thing that we have to do when connecting using ODBC is to install the PostgreSQL ODBC driver. You can find all the information about the installation in the following page: http://www.postgresql.org/ftp/odbc/versions/msi/. The supported versions are: 09.03.0400 (required to use Kerberos authentication), 09.03.0300, 09.00.0310, 09.00.0300, 09.00.0200, 08.04.0200, 08.04.0100).
Once you've installed the ODBC driver you will need to add a new user data source:
- Go to
Control Panel > Administrative Tools > Data Sources (ODBC).
Add User DSNor
Add System DSN(the difference is that "User DSN" can only be used by the current user and "System DSN" can be used by all the users of the system).
- Select the PostgreSQL ANSI or Unicode driver, and click on Finish button.
In the configuration dialog fill in the following information:
- Data Source: name of the ODBC source (e.g.
- Database: database in Denodo. (e.g.
- Server: host name of the Denodo server (e.g.
- Port: port of the Denodo server (e.g.
- User Name / Password: credentials to connect to Denodo (e.g.
Now, you have to configure some of the Advanced properties clicking on the Datasource button (a pop-up will open). Select the same options shown in the screenshots below:
Finally, click on Ok button and then click on Save button to finish.
Accessing Denodo using an ODBC client
Now, you have your environment ready to connect to Denodo using ODBC (remember than the previous steps are only valid to connect to the "tutorial" virtual database, so, if you want to connect to another database you will have to create a new DSN).
As an example of ODBC client application you can use the well-known Microsoft Excel. You will only have to select this DSN as a Data provider to import the customer data into the spreadsheet.
Please, follow these steps and see the results:
- Open a new workbook.
Data > From Other Sources > From Microsoft Query
- In the pop-up, select "Denodo Tutorial" and click on Ok.
- Select "amount_due_by_client" view.
- Click on “Next” (three times) and then “Finish”.
And voilà! Results are populated to the selected MS Excel spreadsheet!