<?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="OpenConnection">
  <title>Connecting to a VDB through JDBC</title>
  <para>At this point you have deployed Teiid and your VDB. Now it's time to connect the sample application
   to this VDB, issue SQL queries, and view the returned, integrated data. Note that
    this process is no different than connecting to any other JDBC source like Oracle.</para>
  <section>
    <title>Stand-alone Java Application Deployment</title>
    
    <para>Before you can make a JDBC connection to the Teiid VDB, add the Teiid's driver jar file to your application's classpath</para>
    <programlisting>${jboss-install}/server/${profile}/lib/teiid-&versionNumber;-client.jar</programlisting>  
          
    <para>
      For a Java application to connect to a JDBC source, it needs a URL, user-id, and password. To
      connect to your VDB all you need is a URL and any additional optional properties that you would like to set.
       Teiid defaults to allowing the "user" as user with password as "user".  Additional user accounts can be added.
        A JDBC connection can be obtained through the Teiid driver
      <code>"org.teiid.jdbc.TeiidDriver"</code>
      with the URL syntax of
    </para>
    <para>
      <emphasis>jdbc:teiid:&lt;VDB-Name&gt;@mm(s)://&lt;host name&gt;:&lt;port&gt;</emphasis>
    </para>
	<para>
		You can add optional properties at the end of the URL using a semi-colon(;) name=value
		format. For example</para>
	<para />
    <para>
      <emphasis>jdbc:teiid:&lt;VDB-Name&gt;@mm(s)://&lt;host name&gt;:&lt;port&gt;;autoCommitTxn=DETECT</emphasis>
    </para>
    <para>
      Check out Client Developer's guide for all the optional connection properties
      in your URL. Here is sample code showing how to make JDBC connection.
    </para>
    <programlisting><![CDATA[public void execute() throws SQLException {
    String url = "jdbc:teiid:Portfolio@mm://localhost:31000";
    String sql = "select firstname, lastname from customer";
    
    Class.forName("org.teiid.jdbc.TeiidDriver");
    
    Connection connection;
    try{
        connection = DriverManager.getConnection(url, "user", "user");
        Statement statement = connection.createStatement();
        ResultSet results = statement.executeQuery(sql);
        while(results.next()) {
          System.out.println(results.getString(1));
          System.out.println(results.getString(2));
          ...
        }
        results.close();
        statement.close();
    } catch (SQLException e){
        e.printStackTrace();
        throw e;
    } finally {
        try{
          connection.close();
        }catch(SQLException e1){
          // ignore
        }              
    }
}]]></programlisting>
    <para>You can also use <code>org.teiid.jdbc.TeiidDataSource</code>
      to make connection in your Java application. For example, you can use following code fragment to make a 
      connection to the VDB and issuing the query exactly same as in the above example</para>
      <programlisting><![CDATA[TeiidDataSource ds = new TeiidDataSource(); 
ds.setDatabaseName("Portfolio");
ds.setUser("user");
ds.setPassword("user");

Connection connection = ds.getConnection();
...]]></programlisting>
      <para>TeiidDataSource source also provides an option to set optional parameters using the "set" methods on the 
       data source look. For all the allowable data source properties check out Client Developer's Guide.</para>       
  </section>
  <section>
  <title>Testing Your Teiid Deployment</title>
  <para>The Teiid installation includes a simple Java class which demonstrates JDBC access of the deployed VDB.
  To execute this demonstration, follow these steps:</para> 
  <orderedlist numeration="arabic">
    <listitem><para>Change to the ${jboss-install}/server/profile/teiid-examples/simpleclient directory</para></listitem>
    <listitem><para>Execute the run script (either for Linux or Windows)</para></listitem>
  </orderedlist>
    <para>Depending on the VDB you deployed, see the relevant README file for example queries.  If you are using a graphical client, such
    as <ulink url="http://www.squirrelsql.org/">SQuirreL</ulink>, have a look at the metadata tree to see not only what is exposed by your
    VDB, but also the SYS schema tables.
   </para>
     
  <para>If your application is Web based, you can create data source for your VDB using the above and treat it as any other JDBC source using 
  <code>org.teiid.jdbc.TeiidDataSource</code> and assigning it a JNDI name. 
  Refer to Client Developer's Guide deployment for more information on creating a DataSource. 
  </para>
  <note><para>"embedded" mode is only available in versions of Teiid up to 6.2</para></note>
  </section>   
</chapter>