HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML
 

Since a middle tier isn't much good without something for it to talk to, we're going to use the Pubs2 database, which is an example database provided with Sybase SQL Server 1.1 Specifically, we'll use the authors table which is described in Figure 1.

Imports
All of the example code is written in one file. It provides methods to do select, insert, update and delete on the authors table. To work with Jaguar, in addition to whatever usual imports you use, you must import a number of packages developed by Sybase. For our example, we use what is shown in Table 1.

Table 1

Methods
We'll declare a class called Pubs2CompImpl, which stands for Pubs2 component implementation. The "Impl" on the end of the class name is sort of a Jaguar standard for the implementation of server components - the actual component name will be Pubs2Comp. In addition to the constructor for this class, there are eight methods.

The code listing shows the class definition. The constructor for Pubs2CompImpl is defined to throw a JException (Jaguar Exception), which will happen for certain events. There are also several class variables, as shown in Table 2.

Table 2

In actuality, it's not difficult to write a component for Jaguar. Some of the classes that Jaguar provides such as Connection, Statement, ResultSet and JCMCache make it easy to get data from an SQL database and send it on to the client, as you'll see in a moment.

Listing 1 shows a couple of variables used to connect to the SQL Server database that we are using as our target. We've used the Sybase Pubs2 example database, which should be available at least to the Sybase community. For those of you who have never heard of this sample database, it contains information about an imaginary publishing company, particularly things like authors, books, royalties, etc. Our examples are all very straightforward (I hope) so there should be no trouble following along.

The password and user name should be familiar to any database developer. What might be new to some is that we're connecting with JDBC, and in particular with Sybase JConnect (hence the tds portion of the DATASERVER string). The string tells you what is shown in Table 3.

Table 3

In our example the values are coded, but there is no reason why a component cannot be created that will accept these parameters in some setup function. This would allow the application to drive what database to connect to.

The constructor for our sample class is shown in Listing 2. This function illustrates the use of the Jaguar Connection Manager (JCM). As we mentioned briefly in Part One of this series, one of the services Jaguar provides is a Connection Cache. This allows a number of logical connections to be managed over a smaller number of physical connections. Since database connection is one of the biggest bottlenecks in application responsiveness, the cache provides a way to speed up the process.

JCM is a static class implemented by Jaguar. The getCache() function returns a connection to the database that is stored in the _cache variable. This function can throw an exception, which must be caught. Should an exception occur, we use the Jaguar static class method writeLog() to send a message to the server log and set the cache to null. Finally, if we've been unsuccessful in connecting to the database, we throw a JException that will be caught by the client.

Listing 3 shows the qt() function, which encloses a string in double quotes. This is useful for dealing with strings and character data in SQL statements.

The doQuery() function is shown in Listing 4. In our sample code, there are two types of operations - those that return result sets (select statements) and those that do not (insert, update and delete statements). The doQuery() function is used to take an SQL statement and return a result set. It takes a string containing the select statement, a statement variable and a Connection variable. It uses the statement object to execute the SQL statement, and returns a result set or throws an exception. Basically, the function is used to encapsulate the common logic of creating a result set.

Listing 5 shows the selAuthors() function, which makes use of the doQuery() function to select all of the data from the authors table. It starts by building an SQL statement into the SQL variable (I apologize to all of you purists who hate select star - it was an attempt to keep things simple). Within the try block, it then attempts to get a connection from the cache. If that is successful, it creates a statement using the createStatement() function. A result set is generated using the doQuery() function. Next comes a very innocent looking function that allows you to send a result set to a client application. In this sense, Jaguar is particularly well suited to Java because the result set on the client side looks just as if it came from a database (which, in fact, it did here). There are other functions that allow you to manually create and forward a result set in a Jaguar server component so you're not limited to what you can pull out of a database. For example, you could process the results of a query and create a summary of the information, and send that instead of a year's worth of information. After the result set is sent, the statement and the connection are closed.

The remaining listings show other functions that we've created to illustrate the concepts of insert, update and delete. Since these are probably familiar to you from a conceptual standpoint, and the code is very similar to the code used in Listings 4 and 5, we won't go into too much detail. Instead, we'll look at how we get the code into Jaguar and close by discussing how we will get the code to the client, which will be the subject of Part 3 of this article.

Registering the Component in Jaguar
It's not particularly difficult to register components in Jaguar. You start Jaguar manager and connect to the server. Then you have several options on how you want to make a component available to the client. First of all, it's important to understand that for a component to be available to a client application it must be part of a package that is in turn part of the server. Server can get kind of confusing in this context, because while you can have multiple named servers on a single machine, they all have the same source for components. You can, however, set up one named server for development and another for deployment, and place packages into deployment only after they have been thoroughly tested in the development named server. Note that each named server listens on a different port and can be started and stopped independently of each other, but they all share the same shared memory space. Think of them as analogs to database instances.

You can either create a component, place it in a package and then place the package into a server, or you can create a component directly in a package that is directly inside a server. One reason you might want to progress in steps is because you have to mark any method that returns a result set as doing so. Unfortunately, there's no way that Jaguar can tell that you want to return a result set. If your component has a large number of methods, this can be tedious. Also, if you have just reregistered an existing component after some changes, this will allow you time to set all of these attributes before some overenthusiastic developer tries to test the changes. As a side note, creating a second named server that listens on a different port allows you to do the equivalent of putting Jaguar in single user-mode. Because all the clients expect to connect to a particular port (the default is 7878), if you run a maintenance instance on some other port (say 5858), you can register components without interference from developers.

Figure 1 shows the registration of a component. You have three choices initially: ActiveX, Java or C/C++. After choosing Java and selecting a logical component name (it does not have to match your class name), you have a choice of selecting a class file or a JavaBean as the source. By default, Jaguar looks in its \html\classes directory for the source files, so you should put your component here or below here in a package directory if the component is to be part of a package.

Figure 1
Figure 1:

Once you've told Jaguar where the component is, it pulls in all of the methods that are declared void (the void declaration is a restriction in 1.1 that will be lifted in the next major release according to Sybase). After you mark the methods as returning result sets, you are almost done.

Part Three
In Part Three, we'll show you how to generate Java stubs for use by client programs. Then we'll provide a fairly simple Java application that will connect to Jaguar and exercise the methods that we've created here.

About the Authors
Sean Rhody is a respected industry consultant and a leading authority on PowerBuilder. Sean is also editor-in-chief of the PowerBuilder Developer's Journal and is the editor of PowerBuilder 6.0: Secrets of the PowerBuilder Masters. You can contact Sean at [email protected] or [email protected]

James A. Walker is a senior consultant with Sybase's NorthEast Professional Services. Currently, he is on a project that utilizes Jaguar CTS and PowerJ. You may reach him at [email protected]

	

Listing 1: Variable initialization.

final String  DB_USER    = "pubs2_user";     // Database user name.
final String  DB_PWD     = "pubs2_user";     // Database user password.
final String  DATASERVER = "jdbc:sybase:Tds:89.86.200.1:5000/pubs2"; 
// URL to the dataserver. 

// other variables
String sql;
static Connection con = null;
static Statement stmt = null;
static ResultSet rs   = null;
JCMCache _cache = null;

Listing 2: Constructor.

public Pubs2CompImpl() throws JException 
{
    try 
    {   // Get a cache reference.
      _cache = JCM.getCache(DB_USER, DB_PWD, DATASERVER); 
    }
    catch (Exception e) 
    {
      Jaguar.writeLog(true, "Pubs2CompImpl constructor getCache(): " +
e.getMessage());
      _cache = null;
    }

    /*
    ** If we can't get a handle to the connection cache, log an error and throw 
    ** a Jaguar exception. The JException gets caught by the component stub class,
    ** which in this case is Pubs2Comp.class. 
    */
    if (_cache == null) 
   {
      Jaguar.writeLog(true, "Pubs2CompImpl(): Could not get connection cache reference.");
      throw new JException("Pubs2CompImpl constructor: Could not create connection 
cache.");
    }
  }

Listing 3: The qt function.

public String qt(String str) 
{
    return("\"" + str + "\""); // Enclose the string in quotes.
}

Listing 4: The doQuery function.

private ResultSet doQuery(String sql, Statement stmt, Connection con) throws JException 
{
    int rows;

    try {
      rows = stmt.executeUpdate("set quoted_identifier off");
      boolean results = stmt.execute(sql);
      rs = stmt.getResultSet();
    }
    catch (SQLException sqle) {
      Jaguar.writeLog(true, "doQuery(): " + sqle.getMessage());
    }
    catch (Exception e) {
      Jaguar.writeLog(true, "doQuery(): " + e.getMessage());
    }
    return rs;
}

Listing 5: The selAuthors Function.

public void selAuthors() throws JException 
{
    sql =       "SELECT * "; 
    sql = sql + "FROM authors ";
    sql = sql + "ORDER by au_lname";

    try {
      Connection con = _cache.getConnection(JCMCache.JCM_FORCE);
      Statement stmt = con.createStatement(); 
      ResultSet rs = doQuery(sql, stmt, con);      
      JContext.forwardResultSet(rs);       
      stmt.close();
      _cache.releaseConnection(con);       
    }
    catch (Exception e) {
      Jaguar.writeLog(true, "selAuthors(): " + e.getMessage());
    }
 }

Listing 6: The selAuthorsByLname function.

public void selAuthorsByLname(String au_lname) throws JException 
{

    // Build the sql statement and put strings in double quotes.
    sql =       "SELECT * "; 
    sql = sql + "FROM authors ";
    sql = sql + "WHERE au_lname = " + qt(au_lname) + " ";
    sql = sql + "ORDER by au_lname";

    try {
      Connection con = _cache.getConnection(JCMCache.JCM_FORCE);
      Statement stmt = con.createStatement(); // Create a connection statement.
      ResultSet rs = doQuery(sql, stmt, con); // This returns a result set so call doQuery().
      JContext.forwardResultSet(rs);          // Forward the result sets to the client.
      stmt.close();                           // Release all statement resources.
      _cache.releaseConnection(con);          // Release the connection back into the pool.
    }
    catch (Exception e) {
      Jaguar.writeLog(true, "selAuthorsByLname(): " + e.getMessage());
    }
  }

Listing 7: The insAuthors function.

public void insAuthor(String au_id, String au_lname, String au_fname,
                        String phone, String address,  String city,
                        String state, String country,  String postal_code) throws JException 
						{

    sql = "INSERT INTO authors VALUES(";
    sql = sql + qt(au_id) + "," + qt(au_lname) + "," + qt(au_fname) + ",";
    sql = sql + qt(phone) + "," + qt(address)  + "," + qt(city) + ",";
    sql = sql + qt(state) + "," + qt(country)  + "," + qt(postal_code) + ")";

    try {
      Connection con = _cache.getConnection(JCMCache.JCM_FORCE);
      Statement stmt = con.createStatement(); 
      doActionQuery(sql, stmt, con);
      stmt.close();
      _cache.releaseConnection(con);
    }
    catch (Exception e) {
      Jaguar.writeLog(true, "insAuthor(): " + e.getMessage());
    }
  }

Listing 8: The delAuthors Function.

public void delAuthor(String au_id) throws JException {

    sql =       "DELETE ";
    sql = sql + "FROM authors ";
    sql = sql + "WHERE au_id = " + qt(au_id);

    try {
      Connection con = _cache.getConnection(JCMCache.JCM_FORCE);
      Statement stmt = con.createStatement(); 
      doActionQuery(sql, stmt, con);
      stmt.close();
      _cache.releaseConnection(con);
    }
    catch (Exception e) {
      Jaguar.writeLog(true, "delAuthor(): " + e.getMessage());
    }
  }

Listing 9: The updAuthors function.

public void updAuthor(String au_id, String au_lname, String au_fname,
                        String phone, String address,  String city,
                        String state, String country,  String postal_code) throws JException 
						{

    sql =       "UPDATE authors ";
    sql = sql + "SET au_lname    = " + qt(au_lname) + ",";
    sql = sql + "    au_fname    = " + qt(au_fname) + ","; 
    sql = sql + "    phone       = " + qt(phone)    + ",";
    sql = sql + "    address     = " + qt(address)  + ",";
    sql = sql + "    city        = " + qt(city)     + ",";
    sql = sql + "    state       = " + qt(state)    + ",";
    sql = sql + "    country     = " + qt(country)  + ",";
    sql = sql + "    postalcode  = " + qt(postal_code);
    sql = sql + "WHERE au_id = " + qt(au_id); 

    try {
      Connection con = _cache.getConnection(JCMCache.JCM_FORCE);
      Statement stmt = con.createStatement(); 
      doActionQuery(sql, stmt, con);
      stmt.close();
      _cache.releaseConnection(con);
    }
    catch (Exception e) {
      Jaguar.writeLog(true, "updAuthor(): " + e.getMessage());
    }
  }

Listing 10: The doActionQuery function.

private void doActionQuery(String sql, Statement stmt, Connection con) throws JException 
{

    int rows = 0;

    try {
      rows = stmt.executeUpdate("set quoted_identifier off");
      rows = stmt.executeUpdate(sql.toString());
      rows = stmt.executeUpdate("commit");
    }
    catch (SQLException sqle) 
	{
      Jaguar.writeLog(true, "doActionQuery(): " + sqle.getMessage());
    }
    catch (Exception e) 
	{
      Jaguar.writeLog(true, "doActionQuery(): " + e.getMessage());
    }
  }
  
      
 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.