Working with a JDBC DataSource

In this section we will review the different ways of adding a JDBC DataSource.

Using the JDBC/ODBC bridge driver

The JDBC/ODBC driver is only available on Microsoft Windows platforms and allows you to connect to Microsoft ODBC interfaces, such as Microsoft Access.

  1. To add a Microsoft Access DataSource to the Elixir Repository we need to first register the ODBC source as described in the previous section. Select the Microsoft Access Driver(*.mdb) from the list of drivers. Enter the name MondrianFoodMart in the Data Source Name text box. Locate your copy of MondrianFoodMart.mdb and set the path accordingly.

  2. Launch the Elixir Repertoire software.

  3. Choose a file system or folder and Add a DataSource, choose JDBC and click Next.

  4. Enter the name as Sales in the text box. Choose DataSource and type JDBC.

  5. By default the JDBC/ODBC_Bridge(Sun JVM) is selected as the Driver Suggestion. Enter the URL jdbc:odbc:MondrianFoodMart and the DataSource name as Sales. Click Next.

  6. Click Query Builder. Select the Sales table from the list of tables and double click on it.

  7. Select the Customer_id, Store_id and Store_sales fields from the table and click OK.

  8. The query, including the selected columns, is displayed in the SQL window. Click Next.

  9. Alternatively, instead of using the Query Builder to build the SQL query, you can enter the query

    Select Customer_id, Store_id and Store_sales from sales

    in the SQL tab window. Click Next.

  10. In the Define DataSource schema screen, click Infer Schema. The schema is inferred from the data query. Click Finish. The Sales.ds data source is added to the repository. The records in the data source can be viewed by clicking on the Load Data menu in the Data Window.

Note

Refer to Appendix B, Samples for the sample files used in this chapter.

Using a Callable Statement

To make use of Stored procedure in the JDBC data source, a stored procedure must first be created in the database.

The following process creates a stored procedure using Oracle:

  1. Create a new table emp with columns Eno number, Dno number, Dname varchar2(12), and Esal number.

  2. We want to create a stored procedure on the table to fetch records with specific value of Eno or Dno or Esal.

  3. A package is created using the code below:

    CREATE OR REPLACE PACKAGE pack AS TYPE 
    empRowType IS REF CURSOR return emp%rowtype; FUNCTION 
    selemp(enumber in number, dnumber in number, esalary in 
    number) RETURN empRowType; End pack;

    Compile the package.

  4. The package body is created using the following code:

    CREATE OR REPLACE PACKAGE BODY pack AS 
    FUNCTION selemp(enumber in number, dnumber in number, 
    esalary in number) RETURN empRowType IS myemp 
    empRowType; BEGIN OPEN myemp for select * from emp 
    where (Eno=enumber) or (Dno=dnumber) or (Esal=esalary); 
    return myemp; End; End pack;

    Compile the package body.

  5. Make sure the Oracle driver classes12.jar is in the /Public/lib/ folder. Click Refresh Job Engine Jars, to load the driver.

  6. Add a FileSystem JDBC Stored1 using the procedure given in the previous chapter. Alternatively, the data source can be added to an existing FileSystem. Now select the folder, and choose Add -> DataSource from the popup menu. Select the JDBC DataSource type and click Next.

  7. Enter the data source name JDBC_Call. Select Oracle(Thin_driver) as the driver suggestion. The driver and URL are assigned automatically. The URL can be altered according to the requirements. To make use of the stored procedure created in the Oracle server from the client system, specify the IP address of the system instead of localhost.

  8. The user name and password are entered. After entering all the details, the JDBC data source screen of the DataSource wizard appears as shown in Figure 3.14, “JNDI Values”.

    Figure 3.14. JNDI Values

    JNDI Values
  9. Click Next. In the SQL window enter the syntax given below and select the Iscallable check box. The Callable procedure tab becomes active.

    {?=call pack.selemp(?,?,?)}

    Where pack is the package name and selemp is the name of the function.

  10. Select the Callable tab.

    Specify the out type in the text box as follows:

    oracle.jdbc.driver.OracleTypes.CURSOR

    The out type is database dependent. In this case, a return cursor is specified as given above. Some databases do not need a return cursor, so the out type need not be specified for them. For more details on stored procedures with JDBC, refer to this article in JavaWorld. The link is:

    http://www.javaworld.com/javaworld/jw-01-2000/jw-01-ssj-jdbc-p2.html

  11. Click Add in the Callable tab and enter the Data type and values of the corresponding parameters. In this case three input parameters of integer Data type are needed.

  12. Click Next and then click Infer Schema to view the schema for the data source. Click Finish. The data source is added to the repository.

  13. Open the JDBC_Call.ds data source. Click the Load Data menu and verify that only the records with columns with the specified input values are fetched.

Using JNDI Connectivity

JNDI, the Java Naming and Directory Interface, allows applications to access various naming and directory services through a common interface. Like JDBC (Java Database Connectivity), JNDI is not a service, but a set of interfaces; it allows applications to access many different directory service providers using a standardized API.

JNDI uses the connection pooling technique to connect to JDBC datasources. Connection pooling is a technique that can be used to share database connections among requesting clients. When a connection has been created and is placed in a runtime object pool, an application can use that connection again. Each application does not have to perform the complete connection process every time it uses a connection.

When an application closes a connection, the connection is cached in the runtime object pool again. Connection pooling permits an application to use a connection from a pool of connections that do not have to be re-established for each use. By using pooled connections, applications can realize significant performance gains because they do not have to perform all the tasks that are involved in establishing a connection. This can be particularly significant for middle-tier applications that connect over a network, or for applications that repeatedly connect and disconnect.

The nodes in a JNDI namespace are known as contexts. The root node is known as the initial context. Initial contexts are created by initial context factories.

Elixir Data Designer provides enhanced support for JDBC DataSources deployed with JNDI.

The basic system requirements for using JNDI connections are:

  • The vendor package providing connectivity for JNDI (Factory context) and the supporting classes.

The connection process is as follows:

  1. Use the filesystem or folder popup menu and select Add -> DataSource.

  2. Choose the JDBC datasource and click Next.

  3. Select the JNDI tab.

  4. Enter the context factory.

  5. Enter the provider URL.

  6. Enter the resource name. The resource points to the database.

  7. Enter the user name and password if required.

  8. The rest of the steps involved in connecting to the JDBC data source through JNDI using the Data Source wizard are similar to those followed in the above procedure using JDBC drivers.