Creating a JDBC Datasource

Java Database connectivity (JDBC) is a standard SQL database access interface, providing uniform access to a wide range of relational databases. JDBC also provides a common base on which higher level tools and interfaces can be built.

Before creating a JDBC datasource
JDBC driver must be placed in <ext> directory of the product before launching of Elixir software.

Creating a JDBC datasource

1. Choose a file system or folder
2. Select Add a DataSource
3. Choose JDBC as the datasource type and click Next
4. Enter the name for the JDBC datasource (e.g. Sales) in the text box.
5. Select the type of the JDBC driver (e.g. Oracle Thin_Driver) that you are using from the Driver Suggestion. By default, the JDBC/ODBC_Bridge(Sun JVM) is selected as the Driver Suggestion.
6. Enter the Database URL (e.g jdbc:oracle:thin:@localhost:1521:orcl), Database Username and the DataSource password (if applicable) and click the Next button.
7. Enter the SQL statement directly in the SQL tab window or use the Query Builder to build the SQL statement and click the Next button.
8. In the Define DataSource schema screen, click the Infer Schema button. The schema will be inferred from the data query.
9. Click Finish button.

Verify if the JDBC datasource is able to get the data
Select and double click on the data source. The records in the data source can be viewed by clicking on the Load Data menu in the Data Window.

Creating a JDBC Datasource (Using JNDI)

JNDI, the Java Naming and Directory Interface, allows applications to access various naming and directory services via 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.

Please use Elixir Repertoire 6.3 or later.

Before creating a JDBC datasource (Using JNDI)
JDBC driver must be placed in directory of the product before launching of Elixir software.
the JDBC driver must be able to support JNDI connection.
Java Platform 2 (1.4 or later)

Creating a JDBC datasource (Using JNDI) with WebLogic

1. Configure connection to Oracle 10g at WebLogic
   • Name : JDBCConnectionPool
   • URL : jdbc:oracle:thin:@<host>:<port>:<dbname>
   • Driver Classname : oracle:jdbc:OracleDriver
   • Deployed : true
2. Configure JDBC Datasource in WebLogic
   • Name : MyOracle
   • JNDIName : MyOracleJNDI
   • JNDIName Separator : ;
   • Pool Name : JDBCConnectionPool
   • Row Prefetch Enabled : true
   • Enable Two Phase Commit : false
   • Stream Chunk Size : 256
   • Row Prefetch Size : 48
   • Deployed : true
3. Place ojdbc14.jar in <ext> folder
4. Place weblogic.jar in <lib> folder
5. Launch Elixir Repertoire Designer tool
6. Create a new JDBC Datasource
7. Click on JNDI tab
8. Fill in the information for connection
   • Context Factory : weblogic.jndi.WLInitialContextFactory
   • Provider URL : t3://<host>:<port>
   • Resource Name : MyOracleJNDI
   • User : <username>
   • Password : <password>
9. Click on next to proceed with SQL query
10. Click on QueryBuilder and select the required fields from table
11. Click on 'Ok' and see that the SQL Query is written under SQL tab
12. Click 'Next' -> 'Next' to proceed with Infer Schema
13. Click on 'Infer Schema'
14. Click on 'Next' -> 'Finish' to complete the process
15. Click on 'Load Data' to view the data retrieve

Sample Working with Database

Working with Oracle10g, JDBC Datasource
Working with Microsoft SQL Server 2005, JDBC Datasource (Stored Procedure)
Working with DB2, JDBC Datasource (Stored Procedure)

-- TanDunlin - 28 Feb 2007