Skip navigation.
Downloads | Product Documentation | Support

Client Application Developer's Guide

# 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:

- About the Data Services Platform JDBC Driver
- Installing the Data Services Platform JDBC Driver with JDK 1.4x
- Using the JDBC Driver
- Connecting to the JDBC Driver from a Java Application
- Connecting to Data Services Platform Client Applications Using the ODBC-JDBC Bridge from Non-Java Applications
- Using Reporting Tools with the Data Services Platform ODBC-JDBC Driver
- DSP and SQL Type Mappings
- SQL-92 Support

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:

- Supports SQL-92 SELECT statements
- Implements JDBC 3.0 API
- Supports Data Services Platform with JDK 1.4
- Usable from both Java and ODBC clients

Notes:

- The Data Services Platform JDBC driver contains the following third party libraries: `Xerces Java - 2.6.2 : xercesImpl.jar`, `xmlParserAPIs.jar`, and `ANTLR 2.7.4 : antlr.jar`.
- The driver also contains the following DSP product libraries: `wlclient.jar`, `ld-client.jar`, `Schemas_UNIFIED_Annotation.jar`,`jsr173_api.jar`, and `xbean.jar`.

## 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 | Table
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:

>     Test.TestDataServices~MyFolder.getCustomer

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:

>     	<WebLogicHome>/liquiddata/lib/

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:

`com.bea.ld.jdbc.LiquidDataJDBCDriver`.

2. Set the driver URL to:

`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

`;debugStdOut=true;debugFile=ldjdbc.log;debugLog=true;`

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.USERNAME_PROPERTY1,"weblogic");
>     props.put(LiquidDataJDBCDriver.PASSWORD_PROPERTY,"weblogic");
>     props.put(LiquidDataJDBCDriver.APPLICATION_NAME_PROPERTY, "RTLApp");
>     props.put(
>      LiquidDataJDBCDriver.PROJECT_NAME_PROPERTY,"DataSErvices~CustomerDB");
>     props.put(LiquidDataJDBCDriver.WLS_URL_PROPERTY,"t3://localhost:7001");
>     props.put(LiquidDataJDBCDriver.DEBUG_STDOUT_PROPERTY,"true");
>     props.put(LiquidDataJDBCDriver.DEBUG_LOG_PROPERTY, new Boolean(true));
>     props.put(
>     	    LiquidDataJDBCDriver.DEBUG_LOG_FILENAME_PROPERTY,"ldjdbc.log");
>     Class.forName(""com.bea.ld.jdbc.LiquidDataJDBCDriver"");
>     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:

>     Class.forName("com.bea.ld.jdbc.LiquidDataJDBCDriver");

>     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:

>     	jdbc:ld@<WLServerAddress>:<WLServerPort>:<LDApplicationName>

For example:

>     	jdbc:ld@localhost:7001:RTLApp

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

>     	com.bea.ld.jdbc.LiquidDataJDBCDriver

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
>     	Class.forName("com.bea.ld.jdbc.LiquidDataJDBCDriver");

>     	//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 (
>     		    "SELECT * FROM CUSTOMER WHERE CUSTOMER.LAST_NAME=?");
>     	preStmt.setString(1,"SMITH");
>     	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

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. Select Add CLASSPATH from the File menu of the driver manager dialog. You should see the `ldjdbc.jar` listed.
3. 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.

4. 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. 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.
5. 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.

# 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:

>      http://www.easysoft.com

2. Creating a system DSN and configuring it with respect to DSP by performing the following steps:

1. Open Administrative tools —>Data Sources (ODBC).

2. Go to the System DSN tab and click Add.
3. Select EasySoft ODBC-JDBC Gateway as shown in the figure below and click Finish.

4. On the next screen, fill in the fields as follows:

For Class Path, enter the absolute path to the ldjdbc.jar

For URL, enter:

>     jdbc:ld@<machine_name>:<port>:<app_name>

For Driver class, enter:

>     com.bea.ld.jdbc.LiquidDataJDBCDriver

5. Click Test. The following screen will display, indicating the connection has completed successfully.

6. 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:

http://www.openlinksw.com/info/docs/uda51/lite/installation.html

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:

>     D:\lddriver\ldjdbc.jar; D:\odbc-odbc\openlink\jdk1.4\opljdbc3.jar; D:\odbc-jdbc\openlink\jdk1.4\megathin3.jar;

2. Update your system path to point to the `jvm.dll`, which should be under your `%javaroot%/jre/bin/server` directory.
3. Open Administrative tools Data Sources (ODBC). You should see the following:

4. Go to the System DSN tab and click Add.
5. Select JDBC Lite for JDK 1.4 (32 bit) and click Finish.

6. Write a name for the DSN. For example, ODBC_JDBC_LITE, as shown in the figure below.

7. Click Next. Then on the next screen, enter the following in the JDBC driver field:

>     com.bea.ld.jdbc.LiquidDataJDBCDriver.

Enter the following in the URL string field:

>     jdbc:ld@<machine_name>:<port>:<app_name>

8. 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.
9. Click Next. The screen shown below will display.

10. Click Next. The following screen will display.

11. Click Test Data Source. This screen will verify the setup is successful.

12. 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:

- Crystal Reports 10 - ODBC
- Crystal Reports 10 - JDBC
- Business Objects 6.1 - ODBC
- Microsoft Access 2000 - ODBC

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.

Figure 7-3 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:

2. 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".)

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

3. 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.

Figure 7-5 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

4. Click the Ok Button to see the Command added to the Right hand side of the window.

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

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

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

5. 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.

Figure 7-10 Template Chooser Screen

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

Figure 7-11 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

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

Figure 7-13 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

## 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.

Figure 7-15 Selecting the DSN Connection

3. 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.

Figure 7-16 Selecting the ODBC Drivers

4. 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.

Figure 7-17 Selecting the Database Engine

5. 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.

Figure 7-18 Selecting the Data Source Name

6. 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.

Figure 7-19 Designer UI Screen

7. 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.

Figure 7-20 Table Browser

8. 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.

Figure 7-21 Available Universe Dialog Box

2. 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.

Figure 7-22 Query Panel

3. 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.

Figure 7-23 Selecting the Object.

4. Click Run to execute the query. The result is seen as shown in Figure 7-24.

Figure 7-24 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.

Figure 7-25 Data Access Dialog Box.

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

Figure 7-26 Free Hand SQL Menu

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

Figure 7-27 Specifying the SQL Query

6. Click Run. You should see the report shown in Figure 7-28.

Figure 7-28 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.

Figure 7-29 Selecting the ODBC Database in Access

2. Once the dialog Select Data Source pops up, click Cancel to close it. You should see the window shown in Figure 7-30.

Figure 7-30 OBDC23: Database Screen

3. Click Queries, then Design as indicated in Figure 7-30. You should see a screen shown similar to that shown in Figure 7-31.

Figure 7-31 Select Query and Show Table Screens

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

Figure 7-32 Selecting SQL Specific and Pass Through

6. Type in your SQL query and click Run, as shown in the Figure 7-33.

Figure 7-33 Running the SQL Query

7. 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.

Figure 7-34 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="http://www.w3.org/2001/XMLSchema". The Java types are defined by java.sql.Types.

Table 7-35 XML to SQL Type Mapping

XML Type | SQL Types
--- | ---
xs:Boolean | Types.BOOLEAN.
xs:byte | Types.TINYINT
xs:dateTime | Types.TIMESTAMP
xs:date | Types.DATE
xs:decimal | Types.DECIMAL
xs:double | Types.DOUBLE
xs:duration | Types.TIMESTAMP
xs:float | Types.FLOAT
xs:int | Types.INTEGER
xs.integer | Types.NUMERIC
xs:long | Types.BIGINT
xs:short | Types.SMALLINT
xs:string | Types.VARCHAR
xs:time | Types.TIME

# 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:

- Only SELECT construct is supported. Inserts, updates, and deletes are not supported.
- SELECT clause with:

- DISTINCT and ALL
- Scalar expressions and functions, CASE statements, CAST, string and date literals, column wildcards.

- Projections (sub-queries) within the select clause are not supported.
- FROM clause with:

- Basic table names
- Sub-queries
- Joins
- Set operations

- GROUP BY clause
- HAVING clause
- WHERE clause with:

- Predicate expressions (arithmetic operators, functions, CASE statements)
- Predicates involving non-correlated and correlated sub-queries
- EXISTS
- BETWEEN
- LIKE
- NULLIF
- COALESCE
- UNIQUE
- IS NULL, IS NOT NULL, IS TRUE, IS FALSE
- ALL, SOME, ANDY

- Joins of the following type:
- Cross joins, inner joins, and union joins
- Natural joins and joins with ON and USING
- Left, right, and full outer joins
- Set operations:

- UNION
- INTERSECT
- MINUS

- Parameterized queries (with standard SQL-92 notation)
- ORDER by clause
- Functions:

- STR
- CONCAT
- CURRENT_TIME
- CURRENT_DATE
- CURRENT_TIMESTAMP
- ROUND
- FLOOR
- LOWER
- UPPER
- SUBSTRING
- CASTTODATE
- CASTTOTIME
- COUNT
- AVG
- SUM
- MIN
- MAX
- EXTRACT
- TRIM

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

- `java.sql.Connection`
- `java.sql.CallableStatement`
- `java.sql.DatabaseMetaData`
- `java.sql.ParameterMetaData`
- `java.sql.PreparedStatement`
- `java.sql.ResultSet`
- `java.sql.ResultSetMetaData`
- `java.sql.Statement`

## Limitations

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

- Each connection points to only one DSP application.
- An XML Schema name can contain special characters that are illegal for database schema names (such as "/" and "."). The Data Services Platform JDBC driver translates the characters to legal characters ("~" and "^", respectively).

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

Unsupported Feature | Comments | Example
--- | --- | ---
OVERLAPS | Intervals not supported | WHERE ( , , ) OVERLAPS ( , , )
range-variable- comma-list | The table_name can have an alias, but you cannot specify the `colmn_name_alias_list` within it. | SELECT ID, NM, CT FROM STAFF AS (ID, NM, GD, CT);
Assignment in select | Not supported. | SELECT MYCOL = 2 FROM VTABLE WHERE COL4 IS NULL
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. | (SELECT NAME, CITY FROM CUSTOMER1) UNION CORRESPONDING BY (CITY, NAME) (SELECT CITY, NAME FROM CUSTOMER2) The supported query is: (SELECT NAME, CITY FROM CUSTOMER1) UNION (SELECT NAME, CITY FROM CUSTOMER2)
"...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. | SELECT * FROM TABLE CUSTOMER1 UNION TABLE CUSTOMER2 Where TABLE is a keyword not supported by the LDJDBC SQL interface. The supported version is: SELECT * FROM (SELECT * FROM CUSTOMER1 UNION SELECT * FROM CUSTOMER2) T1 Other supported UNION constructs: SELECT * FROM CUSTOMER1 UNION SELECT * FROM CUSTOMER2 SELECT * FROM CUSTOMER1 UNION (SELECT * FROM CUSTOMER2 UNION SELECT * FROM CUSTOMER3)
SELECT-query within the SELECT clause | Not supported. | SELECT A, (SELECT B FROM C) FROM... WHERE...