Table of Contents | Previous | Next | Index


Chapter 9
Working with a Database

This chapter discusses working with DB2, Informix, ODBC, Oracle, or Sybase relational databases. It describes how to retrieve information from the database and use it in your application, how to work with database transactions, and how to execute database stored procedures.

Remember that if your application runs on Netscape FastTrack Server instead of Netscape Enterprise Server, it can access only databases on servers using the ODBC standard.

This chapter contains the following sections:


Interacting with a Relational Database

The LiveWire Database Service allows you to interact with a relational database in many ways. You can do all of the following:

For information on how to set up and manage your database connections, see Chapter 8, "Connecting to a Database."


Automatically Displaying Query Results

The simplest and quickest way to display the results of database queries is to use the SQLTable method of the database object or a Connection object. The SQLTable method takes an SQL SELECT statement and returns an HTML table. Each row and column in the query is a row and column of the table. The HTML table also has column headings for each column in the database table.

The SQLTable method does not give you control over formatting of the output. Furthermore, if that output contains a Blob object, that object does not display as an image. (For information on blobs, see "Working with Binary Data.") If you want to customize the appearance of the output, use a database cursor to create your own display function. For more information, see "Manipulating Query Results with Cursors."

As an example, if myconn is a Connection object, the following JavaScript statement displays the results of the database query in a table:

myconn.SQLTable("select * from videos");
The following is the first part of the table that could be generated by these statements:

Title ID Year Category Quantity On Hand Synopsis

A Clockwork Orange

1

1975

Science Fiction

5

3

Little Alex and his droogies stop by the Miloko bar for a refreshing libation before a wild night on the town.

Philadelphia Story

1

1940

Romantic Comedy

Katherine Hepburn and Cary Grant are reunited on the eve of her remarriage, with Jimmy Stewart for complications.


Executing Arbitrary SQL Statements

The execute method of the database object or a Connection object enables an application to execute an arbitrary SQL statement. Using execute is referred to as performing passthrough SQL, because it passes SQL directly to the server.

You can use execute for any data definition language (DDL) or data manipulation language (DML) SQL statement supported by the database server. Examples include CREATE, ALTER, and DROP. While you can use it to execute any SQL statement, you cannot return data with the execute method.

Notice that execute is for performing standard SQL statements, not for performing extensions to SQL provided by a particular database vendor. For example, you cannot call the Oracle describe function or the Informix load function from the execute method.

To perform passthrough SQL statements, simply provide the SQL statement as the parameter to the execute method. For example, you might want to remove a table from the database that is referred to by the project object's oldtable property. To do so, you can use this method call:

connobj.execute("DROP TABLE " + project.oldtable);
Important When using execute, your SQL statement must strictly conform to the SQL syntax requirements of the database server. For example, some servers require each SQL statement to be terminated by a semicolon. For more information, see your database server documentation.
If you have not explicitly started a transaction, the single statement is committed automatically. For more information on transaction control, see "Managing Transactions."

To perform some actions, such as creating or deleting a table, you may need to have privileges granted by your database administrator. Refer to your database server documentation for more information, or ask your database administrator.


Manipulating Query Results with Cursors

In many situations, you do not simply want to display a table of query results. You may want to change the formatting of the result or even do arbitrary processing, rather than displaying it at all. To manipulate query results, you work with a database cursor returned by a database query. To create an instance of the Cursor class, call the database object's or a Connection object's cursor method, passing an SQL SELECT statement as its parameter.

You can think of a cursor as a virtual table, with rows and columns specified by the query. A cursor also implies the notion of a current row, which is essentially a pointer to a row in the virtual table. When you perform operations with a cursor, they usually affect the current row.

When finished, close the database cursor by calling its close method. A database connection cannot be released until all associated cursors have been closed. For example, if you call a Connection object's release method and that connection has an associated cursor that has not been closed, the connection is not actually released until you close the cursor.

The following table summarizes the methods and properties of the Cursor class.

Table 9.1 Cursor properties and methods  

Method or Property Description

colName

Properties corresponding to each column in the cursor. The name of each colName property is the name of a column in the database.

close

Disposes of the cursor.

columns

Returns the number of columns in the cursor.

columnName

Returns the name of a column in the cursor.

next

Makes the next row in the cursor the current row.

insertRow

Inserts a new row into the specified table.

updateRow

Updates records in the current row of the specified table.

deleteRow

Deletes the current row of the specified table.

For complete information on these methods, see the description of the Cursor class in the Server-Side JavaScript Reference.

Creating a Cursor

Once an application is connected to a database, you can create a cursor by calling the cursor method of the associated database or Connection object. Creating the Cursor object also opens the cursor in the database. You do not need a separate open command. You can supply the following information when creating a Cursor object:

For example, the following statement creates a cursor for records from the CUSTOMER table. The records contain the columns id, name, and city and are ordered by the value of the id column.

custs = connobj.cursor ("select id, name, city
   from customer order by id");
This statement sets the variable custs to a Cursor object. The SQL query might return the following rows:

1 Sally Smith Suva
2 Jane Doe Cupertino
3 John Brown Harper's Ferry
You can then access this information using methods of the custs Cursor object. This object has id, name, and city properties, corresponding to the columns in the virtual table.

When you initially create a Cursor object, the pointer is positioned just before the first row in the virtual table. The following sections describe how you can get information from the virtual table.

You can also use the string concatenation operator (+) and string variables (such as client or request property values) when constructing a SELECT statement. For example, the following call uses a previously stored customer ID to further constrain the query:

custs = connobj.cursor ("select * from customer where id = "
   + client.customerID);
You can encounter various problems when you try to create a Cursor object. For example, if the SELECT statement in your call to the cursor method refers to a nonexistent table, the database returns an error and the cursor method returns null instead of a Cursor object. In this situation, you should use the majorErrorCode and majorErrorMessage methods to determine what error has occurred.

As a second example, suppose the SELECT statement refers to a table that exists but has no rows. In this case, the database may not return an error, and the cursor method returns a valid Cursor object. However, since that object has no rows, the first time you use the next method on the object, it returns false. Your application should check for this possibility.

Displaying Record Values

When you create a cursor, it acquires a colName property for each named column in the virtual table (other than those corresponding to aggregate functions), as determined by the SELECT statement. You can access the values for the current row using these properties. In the example above, the cursor has properties for the columns id, name, and city. You could display the values of the first returned row using the following statements:

// Create the Cursor object.
custs = connobj.cursor ("select id, name, city
   from customer order by id");
// Before continuing, make sure a real cursor was returned
// and there was no database error.
if ( custs && (connobj.majorErrorCode() == 0) ) {
   // Get the first row
   custs.next();
   // Display the values
   write ("<B>Customer Name:</B> " + custs.name + "<BR>");
   write ("<B>City:</B> " + custs.city + "<BR>");
   write ("<B>Customer ID:</B> " + custs.id);
   //Close the cursor
   custs.close();
}
Initially, the current row is positioned before the first row in the table. The execution of the next method moves the current row to the first row. For example, suppose this is the first row of the cursor:

1 Sally Smith Suva
In this case, the preceding code displays the following:

Customer Name: Sally Smith
City: Suva
Customer ID: 1

You can also refer to properties of a Cursor object (or indeed any JavaScript object) as elements of an array. The zero-index array element corresponds to the first column, the one-index array element corresponds to the second column, and so on.

For example, you could use an index to display the same column values retrieved in the previous example:

write ("<B>Customer Name:</B> " + custs[1] + "<BR>");
write ("<B>City:</B> " + custs[2] + "<BR>");
write ("<B>Customer ID:</B> " + custs[0]);
This technique is particularly useful inside a loop. For example, you can create a Cursor object named custs and display its query results in an HTML table with the following code:

// Create the Cursor object.
custs = connobj.cursor ("select id, name, city
   from customer order by id");
// Before continuing, make sure a real cursor was returned
// and there was no database error.
if ( custs && (connobj.majorErrorCode() == 0) ) {
   write ("<TABLE BORDER=1>");
   // Display column names as headers.
   write("<TR>");
   i = 0;
   while ( i < custs.columns() ) {
      write("<TH>", custs.columnName(i), "</TH>");
      i++;
   }
   write("</TR>");
   // Display each row in the virtual table.
   while(custs.next()) {
      write("<TR>");
      i = 0;
      while ( i < custs.columns() ) {
         write("<TD>", custs[i], "</TD>");
         i++;
      }
   write("</TR>");
   }
   write ("</TABLE>");
   // Close the cursor.
   custs.close();
}
This code would display the following table:

ID NAME CITY

1

Sally Smith

Suva

2

Jane Doe

Cupertino

3

John Brown

Harper's Ferry

This example uses methods discussed in the following sections.

Displaying Expressions and Aggregate Functions

SELECT statements can retrieve values that are not columns in the database, such as aggregate values and SQL expressions. For such values, the Cursor object does not have a named property. You can access these values only by using the Cursor object's property array index for the value.

The following example creates a cursor named empData, navigates to the row in that cursor, and then displays the value retrieved by the aggregate function MAX. It also checks to make sure the results from the database are valid before using them:

empData = connobj.cursor ("select min(salary), avg(salary),
   max(salary) from employees");
if ( empData && (connobj.majorErrorCode() == 0) ) {
   rowexists = empData.next();
   if (rowexists) { write("Highest salary is ", empData[2]); }
}
This second example creates a cursor named empRows to count the number of rows in the table, navigates to the row in that cursor, and then displays the number of rows, once again checking validity of the data:

empRows = connobj.cursor ("select count(*) from employees");
if ( empRows && (connobj.majorErrorCode() == 0) ) {
   rowexists = empRows.next();
   if (rowexists) { write ("Number of rows in table: ", empRows[0]); }
}

Navigating with Cursors

Initially, the pointer for a cursor is positioned before the first row in the virtual table. Use the next method to move the pointer through the records in the virtual table. This method moves the pointer to the next row and returns true as long it found another row in the virtual table. If there is not another row, next returns false.

For example, suppose a virtual table has columns named title, rentalDate, and dueDate. The following code uses next to iterate through the rows and display the column values in a table:

// Create the cursor.
custs = connobj.cursor ("select * from customer");
// Check for validity of the cursor and no database errors.
if ( custs && (connobj.majorErrorCode() == 0) ) {
   write ("<TABLE>");
   // Iterate through rows, displaying values.
   while (custs.next()) {
      write ("<TR><TD>" + custs.title + "</TD>" +
         "<TD>" + custs.rentalDate + "</TD>" +
         "<TD>" + custs.dueDate + "</TD></TR>");
   }
   write ("</TABLE>");
   // Always close your cursors when finished!
   custs.close();
}
This code could produce output such as the following:

Clockwork Orange

6/3/97

9/3/97

Philadelphia Story

8/1/97

8/5/97

You cannot necessarily depend on your place in the cursor. For example, suppose you create a cursor and, while you're working with it, someone else adds a row to the table. Depending on the settings of the database, that row may appear in your cursor. For this reason, when appropriate (such as when updating rows) you may want your code to have tests to ensure it's working on the appropriate row.

Working with Columns

The columns method of the Cursor class returns the number of columns in a cursor. This method takes no parameters:

custs.columns()
You might use this method if you need to iterate over each column in a cursor.

The columnName method of the Cursor class returns the name of a column in the virtual table. This method takes an integer as a parameter, where the integer specifies the ordinal number of the column, starting with 0. The first column in the virtual table is 0, the second is 1, and so on.

For example, the following expression assigns the name of the first column in the custs cursor to the variable header:

header = custs.columnName(0)
If your SELECT statement uses a wildcard (*) to select all the columns in a table, the columnName method does not guarantee the order in which it assigns numbers to the columns. That is, suppose you have this statement:

custs = connobj.cursor ("select * from customer");
If the customer table has 3 columns, ID, NAME, and CITY, you cannot tell ahead of time which of these columns corresponds to custs.columnName(0). (Of course, you are guaranteed that successive calls to columnName have the same result.) If the order matters to you, you can instead hard-code the column names in the select statement, as in the following statement:

custs = connobj.cursor ("select ID, NAME, CITY from customer");
With this statement, custs.columnName(0) is ID, custs.columnName(1) is NAME, and custs.columnName(2) is CITY.

Changing Database Information

You can use an updatable cursor to modify a table based on the cursor's current row. To request an updatable cursor, add an additional parameter of true when creating the cursor, as in the following example:

custs = connobj.cursor ("select id, name, city from customer", true)
For a cursor to be updatable, the SELECT statement must be an updatable query (one that allows updating). For example, the statement cannot retrieve rows from more than one table or contain a GROUP BY clause, and generally it must retrieve key values from a table. For more information on constructing updatable queries, consult your database vendor's documentation.

When you use cursors to make changes to your database, you should always work inside an explicit transaction. You do so using the beginTransaction, commitTransaction, and rollbackTransaction methods, as described in "Managing Transactions." If you do not use explicit transactions in these situations, you may get errors from your database.

For example, Informix and Oracle both return error messages if you use a cursor without an explicit transaction. Oracle returns Error ORA-01002: fetch out of sequence; Informix returns Error -206: There is no current row for UPDATE/DELETE cursor.

As mentioned in "Navigating with Cursors," you cannot necessarily depend on your position in the cursor. For this reason, when making changes to the database, be sure to test that you're working on the correct row before changing it.

Also, remember that when you create a cursor, the pointer is positioned before any of the rows in the cursor. So, to update a row, you must call the next method at least once to establish the first row of the table as the current row. Once you have a row, you can assign values to columns in the cursor.

The following example uses an updatable cursor to compute the bonus for salespeople who met their quota. It then updates the database with this information:

connobj.beginTransaction ();
emps = connobj.cursor(
   "select * from employees where dept='sales'", true);
// Before proceeding make sure the cursor was created and
// there was no database error.
if ( emps && (connobj.majorErrorCode() == 0) ) {
   // Iterate over the rows of the cursor, updating information
   // based on the return value of the metQuota function.
   while ( emps.next() ) {
      if (metQuota (request.quota, emps.sold)) {
         emps.bonus = computeBonus (emps.sold);
      }
      else emps.bonus = 0;
      emps.updateRow ("employees");
   }
   // When done, close the cursor and commit the transaction.
   emps.close();
   connobj.commitTransaction();
}
else {
   // If there wasn't a cursor to work with, roll back the transaction.
   connobj.rollbackTransaction();
}
This example creates an updatable cursor of all employees in the Sales department. It iterates over the rows of that cursor, using the user-defined JavaScript function metQuota to determine whether or not the employee met quota. This function uses the value of quota property of the request object (possibly set in a form on a client page) and the sold column of the cursor to make this determination. The code then sets the bonus appropriately and calls updateRow to modify the employees table. Once all rows in the cursor have been accessed, the code commits the transaction. If no cursor was returned by the call to the cursor method, the code rolls back the transaction.

In addition to the updateRow method, you can use the insertRow and deleteRow methods to insert a new row or delete the current row. You do not need to assign values when you use deleteRow, because it simply deletes an entire row.

When you use insertRow, the values you assign to columns are used for the new row. If you have previously called the cursor's next method, then the values of the current row are used for any columns without assigned values; otherwise, the unassigned columns are null. Also, if some columns in the table are not in the cursor, then insertRow inserts null in these columns. The location of the inserted row depends on the database vendor library. If you need to access the row after you call the insertRow method, you must first close the existing cursor and then open a new cursor.

NOTE: DB2 has a Time data type. JavaScript does not have a corresponding data type. For this reason, you cannot update rows with values that use the DB2 Time data type

Managing Transactions

A transaction is a group of database actions that are performed together. Either all the actions succeed together or all fail together. When you apply all actions, making permanent changes to the database, you are said to commit a transaction. You can also roll back a transaction that you have not committed; this cancels all the actions.

Transactions are important for maintaining data integrity and consistency. Although the various database servers implement transactions slightly differently, the LiveWire Database Service provides the same methods for transaction management with all databases. Refer to the database vendor documentation for information on data consistency and isolation levels in transactions.

You can use explicit transaction control for any set of actions. For example, actions that modify a database should come under transaction control. These actions correspond to SQL INSERT, UPDATE, and DELETE statements. Transactions can also be used to control the consistency of the data you refer to in your application.

For most databases, if you do not control transactions explicitly, the runtime engine uses the underlying database's autocommit feature to treat each database statement as a separate transaction. Each statement is either committed or rolled back immediately, based on the success or failure of the individual statement. Explicitly managing transactions overrides this default behavior.

In some databases, such as Oracle, autocommit is an explicit feature that LiveWire turns on for individual statements. In others, such as Informix, autocommit is the default behavior when you do not create a transaction. In general, LiveWire hides these differences and puts an application in autocommit mode whenever the application does not use beginTransaction to explicitly start a transaction.

For Informix ANSI databases, LiveWire does not use autocommit. For these databases, an application always uses transactions even if it never explicitly calls beginTransaction. The application must use commitTransaction or rollbackTransaction to finish the transaction.

NOTE: You are strongly encouraged to use explicit transaction control any time you make changes to a database. This ensures that the changes succeed or fail together. In addition, any time you use updatable cursors, you should use explicit transactions to control the consistency of your data between the time you read the data (with next) and the time you change it (with insertRow, updateRow, or deleteRow). As described in "Changing Database Information" on page 225, using explicit transaction control with updatable cursors is necessary to avoid errors in some databases such as Oracle and Informix.

Using the Transaction-Control Methods

Use the following methods of the database object or a Connection object to explicitly manage transactions:

The LiveWire Database Service does not support nested transactions. If you call beginTransaction multiple times before committing or rolling back the first transaction you opened, you'll get an error.

For the database object, the maximum scope of a transaction is limited to the current client request (HTML page) in the application. If the application exits the page before calling the commitTransaction or rollbackTransaction method, then the transaction is automatically either committed or rolled back, based on the setting of the commitflag parameter provided when you connected to the database.

For Connection objects, the scope of a transaction is limited to the lifetime of that object. If you release the connection or close the pool of connections before calling the commitTransaction or rollbackTransaction method, then the transaction is automatically either committed or rolled back, based on the setting of the commitflag parameter provided when you made the connection, either with the connect method or in the DbPool constructor.

If there is no current transaction (that is, if the application has not called beginTransaction), calls to commitTransaction and rollbackTransaction can result in an error from the database.

You can set your transaction to work at different levels of granularity. The example described in "Changing Database Information" creates a single transaction for modifying all rows of the cursor. If your cursor has a small number of rows, this approach is sensible.

If, however, your cursor returns thousands of rows, you may want to process the cursor in multiple transactions. This approach can both cut down the transaction size and improve the concurrency of access to that information.

If you do break down your processing into multiple transactions, be certain that a call to next and an associated call to updateRow or deleteRow happen within the same transaction. If you get a row in one transaction, finish that transaction, and then attempt to either update or delete the row, you may get an error from your database.

How you choose to handle transactions depends on the goals of your application. You should refer to your database vendor documentation for more information on how to use transactions appropriately for that database type.


Working with Binary Data

Binary data for multimedia content such as an image or sound is stored in a database as a binary large object (BLOb). You can use one of two techniques to handle binary data in JavaScript applications:

If you do not need to keep BLOb data in a database, you can store the filenames in the database and access them in your application with standard HTML tags. For example, if you want to display an image for each row in a database table, you could have a column in the table called imageFileName containing the name of the desired image file. You could then use this HTML expression to display the image for each row:

<IMG SRC=`mycursor.imageFileName`>
As the cursor navigates through the table, the name of the file in the IMG tag changes to refer to the appropriate file.

If you need to manipulate actual binary data in your database, the JavaScript runtime engine recognizes when the value in a column is BLOb data. That is, when the software creates a Cursor object, if one of the database columns contains BLOb data, the software creates a Blob object for the corresponding value in the Cursor object. You can then use the Blob object's methods to display that data. Also, if you want to insert BLOb data into a database, the software provides a global function for you to use.

The following table outlines the methods and functions for working with BLOb data.

Table 9.2  Methods and functions for working with Blobs  

Method or Function Description

blobImage

Method to use when displaying BLOb data stored in a database. Returns an HTML IMG tag for the specified image type (GIF, JPEG, and so on).

blobLink

Method to use when creating a link that refers to BLOb data with a hyperlink. Returns an HTML hyperlink to the BLOb.

blob

Global function to use to insert or update a row containing BLOb data. Assigns BLOb data to a column in a cursor.

The blobImage method fetches a BLOb from the database, creates a temporary file of the specified format, and generates an HTML IMG tag that refers to the temporary file. The runtime engine removes the temporary file after the page is generated and sent to the client.

The blobLink method fetches BLOb data from the database, creates a temporary file, and generates an HTML hypertext link to the temporary file. The runtime engine removes the temporary file after the user clicks the link or 60 seconds after the request has been processed.

The following example illustrates using blobImage and blobLink to create temporary files. In this case, the FISHTBL table has four columns: an ID, a name, and two images. One of these is a small thumbnail image; the other is a larger image. The example code writes HTML for displaying the name, the thumbnail, and a link to the larger image.

cursor = connobj.cursor ("select * from fishtbl");
if ( cursor && (connobj.majorErrorCode() == 0) ) {
   while (cursor.next()) {
      write (cursor.name);
      write (cursor.picture.blobImage("gif"));
      write (cursor.picture.blobLink("image\gif", "Link" + cursor.id));
      write ("<BR>");
   }
   cursor.close();
}
If FISHTBL contains rows for four fish, the example could produce the following HTML:

Cod <IMG SRC="LIVEWIRE_TEMP9"> 
   <A HREF="LIVEWIRE_TEMP10">Link1 </A> <BR>
Anthia <IMG SRC="LIVEWIRE_TEMP11">
   <A HREF="LIVEWIRE_TEMP12">Link2 </A> <BR>
Scorpion <IMG SRC="LIVEWIRE_TEMP13">
   <A HREF="LIVEWIRE_TEMP14">Link3 </A> <BR>
Surgeon <IMG SRC="LIVEWIRE_TEMP15">
   <A HREF="LIVEWIRE_TEMP16">Link4 </A> <BR>
If you want to add BLOb data to a database, use the blob global function. This function assigns BLOb data to a column in an updatable cursor. As opposed to blobImage and blobLink, blob is a top-level function, not a method.

The following statements assign BLOb data to one of the columns in a row and then update that row in the FISHTBL table of the database. The cursor contains a single row.

// Begin a transaction.
database.beginTransaction();
// Create a cursor.
fishCursor = database.cursor ("select * from fishtbl where
   name='Harlequin Ghost Pipefish'", true);
// Make sure cursor was created.
if ( fishCursor && (database.majorErrorCode() == 0) ) {
   // Position the pointer on the row.
   rowexists = fishCursor.next();
   if ( rowexists ) {
      // Assign the blob data.
      fishCursor.picture = blob ("c:\\data\\fish\\photo\\pipe.gif");
      // Update the row.
      fishCursor.updateRow ("fishtbl");
      // Close the cursor and commit the changes.
      fishCursor.close();
      database.commitTransaction();
   }
   else {
      // Close the cursor and roll back the transaction.
      fishCursor.close();
      database.rollbackTransaction();
   }
}
else {
   // Never got a cursor; rollback the transaction.
   database.rollbackTransaction();
}
Remember that the backslash (\) is the escape character in JavaScript. For this reason, you must use two backslashes in NT filenames, as shown in the example.


Calling Stored Procedures

Stored procedures are an integral part of operating and maintaining a relational database. They offer convenience by giving you a way to automate processes that you do often, but they offer other benefits as well:

The LiveWire Database Service provides two classes for working with stored procedures, Stproc and Resultset. With the methods of these classes you can call a stored procedure and manipulate the results of that procedure.

Exchanging Information

Stored procedures work differently for the various databases supported by the LiveWire Database Service. The most important distinction for LiveWire is how you pass information to and from the stored procedure in a JavaScript application. You always use input parameters to the stored procedure to pass information into a stored procedure.

However, conceptually there are several distinct ways you might want to retrieve information from a stored procedure. Not every database vendor lets you retrieve information in all of these ways.

Result Sets

A stored procedure can execute one or more SELECT statements, retrieving information from the database. You can think of this information as a virtual table, very similar to a read-only cursor. (For information on cursors, see "Manipulating Query Results with Cursors.")

LiveWire uses an instance of the Resultset class to contain the rows returned by a single SELECT statement of a stored procedure. If the stored procedure allows multiple SELECT statements, you get a separate Resultset object for each SELECT statement. You use the resultSet method of the Stproc class to obtain a result set object and then you use that object's methods to manipulate the result set.

Different database vendors return a result set in these varying ways:

Output and Input/Output Parameters

In addition to standard input parameters, some database vendors allow other types of parameters for their stored procedures. Output parameters store information on return from the procedure and input/output parameters both pass in information and return information.

For most databases, you use the outParamCount and outParameters methods of the Stproc class to access output and input/output parameters. However, Informix does not allow output or input/output parameters. Therefore, you should not use the outParamCount and outParameters methods with Informix stored procedures.

Return Values

Seen as a simple function call, a stored procedure can have a return value. For Oracle and Sybase, this return value is in addition to any result sets it returns.

You use the returnValue method of the Stproc class to access the return value. However, the return values for Informix stored procedures are used to generate its result set. For this reason, returnValue always returns null for Informix stored procedures. In addition, return values are not available for ODBC and DB2 stored procedures.

Steps for Using Stored Procedures

Once you have a database connection, the steps for using a stored procedure in your application vary slightly for the different databases:

  1. (DB2 only) Register the stored procedure in the appropriate system tables. (You do this outside of JavaScript.)
  2. (DB2, ODBC, and Sybase) Define a prototype for your stored procedure.
  3. (All databases) Execute the stored procedure.
  4. (All databases) Create a resultSet object and get the data from that object.
  5. (DB2, ODBC, and Sybase) Complete the execution by accessing the return value.
  6. (DB2, ODBC, Oracle, and Sybase) Complete the execution by getting the output parameters.
Notice that for several databases you can complete execution of your stored procedure either by getting the return value or by accessing the output parameters. Once you have done either of these things, you can no longer work with any result sets created by execution of the stored procedure.

The following sections describe each of these steps in more detail.

Registering the Stored Procedure

This step applies only to DB2.

DB2 has various system tables in which you can record your stored procedure. In general, entering a stored procedure in these tables is optional. However, to use your stored procedure with LiveWire, you must make entries in these tables. You perform this step outside of the JavaScript application.

For DB2 common server, you must create the DB2CLI.PROCEDURES system table and enter your DB2 stored procedures in it. DB2CLI.PROCEDURES is a pseudo-catalog table.

If your DB2 is for IBM MVS/EA version 4.1 or later, you must define the name of your stored procedures in the SYSIBM.SYSPROCEDURES catalog table.

Remember you use C, C++, or another source language to write a DB2 stored procedure. The data types you use with those languages do not match the data types available in DB2. Therefore, when you add the stored procedure to DB2CLI.PROCEDURES or SYSIBM.SYSPROCEDURES, be sure to record the corresponding DB2 data type for the stored procedure parameters and not the data types of the source language.

For information on DB2 data types and on how to make entries in these tables, see your DB2 documentation.

Defining a Prototype for a Stored Procedure

This step is relevant only for DB2, ODBC, and Sybase stored procedures, both user-defined and system stored procedures. You do not need to define a prototype for stored procedures for Oracle or Informix databases.

For DB2, ODBC, and Sybase, the software cannot determine at runtime whether a particular parameter is for input, for output, or for both. Consequently, after you connect to the database, you must create a prototype providing information about the stored procedure you want to use, using the storedProcArgs method of the database or DbPool object.

You need exactly one prototype for each stored procedure in your application. The software ignores additional prototypes for the same stored procedure.

In the prototype, you provide the name of the stored procedure and the type of each of its parameters. A parameter must be for input (IN), output (OUT), or input and output (INOUT). For example, to create a prototype for a stored procedure called newhire that has two input parameters and one output parameter, you could use this method call:

poolobj.storedProcArgs("newhire", "IN", "IN", "OUT");

Executing the Stored Procedure

This step is relevant to all stored procedures.

To execute a stored procedure, you create a Stproc object using the database or Connection object's storedProc method. Creating the object automatically invokes the stored procedure. When creating a stored-procedure object, you specify the name of the procedure and any parameters to the procedure.

For example, assume you have a stored procedure called newhire that takes one string and one integer parameter. The following method call creates the spObj stored-procedure object and invokes the newhire stored procedure:

spObj = connobj.storedProc("newhire", "Fred Jones", 1996);
In general, you must provide values for all input and input/output parameters to the stored procedure. If a stored procedure has a default value defined for one of its parameters, you can use the "/Default/" directive to specify that default value. Similarly, if a stored procedure can take a null value for one of its parameters, you can specify the null value either with the "/Null/" directive or by passing in the null value itself.

For example, assume the demosp stored procedure takes two string parameters and one integer parameter. You could supply all the parameters as follows:

spobj = connobj.storedProc("demosp", "Param_1", "Param_2", 1);
Alternatively, to pass null for the second parameter and to use the default value for third parameter, you could use either of these statements:

spobj = connobj.storedProc("demosp", "Param_1", "/Null/", "/Default/");
spobj = connobj.storedProc("demosp", "Param_1", null, "/Default/");
NOTE: On Informix, default values must occur only after all specified values. For example, you cannot use /Default/ for the second parameter of a stored procedure and then specify a value for the third parameter.
You can also use the "/Default/" and "/Null/" directives for input/output parameters.

An Oracle stored procedure can take ref cursors as input/output or output parameters. For example, assume you have an Oracle stored procedure named proc1 that takes four parameters: a ref cursor, an integer value, another ref cursor, and another integer value. The call to that stored procedure from SQL Plus might look as follows:

execute proc1 (refcursor1, 3, refcursor2, 5);
When you call this stored procedure from within a JavaScript application, however, you do not supply the ref cursor parameters. Instead, the equivalent call would be:

spobj = connobj.storedProc("proc1", 3, 5);
For information on output parameters, see "Working with Output Parameters." Output parameters cannot be null; however, you can assign a null value to input or input/output parameters.

The following table summarizes the methods of a stored-procedure object.

Table 9.3 Stproc methods  

Method Description

resultSet

Returns the next result set for the stored procedure.

For Informix, you can have zero or one result set. For other databases, you can have zero, one, or more result sets.

returnValue

Retrieves the return value of the stored procedure.

For Informix, DB2, and ODBC, this method always returns null.

outParameters

Returns the specified output parameter.

Because Informix stored procedures do not use output parameters, do not use this method with Informix.

outParamCount

Returns the number of output parameters.

For Informix, this method always returns 0, because Informix stored procedures do not use output parameters.

Working with Result Sets

This step is relevant for all stored procedures.

As described in "Result Sets," different databases returns result sets in different ways. For example, assume you have the CUSTINFO table with the columns id, city, and name. In Sybase, you could use this stored procedure to get the first 200 rows of the table:

create proc getcusts as
begin
   select id, name, city from custinfo where custno < 200
end
If CUSTINFO were an Informix table, the equivalent Informix stored procedure would be this:

create procedure getcusts returning int, char(15), char(15);
define rcity, rname char (15);
define i int;
foreach
   select id, name, city into i, rname, rcity
      from custinfo
      where id < 200;
   return i, rname, rcity with resume;
end foreach;
end procedure;
If CUSTINFO were an Oracle table, the equivalent Oracle stored procedure would be:

create or replace package orapack as
   type custcurtype is ref cursor return custinfo%rowtype
end orapack;
create or replace custresultset (custcursor inout orapack.custcurtype)
as begin
   open custcursor for select id, name, city from custinfo
      where id < 200
end custresultset;
In all cases, you create a resultSet object to retrieve the information from the stored procedure. You do so by using the stored-procedure object's resultSet method, as follows:

resObj = spObj.resultSet();
As for Cursor objects, resultSet objects have a current row, which is simply the row being pointed to in the result set. Initially, the pointer is positioned before the first row of the result set. To see the values in the rows of the result set, you use the next method to move the pointer through the rows in the result set, as shown in the following example:

spobj = connobj.storedProc("getcusts");
if ( spobj && (connobj.majorErrorCode() == 0) ) {
   // Creates a new resultSet object.
   resobj = spobj.resultSet();
   // Make sure you got a result set before continuing.
   if ( resobj && (connobj.majorErrorCode() == 0) ) {
      // Initially moves the resultSet object pointer to the first 
      // result set row and then loops through the rows.
      while (resObj.next())
      {
         write("<TR><TD>" + resObj.name + "</TD>");
         write("<TD>" + resObj.city + "</TD>");
         write("<TD>" + resObj.id + "</TD></TR>");
      }
      resobj.close();
   }
}
As long as there is another row in the result set, the next method returns true and moves the pointer to the next row. When the pointer reaches the last row in the result set, the next method returns false.

The preceding example works for a Sybase stored procedure. In that case, the resultSet object contains a named property for each column in the result set. For Informix and DB2 stored procedures, by contrast, the object does not contain named columns. In this case, you can get the values by referencing the column position. So, for Informix and DB2, you would use this code to display the same information:

spobj = connobj.storedProc("getcusts");
if ( spobj && (connobj.majorErrorCode() == 0) ) {
   // Creates a new resultSet object.
   resobj = spobj.resultSet();
   // Make sure you got a result set before continuing.
   if ( resobj && (connobj.majorErrorCode() == 0) ) {
      // Initially moves the resultSet object pointer to the first 
      // result set row and then loops through the rows.
      while (resObj.next())
      {
         write("<TR><TD>" + resObj[1] + "</TD>");
         write("<TD>" + resObj[2] + "</TD>");
         write("<TD>" + resObj[0] + "</TD></TR>");
      }
      resobj.close();
   }
}
You can use the column position for result sets with any database, not just with Informix and DB2. You can use the column name for stored procedures for all database types other than Informix or DB2.

Multiple Result Sets

A Sybase, Oracle, DB2, or ODBC stored procedure can create multiple result sets. If it does, the stored procedure provides one resultSet object for each. Suppose your stored procedure executes these SQL statements:

select name from customers where id = 6767
select * from orders where id = 6767
You could use the multiple resultSet objects generated by these statements as follows:

// This statement is needed for DB2, ODBC, and Sybase. 
poolobj.storedProcArgs("GetCustOrderInfo","IN");
spobj = connobj.storedProc("GetCustOrderInfo",6767);
if ( spobj && (connobj.majorErrorCode() == 0) ) {
   resobj1 = spobj.resultSet();
   // Make sure result set exists before continuing.
   if ( resobj1 && (connobj.majorErrorCode() == 0) ) {
      // This first result set returns only one row. 
      // Make sure that row contains data.
      rowexists = resobj1.next();
      if ( rowexists )
         write("<P>Customer " + resobj1.name +
            " has the following orders:</P>");
      resobj1.close();
      // The second result set returns one row for each order placed 
      // by the customer. Make sure the rows have data.
      resobj2 = spobj.resultSet();
      var i = 0;
      if ( resobj2 && (connobj.majorErrorCode() == 0) ) {
         write("\nOrder# Quantity Total</P>");
         while(resobj2.next()) {
            write(resobj2.orderno + " " + resobj2.quantity
               + " " + resobj2.Totalamount + "</P>");
            i++;
         }
         resobj2.close();
         write("Customer has " + i + " orders.</P>");
      }
      else write("Customer has no orders.</P>");
   }
}
spobj.close();
For an example of using multiple Oracle ref cursors in a stored procedure, see the description of the Resultset class in the Server-Side JavaScript Reference.

Result Set Methods and Properties

The following table summarizes the methods and properties of the Resultset class.

Table 9.4 Resultset methods and properties  

Method or Property Description

colName

Properties corresponding to each of the columns in the result set. The name of each property is the name of the column in the database.

Since Informix and DB2 stored procedures do not return named columns, these properties are not created for Informix or DB2 stored procedures.

columns

Returns the number of columns in the result set.

For Informix, this method returns the number of return values for a single row.

columnName

Returns the name of a column in the result set.

Because Informix and DB2 stored procedures do not have associated column names, do not use this method for stored procedures for those databases.

close

Disposes of the Resultset object.

next

Makes the next row in the result set the current row. Returns false if the current row is the last row in the result set; otherwise, returns true.

A resultSet object is a read-only, sequential-style object. For this reason, the class does not have the insertRow, deleteRow, and updateRow methods defined for Cursor objects.

When You Can Use Result Sets

A resultSet object is not valid indefinitely. In general, once a stored procedure starts, no interactions are allowed between the database client and the database server until the stored procedure has completed. In particular, there are three circumstances that cause a result set to be invalid.

  1. If you create a result set as part of a transaction, you must finish using the result set during that transaction. Once you either commit or roll back the transaction, you can't get any more data from a result set, and you can't get any additional result sets. For example, the following code is illegal:
  2. database.beginTransaction();
    spobj = database.storedProc("getcusts");
    resobj = spobj.resultSet();
    database.commitTransaction();
    // Illegal! Result set no longer valid!
    col1 = resobj[0];

  3. For Sybase, ODBC, and DB2, you must retrieve resultSet objects before you call a stored-procedure object's returnValue or outParameters methods. Once you call either of these methods, you can't get any more data from a result set, and you can't get any additional result sets. See "Working with Return Values," for more information about these methods.
  4. spobj = database.storedProc("getcusts");
    resobj = spobj.resultSet();
    retval = spobj.returnValue();
    // Illegal! Result set no longer valid!
    col1 = resobj[0];

  5. For Sybase, you must retrieve resultSet objects before you call the cursor or SQLTable method of the associated connection. Once you call cursor or SQLTable, the result set is no longer available. For example, the following code is illegal:
  6. spobj = database.storedProc("getcusts");
    resobj = spobj.resultSet();
    curobj = database.cursor ("select * from orders");
    // Illegal! The result set is no longer available!
    col1 = resobj[0];

  7. For ODBC, a slightly different restriction holds. Again, you must work with the resultSet objects before you call the associated connection's cursor or SQLTable method. For ODBC, if you get a cursor, then access the result set, and then use the cursor, the Cursor object is no longer available. For example, the following code is illegal:
  8. spbobj = database.storedProc("getcusts");
    resobj = spobj.resulSet();
    curobj = database.cursor ("select * from orders");
    col1 = resobj[0];
    // Illegal! The cursor is no longer available.
    curobj.next();

Working with Return Values

This step is relevant to Sybase and Oracle stored procedures. For Informix, ODBC, and DB2 stored procedures, the returnValue method always returns null.

If your stored procedure has a return value, you can access that value with the returnValue method.

On DB2, ODBC, and Sybase, you must use stored procedures and cursors sequentially. You cannot intermix them. For this reason, you must let the system know that you have finished using the stored procedure before you can work with a cursor. You do this by calling the returnValue method of the stored procedure object. This method provides the stored procedure's return value (if it has one) and completes the execution of the stored procedure. You should also close all objects related to stored procedures when you have finished using them.

NOTE: For DB2, ODBC, and Sybase, you must retrieve resultSet objects before you call the returnValue method. Once you call returnValue, you can't get any more data from a result set, and you can't get any additional result sets. You should call returnValue after you have processed the result set and before you retrieve the output parameters.

Working with Output Parameters

This step is relevant to Sybase, Oracle, DB2, or ODBC stored procedures. For Informix stored procedures, the methods discussed here are not applicable.

To determine how many output parameters the procedure has (including both output and input/output parameters), you use the outParamCount method. You can work with the output parameters of a stored procedure by using the object's outParameters method. If outParamCount returns 0, the stored procedure has no output parameters. In this situation, do not call outParameters.

For example, suppose you created a stored procedure that finds the name of an employee when given an ID. If there is an employee name associated with the given ID, the stored procedure returns 1, and its output parameter contains the employee name. Otherwise, the output parameter is empty. The following code either displays the employee name or a message indicating the name wasn't found:

id = 100;
getNameProc = connobj.storedProc("getName", id);
returnValue = getNameProc.returnValue();
if (returnValue == 1)
   write ("Name of employee is " + getNameProc.outParameters(0));
else
   write ("No employee with id = " + id);
Assume a stored procedure has one input parameter, one input/output parameter, and one output parameter. Further, assume the call to the stored procedure sends a value for the input parameter and the input/output parameter as shown here:

spobj = connobj.storedProc("myinout", 34, 56);
The outParameters method returns any input/output parameters before it returns the first output parameter.

In the preceding example, if you call outParameters(1), it returns the value returned from the stored procedure. By contrast, if you call outParameters(0), the method returns 56. This is the value passed to the stored procedure in the input/output parameter position.

NOTE: Output parameters cannot be null; however, you can assign a null value to input or input/output parameters.
For DB2, ODBC, and Sybase, you must retrieve resultSet objects and use the returnValue method before you call outParameters. Once you call returnValue or outParameters, you can't get any more data from a result set, and you can't get any additional result sets. You should call outParameters after you have processed the result set and any return values.

Informix and Sybase Exceptions

Informix and Sybase stored procedures can return error codes using exceptions. After you run the stored procedure, you can retrieve these error codes and error messages using the majorErrorCode and majorErrorMessage methods of the associated database or Connection object.

For example, assume you have the following Informix stored procedure:

create procedure usercheck (user varchar(20))
if user = 'LiveWire' then
raise exception -746, 0, 'User not Allowed';
endif
end procedure
When you run this stored procedure, you could check whether an error occurred and then access the error code and message as follows:

spobj = connobj.storedProc("usercheck");
if ( connobj.majorErrorCode() ) {
   write("The procedure returned this error code: " +
      connobj.majorErrorCode());
   write("The procedure returned this error message: " +
      connobj.majorErrorMessage());
}

Table of Contents | Previous | Next | Index

Last Updated: 09/29/99 18:01:57

© Copyright � 1999 Sun Microsystems, Inc. Some preexisting portions Copyright � 1999 Netscape Communications Corp. All rights reserved.