Author: rareddy Date: 2010-07-19 18:06:04 -0400 (Mon, 19 Jul 2010) New Revision: 2356 Added: trunk/documentation/client-developers-guide/src/main/docbook/en-US/content/odbc.xml trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/ trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup1.png trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup2.png trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup3.png trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup4.png trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup5.png Modified: trunk/documentation/client-developers-guide/src/main/docbook/en-US/main.xml Log: TEIID-860: Adding ODBC support chapter to Client Developer's Guide. Added: trunk/documentation/client-developers-guide/src/main/docbook/en-US/content/odbc.xml =================================================================== --- trunk/documentation/client-developers-guide/src/main/docbook/en-US/content/odbc.xml (rev 0) +++ trunk/documentation/client-developers-guide/src/main/docbook/en-US/content/odbc.xml 2010-07-19 22:06:04 UTC (rev 2356) @@ -0,0 +1,234 @@ +<?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>Short for Open DataBase Connectivity, a standard database access method developed by the SQL Access group in 1992. + The goal of ODBC is to make it possible to access any data from any application, regardless of which database management + system (DBMS) is handling the data. ODBC manages this by inserting a middle layer, called a database driver , + between an application and the DBMS. The purpose of this layer is 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 provides the ODBC access to deployed VDBs in the Teiid runtime through + <ulink url="http://www.postgresql.org/">PostgreSQL</ulink>'s ODBC driver. This is possible by redirecting the ODBC commands + from a client application to a different port than default PostgreSQL port.</para> + + <note><para>By default, ODBC on the Teiid is enabled and running on on port <emphasis>35432</emphasis>.</para></note> + + <para>Before your application can use the ODBC, you must first install ODBC driver on same machine that + your application is running and then create Data Source Name (DSN) for that represents a connection profile for your Teiid VDB. + </para> + + <sect1 id="install"> + <title>Installing the ODBC Driver Client</title> + <para>The PostgreSQL ODBC driver that is needed to make the ODBC connection to Teiid is <emphasis>not</emphasis> + bundled with Teiid distribution. The driver needs be <ulink url="http://www.postgresql.org/ftp/odbc/versions/">downloade... + directly from PostgreSQL web site. We have tested with <emphasis>8.04.200</emphasis> version of the ODBC driver for + this release.</para> + + <sect2> + <title>Microsoft Windows</title> + <orderedlist> + <listitem><para>Download the ODBC driver from <ulink url="http://wwwmaster.postgresql.org/download/mirrors-ftp/odbc/versi... + PostgreSQL download site</ulink>.</para></listitem> + <listitem><para>Extract the contents of the ZIP file into a temporary location on you system. For example: "c:\temp\pgodbc"</para></listitem> + <listitem><para>Double click on "psqlodbc.msi" file 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 if you want install. + 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> + </sect2> + <sect2> + <title>Other *nix Platform Installations</title> + <para>For all other platforms other than Microsoft Windows, the ODBC driver needs compiled and built from the + source files provided. Download the ODBC driver source files from + <ulink url="http://wwwmaster.postgresql.org/download/mirrors-ftp/odbc/versi... + 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[ + perl Makefile.PL + make + make test + make install + ]]></programlisting> + + <para>Make sure that there are no errors during the "make test" phase. The the next step in the process is to create a + DSN for accessing the Teiid VDB.</para> + </sect2> + </sect1> + + <sect1> + <title>Configuring the Data Source Name (DSN)</title> + <sect2> + <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>Leave <emphasis>SSL Mode</emphasis> to disabled. SSL connections are currently not supported.</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> + </sect2> + + <sect2> + <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... 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 + UserName = <user-name> + Password = <password> + Database = <vdb-name> + ReadOnly = no + ServerType = Postgres + ConnSettings = + UseServerSidePrepare=1 + ByteaAsLongVarBinary=1 + Optimizer=0 + Ksqo=0 + Trace = No + TraceFile = /var/log/trace.log + Debug = No + DebugFile = /var/log/debug.log + ]]></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> + </sect2> + + </sect1> +</chapter> \ No newline at end of file Property changes on: trunk/documentation/client-developers-guide/src/main/docbook/en-US/content/odbc.xml ___________________________________________________________________ Name: svn:mime-type + text/plain Added: trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup1.png =================================================================== (Binary files differ) Property changes on: trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup1.png ___________________________________________________________________ Name: svn:mime-type + application/octet-stream Added: trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup2.png =================================================================== (Binary files differ) Property changes on: trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup2.png ___________________________________________________________________ Name: svn:mime-type + application/octet-stream Added: trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup3.png =================================================================== (Binary files differ) Property changes on: trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup3.png ___________________________________________________________________ Name: svn:mime-type + application/octet-stream Added: trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup4.png =================================================================== (Binary files differ) Property changes on: trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup4.png ___________________________________________________________________ Name: svn:mime-type + application/octet-stream Added: trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup5.png =================================================================== (Binary files differ) Property changes on: trunk/documentation/client-developers-guide/src/main/docbook/en-US/images/winsetup5.png ___________________________________________________________________ Name: svn:mime-type + application/octet-stream Modified: trunk/documentation/client-developers-guide/src/main/docbook/en-US/main.xml =================================================================== --- trunk/documentation/client-developers-guide/src/main/docbook/en-US/main.xml 2010-07-16 19:13:15 UTC (rev 2355) +++ trunk/documentation/client-developers-guide/src/main/docbook/en-US/main.xml 2010-07-19 22:06:04 UTC (rev 2356) @@ -50,6 +50,7 @@ <xi:include href="content/jdbc-transactions.xml" xmlns:xi="http://www.w3.org/2001/XInclude" /> <xi:include href="content/ssl.xml" xmlns:xi="http://www.w3.org/2001/XInclude" /> <xi:include href="content/using-hibernate.xml" xmlns:xi="http://www.w3.org/2001/XInclude" /> + <xi:include href="content/odbc.xml" xmlns:xi="http://www.w3.org/2001/XInclude" /> <xi:include href="content/appendix-unsupported-jdbc.xml" xmlns:xi="http://www.w3.org/2001/XInclude" /> <xi:include href="content/appendix-selfcerts.xml" xmlns:xi="http://www.w3.org/2001/XInclude" />