The JDBC DataSource Wizard allows a new JDBC DataSource to be created. The wizard is shown in Figure 3.3, “JDBC DataSource Wizard”
"Define JDBC DataSource" is the first screen of the DataSource wizard. In this screen, JDBC DataSource parameters can be entered.
Name: A unique name to identify the DataSource.
Description: Extra description for the data source.
JDBC connection: Three methods of connection are available: JDBC, JNDI or Pool.
a) JDBC
Driver Suggestions: The type of the JDBC driver can be selected from the Driver Suggestions combo box. A green or red symbol next to each driver indicate whether the driver is available for use. If the specific driver jar file is copied to the ext folder before Elixir Repertoire is launched, a green symbol will appear next to that driver indicating that the driver has been loaded and is available for use.
Driver: When the type of the Driver is selected from the Driver Suggestions combo box the default Driver is automatically entered in the Driver Text box. The Driver class name in the text box can be altered in case your DBMS vendor modifies the class name.
URL: When the type of the Driver is selected from the Driver Suggestions combo box the default URL is automatically entered in the URL Text box. The parameters in the URL text box can be altered according to your JDBC vendor's requirements.
b) JNDI
Context Factory: The context factory accepts information about how to create a context, such as a reference, and returns the instance of the context.
Provider URL: Provides the URL of the resource to bind to.
Resource Name: This identifies the resource name, that binds to the data source.
c) Pool
Connection Pool: The connection pool from the Repository that will provide the connection to the database. See the section called “Connection Pools”, later in this chapter, for more details.
Timeout: The amount of time (in milliseconds) that the DataSource should wait for a connection to become available. A value of 0 means wait forever.
Resource Name: This identifies the resource name, that binds to the data source.
User: The user name is entered if required.
Password: The password is entered if required.
When the data source parameters have been entered, click Next to see the screen as shown in Figure 3.4, “Define JDBC DataSource”. In this window there are two tabbed panes namely the SQL tab and the Callable tab. An SQL Statement can be entered in the SQL editor. Stored procedures can be defined in the Callable tab. The Query Builder button can also be used to build a query using visual tools.
Clicking the Query Builder button in the SQL window, opens the dialog box as shown in Figure 3.5, “Elixir Query Builder”.
There are three panels in this dialog window. The panel on the left side lists the tables and relationships present in the database. There is a Show Views check box present on top of the left panel. When this check box is selected the views present in the database are listed. Tables and Views can be selected to form the basis of the query.
In the right panel, fields of the selected tables and views are displayed. The fields to be included in the query can be selected. When the fields are selected the properties of the selected fields are displayed in the Details tab of the lower panel. Similarly, the field names are included in the SQL statement that is displayed in the SQL tab of the lower panel.
If the "Select Distinct" check box on the right panel is checked then only distinct records of the table are selected - no duplicate records will be retrieved.
Instead of using the Query Builder to create a SQL statement the SQL statement can be entered directly in the SQL window.
Elixir Data Designer allows stored procedures to be invoked, including passing parameters to the database server (subject to database support). If you need to use a stored procedure, the callable statement syntax has to be entered in the SQL Window. Then the " Callable Statement" checkbox should be selected. On selecting the check box, the Callable tab is activated. In the Callable tab as shown in Figure 3.6, “Callable Tab”, the type of the output parameter must be specified.
Note
If you are using the Remote version of Elixir Repertoire, remember that all data queries and operations are performed by the Elixir Repertoire Server. If you use "localhost" in your connection URL, localhost will refer to the server, not the client. Because all data operations are done on the server, you don't need a client-side version of the JDBC driver.
To add the JDBC driver to your system class path, find the location where Java is installed. This may be either the development kit (jdk), or runtime (jre). For the jdk there is directory jdk/jre/lib/ext, whereas for the jre it is just jre/lib/ext. Put the JDBC driver in this ext directory before launching the Remote tool. This step is only required if you want to use the Query Builder from the Remote tool. The JDBC-ODBC bridge provided by Sun is already included in the Java distribution and doesn't require any additional configuration.
After entering the SQL or callable statement, click the Next button. The page appears as shown in Figure 3.7, “Define DataSource Schema”.
In this screen the schema can be inferred from the data query. Click the Infer Schema button. If a connection to the database can be made, the inferred fields and their corresponding data types will be listed.
