|
|
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. |
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.
The Data Services Platform JDBC driver has the following features:
Xerces Java - 2.6.2 : xercesImpl.jar, xmlParserAPIs.jar, and ANTLR 2.7.4 : antlr.jar.wlclient.jar, ld-client.jar, Schemas_UNIFIED_Annotation.jar, jsr173_api.jar, and xbean.jar.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 (Table 8-1) shows the equivalent terminology between the two.
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.
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:
ldjdbc.jar to the client computer. ldjdbc.jar to the computer's classpath. %JAVA_HOME%\jre\bin to your path.
com.bea.ld.jdbc.LiquidDataJDBCDriver.
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());
|
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>
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.
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.
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);
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. |
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.executeQuery();
return resultSet;
}
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.
This section describes how to connect to the driver from DBVisualizer. Figure 8-2 shows a sample application as viewed from DbVisualizer for WebLogic Workshop.
To use DBVisualizer, perform the following steps:
ldjdbc.jar exists in your CLASSPATH. Start DBVisualiser from the Database menu select Driver Manager.
ldjdbc.jar listed.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.
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.
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:
Data Sources (ODBC).For Class Path, enter the absolute path to the ldjdbc.jar
jdbc:ld@<machine_name>:<port>:<app_name> |
|
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:
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. |
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; |
jvm.dll, which should be under your %javaroot%/jre/bin/server directory.
com.bea.ld.jdbc.LiquidDataJDBCDriver. |
Enter the following in the URL string field:
jdbc:ld@<machine_name>:<port>:<app_name> |
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.
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. |
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:
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:
Once authenticated, Crystal Reports will show you a view of the DSP application on the server as shown in Figure 8-4.
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 8-6.
Clicking Next in the wizard shows you all the available views for this Report generation, as shown in Figure 8-7.
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 8-8.
| 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 8-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.)
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 8-12.
Figure 8-13 shows screen that requests the connection parameters for the JDBC Interface of Crystal Reports.
| 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 8-13. |
Clicking the Finish button on the previous screen. This takes you the metadata browser shown in Figure 8-14. The rest of the process is similar to the procedure described in the section Crystal Reports 10 - 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:
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.
Pass-Through as indicated in Figure 8-31. This will open an editor.
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 8-34.
xmlns:xs="http://www.w3.org/2001/XMLSchema" |
The Java types are defined by:
java.sql.Types |
XML to SQL Type Mappings are shown in Table 8-34.
This section outlines the SQL-92 support in the Data Services Platform JDBC driver.
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 (Table 8-35) notes additional limitations that apply to SQL language features.
|
