Skip navigation.

Client Application Developer's Guide

  Previous Next vertical dots separating previous/next from contents/index/pdf Contents View as PDF   Get Adobe Reader

Using the Data Services Platform JDBC Driver

The BEA AquaLogic Data Services Platform (DSP) JDBC driver gives client applications a means to obtain JDBC access to the information made available by data services. The driver implements the java.sql.* interface in JDK 1.4x to provide access to an DSP server through the JDBC interface. You can use the JDBC driver to execute SQL92 SELECT queries, or stored procedures over DSP applications. This chapter explains how to install and use the Data Services Platform JDBC driver. It covers the following topics:

Note: For data source and configuration pool information, refer to the WebLogic Administration Guide. Your configuration settings may affect performance.


About the Data Services Platform JDBC Driver

The JDBC driver is intended to enable SQL access to data services. The Data Services Platform JDBC driver enables JDBC and ODBC clients to access information available from data services. The JDBC driver increases the flexibility of the DSP integration layer by enabling access from database visualization and reporting tools, such as Crystal Reports. From the point of view of the client, the DSP integration layer appears as a relational database, with each data service function comprising a table. Internally, DSP translates SQL queries into XQuery.

There are several constraints associated with the Data Services Platform JDBC driver. Because SQL provides a traditional, two-dimensional approach to data access (as opposed to the multiple level, hierarchical approach defined by XML), the Data Services Platform JDBC driver can only be used to access data through data services that have a flat data shape; that is, the data service type cannot have nesting.

Also, SQL tables do not have parameters; therefore, the Data Services Platform JDBC driver only exposes non-parameterized flat data service functions as tables. (Parameterized flat data services are exposed as SQL stored procedures.)

To expose non-flat data services, you can create flat views to be used from the JDBC driver.

Features of the Data Services Platform JDBC Driver

The Data Services Platform JDBC driver has the following features:


Data Services Platform and JDBC Driver Terminology

DSP views data retrieved from a database as comprised of data sources and functions. This means that Data Services Platform terminology and the terminology used when accessing data through the Data Services Platform JDBC driver, which provides access to a database, is different. The following table shows the equivalent terminology between the two.

Table 7-1

Data Services Platform Terminology

JDBC Driver Terminology

DSP Application Name

Database Catalog Name

Path from the DSP project folder up to the folder name of the data source separated by a ~ (tilde)

Database Schema Name

Function with parameters

Stored procedure

Function without parameters


Function without parameters return type schema's elements

Table's Columns

Function with parameters return type schema's elements

Stored Procedure's Columns

Data Services Platform and JDBC Driver Terminology

For example, if you have an application Test with a project TestDataServices, and CUSTOMERS.ds with a function getCustomers() under a folder MyFolder, the table getCustomers can be describes as:


where Test is the catalog and TestDataServices~MyFolder is the schema.


Installing the Data Services Platform JDBC Driver with JDK 1.4x

The Data Services Platform JDBC driver is located in an archive file named ldjdbc.jar. In a DSP installation, the archive is in the following directory:


To use the driver on a client computer, perform the following steps:

  1. Copy the ldjdbc.jar to the client computer.
  2. Add ldjdbc.jar to the computer's classpath.
  3. Set the appropriate supporting path by adding %JAVA_HOME%\jre\bin to your path.
  4. To configure the JDBC driver:
    1. Set the driver class name to:
    2. com.bea.ld.jdbc.LiquidDataJDBCDriver.

    3. Set the driver URL to:
    4. jdbc:ld@<LDServerName>:<LDServerPortNumber>[:<LDCatalogAlias>]

      For example, jdbc:ld@localhost:7001 or jdbc:ld@localhost:7001:ldCatalogName.

      If you want to enable logging for debugging use, you can append the following to the driver URL


    You can also specify configuration parameters as a Properties object or as a part of the JDBC URL. The following is an example of how to specify the parameters as part of a Properties object:

    props = new Properties();
    props.put(LiquidDataJDBCDriver.APPLICATION_NAME_PROPERTY, "RTLApp");
    props.put(LiquidDataJDBCDriver.DEBUG_LOG_PROPERTY, new Boolean(true));
    con = DriverManager.getConnection(
    "jdbc:ld@localhost:7001:Demo:DemoLdProject", props);

    Alternatively, you can specify all the parameters in the JDBC URL itself as shown in the following example:

    con = DriverManager.getConnection("jdbc:ld@localhost:7001:Demo:DemoLdProject; ;debugStdOut=true;debugFile=ldjdbc.log;debugLog=true;username=weblogic;password=weblogic;", new Properties());


Using the JDBC Driver

The steps for connecting an application to DSP as a JDBC/SQL data source are substantially the same as for connecting to any JDBC/SQL data source. In the database URL, simply use the DSP application name as the database identifier with "ld" as the sub-protocol, in the form:


For example:


The name of the Data Services Platform JDBC driver class is:


Note: If you are using the WebLogic Administration Console to configure the JDBC connection pool, set the initial connection capacity to 0. The Data Services Platform JDBC driver does not support connection pooling.

The following section describes how to connect using the driver class in a client application.

Obtaining a Connection

A JDBC client application can connect to a deployed DSP application in the same way as it can to any database. It loads the Data Services Platform JDBC driver and then establishes a connection to DSP.

For example:

	Properties props = new Properties();
props.put("user", "weblogic");
props.put("password", "weblogic");

// Load the driver
//get the connection
Connection con =
DriverManager.getConnection("jdbc:ld@localhost:7001", props);

Using the preparedStatement Interface

The following method demonstrates how to use the preparedStatement interface given a connection object (con) that is a valid connection obtained through the java.sql.Connection interface to a WebLogic server hosting DSP. (In the method, CUSTOMER refers to a CUSTOMER data service.)

public ResultSet storedQueryWithParameters() throws java.sql.SQLException {
	PreparedStatement preStmt = 	
con.prepareStatement (
ResultSet res = preStmt.executeQuery();
return res;

Note: You can create a preparedStatement for a non-parametrized query as well. The statement can also be used in the same manner.

Getting Data Using JDBC

Once a connection is established to a server where DSP is deployed, you can call a data service function to obtain data by using a parameterized data service function call.

The following method demonstrates calling a stored query with a parameter (where con is a connection to the Data Services Platform server obtained through the java.sql.Connection interface). In the snippet, a stored query named dtaQuery is executed where custid is the parameter name and CUSTOMER2 is the parameter value.

public ResultSet storedQueryWithParameters(String paramName) 
throws java.sql.SQLException {
	//prepare a stored query to execute
CallableStatement call = con.prepareCall("dtaQuery");
call.setString(1, "CUSTOMER2");
ResultSet resultSet = call.execute();
return resultSet;


Connecting to the JDBC Driver from a Java Application

You can also use the Data Services Platform JDBC driver from client Java applications. This is a good way to learn how Data Services Platform exposes its artifacts through its JDBC/SQL driver.

Note: For details on supported reporting applications and connectivity software see "Configuring the Data Services Platform JDBC Driver for Reporting Applications" in the Preparing to Install Data Services Platform chapter of the DSP Installation Guide.

This section describes how to connect to the driver from DBVisualizer. Figure 7-2 shows a sample application as viewed from DbVisualizer for WebLogic Workshop.

Figure 7-2 DbVisualizer View of DSP

DbVisualizer View of DSP


To use DBVisualizer, perform the following steps:

  1. Configure DBVisualizer.
    1. Ensure that ldjdbc.jar exists in your CLASSPATH. Start DBVisualiser from the Database menu select Driver Manager.
    2. DbVisualizer View of DSP


    3. Select Add CLASSPATH from the File menu of the driver manager dialog. You should see the ldjdbc.jar listed.
    4. Select ldjdbc.jar from the list shown then select Find Drivers from the Edit menu of the driver manager. You should see the com.bea.ld.jdbc.LiquidDataJDBCDriver. This means the JDBC driver has been located.
    5. DbVisualizer View of DSP

    6. Close the driver manager.
  2. Add connection parameters by performing the following steps:
    1. On the right pane select the JDBC Driver as com.bea.ld.jdbc.LiquidDataJDBCDriver, dropping down the list.
    2. For the Database URL, enter jdbc:ld@<machine_name>:<port>:<app_name>. For example "jdbc:ld@localhost:7001:RTLApp"
    3. Provide the username and password for connecting to the DSP application.
  3. Click connect. On completion of a successful connection, you should see the following:
  4. DbVisualizer View of DSP

  5. On the right pane of the window (see figure in step 3), you can see various tabs. The Tables tab helps you view the information about the tables, including their metadata. The References tab lets you view the field information and primary key of each table.
  6. Execute ad hoc queries by activating the SQL Commander tab as shown in the following figure. Type in your SQL query and click the execute button.
  7. DbVisualizer View of DSP



Connecting to Data Services Platform Client Applications Using the ODBC-JDBC Bridge from Non-Java Applications

You can use an ODBC-JDBC bridge to connect to Data Services Platform JDBC driver from non-Java applications. This section describes how to configure the OpenLink and EasySoft ODBC-JDBC bridges to connect non-Java applications to the Data Services Platform JDBC driver.

Note: For details on supported reporting applications and connectivity software see "Configuring the Data Services Platform JDBC Driver for Reporting Applications" in the Preparing to Install Data Services Platform chapter of the DSP Installation Guide.

Using the EasySoft ODBC-JDBC Bridge

Applications can also communicate with the Data Services Platform JDBC Driver using EasySoft's ODBC-JDBC Gateway. The installation and use of the EasySoft Bridge is similar to the OpenLink bridge discussed in the previous section.

To use the EasySoft bridge, perform the following steps:

  1. Install the EasySoft ODBC-JDBC bridge. Go to the EasySoft site for information about installation:
  3. Creating a system DSN and configuring it with respect to DSP by performing the following steps:
    1. Open Administrative tools —>Data Sources (ODBC).
    2. DbVisualizer View of DSP

    3. Go to the System DSN tab and click Add.
    4. Select EasySoft ODBC-JDBC Gateway as shown in the figure below and click Finish.
    5. DbVisualizer View of DSP

    6. On the next screen, fill in the fields as follows:
    7. For Class Path, enter the absolute path to the ldjdbc.jar

      For URL, enter:


      For Driver class, enter:


      DbVisualizer View of DSP

    8. Click Test. The following screen will display, indicating the connection has completed successfully.
    9. DbVisualizer View of DSP

    10. Click OK to complete the set-up sequence.

Using OpenLink ODBC-JDBC Bridge

The Openlink ODBC-JDBC driver can be used to interface with the Data Services Platform JDBC driver to query DSP applications with client applications, such as Crystal Reports 10, Business Objects 6.1, and MS Access 2000.

To use the OpenLink bridge, you will need to install the bridge and create a system DSN using the bridge. The following are the steps for these two tasks:

  1. Install the OpenLink ODBC-JDBC bridge (called ODBC-JDBC-Lite). For information on the installation of OpenLink ODBC-JDBC-Lite, see:

Warning: For Windows platforms, be sure that you preserve your CLASSPATH before installation. The installer might overwrite it.

  1. Create a system DSN and configure it for your DSP application by performing the following steps:
    1. Ensure that the CLASSPATH contains the following jars required by ODBC-JDBC-Lite, as well as the ldjdbc.jar. A typical CLASSPATH might look like:
    2. D:\lddriver\ldjdbc.jar; D:\odbc-odbc\openlink\jdk1.4\opljdbc3.jar; D:\odbc-jdbc\openlink\jdk1.4\megathin3.jar;
    3. Update your system path to point to the jvm.dll, which should be under your %javaroot%/jre/bin/server directory.
    4. Open Administrative tools Data Sources (ODBC). You should see the following:
    5. DbVisualizer View of DSP

    6. Go to the System DSN tab and click Add.
    7. Select JDBC Lite for JDK 1.4 (32 bit) and click Finish.
    8. DbVisualizer View of DSP

    9. Write a name for the DSN. For example, ODBC_JDBC_LITE, as shown in the figure below.
    10. DbVisualizer View of DSP

    11. Click Next. Then on the next screen, enter the following in the JDBC driver field:
    12. com.bea.ld.jdbc.LiquidDataJDBCDriver.

      Enter the following in the URL string field:



      DbVisualizer View of DSP

    13. Check the Connect now to verify that all settings are correct checkbox. Provide the login and password to connect to the Data Services Platform WebLogic server.
    14. Click Next. The screen shown below will display.
    15. DbVisualizer View of DSP

    16. Click Next. The following screen will display.
    17. DbVisualizer View of DSP

    18. Click Test Data Source. This screen will verify the setup is successful.
    19. DbVisualizer View of DSP

    20. Click Finish.



Using Reporting Tools with the Data Services Platform ODBC-JDBC Driver

Once you have configured your ODBC-JDBC Bridge, you can use your application to access the data source presented by DSP. The usual reason for doing so is to connect Data Services Platform to your favorite reporting tool.

Note: For details on supported reporting applications and connectivity software see "Configuring the Data Services Platform JDBC Driver for Reporting Applications" in the Preparing to Install Data Services Platform chapter of the DSP Installation Guide.

This section describes how to configure the following reporting tools to use the Data Services Platform ODBC-JDBC driver:

Note: Some reporting tools issue multiple SQL statement executions to emulate a scrollable cursor if the ODBC-JDBC bridge does not implement one. Some drivers do not implement a scrollable cursor, so the reporting tool issues multiple SQL statements. This can affect performance.

Crystal Reports 10 - ODBC

This section describes how to connect Crystal Reports to the Data Services Platform ODBC-JDBC driver. To connect Crystal Reports to the driver, perform the following steps:

  1. In Crystal Reports 10, you need to create a new Connection on ODBC RDO. You can do this by clicking on the New Report wizard button, which will prompt you immediately for a data source. Select the ODBC (RDO) option in the left-hand window as shown in the Figure 7-3.
  2. Figure 7-3 Data Source Selection

    Data Source Selection


    You can select the DSN you have created earlier (see the procedure in section Using OpenLink ODBC-JDBC Bridge or Using the EasySoft ODBC-JDBC Bridge). In this example, it is ODBC_JDBC_LITE.

    Selecting ODBC_JDBC_LITE, prompts the following dialog:

    Data Source Selection

  3. Enter the domain login and password. Note that because the URL contains the Data Services Platform RTLApp application, you should use the domain login and password that the domain of the RTLApp application uses. (These will most likely be "weblogic".)
  4. Once authenticated, Crystal Reports will show you a view of the DSP application on the server as shown in Figure 7-4.

    Figure 7-4 Available Data Sources

    Available Data Sources

  5. Generate a report using the Add command or by dragging the metadata to the right. In this example we will be using both options. You can choose the tables you want to use in the report as shown in Figure 7-5.
  6. Figure 7-5 Selecting the Table View

    Selecting the Table View


    Alternatively, you can choose the Add Command option to type an SQL query directly, which will show you a window like one in the Figure 7-6.

    Figure 7-6 Add Command

    Add Command

  7. Click the Ok Button to see the Command added to the Right hand side of the window.
  8. Clicking Next in the wizard shows you all the available views for this Report generation, as shown in Figure 7-7.

    Figure 7-7 Link Screen

    Link Screen


    Clicking Next again will take you to the Column chooser window, which allows you to select which Columns you want to see in the final Report, which appears as shown in Figure 7-8.

    Figure 7-8 Column Chooser

    Column Chooser


    Note: This example chooses columns from the user-generated Command and the view CUSTOMER.

    Clicking on Next again takes us to the Group by screen (as shown in Figure 7-9), which allows you to choose a column to group by. (This is grouping is performed by Crystal Reports. The Group-by information is not passed on to the JDBC driver.)

    Figure 7-9 Group-by Screen

    Group-by Screen

  9. Skip the next few screens for now, clicking Next till you reach the Template Chooser Screen Figure 7-10. Choose any appropriate Template. In this example, the user has chosen the Block (Blue) Template.
  10. Figure 7-10 Template Chooser Screen

    Template Chooser Screen

  11. Click Finish. A Report similar to that shown in Figure 7-11 is generated.
  12. Figure 7-11 Generated Report

    Generated Report


Crystal Reports 10 - JDBC

Crystal Reports 10.0 comes with a direct JDBC interface that can be used to interact directly with the Data Services Platform JDBC driver. The only difference between the ODBC and JDBC approach is that in JDBC, a new type of connection is used, as shown in Figure 7-12.

Figure 7-12 Connection Dialog Box

Connection Dialog Box


Figure 7-13 shows screen that requests the connection parameters for the JDBC Interface of Crystal Reports.

Figure 7-13 Connection Information Dialog Box

Connection Information Dialog Box


Note: The Database drop down box is populated with the available catalogs (DSP applications) once you have specified the correct parameters for User ID and, Password, as shown in Figure 7-13.

Clicking the Finish button on the previous screen. This takes you the metadata browser shown in Figure 7-14. The rest of the process is similar to the procedure described in the section Crystal Reports 10 - ODBC.

Figure 7-14 Metadata Browser Window

Metadata Browser Window


Business Objects 6.1 - ODBC

Business Objects 6.1 allows you to create a Universe and also allows you to generate reports based on the specified Universe. In addition, you can execute pass-through SQL queries against Business Objects that do not need the creation of a Universe.

To generate a report, perform the following steps:

  1. Creating a Universe by doing the following:
    1. Run the Business Objects 6.1 Designer application and click New to create a new universe.
    2. Fill in a name for your Universe and select the appropriate DSN connection from the drop-down list, as shown in Figure 7-15.
    3. Figure 7-15 Selecting the DSN Connection

      Selecting the DSN Connection

    4. If the DSN you wish doesn't appear in the list (this happens if you are using the application for the first time), use New to create a new connection. Select ODBC Drivers, as shown in Figure 7-16, and click OK.
    5. Figure 7-16 Selecting the ODBC Drivers

      Selecting the ODBC Drivers

    6. Now select the database engine as a Generic ODBC data source, as shown in Figure 7-17. Use the ODBC Admin button to check if the DSN you wish is already created. For any help creating a DSN using OpenLink or EasySoft please refer to the section ODBC-JDBC bridge of this document.
    7. Figure 7-17 Selecting the Database Engine

      Selecting the Database Engine

    8. Now select the data source name as shown in Figure 7-18. This would be the name of DSN you wish to connect to. Refer to the picture below. Click OK to get back to the Universe creation window.
    9. Figure 7-18 Selecting the Data Source Name

      Selecting the Data Source Name

    10. Fill in the other details and click Test to see if the connection is successful. Click OK. You should see a new blank panel, as shown in Figure 7-19.
    11. Figure 7-19 Designer UI Screen



      Designer UI Screen

    12. From the Insert menu select Table, as shown in Figure 7-19. Once the list of tables is shown in the Table Browser, double click on the tables you wish to put in the Universe you are creating. You should see a screen similar to that shown in Figure 7-20.
    13. Figure 7-20 Table Browser

      Table Browser

    14. Save the Universe and exit.
  2. Creating a report using the New Report wizard. To create a new report, follow these steps:
    1. Run the Business Objects application. Click New to open the New Report Wizard. Choose Specify to access data and click Begin. You should see the dialog-box shown in Figure 7-21.
    2. Figure 7-21 Available Universe Dialog Box

      Available Universe Dialog Box

    3. Choose a Universe. Click Next. On the left pane, you should see the tables and their fields (columns) on expansion, as shown in Figure 7-22.
    4. Figure 7-22 Query Panel

      Query Panel

    5. Select the Universe of your choice and click Finish. Double-click a column (table-field) in the left pane to select it in the result, as shown in Figure 7-23.
    6. Figure 7-23 Selecting the Object.

      Selecting the Object.

    7. Click Run to execute the query. The result is seen as shown in Figure 7-24.
    8. Figure 7-24 Business Objects Panel.

      Business Objects Panel.

  3. You can execute the pass-through queries as follows:
    1. In the Business Object application, click New to create a new report.
    2. In the New Report Wizard choose Others instead of Universe as shown in Figure 7-25.
    3. Figure 7-25 Data Access Dialog Box.

    4. Choose Free-hand SQL and click Finish.
    5. Select the connection you made using Designer 6.1, as shown in Figure 7-26.
    6. Figure 7-26 Free Hand SQL Menu

      Free Hand SQL Menu

    7. Type in your SQL query and click Run to generate the report, as shown in Figure 7-27.
    8. Figure 7-27 Specifying the SQL Query



      Specifying the SQL Query

    9. Click Run. You should see the report shown in Figure 7-28.
    10. Figure 7-28 Business Objects Report

      Business Objects Report


Microsoft Access 2000 - ODBC

This section describes the procedure for connecting Microsoft Access 2000 to DSP through an ODJB-JDBC bridge.

Note: If you are using Microsoft Access 2000 you should use OpenLink's ODBC- JDBC bridge. The EasySoft bridge does not support Microsoft Access 2000.

To connect Access 2000 to the bridge, perform the following steps.

  1. Run MS Access, click File Open, then select ODBC Databases as the file type as shown in the Figure 7-29.
  2. Figure 7-29 Selecting the ODBC Database in Access

    Selecting the ODBC Database in Access

  3. Once the dialog Select Data Source pops up, click Cancel to close it. You should see the window shown in Figure 7-30.
  4. Figure 7-30 OBDC23: Database Screen

    OBDC23: Database Screen

  5. Click Queries, then Design as indicated in Figure 7-30. You should see a screen shown similar to that shown in Figure 7-31.
  6. Figure 7-31 Select Query and Show Table Screens

    Select Query and Show Table Screens

  7. Close the Show Table dialog box. You should now be able to see the Select Query dialog.
  8. Right click in the upper pane and select SQL Specific —> Pass-Through as indicated in Figure 7-32. This will open an editor.
  9. Figure 7-32 Selecting SQL Specific and Pass Through

    Selecting SQL Specific and Pass Through

  10. Type in your SQL query and click Run, as shown in the Figure 7-33.
  11. Figure 7-33 Running the SQL Query

    Running the SQL Query

  12. In the dialog that pops up (as shown in Figure 7-34), move to the tab Machine Data Source and select the appropriate DSN for the database connectivity.
  13. Figure 7-34 Selecting the DSN for the Database

    Selecting the DSN for the Database



DSP and SQL Type Mappings

When data service information is accessed from a JDBC client, the data is mapped from its XML Schema format to SQL types. The mapping between the types is shown in Table 7-35.

The XML types are defined by xmlns:xs="". The Java types are defined by java.sql.Types.

Table 7-35 XML to SQL Type Mapping

XML Type

SQL Types






























SQL-92 Support

This section outlines the SQL-92 support in the Data Services Platform JDBC driver.

Supported Features

The Data Services Platform JDBC driver supports many standard SQL-92 features. In particular, supported features include:

The Data Services Platform JDBC driver implements the following interfaces from java.sql package specified in JDK 1.4x:


The following limitations are known to exist in the Data Services Platform JDBC driver:

The following table notes additional limitations that apply to SQL language features.

Unsupported Feature




Intervals not supported

WHERE ( , , ) OVERLAPS ( , , )


The table_name can have an alias, but you cannot specify the colmn_name_alias_list within it.


Assignment in select

Not supported.


The CORRESPONDING BY construct with the set-Operations(UNION, INTERSECT and EXCEPT)

The SQL-92 specified default column ordering in the set operations is supported.

Both the table-expressions (the operands of the set-operator) must conform to the same relational schema.


The supported query is:


"...table1 UNION table2..."

Not supported. Also not supported are set operations between tables in a FROM clause, except through a sub-query.

The TABLE keyword is not supported.


Where TABLE is a keyword not supported by the LDJDBC SQL interface.

The supported version is:


Other supported UNION constructs:



SELECT-query within the SELECT clause

Not supported.



Skip navigation bar  Back to Top Previous Next