JDBC DataSource Wizard

The JDBC DataSource Wizard allows a new JDBC DataSource to be created. The wizard is shown in Figure 3.3, “JDBC DataSource Wizard”

Figure 3.3. JDBC DataSource Wizard

JDBC DataSource Wizard

JDBC DataSource Details

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 symbol next to each driver indicates the driver is available for use. Make sure the specific driver jar file is copied to the Public/lib folder in Repository. Click Refresh Job Engine Jars... before launching Elixir Repertoire.

    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 which to bind.

    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.

  • User: The user name is entered if required.

  • Password: The password is entered if required.

JDBC Properties

When the data source parameters have been entered, click Next to display the screen as shown in Figure 3.4, “JDBC Properties”. You have the option to set any customized properties specifically for your JDBC driver by setting the keys and values. For more information on how to fill in Key and Value for JDBC custom properties, refer to the section called “Configuring JDBC Properties”.

Figure 3.4. JDBC Properties

JDBC Properties

SQL

When the JDBC Properties have been entered, click Next to see the screen as shown in Figure 3.5, “Define JDBC DataSource”. In this screen 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.

When Time SQL is clicked, there will be a pop-up window displaying the number of records retrieved and the time taken to do so.

Figure 3.5. Define JDBC DataSource

Define JDBC DataSource

Figure 3.6. Elixir Query Builder

Elixir Query Builder
  • Clicking Query Builder in the SQL window, opens the dialog box as shown in Figure 3.6, “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 option present on top of the left panel. When this option 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.

  • Show is enabled if there are multiple schemas in the database. On clicking, the user will be able to select specific schemas to be listed under the Query Builder dialog.

  • If the Select Distinct check box on the right panel is checked, then only the 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.7, “Callable Tab”, the type of the output parameter must be specified.

    Figure 3.7. Callable Tab

    Callable Tab

JDBC Options

Max Rows: Specify a maximum number of rows, and the datasource will load only these rows.

Fetch Size: Many JDBC drivers have their default fetch size value as 10. This value affects the number of network trips to database and hence the application performance.

Timeout: Specify the timeout in seconds for loading records from the database. If the timeout is reached, the loading will be cancelled.

Note

If you intend to use MySQL, the Timeout feature requires that JDBC driver mysql-connector-java-5.1.21-bin.jar (or later) should be used.

Auto Commit: When checked, a new record that is added, will be updated accordingly.

Read Only: When checked, the datasource will only read from the database once. From then, the datasource will load data based on the data that was first read. Data will be retrieved at a faster speed as the driver assumes that no changes is made to the database.

Figure 3.8. Define DataSource Options

Define DataSource Options

Infer Schema

After entering the SQL or callable statement, click Next. The page appears as shown in Figure 3.9, “Define DataSource Schema”.

In this screen, the schema can be inferred from the data query. Click Infer Schema. If a connection to the database can be made, the inferred fields and their corresponding data types will be listed.

Figure 3.9. Define DataSource Schema

Define DataSource Schema