8.0 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 ODBC driver. Like any other ODBC driver, 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 also 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 Denodo ODBC driver. Denodo Platform 8.0 includes an ODBC driver named DenodoODBC and it is located under the <DENODO_HOME>\tools\client-drivers\odbc directory. Extract the folders in this directory and run the programs inside to install the drivers. Once this is complete, restart your Virtual DataPort Server from the Denodo Control Panel.

Select the 32-bit or 64-bit 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 DenodoODBC ANSI or Unicode driver, and click on the 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 by clicking on the Datasource button (a pop-up will open). Select the same options shown in the screenshots below and write "SET QUERYTIMEOUT TO 3600000" in the Connect Settings Box on page 2:

Create Driver

Finally, click the Ok button and then click 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).

For an example of an 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 the 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 the > button in the middle and you should see name, surname, client_id, and total_amount appear underneath "Columns in your query:"
  6. Click on Next (three times) and then Finish.

And voilà! The results from Denodo are populated into the MS Excel spreadsheet!

Create Driver