Level: Intermediate Guy F. Bowerman (guyb@us.ibm.com), Senior Software Engineer, IBM
26 Oct 2006 IBM® Informix® Dynamic Server (IDS) user-defined routines (UDRs) can be written in C, Java™, and Stored Procedure Language (SPL). A recently documented extension to Java UDR support - codenamed Solano - is the ability to host a Java application service, such as an HTTP server, directly from within the data server.
Introduction
Why Solano? A new spin on the two-tier database model
A typical browser-based three-tier database application architecture places much of the business and application logic in the middle tier in the form of an application server providing dynamic HTML or XML generated by a programming language or framework such as JSP or PHP. The data server is used to simply serve data to the application server over the network through Open Database Connectivity (ODBC) or Java Database Connectivity (JDBC) requests, or to additionally perform some of the business logic in the form of stored procedures and UDRs.
Figure 1. Three-tier browser-based database application architecture
The three-tier model is flexible and a wide range of ready made application
servers and programming systems are available. While this model provides
benefits to large enterprise users, for smaller SMB users, some of the costs
involved in maintaining the middle tier, such as deployment, administration,
upgrades, security, and network bandwidth might not be as advantageous.
An alternative model incorporates the three tiers into two tiers with
the application server running in the same address space as the data server.
The Solano feature of IDS with J/Foundation allows UDRs to be mapped to Java methods that start and stop an
application. The application runs within an Informix Virtual Processor,
known as a Java Virtual Processor (JVP). It can listen on its own network ports and respond
directly to client requests while maintaining high-performance server-side
JDBC connections to the data server.
Figure 2. Two-tier database architecture with Solano
This revised form of the two-tier model offers several potential advantages:
- Deployment can be simplified; a self-contained application can be installed by running an SQL script, removing the requirement to install, and maintain a third-party application server.
- Security is improved. Possible attack vectors can be reduced by disabling external connections into the data server and having all non-local database access go through the Solano application, which makes its own internal database connections.
- Network overhead between the application server and data server is eliminated.
- Performance is improved. Applications services running in the same
address space as the database server can use the server-side JDBC driver for
high-performance access to the database. This, in turn, allows the application
to make more frequent calls to the data server to maintain its data cache,
improving data integrity.
- Session tracking; an application server can maintain a set of transient
information about the clients sessions that can in turn be accessed and
processed by the data server if they share the same address space.
Some applications will always be better suited to the three-tier model. Tried and tested load balancing solutions have been developed for the three-tier system to manage scalability. Not all Java application servers will be suitable to run inside a data server without customization. And writing an application server from scratch can imply a significant investment of resources.
The example described in this article makes use of the Apache Jakarta HttpCore Components to show how a simple Java application server can be written and embedded into IDS with very little effort.
The embedded RSS server
This example queries the IDS demo database stores_demo to
serve an RSS feed showing the last 20 orders. An RSS feed can be added to a
Mozilla Firefox browser toolbar or an RSS compatible newsreader to provide a
regularly updated data feed in a summary format. The same example was
previously implemented with both JSP and PHP in a developerWorks blog post.
The example code was tested on Linux and Windows. Because it
is written in Java, the example should work on any platform IDS is available for.
Figure 3. An RSS feed
Ingredients
The following components are required:
- Java 2 SDK 1.4.2
- Apache Jakarta HttpCore Components 4.0-Alpha 2
- Informix JDBC 3.0.JC3
- IDS 10.0
Step 1. Write a standalone RSS server
Writing a standalone RSS server may sound like a complex task, but the HttpCore components allow a standalone Java server to be written with just a few lines of code.
To keep coding to a minimum, the Jakarta Simple HTTP server example written by Oleg Kalnichevksi is used as a starting point. A small modification changes it from serving HTML files to serving an XML RSS feed. The main change is in the doService() method, which reads in a URL request from a client, converts it into a file entity, and serves the file. To become an RSS server it connects to the database, runs a query, and builds an XML formatted string entity with the result set to serve to the client.
Listing 1. Standalone RSS server doService() method
protected void doService(final HttpRequest request, final HttpResponse response)
throws HttpException, IOException {
String method = request.getRequestLine().getMethod();
if (!method.equalsIgnoreCase("GET") && !method.equalsIgnoreCase("HEAD")) {
throw new MethodNotSupportedException(method + " method not supported");
}
String target = request.getRequestLine().getUri();
response.setStatusCode(HttpStatus.SC_OK);
// build a string entity to return to the client
String s = getRSSFeed(target);
StringEntity body = new StringEntity(s);
// set the content type to text/xml
body.setContentType("text/xml");
response.setEntity(body);
}
|
The RSS string is constructed in a new method called getRSSFeed().
In this example the target argument to getRSSFeed(), which
represents the URL string requested by the browser or newsreader, is not used.
A more complex example might interpret the URL when building a database query
in order to return customized RSS feeds.
Listing 2. Standalone RSS server getRSSFeed() method
// getRSSFeed - connect to data server, execute a query, convert data to RSS feed
protected String getRSSFeed(String target)
{
// begin generating the RSS feed string (use a dummy link)
String rssFeed = "<rss version=\"2.0\">\n<channel>\n<title>IDS Feed " +
"<title>\n" + "<link>http://localhost:8080<link>\n" +
"<description>IDS Feed<description>\n";
// get database connection
try {
Class.forName("com.informix.jdbc.IfxDriver");
Connection conn =
DriverManager.getConnection(
"jdbc:informix-sqli://mymachine.beaverton.ibm.com:1526/stores_demo:" +
"INFORMIXSERVER=myinstance", "myuser" ,"mypasswd");
// execute a query and dynamically build the RSS feed string
Statement stmt = conn.createStatement();
String sqlStr = "select first 20 i.order_num, quantity, " +
"trim(description) desc, order_date, total_price, trim(company) co " +
"from items i, orders o, customer c, stock s " +
"where i.order_num = o.order_num and o.customer_num = c.customer_num " +
"and i.stock_num = s.stock_num order by order_date desc";
ResultSet rs = stmt.executeQuery(sqlStr);
// add a new item to the RSS feed for every row returned
while(rs.next())
{
String rssTitle = rs.getString("quantity") + " " + rs.getString("desc") +
" $" + rs.getString("total_price") + " " + rs.getString("co") + " " +
rs.getString("order_date");
// replace chars which might be interpreted as XML control characters
rssTitle = rssTitle.replaceAll("&", "&");
rssFeed += "<item>\n<title>" + rssTitle + "<title>\n";
rssFeed += "<link>http://localhost:8080/<link>\n<item>\n";
}
// close ResultSet and Connection
rs.close();
conn.close();
} catch (Exception e)
{
return e.getMessage();
}
// write the end of the feed
rssFeed += "<channel>\n<rss>";
return rssFeed;
}
|
To build and launch the standalone RSS server, include the Informix JDBC drivers, the HttpCore package, and the current directory in your CLASSPATH.
Listing 3. Building and launching the standalone RSS server
export CLASSPATH=.:/work/jakarta-httpcore-4.0-alpha2/lib/jakarta-httpcore-4.0-alpha2.jar:
/usr/jdbc_30JC3/lib/ifxjdbc.jar:/usr/jdbc_30JC3/lib/ifxjdbcx.jar:$CLASSPATH
javac rssServer.java
java rssServer
|
Make sure the data server specified in the connection string is online, and point your browser to http://localhost:8080/ to confirm the RSS server is working. You should see the RSS XML data displayed on the screen.
Step 2. Modify the RSS server to run inside IDS
In order to run the RSS server in IDS, the main() method from the standalone RSS server is replaced with a new method rss_start() that will be mapped to a UDR.
Listing 4. New method rss_start() to replace main()
public static void rss_start() throws Exception {
Thread t = new RequestListenerThread(8080);
t.setDaemon(false);
t.start();
}
|
The JDBC connection string is also modified to request an internal, Solano
style connection. Note that it is the addition of the database name and user
credentials to the standard server-side JDBC direct connection string that
tells IDS this is a Solano application that can run unattended.
Listing 5. Solano style internal connection
Connection conn = DriverManager.getConnection(
"jdbc:informix-direct:/stores_demo:user=myuser;password=mypasswd;");
|
The IDS RSS server can now be built with javac using the same CLASSPATH used to build the standalone server. It cannot be launched directly without a main method, so the next step is to set up IDS to launch it.
Step 3. Set up J/Foundation and modify the JVPCLASSPATH
To enable J/Foundation, the VPCLASS ONCONFIG parameter, to create a JVP, needs to be uncommented. With this option enabled, a JVP will be launched when the first Java UDR is executed. For a detailed guide to setting up J/Foundation, refer to the Resources section.
Modify the JVPCLASSPATH ONCONFIG parameter to include the IDS RSS server class files and the HTTPCore JAR file. Putting the directory of the IDS RSS server class files in the CLASSPATH means that the full path of the RSS server start method does not need to be specified when registering the UDR.
An example might look like this if the Java file is built in a directory where
/work/ids_rss and the HttpCore components were installed in /work:
Listing 6. Onconfig parameter modifications
VPCLASS jvp,num=1
...
JVPCLASSPATH
/usr/informix/extend/krakatoa/krakatoa.jar:/usr/informix/extend/krakatoa/jdbc.jar:
/work/ids_rss/.:/work/jakarta-httpcore-4.0-alpha2/lib/jakarta-httpcore-4.0-alpha2.jar:$CLASSPATH
|
Once these ONCONFIG parameters are changed, IDS needs to be re-started for them to take effect.
Step 4. Register and execute the UDR to start the RSS server
The following SQL script maps the RSS server rss_start routine to a
UDR and starts the RSS server:
Listing 7. SQL script to register and start the IDS RSS server
database stores_demo;
{ create the start routine and map it to the Java method }
create procedure start_rss()
external name 'ids_rss.rss_start()' language java;
{ execute the start routine }
execute procedure start_rss();
|
Note that in this example, the path of the ids_rss.rss_start() method
does not need to be specified because the JVPCLASSPATH ONCONFIG parameter was updated to include the directory where the classes were built.
Once the start_rss UDR is executed, the RSS server is running and
ready to receive incoming connections.
When a Java UDR calls the System.out.println() method, the
output is written to the JVP log file specified by the JVPLOGFILE
ONCONFIG parameter. This enables status messages from the RSS server to be monitored by tailing the log file. When the RSS server is launched and
receiving incoming connections, a typical output might look like this:
2006/07/22 00:07:31.639 JVP#3: Registering service Tracer
Listening on port 8080
Incoming connection from /9.76.209.230
New connection thread
Request received
2006/07/22 00:07:37.029 JVP#3: Cannot get UDRManager environment
Response sent
Connection kept alive
IO error: Read timed out
Incoming connection from /9.76.209.230
New connection thread
Request received
2006/07/22 00:13:05.540 JVP#3: Cannot get UDRManager environment
Response sent
Connection kept alive
IO error: Read timed out
|
The above log shows that two incoming connections were received from the same
IP address and a response was sent. The RSS server has sent the RSS
feed showing the 20 most recent orders to the requesting browsers or
newsreaders, and is ready to receive new requests.
The Cannot get UDRManager environment log entry is not an error but an
expected message from the JVP when a UDR is not using the UDREnv
interface, documented in the J/Foundation Developer's Guide.
IO Error: Read timed out is an expected status message from the RSS
server printed when the configurable keepalive value times out after a client
requests a page. Messages like this from the application can be removed or
added to with simple modifications to the code.
Conclusion
What's next?
This example shows how a small modification to the Jakarta
Simple HTTP server can produce a working Java application
server running inside an IDS instance. The same
example can be expanded to create a custom HTTP server that serves a
mixture of HTML files and data from a database retrieved through the server-side connection depending on the URL supplied, or a Web server that serves all its files from the database. A more complex RSS server based on this framework might serve
customized data feeds based on the incoming URL. Imagine a news server that
used the incoming URL to configure and serve customized news feeds based on date and subject matter.
Web services
A yet-untapped use for this technology is as a backend providing Web services
from the database to an AJAX-style application. Javascript running in the
client's browser could trigger Solano Web services running directly in the
database to dynamically serve content. This would allow the Web services to
be componentized within the server rather than written as part of a middleware
application.
Secure connections
Another application for Solano would be to host an HTTPS server, enabling end-to-end security directly from browser to database.
Download | Description | Name | Size | Download method |
|---|
| Sample code for this article | solano.zip | 7KB | HTTP |
|---|
Resources Learn
Get products and technologies
Discuss
About the author  | 
| | Guy Bowerman is a senior software engineer in IBM's Resolution, Engineering, and Development (RED) team, providing advanced technical support, product development, training, and bug fixing for Informix products. Guy has worked for Informix Software since 1994 in the U.K. and U.S. |
Rate this page
| 
