In our last article, we introduced what is now our column project: Informer. Informer is a small contact database application that is designed to provide contact information about various personnel over an Intranet or Internet. The idea for this project evolved from one of our N-ARY designers, Frode Hegland, who wanted to make information more available to people without saddling them with horrendous client-side applications. So, we came up with the Informer. Over this series of columns, we will be building new features into the Informer, showing you how easy Java can make building what, on the face of it, seems to be a rather complex system.
The Informer is a small Java applet that connects to a MS-Access database sitting on the server using Symantec's dbAnywhere software. dbAnywhere ships with Visual Café Professional and can be used to provide database access for client-side applets. This is performed using a solution known as middleware, or three-tiered access solutions. Figure 1 illustrates this concept. When an applet wishes to connect to a database, the most logical solution is to use the JDBC set of classes that ship as standard with the Java library. A number of potential problems exist with this. First, in order for the applet to access a database table, the correct driver must be installed on the client machine. This makes distribution of your applet much more complicated. Second, in order not to upset the Java security manager the database that the applet will connect to must reside on the same host the applet was downloaded from. This makes implementing distributed database solutions almost impossible.
A better solution would be to pass all database requests onto another piece of software that had all the drivers installed and a connection established to the database. This software could live on the Web server and pass requests between the client applet and the database. Fortunately, many commercial solutions are available to the developer for this very case. Symantec's dbAnywhere is one of the most popular and most flexible. dbAnywhere comes in two parts: the server software and the client API classes. The server software sits and executes on the server, listening for incoming connections on a specific TCP port which defaults to 8889. The server software knows nothing of the databases available to it and therefore, to the relief of many, setting this server up is a piece of cake. Simply start it up and leave it to run in the background. The real magic is in the Java dbAnywhere API.
The dbAnywhere API gives the developer all the access facility that is available under JDBC and then some. Everything is done from the applet, with the dbAnywhere suite simply passing requests on to the server and then collating the results back again. For example, consider the code snippet shown in Listing 1.
This code would be situated within the Java applet and, when run, first attempts to make a connection to the dbAnywhere server situated on the N-ARY Web server at port 8889. Once connected, it tries to open up a connection to the database "Contacts", using the user name "alan" and password "runrig". If anything goes wrong, an exception is thrown. Possible reasons for an exception being thrown include:
Once a connection has been opened, SQL statements can be constructed and sent to the database, with the results coming back in much the same way as JDBC processes them.
- It can't make a connection to the dbAnywhere server due to either a security violation or the server not running at the specific port.
- The dbAnywhere server couldn't actually make a connection to the specified database.
- The username and password are incorrect.
But if all that seems a bit too complicated to begin with, or your knowledge of SQL and databases is a bit rusty, have no fear. In true Visual Café fashion, a lot can be achieved before we even get to editing source code. The remainder of this article will look at setting up the database connection and making the first version of the Informer, which will give basic database functionality to the user; adding new records and modifying existing records and basic navigation.
Step #1: Database Selection
The first item on the agenda is to create the database table that will be used to store all the contact information. In my previous column, I hinted at using an MS Excel spreadsheet to store the information. Although this is perfectly legal, using an ODBC bridge, I felt that Informer could quickly outgrow the spreadsheet so I opted for the next level up and used MS Access as the basis for the contact database. Creating a new Access database file was a simple matter of defining the fields and saving the database in a file somewhere on the server. We have two main ways we can access this database: using dbAnywhere driver or using ODBC. Since setting up the ODBC is just as easy, I decided to do this, since it means we can change the Access database to an Oracle database without re-deploying the Java applet.
Setting the ODBC driver up is a simple matter of adding a new database table using the 32bit ODBC' utility, situated in the Control Panel, and giving it a logical name. in this instance, I used "Contacts". Once completed, believe it or not, the hardest part is over. Before you start to build the applet, ensure that the dbAnywhere server is up and running. Now we are ready to code.
Step #2: Project Creation
One of the easiest ways to create a dbAnywhere project is to use the Project Wizard. We create a new "dbAnywhere Project" and follow the seven steps to database enlightenment.
Once these steps have been completed, the wizard will create the first version of the applet, which at this stage doesn't look at all pretty. So, we shall change the attributes of the fields and end up with something that looks like the applet shown in Figure 2.
- Project Type: Here we specify whether it's an applet or an application. In this case, we are developing an applet.
- dbAnywhere Server: This is where we give details about the dbAnywhere server we will be connecting to and port it is listening on. The default address is localhost', which needs to be changed to the host name of the Web server; otherwise, a security violation will be thrown when the applet attempts the connection.
- Data Source: This is where we choose the database source. This will display all the sources that the dbAnywhere server can see. If all went well in the Control Panel you should see a source named "Contacts". If not, check the ODBC driver.
- Database Table: The actual database table the applet will be initially connecting to should be chosen and, in this instance, only one option should be available to us.
- Database Columns: After selecting the database table, the columns of data associated with this table will be displayed. This allows you to select which columns of data you will be using and, in this instance, leave all of them selected.
- Component and Labels: This section allows us to change the names of the labels. Once again, we shall leave them for the moment. In addition to the label names, the field type can be modified. For this, we will change the field type from TextArea to TextField.
- Database Operations: Finally, the type of database operations we will support can be chosen. For this example, we will use only a subset of them which will include next, prev, add and save.
Configuring the attributes was a simple matter of clicking on the field and changing the various parameters in the Properties window. Then, by running the applet the source compiled and a connection to the dbAnywhere server was made. It was good to see this all happened without even a hint of an error - which to the first time developer is never a bad thing.
That's it! The first version of Informer is complete. We have full database functionality and an applet that is ready for widespread deployment. As I said in previous articles, remember to copy the Symantec classes into the directory where the applet will be downloaded from; otherwise, the browser won't be able to find all the dbAnywhere classes.
In this column we took the first steps to building a true database solution, using Java at the client side. Using Visual Café and dbAnywhere, we developed a sophisticated application without having to code a single line of Java ourselves. Unfortunately, from now on we will have to get our hands dirty, Java'ly speaking. In the next column, I will take Informer to the next level of functionality by adding a search facility and a more advanced viewing form.
About the Author
Alan Williamson is on the Board of Directors at N-ARY Limited, a UK-based Java software company specializing in Java/JDBC/Servlets. He has recently completed his second book, which focuses on Java Servlets. His first book looked at using Java/JDBC/Servlets to provide a very efficient database solution. Alan can be reached at [email protected] (http://www.n-ary.com). He welcomes suggestions and comments.
DBANYWHERE = new symantec.itools.db.pro.Session(
Contacts = new symantec.itools.db.pro.ConnectionInfo("Contacts");
symantec.itools.db.pro.Request _Request =
catch (symjava.sql.SQLException e)