Client Application Developer's Guide
|
 |
 |
|
 |
 |
The Liquid Data 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 a Liquid Data server through the JDBC interface. You can use the JDBC driver to execute SQL92 SELECT queries, or stored procedures over Liquid Data applications. This chapter explains how to install and use the Liquid Data 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 Liquid Data JDBC driver enables JDBC and ODBC clients to access information available from data services. The JDBC driver increases the flexibility of the Liquid Data integration layer by enabling access from database visualization and reporting tools, such as Crystal Reports. From the point of view of the client, the Liquid Data integration layer appears as a relational database, with each data service function comprising a table. Internally, Liquid Data translates SQL queries into XQuery.
There are several constraints associated with the Liquid Data 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 Liquid Data 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 Liquid Data 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 Liquid Data 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.Liquid Data views data retrieved from a database as comprised of data sources and functions. This means that Liquid Data terminology and the terminology used when accessing data through the Liquid Data JDBC driver, which provides access to a database, is different. The following table shows the equivalent terminology between the two.
|
Path from the Liquid Data project folder up to the folder name of the data source separated by a ~ (tilde) |
|
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 Liquid Data JDBC driver is located in an archive file named ldjdbc.jar. In a Liquid Data installation, the archive is in the following directory:
<WebLogicHome>/liquiddata/lib/
To use the driver on a client computer, perform the following steps:
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 Liquid Data 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 Liquid Data 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 Liquid Data 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 Liquid Data 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 Liquid Data application in the same way as it can to any database. It loads the Liquid Data JDBC driver and then establishes a connection to Liquid Data.
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 Liquid Data. (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 Liquid Data 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 Liquid Data 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;
}
You can also use the Liquid Data JDBC driver from client Java applications. This is a good way to learn how Liquid Data exposes its artifacts through its JDBC/SQL driver.
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 Liquid Data
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.
com.bea.ld.jdbc.LiquidDataJDBCDriver, dropping down the list.
You can use an ODBC-JDBC bridge to connect to Liquid Data 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 Liquid Data JDBC driver.
Applications can also communicate with the Liquid Data 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:
The Openlink ODBC-JDBC driver can be used to interface with the Liquid Data JDBC driver to query Liquid Data 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:
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.
Once you have configured your ODBC-JDBC Bridge, you can use your application to access the data source presented by Liquid Data. The usual reason for doing so is to connect Liquid Data to your favorite reporting tool. This section describes how to configure the following reporting tools to use the Liquid Data 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 Liquid Data ODBC-JDBC driver. To connect Crystal Reports to the driver, perform the following steps:
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:
Once authenticated, Crystal Reports will show you a view of the Liquid Data application on the server as shown in Figure 7-4.
Figure 7-4 Available Data Sources
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.
Clicking Next in the wizard shows you all the available views for this Report generation, as shown in Figure 7-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 7-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 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-10 Template Chooser Screen
Crystal Reports 10.0 comes with a direct JDBC interface that can be used to interact directly with the Liquid Data 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 (Liquid Data 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 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:
Figure 7-15 Selecting the DSN Connection
Figure 7-16 Selecting the ODBC Drivers
Figure 7-17 Selecting the Database Engine
Figure 7-18 Selecting the Data Source Name
Figure 7-19 Designer UI Screen
Figure 7-21 Available Universe Dialog Box
Figure 7-23 Selecting the Object.
Figure 7-24 Business Objects Panel.
Figure 7-25 Data Access Dialog Box.
Figure 7-26 Free Hand SQL Menu
Figure 7-27 Specifying the SQL Query
Figure 7-28 Business Objects Report
This section describes the procedure for connecting Microsoft Access 2000 to the Liquid Data 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.
Figure 7-29 Selecting the ODBC Database in Access
Figure 7-30 OBDC23: Database Screen
Figure 7-31 Select Query and Show Table Screens
Figure 7-32 Selecting SQL Specific and Pass Through
Figure 7-33 Running the SQL Query
Figure 7-34 Selecting the DSN for the Database
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.
This section outlines the SQL-92 support in the Liquid Data JDBC driver.
The Liquid Data JDBC driver supports many standard SQL-92 features. In particular, supported features include:
The Liquid Data JDBC driver implements the following interfaces from java.sql package specified in JDK 1.4x:
java.sql.Connectionjava.sql.CallableStatementjava.sql.DatabaseMetaDatajava.sql.ParameterMetaDatajava.sql.PreparedStatementjava.sql.ResultSetjava.sql.ResultSetMetaDatajava.sql.StatementThe following limitations are known to exist in the Liquid Data JDBC driver:
The following table notes additional limitations that apply to SQL language features.
|
|
|