rules repository, which itself is a number of tables stored in an Oracle database. A Repository Manager written using Oracle Forms is used as a front-end for this repository.
As part of the ETL architecture, there is a PL/SQL routine consisting of about one thousand lines of code which acts as the code generator for the repository. The code generator generates approximately 300,000 lines of PL/SQL code to handle all of the logic for generating and parsing the XML files from the Oracle database.
Another function of the original system allows users to work offline by extracting data from a customer file into an XML file and reading and writing from an XML data source rather than an Oracle data source. To support this functionality, the code generator was extended to generate Java as well as PL/SQL. This extension proved very useful when moving the system to SQL Server.
The user environment is a thick-client Java one with all of the unpopulated PureEdge forms residing on the client machines.
SQLServer/.NET/J2EE System Description
Another part of the same client organization wanted to be able to use PureEdge forms in their web-based environment. This part of the organization used .NET applications exclusively in a SQL Server environment. Originally, it was proposed that the entire architecture would be ported into a .NET/SQL Server environment requiring approximately six months. However, because of funding constraints, the project needed to be completed in six weeks. Since the development team was not experienced with .NET or SQL Server, the decision was made to use the J2EE architecture wherever possible in order to take advantage of the portions of the existing Java environment system that were already up and running.
Proposed System Architecture
In order to create a system that would meet the requirements within the short timeframe necessary, a repository-based approach was adopted. The following list refers to the steps shown in Figure 1.
(1) A user and an analyst work together to articulate the rules of the system. These rules are represented in a technology-independent business rules repository stored within the database.
(2) Once the application is articulated, a code generator generates the application.
(3) Some of the generated code goes to the application server and some to the database server. Some rules in the repository may not generate any code at all, but may be accessed at runtime.
Figure 1: Application Specification and Generation
The independence of the articulation of the application and its implementation is a key feature of the architecture. Since the generator comprises a relatively small portion of the system, design modifications that change what code is generated to the application server or database are relatively straightforward. Even drastic redesigns such as changing the database server or application development architecture can be handled fairly easily.
Figure 2 shows the runtime application usage for the proposed system.
Figure 2: Runtime Application Usage
When the application is running, code may be generated in such a way that rules are implemented in (1) the client, (2) the application server, (3) code generated to the database, or (4) runtime interrogation of the business rules repository.
Solution
The solution was to move the rules environment from Oracle to SQL Server. The Oracle Forms-based Repository Manager had to be rebuilt as a J2EE application. The PL/SQL code generator had to be rewritten in Java using standard JDBC technology.
Using this approach, the .NET applications would run on Microsoft’s IIS Application Server, requesting PDFs of populated PureEdge forms from the J2EE environment. From an interoperability perspective, there were a number of challenges:
1. Take an Oracle Forms client/server application running against Oracle and port it to a J2EE application running against SQL Server.
2. Make a .NET application running on IIS able to request and receive information from a J2EE application running on an Oracle Application Server (OAS).
Phases of the Project
This section describes in more detail the steps needed to move the application described above from one environment to another.
I.
Make a J2EE/Oracle Application Communicate with a
SQL Server Database
“The devil you know is always better than the devil you don’t.” Writing applications to communicate with a SQL Server database was a challenging experience. There were three particular problems which arose when handling this task.
1. The SQL Server development culture is less formal and arguably less mature. As an example, handling numeric primary keys is done differently in SQL Server than it is in Oracle. SQL Server lacks sequences and autonomous transactions. However, it is possible to declare “identity columns” which are automatically populated when the record is created. The problem is that it is not possible to get the number before the record is created. Therefore, if you want to create master/detail records in the same transaction, it is necessary to “pre-identify” the primary key value of the parent object. Although this is not a requirement of all applications, it occurs regularly enough that there should be a standard way of solving this problem. After consulting with several SQL Server experts and searching the web, it was concluded that no industry standard existed to handle this situation. Even within the client organization, there was no consistent solution used by the various development teams.
2. SQL Server does not include a CONNECT BY clause to support recursive structures. Consequently, in most SQL Server shops, there is a tendency to avoid recursive data structures. Even in Microsoft Project’s data model, the obviously recursive TASK table supports recursion using Order and Level columns. Again, there was no consistency among SQL Server developers about how to handle recursive structures.
3. SQL Server has an 8,000 character limit per record. This means that for records in Oracle with several description columns designated as VARCHAR2(4000), the conversion is to SQL Server is not a simple one-to-one. SQL Server has its own version of a CLOB to handle larger records.
Solutions
The following solutions were devised for the three problems
listed above:
1. The primary key issue was handled by replicating the idea of a sequence in SQL Server using a small, single-column table with a SQL Server identity. Anytime a number was needed, a record was inserted into this table to retrieve the number immediately. This was the equivalent of using the following Oracle command in SQL Server:
SELECT mysequence.nextval from dual
2. It was not optimal to abandon the use of recursive structures in the database in order to solve the CONNECT BY problem. Instead, these queries were handled by writing multiple self-joins using outer joins to replicate the CONNECT BY operation. Since these queries were being run with repository tables containing less than a few hundred records, even a 10-table self-join had no discernible performance problems.
3. The 8,000 character limit was more annoying than technically difficult. By judiciously decreasing column widths, it was possible to accommodate this limitation. However, in general, this appears to be a difficult limitation to abide by.
Remaining Database Issues
In addition to the problems listed above, there were a number of things that an Oracle database can handle that a SQL Server database cannot for which there were no nice workarounds:
1. PL/SQL is a proprietary programming language that only works with the Oracle DBMS. SQL Server has its own scripting language but it was decided not to try to convert PL/SQL code to SQL Server’s scripting language. This decision caused the team to appreciate the common reluctance of Java programmers to examine database capabilities. In this case, a relatively simple application was being built. For a high-end production application, some level of thick-database approach would be necessary to achieve adequate performance.
2. SQL Server does not allow for INSTEAD OF trigger views which, although not commonly used by Oracle developers, were used in the existing Oracle application to help reduce its complexity. These types of views are now available in SQL Server but were not when this project was underway. In Oracle, object collections can be created so that a view can display the contents of the object collection. These views also use INSTEAD OF triggers. This very useful Oracle functionality does not exist in SQL Server. For the particular project described here, this lack was not particularly significant because the applications were fairly straightforward utility applications only designed to be used by IT professionals.
JDeveloper and SQL Server
According to Oracle, it is possible to use JDeveloper to build
applications that can seamlessly work with any database. Although it is
possible to accomplish this, being successful at it is not a trivial task.
First, it is necessary to find and load the appropriate driver to allow
JDeveloper to connect to SQL Server. However, the JDeveloper wizards were
designed to work with the Oracle system tables. When building
Once the ETL Repository Manager application had been built, the problem of deployment still remained. The .NET and J2EE environments are entirely different. The Oracle and Microsoft application servers are as different as Windows is from Unix.
Microsoft’s IIS has its own Java Virtual Machine (JVM). Therefore, it “should” be possible to simply deploy a JDeveloper J2EE application to the IIS application server. After some trial and error, it was discovered that the Microsoft JVM is different enough from the standard JVM that this was not a viable deployment strategy. Sophisticated Java applications will not work using the Microsoft IIS.
There are several J2EE/.NET interoperability products that claim they allow you to run an application created in one environment on the application server in the other environment. What these products actually do is load a second application server on the same server which requires sharing memory and system resources. Application servers usually require enough resources that performance will degrade significantly, the applications will not scale, and when problems occur, their sources are very difficult to pinpoint.
The solution arrived at was to use a dedicated Oracle application server, deploy the application to it, and then let the .NET application running on IIS communicate with it.
As part of the J2EE application, a code generator was written in Java to generate Java code. This code was used to manage the generation and parsing of the XML files from the database. This code also ran on the Oracle application server.
II. Make a .NET Application Interact with the J2EE Application
There are several possible approaches to the problem of making a
.NET and a J2EE application communicate. The most obvious would be to
completely isolate the two systems and use web services to communicate between
them. In this case, the J2EE application can publish a web service that could
be called by the .NET application. However, web services bring their own
significant overhead. Because the transactions for the system needed to be done
quickly, performance was an issue. A
search of possible solutions ended with a tool called JNBridge. This software
can run on either application server with little performance overhead.
The software was installed on the IIS application server and the
IIS server effectively acted as a client to the OAS application server.
The resulting architecture is illustrated in Figure 3.
Figure 3: System architecture
The overall process was as follows:
(a) The user makes a browser request to load a form.
(b) The IIS makes a call to the J2EE code running on OAS using JNBridge.
(c) That code generates an XML file that is used to populate a PureEdge form which is then returned to the .NET application.
(d) This application passes it onto the client where it is opened in a PureEdge Viewer.
Conclusions
Taking a J2EE/Oracle application and making it work in a SQL Server/.NET environment is not as easy as the vendors would have you believe, but it is possible. The main lessons learned were as follows:
1) IIS will never run a J2EE application and a J2EE application server will never run a .NET application. If you are going to use both architectures, you will need both application servers.
2) If you are doing anything that requires relatively fast performance, a web service will not give you adequate performance. You need to use something like JNBridge to make the two application servers talk to each other.
3)
SQL Server has enough limitations that moving to it
from the Oracle environment will take some adjustments.
About the Author
Dr.