Data Access With IBM VisualAge for the Internet

by Luc Chamberland

''Would you like to use the Web to check a supplier's inventory or place orders through an electronic catalog? Want to leverage your existing investment in database technology across the Internet?''

As both corporate and consumer demand for sophisticated electronic commerce increases, the need to access remote databases via the Web grows. IBM recognizes the need for network solutions and meets it with the VisualAge for Java suite of tools.

Let's first look at the various levels of web interaction. In terms of client/server interaction, a web can be classified into one of the following categories: In addition, the data access component can be coded using Sun's JDBC (Java Database Connectivity) industry standard, which is part of the JDK 1.1. Much the same way that the ODBC standard defines a set of APIs that enables applications to access any ODBC-compliant database, the JDBC standard defines a common base on which other data access tools can be built. If you want to leverage existing ODBC databases using Java, Sun's JDBC-ODBC bridge driver translates JDBC calls into ODBC calls.
 * Static: Interaction is limited to HyperText Markup Language (HTML) hypertext links, using the HyperText Transfer Protocol (HTTP). Database queries are not possible.
 * Interactive: An HTML form gathers information from the user through text fields, check boxes, and lists. When submitted, the form invokes a Common Gateway Interface (CGI) program running on an HTTP server. The program parses the form information, performs any requisite queries or calculations, and returns an HTML page to the client browser. To interact with a remote database, the CGI program must issue appropriate SQL/ODBC-compliant calls. Heavy interaction with the CGI program, however, may pose performance problems.
 * Distributed: The distributed page replaces the clumsy HTML form with a Java applet. The applet can provide lively animation and rich GUI components. More importantly for data access, the applet also can perform some of the server's tasks (for example, parameter checking) within the client web browser. However, you're still limited to the HTTP protocol and to hand-coding the CGI data access component.
 * Enterprise: Full flexibility is achieved by moving the data access component from the HTTP server (CGI) to a Java server. As part of the Java applet, the data access component does not need to use the HTTP protocol to access a remote database client as shown in Figure 1. Several middleware protocols are available that scale better for enterprise solutions.



''Figure 1. A 3-tiered model''

What tiered solution would best reflect the enterprise model? A 2-tier model would require all users to have a DB2 client installed on their machines. In a 3-tier model, the client only needs to have a Web browser and TCP/IP connection installed. The applet running on the client connects to an intermediate server via a proxy connection. The Java server containing the data access code connects to a DB2 client, which in turn connects to the database itself. This thin-client solution offers all the benefits of networked data access: Web data equals enterprise data. (A 3-tier, fat-client scenario also is possible, where the data access code is downloaded with the rest of the applet code from the HTTP server. While the remote access code is built-in, the demand on client resources increases.)

Although this Java-based 3-tier model offers an open, client/server solution, coding a JDBC-compliant program by hand can be difficult and time-consuming as shown in the following sample of data-access Java code. public synchronized void add throws DAException {

if ( isAsynchronous && !isBusy ) { _putOnBackgroundThread( new EmployeeOnThread          ( this, "add", new Vector ) ); return; }

try { PreparedStatement stmt = currentDatastore.getConnectObject.prepareStatement(          "INSERT INTO USERID.EMPLOYEE (EMPNO,

stmt.setString(1, getEmpno); stmt.setString(2, getFirstnme); stmt.setString(3, getMidinit); stmt.setString(4, getLastname); stmt.setString(5, getWorkdept); stmt.setString(6, getPhoneno); stmt.setString(7, getHiredate); stmt.setString(8, getJob); stmt.setInt(9,getEdlevel.intValue); stmt.setString(10, getSex); stmt.setString(11, getBirthdate); stmt.setNumeric(12, getSalary); stmt.setNumeric(13, getBonus); stmt.setNumeric(14, getComm);

stmt.executeUpdate; stmt.close; }       catch(SQLException exc) { throw new DAException (DAMessage.METHOD_FAILED,          exc.getMessage, exc); } } Fortunately, tools exist that will generate this code for you.

VisualAge for Java Makes Data Access Easy
IBM's VisualAge for Java product is based on a visual programming paradigm, enabling you to create and manipulate your object-oriented components within graphical interfaces. VisualAge for Java also generates Java source code for you.

Let's look at the major steps you need to take to create a 3-tiered solution.

Creating the Data Access Component
The Data Access Builder tool, already seen in the award-winning VisualAge for C++ product, provides a GUI interface and source code generator to create, retrieve, update, and delete database information. To create a 3-tiered solution, do the following: In the column-to-attribute pane, each column of the schema is graphically linked to the Java data field to which it is mapped. If you define a method using an SQL statement, the Data Access Builder validates the SQL syntax and checks that all database references are reflected in the schema. Once validated, the elements of your SQL statement are populated in the fields of the dialog. You can then customize each parameter in terms of type, SQL name, attributes, and so on. This SQL statement is invoked on the DB2 client when the corresponding Java method is called. If your database contains stored procedures, you can define the parameters of a Java method that calls the stored procedure. As with the method defined by an SQL statement, you can modify the attributes of existing store procedure calls. When called by your Java applet, the stored procedure runs on the DB2 server. Commonly, you call the generated data access methods in your init and start methods.
 * 1) Open the Data Access Builder SmartGuide. This SmartGuide helps you specify the database tables that will be accessed by the generated Java classes. To define this information, you can access an existing table, or you can submit an SQL statement that dynamically joins existing tables to create a schema.
 * 2) The grid icon in the Data Access Builder window shown in Figure 3 represents the database information (called the schema). Create a mapping from the schema to define the Java methods and data attributes that reflect how the schema information will be ultimately accessed from your Java program. The Bean icon represents the mapping.va-web3.gif
 * 1) Customize the mapping by adding or modifying your method definitions (for rows or collections of rows). You can add methods by specifying either an SQL statement or a stored procedure call. The Data Access Builder provides simple dialog boxes where these methods can be added or modified (see Figure 4).
 * 1) Generate the Java source code using the Data Access Builder GUI. The generated code includes any methods defined when you customized the mapping, and is completely JDBC-compliant.
 * 2) Write a Java applet or application that uses the generated Java classes to access the database information. You'll need to import the Java packages you just created, as well as any other IBM Java packages you need (for example, java.sql.*).

Distributing the Data Access Part
The Java source code generated so far provides a link from the applet to the DB2 client. This assumes, of course, that the applet and client are running on the same machine. To achieve a 3-tiered, thin-client scenario, you need to distribute the data access part. A remote-method invocation (RMI) proxy establishes a client/server link between the thin client and the intermediate server, where the DB2 client resides. The proxy handles the network connection, manages communication to the middleware, and marshals the data between client and server.

But you don't need to write the proxy yourself. To create a proxy, use the Proxy Builder tool, which is part of VisualAge for Java's Distributed Application Development Toolkit.

Putting the Interface Together
Once you've created the proxy, you're ready to hook it in with the rest of your Java code for the end-user interface. Using the Visual Builder tool, you can visually link all your application components together. The Visual Builder displays and maintains information on all wiring between components, so you're not left guessing what dependencies the components have on one another. In addition, the Visual Builder doesn't limit you to visual parts (such as buttons, list boxes, text fields); you also can wire in non-visual parts (such as proxies, Java Beans, CICS access components). So your distributed data access component can be wired directly into the GUI, allowing the end-user to visually query the database.

Managing and Compiling Your Java Code
When all the source code has been generated for an end-to-end solution, use the VisualAge for Java Integrated Development Environment (IDE) to store all your Java source code into a project. Robust version control facilities let you manage multiple releases of your Java classes.

To compile your Java code, simply save the project. The IDE keeps track of both your source and output, ensuring that the latest updates to your source are reflected in the compiled output.

And that's it. With only a minimal amount of Java code to write yourself, you can code a complete data access enterprise solution.

Summary
VisualAge for Java provides a robust suite of tools for you to rapidly assemble enterprise data access applications. The visual programming paradigm and diverse capability to generate Java source code make quick and easy prototyping a snap. Access to host-based JDBC/ODBC-compliant databases from your web browser has never been easier. For more details on VisualAge for Java, visit our Web site at http://www.software.ibm.com/ad/vajava/.