Making the database connection Use Netscape's LiveWire technology to connect your Web pages and databases By Yun Wang Summary LiveWire uses server-side JavaScript to provide fast connections between your Web pages and database servers. Here's a concise primer on using LiveWire to build database connectivity applications with minimum hassle. (2,100 words)

December  1997

he LiveWire technology included with Netscape Enterprise Server 3.0 provides you with an efficient, relatively simple way to create Web database applications. It gives you the power to use a Web front end to access sophisticated databases via a simple programming style. LiveWire technology uses server-side JavaScript to create database applications which are similar to CGI applications. Unlike CGI applications, however, it allows clients to share database connections and it maintains states among application clients. Therefore, it provides better performance than CGI -- CGI applications need to connect to a database server, retrieve data, and then disconnect from the server for every single request. Since LiveWire keeps the connection, there is no need to reconnect to the database server, saving performance time. It also has the advantage of providing native connectivity to databases like DB2, Informix, Oracle and Sybase, and it provides ODBC connections to other databases of all scales, from desktop to mainframe. LiveWire used to be a separate stand-alone product, but Netscape has now integrated it with Enterprise Server 3.0 and discontinued it as a separate product. One of the few drawbacks of using the technology is that LiveWire database applications can only run under Enterprise Server. Although it provides good cross-platform portability between NT and various UNIX flavors, all these platforms must be running Enterprise Server to run LiveWire applications. To create database applications under LiveWire database technology, it's necessary to know server-side JavaScript, SQL (structure query language), and HTML syntax. Before we get into actual programming, let's clear up some of the confusion between client- and server-side JavaScript. Server-side JavaScript JavaScript is an object-oriented scripting language for client- and server-side Web applications; it's being developed by Netscape and Sun Microsystems. Client-side JavaScript has already earned a wide following among developers who want to create downloadable applications in their Web pages. Both server and client side JavaScript shares the same JavaScript core language, but they perform different tasks using different objects. There are objects that work only on the client side and those that work only on the server side -- for example, you can't perform database connections with client-side JavaScript. Furthermore, client-side objects are wiped off of the client after users exit the pages where the objects were embedded, but server-side objects can stay alive even as users move on to other pages or applications. Therefore, server-side objects can be used to aggregate or gather information from client requests. Client-side JavaScript is embedded directly in HTML pages by including the actual scripting code inside of <script> tags; the script is interpreted by the browser at runtime. Server-side JavaScripts can also be embedded in HTML pages (by enclosing them inside of <server> tags), but the script must be pre-compiled before installing it on a Web server. When users request a page that includes server-side JavaScript, Enterprise Server loads the pre-compiled JavaScripts, processes them, and then dynamically generates HTML which it inserts at the appropriate place in the original HTML page. It then serves users the new pages. If the original HTML page also includes client-side JavaScript, Enterprise Server ignores it. You can develop your LiveWire database applications by implementing both server- and client-side JavaScript, but the server-side JavaScript objects are the ones you will use to connect to and interact with the database server. A server-side JavaScript application may consist of HTML files(.html or .htm) and/or JavaScript files(.js). Before you install the JavaScript onto Enterprise Server, you have to use Enterprise Server's JavaScript compiler to compile all the HTML and JavasScript files into a one big .web file. Configuring the environment Before jumping into development with LiveWire, you have to configure Enterprise Server's environment to activate the server-side JavaScript application environment and to connect to database servers properly. To enable Enterprise Server's server-side JavaScript application environment, follow these steps: Sign on to the Enterprise administration server. Select the server that you would like to implement your database applications. Click the "server-side JavaScript" option under the Programs menu. Click "yes" on "activate the server-side JavaScript application environment" Select whether or not to require a password to run server-side JavaScript applications. Once you choose whether or not to set up this access authority requirement, it takes affect for every server-side JavaScript you develop -- in future releases, we'd like to be able to set this feature by application instead of globally. Even though LiveWire supports native connections to various database servers, the database client library must be installed and configured for use with the LiveWire Database Service. Review the Enterprise Server manual carefully to verify which versions of database servers are supported and which client libraries they need. We ran into trouble while trying to use ODBC drivers to connect to our database servers -- we found the ODBC manager which we are using was a newer version that wasn't yet supported. To verify that your Enterprise Server is ready for running server-side JavaScript applications or to verify the connections between Enterprise Servers and database servers, you can run a sample application called dbadmin. Dbadmin can be easily invoked under Application Manager (the environment for configuring server-side JavaScript applications), or you can simply type http://your-domain-name/dbadmin in your Navigator browser. Designing your applications Enterprise Server 3.0 provides a variety of methods to connect to and interact with a database server. Implementing different methods may cause different performance results. It's very important to consider each method carefully and implement the one which is the best fit for your environment. Database connections In the previous version of the LiveWire Database Service, you were limited to only one database connection per application. Thanks to a new object in Enterprise Server 3.0 called dbPool, you can connect to multiple databases and you can connect multiple times to the same database. This new feature not only improves the efficiency of database connections but also lets an application connect to different databases simultaneously and thus access data from different resources. When you create a database connection pool, you can either include a connection to a specific database or you can create a generic dbPool object that isn't limited to a certain connection. If your application needs to connect to a few specific databases or connect to the same database using different security authority every time the application is requested, it might be wise to define a set of specific dbPool objects on the application's initial page. But if your application needs to connect to a large number of databases during its life cycle and it's hard to predict which databases will be needed, you'll probably want to define a set of generic database connection pools instead. To create a specific database connection pool that includes the connection at the same time, create a new DbPool object using the following parameters: myPool = new DbPool("SYBASE", "servername", "user", "pwd", "databasename", 5, true); Here's an explanation of the above parameters: 1st parameter: database server type, such as Informix, Oracle, Sybase, DB2, or ODBC 2nd parameter: database server's name 3rd parameter: user id 4th parameter: password for the user id 5th parameter: name of the database that you would like to connect to 6th parameter: the number of available connections (set this to a proper number depending on your need but make sure it doesn't exceed your database servers' license seat) 7th parameter (optional): whether to commit open transactions at the end of the request To create a generic DbPool by simply creating a new DbPool without passing any parameters, all you need to code is: myPool = new DbPool(); More design issues: Since your database connection pools are shared by all of the clients, it's a good practice to create an object to track occupied connections. It may also be helpful to track how long a client occupies a connection and implement a function to collect and close idle connections. Manipulating data in the databases The LiveWire database service provides various methods to manipulate data in database servers. Here are your options and the commands to implement them once you have made the database connection. SQLTable This is the easiest way to request data from a database and display a query result. It takes an SQL statement and returns results in a HTML table. It only has one format for query results, however, and no binary data (such as an image) would be displayed even if it were part of the query result. So if you need to display your result in a certain format, SQLTable is not a good option. Command to use SQLTable: databaseconn.SQLTable("Select * from category"). Cursors Creating a cursor is the most common way to insert, retrieve, or update data in a database. It lets you browse through qualify records and insert, delete, or update records. If you use cursors alone with JavaScript's Write function, you can display data in whatever format you like. Command to create a cursor: //create cursor mycursor = connobj.cursor("select category_no, category_desc from category"); Passthrough SQL If the data manipulation performed by your application requires no return data set (such as delete qualify records), executing a passthrough SQL statement is the easiest way to go. All you need to do is execute standard SQL statements to the database. Beware, though -- different database servers may have different syntax for certain SQL statements. To be safe, check the syntax for the particular SQL statements you would like to implement. Command to execute a passthrough SQL statement: connobj.execute("Delete from category where category_no = 1000"); Transaction begin, commit, and rollback Transaction management is very important for mission critical database applications. For certain groups of actions, it's necessary that they all be completed successfully or otherwise you need to reserve them all. For example, to process an order entry, first we need to make sure the order is successfully inserted into the order table, then the inventory needs to be updated and the shipping order and invoice printed out. If any of these steps fail, it will ruin data integrity and consistency in the database (not to mention the order will mess up as well). So for these types of group actions, it's very important to use transaction management. Command for transaction management: //begin the transaction connobj.beginTransaction(); connobj.execute("Insert into order (order_no, cust_id) values (11111, 222222); : : connnobj.commitTranscation(); Calling Stored Procedures The improved LiveWire allows users to call stored procedures inside of a server-side JavaScript. But to use stored procedures under DB2, ODBC, and Sybase database servers, it's necessary to define prototypes for stored procedures before actually using them. You don't need to define prototypes for Oracle or Informix database servers. Here are the steps to call a stored procedure: 1. Define a prototype for a stored procedure. You need exactly one prototype for each stored procedure you want to use in your application. In the prototype, you provide the name of the procedure and the type of each of its parameters. A parameter must be either input (IN), output (OUT), or input and output (INOUT). The following is a sample definition: poolobj.storedProcArgs("spname", "IN", "IN", "INOUT", "OUT"); 2. Execute the stored procedure. Sample code: spobj = connobj.storeProc("spname","parm1", parm2, parm3); 3. Create a result set to store the result. Sample code: resobj = spobj.resultSet(); 4. Create other objects to receive return values. Here's a sample of the code for the entire process of calling a stored procedure: //creating prototype connobj.storedProcArgs("spname", "IN", "IN",); //calling sp spobj = connobj.storeProc("spname", "param1", param2); //retrieve from stored procedure resobj = spobj.resultSet(); Compile your application A sever-side JavaScript needs to be compiled into a .web file before loading it into Enterprise Server. In the newest version of Enterprise Server, the compiler has been renamed jsac and you can compile your code under the command line prompt. You need to include any .html files that define and/or use your server-side JavaScripts and any .js files which define server-side JavaScript functions. The compiler will integrate all these files into a .web file. For example: jsac -o example.web home.html start.html check.js The compiling option -o puts your compiled JavaScript into example.web file. Load, test, and debug your application You can load server-side JavaScript applications under Enterprise Server's Application Manager. Under the Application Manager, type in your application's path, its default and initial pages, maximum database connection, external library (if there is any), and client object maintenance method. Then start the application by clicking the start button. It's also easy to debug your application under Application Manager by highlighting it and clicking the debug button. To run your application, you can either select it in the Application Manager and click on the "run" button or type the URL into your Netscape browser. As with any application, it's wise to play with it a bit before releasing it to your users, just to make sure it does everything you thought it would. Also this month in Netscape Enterprise Developer - Will 1998 be a 'Banner' Year? - Beat those hand-coding blues - Enterprise Insight: Technology grows up - The OBI standard gains momentum - New Products: Netscape Takes Over Actra Products - More New Products: Netscape, HP to deliver workflow apps - Will 1998 be a "Banner" Year? - Technology grows up - The OBI standard gains momentum - Beat those hand coding blues - Netscape takes over Actra products - Netscape, HP to deliver workflow apps

Mail this article to a friend

Resources

• Netscape http://www.netscape.com
• Oracle http://www.oracle.com
• Informix http://www.informix.com
• IBM http://www.ibm.com
• Sybase http://www.sybase.com

About the author
Yun Wang is Senior Software Engineer in InfoWorld's Test Center. She is an experienced developer in a variety of languages. Reach Yun at yun.wang@ne-dev.com.

If you have problems with this magazine, contact webmaster@ne-dev.com
URL: http://www.ne-dev.com/ned-12-1997/ned-12-dbconn.html
Last modified: Saturday, November 20, 1999 HYIP Monitor