How To Model JDBC Data Source

Version 4

    Home | Sample Data | Back to Examples....

     

    One of the most common data base types is JDBC.  This tutorial walks you through the process of creating a VDB containing a single relational model created from metadata for a JDBC source.

     

    Teiid Designer's Guides view contains a Model JDBC Source action set that will quickly guide you through the steps which are shown below.  Execute each action by double-clicking it, or selecting the action and clicking the Execute selected action link.

     

         Guides View

     

    Step 1 : Define Teiid Model Project action...

     

    The Model Project Definition dialog allows you to either select an Existing... or create a New... Teiid model project.

     

         Define Teiid Model Project Dialog

     

    Selecting the New... button displays the New Model Project wizard. Enter a unique project name and click Finish or click Next> to define optional project set-up parameters.

     

         New Model Project Wizard

     

    NOTE:  The New Model Project wizard is also accessible through the standard Eclipse File > New... wizard action and associated toolbar button.

    Step 2 : Create JDBC Connection action...

     

    Teiid Designer leverages Eclipse's Data Tools project and its definition and management of Connection Profiles.  You can access the New Connection Profile wizard via the Data Tools' Data Source Explorer view or directly from the Create JDBC Connection action. In the Create Connection dialog, select a connection type (Oracle in this case) and enter a unique profile name for reference and click Next >.

     

         Create Connection Profile Page 1

     

    Choose an applicable driver and enter appropriate properties. You can also Test Connection before finishing.

     

         Create Connection Profile Page 2

     

    action...

    Step 3 : Create source model for JDBC data source action...

     

    Once the JDBC connection info is defined, Teiid Designer's JDBC Import Wizard provides a means to connect to that data source, disiplay the referenced schema (tables, procedure, columns, etc..) and allow for selecting the metadata you wish to include in a your relational metamodel.

     

    The first page of the wizard will show your previously defined connection profile as the selected profile.  If you had entered your data source username and/or password and chosen the "Save password" option, these values will be utilized.  Click Next > to continue.

     

         Import JDBC Wizard Page 1

     

    The second page provides setting up various import filters for your schema to include or exclude different types of metadata. For example, you may wish to include only Table objects as well as Foreign Keys as shown below.

     

         Import JDBC Wizard Page 2

     

    On the third wizard page, the contents of your filtered schema will be displayed. You can further customize what metadata is modeled by checking/unchecking the Tables in the tree. Note that by selecting a table in the tree you can view the details and properties of it's related columns, indexes and keys.

     

     

         Import JDBC Wizard Page 3

     

    On the last page of the wizard, you will define the model you wish you create with your data source metadata in addition to customizing

     

         Import JDBC Wizard Page 4

     

     

         Model Structure

     

    Step 4 : Preview Data action...

     

     

     

         Preview Data Table Definition Dialog

     

         Preview Data Select Table Dialog

     

         Show SQL Result View Confirm Dialog

     

         Preview Data SQL Results

     

    Step 5 : Define VDB action...

     

     

     

         Define VDB Dialog

    <

         New VDB Wizard

     

         VDB Editor

     

    Step 6 : Execute VDB action...

     

     

     

         Execute VDB Dialog

     

         SQL Scrapbook Editor With Template

     

         Execute VDB Results