<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN" "http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd" [
<!ENTITY % CustomDTD SYSTEM "../../../../../../docbook/custom.dtd">
%CustomDTD;
]>
<chapter id="ODBC">
    <title>ODBC Support</title>
    
    <para>Open DataBase Connectivity (ODBC) is a standard database access method developed by the SQL Access group in 1992. 
    ODBC, just like JDBC in Java, allows consistent client access regardless of which database management 
    system (DBMS) is handling the data.  ODBC uses a driver to translate the application's data queries 
    into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant 
    -- that is, the application must be capable of issuing ODBC commands and the 
    DBMS must be capable of responding to them.</para>

    <para>Teiid can provide ODBC access to deployed VDBs in the Teiid runtime through 
     <ulink url="http://www.postgresql.org/">PostgreSQL</ulink>'s ODBC driver. 
     This is possible because Teiid has specialized handling that allows it emulate a PostgreSQL server and respond appropriate to expected
     metadata queries.</para>
     
     <note><para>By default, ODBC on the Teiid is enabled and running on on port <emphasis>35432</emphasis>.</para></note>
     
     <para>Before an application can use ODBC, you must first install the ODBC driver on same machine that 
     the application is running on and then create Data Source Name (DSN) that represents a connection profile for your Teiid VDB.
     </para>
     
     <warning><para>Teiid currently only supports plain text passward authentication for ODBC.  
     If the client/server are not configured to use SSL, the password will be sent in plain text over the network.  
     If you need secure passwords in transit and are not using SSL, then consider installing a security domain 
     that will accept safe password values from the client (for example encrypted or hashed).</para></warning>

    <section id="install">
        <title>Installing the ODBC Driver Client</title>
        <para>A PostgreSQL ODBC driver needed to make the ODBC connection to Teiid is <emphasis>not</emphasis> 
        bundled with the Teiid distribution. The appropriate driver needs be <ulink url="http://www.postgresql.org/ftp/odbc/versions/">downloaded</ulink> 
        directly from the PostgreSQL web site. We have tested with <emphasis>8.04.200</emphasis> version of the ODBC driver.</para>
        
        <section>
            <title>Microsoft Windows</title>
            <orderedlist>
                <listitem><para>Download the ODBC driver from <ulink url="http://wwwmaster.postgresql.org/download/mirrors-ftp/odbc/versions/msi/psqlodbc_08_04_0200.zip">
                PostgreSQL download site</ulink>. If you are looking for 64-bit Windows driver download the driver from <ulink url="http://code.google.com/p/visionmap/wiki/psqlODBC">here</ulink>. </para></listitem>
                <listitem><para>Extract the contents of the ZIP file into a temporary location on your system. For example: "c:\temp\pgodbc"</para></listitem>
                <listitem><para>Double click on "psqlodbc.msi" file or (.exe file in the case of 64 bit) to start installation of the driver.</para></listitem>
                 <listitem>
                    <para>The Wizard appears as</para>
                    <figure id="step1">
                    <title>Welcome Screen </title>
                    <graphic align="center" scale="100" fileref="../images/winsetup1.png" />
                    </figure>
                    <para>Click "Next".</para>
                </listitem>
                 <listitem>
                    <para>The next step of the wizard displays.</para>
                    <figure id="step2">
                    <title>End-User License Agreement </title>
                    <graphic align="center" scale="100" fileref="../images/winsetup2.png" />
                    </figure>
                    <para>Carefully read it, and check the "I accept the terms in the License Agreement", 
                    if you are agreeing to the licensing terms. Then click "Next".</para>
                </listitem>
                 <listitem>
                    <para>The next step of the wizard displays.</para>
                    <figure id="step3">
                        <title>Setup</title>
                        <graphic align="center" scale="100" fileref="../images/winsetup3.png" />
                    </figure>
                    <para>If you want to install in a different directory than the default that is already selected, 
                    click the "Browse" button and select a directory. 
                    Click "Next" to start installing in the selected directory.</para>
                </listitem>
                 <listitem>
                    <para>The next step of the wizard displays.</para>
                    <figure id="step4">
                    <title>Confirm the Install</title>
                    <graphic align="center" scale="100" fileref="../images/winsetup4.png" />
                    </figure>
                    <para>This step summarizes the choices you have made in the wizard.  Review this information.  
                    If you need to change anything, you can use the Back button to return to previous steps. 
                    Click "Install" to proceed.</para>
                </listitem>
                 <listitem>
                    <para>1.The installation wizard copies the necessary files to the location 
                    you specified.  When it finishes, the following screen displays.</para>
                    <figure id="step5">
                    <title>Finish</title>
                    <graphic align="center" scale="100" fileref="../images/winsetup5.png" />
                    </figure>
                    <para>Click "Finish" to complete.</para>
                </listitem>                
            </orderedlist>
        </section>
        <section>
            <title>Other *nix Platform Installations</title>
            <para>For all other platforms other than Microsoft Windows, the ODBC driver needs built from the 
            source files provided. Download the ODBC driver source files from 
            <ulink url="http://wwwmaster.postgresql.org/download/mirrors-ftp/odbc/versions/src/psqlodbc-08.04.0200.tar.gz">
                the PostgreSQL download site</ulink>. Untar the files to a temporary location. For example: "~/tmp/pgodbc". 
                Build and install the driver by running the commands below.</para>
            <note><para>You should use super user account or use "sudo" command for running the "make install" command. </para></note>
            <programlisting><![CDATA[
                % tar -zxvf psqlodbc-xx.xx.xxxx.tar.gz
                % cd psqlodbc-xx.xx.xxxx
                % ./configure
                % make
                % make install
            ]]></programlisting>
            <para>Some *nix distributions may already provide binary forms of the appropriate driver, which can be used as an alternative to
            building from source.</para>
        </section>
    </section>
    
    <section>
        <title>Configuring the Data Source Name (DSN)</title>
        <section>
            <title>Windows Installation</title>
            <para>Once you have installed the ODBC Driver Client software on your workstation, you have to 
            configure it to connect to a Teiid Runtime.  Note that the following instructions are specific 
            to the Microsoft Windows Platform.</para>
            
            <para>To do this, you must have logged into the workstation with administrative rights, and you 
            need to use the Control Panel’s <emphasis>Data Sources (ODBC)</emphasis> applet to add a new data source name.            
            </para>
            
            <para>Each data source name you configure can only access one VDB within a Teiid System.  To make more than 
            one VDB available, you need to configure more than one data source name.</para>
            
            <para>Follow the below steps in creating a data source name (DSN)</para>
            <orderedlist numeration="arabic">
                <listitem><para>From the Start menu, select Settings > Control Panel.</para></listitem>
                <listitem><para>The Control Panel displays.  Double click <emphasis>Administrative Tools</emphasis>.</para></listitem>
                <listitem><para>Then Double-click <emphasis>Data Sources (ODBC)</emphasis>.</para></listitem>
                <listitem><para>The ODBC Data Source Administrator applet displays.  Click the tab 
                associated with the type of DSN you want to add.</para></listitem>
                <listitem><para>The Create New Data Source dialog box displays.  In the Select a driver for which you 
                want to set up a data source table, select <emphasis>PostgreSQL Unicode</emphasis>.</para></listitem>
                <listitem><para>Click Finish</para></listitem>
                <listitem><para>The PostgreSQL ODBC DSN Setup dialog box displays.</para></listitem>
                
                <listitem>
                    <figure id="dsn1">
                        <title>Main Screen</title>
                        <graphic align="center" scale="100" fileref="../images/dsnsetup1.png" />
                    </figure> 
                    <para>In the <emphasis>Data Source</emphasis> Name edit box, type the name you want to assign to this data source.</para>
                    <para>In the <emphasis>Database</emphasis> edit box, type the name of the virtual database you want to access through this data source.</para>               
                    <para>In the <emphasis>Server</emphasis> edit box, type the host name or IP address of your Teiid runtime. If connecting 
                    via a firewall or NAT address, the firewall address or NAT address should be entered. </para>
                    <para>In the Port edit box, type the port number to which the Teiid System listens for ODBC requests. By default, Teiid 
                    listenes for ODBC requests on port 35432</para>
                    <para>In the <emphasis>User Name</emphasis> and <emphasis>Password</emphasis> edit boxes, supply the user name and password
                    for the Teiid runtime access.</para>
                    <para>Provide any description about the data source in the <emphasis>Description</emphasis> field.</para>
                </listitem>
                
                <listitem>
                    <para>Click on the <emphasis>Datasource</emphasis> button, you will see this below figure. Configure options as shown.</para>
                    <figure id="dsn2">
                        <title>DSN Options Page-1</title>
                        <graphic align="center" scale="100" fileref="../images/dsnsetup2.png" />
                    </figure>                 
                    
                    <para>Click on "page2" and make sure the options are selected as shown</para>
                    <figure id="dsn3">
                        <title>DSN Options Page-2</title>
                        <graphic align="center" scale="100" fileref="../images/dsnsetup3.png" />
                    </figure>                                                         
                </listitem>
                
                <listitem><para> Click "save" and you can optionally click "test" to validate your 
                connection if the Teiid is running.</para></listitem>
            </orderedlist>
            <para>You have configured a Teiid's  virtual database as a data source for your ODBC applications. Now you can 
            use applications such as Excel, Access to query the data in the VDB</para>            
        </section>
        
        <section>
            <title>Other *nix Platform Installations</title>
            <para>Before you can access Teiid using ODBC on any *nix platforms, you need to either install a ODBC driver manager or verify 
            that one already exists. As the ODBC Driver manager Teiid recommends <ulink url="http://www.unixodbc.org/">unixODBC</ulink>. 
            If you are working with RedHat Linux or Fedora you can check the graphical "yum" installer to search, find and install unixODBC.
            Otherwise you can <ulink url="http://www.unixodbc.org/unixODBC-2.3.0.tar.gz">download</ulink> the unixODBC manager here. 
            To install, simply untar the contents of the file to a temporary location and execute the following commands as super user.</para>
            <programlisting><![CDATA[
                ./configure
                make
                make install 
            ]]></programlisting>
            <para>Check  <ulink url="http://www.unixodbc.org/">unixODBC</ulink> website site for more information, if you 
            run into any issues during the installation.</para>        
            
            <para>Now, to o verify that PostgreSQL driver installed correctly from earlier step, execute the following command</para>    
            <programlisting><![CDATA[
            odbcinst -q -d
            ]]></programlisting>
            
            <para>That should show you all the ODBC drivers installed in your system. Now it is time to create a DSN. 
            Edit "/etc/odbc.ini" file and add the following </para>
            
            <programlisting><![CDATA[
                [<DSN name>]
                Driver = /usr/lib/psqlodbc.so
                Description = PostgreSQL Data Source
                Servername = <Teiid Host name or ip>
                Port = 35432 
                Protocol = 7.4-1
                UserName = <user-name> 
                Password = <password>
                Database = <vdb-name>
                ReadOnly = no
                ServerType = Postgres
                ConnSettings = 
                UseServerSidePrepare=1
                ByteaAsLongVarBinary=1
                Optimizer=0
                Ksqo=0
                Debug=0
                Fetch = 10000
                # enable below when dealing large resultsets
                #UseDeclareFetch=1                
            ]]></programlisting>     
            
            <para>Note that you need "sudo" permissions to edit the "/etc/odbc.ini" file. 
            For all the available configurable options that you can use in defining a DSN can be found <ulink url="http://psqlodbc.projects.postgresql.org/config.html">
            here</ulink> on postgreSQL ODBC page.</para>    
            
            <para>Once you are done with defining the DSN, you can verify your DSN using the following command            
            </para>
            <programlisting><![CDATA[
                    isql <DSN-name> [<user-name> <password>] < commands.sql            
            ]]></programlisting>                             
            
            <para>where "commands.sql" file contains the SQL commands you would like to execute.</para>
        </section>
        
    </section>
    
    <section>
        <title>DSN Less Connection</title>
        <para>You can also connect to Teiid VDB using ODBC with out explicitly creating a DSN. However, in these scenarios your 
        application needs, what is called as "DSN less connection string". The below is a sample connection string </para>
        
        <para>For Windows:</para>
        <programlisting><![CDATA[
           ODBC;DRIVER={PostgreSQL Unicode};DATABASE=<vdb-name>;SERVER=<host-name>;PORT=<port>;Uid=<username>;Pwd=<password>            
        ]]></programlisting>
        
        <para>>For *nix:</para>
        <programlisting><![CDATA[
           ODBC;DRIVER={PostgreSQL};DATABASE=<vdb-name>;SERVER=<host-name>;PORT=<port>;Uid=<username>;Pwd=<password>            
        ]]></programlisting>  
                  
    </section>
</chapter>