5.5 Tutorials

You can translate the document:

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).

Select the 32-bits or 64-bits version depending on the client that will use it.
E.g. Clients such as old MS Excel versions can use only the 32-bits ODBC driver, even if it is running on a 64 bits O.S.

NOTE

Once you've installed the ODBC driver you will need to add a new user data source:

  1. Go to Control Panel > Administrative Tools > Data Sources (ODBC).
  2. Select Add User DSN or 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).
  3. Select the PostgreSQL ANSI or Unicode driver, and click on Finish button.
Create Driver

In the configuration dialog fill in the following information:

  1. Data Source: name of the ODBC source (e.g. Denodo Tutorial).
  2. Database: database in Denodo. (e.g. tutorial).
  3. Server: host name of the Denodo server (e.g. localhost).
  4. Port: port of the Denodo server (e.g. 9996).
  5. User Name / Password: credentials to connect to Denodo (e.g. admin/admin).
Create Driver

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:

Create Driver

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.

This example was tested in MS Office 2010 version

NOTE

Please, follow these steps and see the results:

  1. Open a new workbook.
  2. Select Data > From Other Sources > From Microsoft Query
  3. In the pop-up, select "Denodo Tutorial" and click on Ok.
    Create Driver
  4. Select "amount_due_by_client" view.
    Create Driver
  5. Click on “Next” (three times) and then “Finish”.

And voilà! Results are populated to the selected MS Excel spreadsheet!

Create Driver

Add feedback