| Server-side object | |
| Implemented in | Netscape Server 3.0 |
Created by
The resultSet method of a Stproc object. The Resultset object does not have a constructor.
Description
For Sybase, Oracle, ODBC, and DB2 stored procedures, the stored-procedure object has one result set object for each SELECT statement executed by the stored procedure. For Informix stored procedures, the stored-procedure object always has one result set object.
A result set has a property for each column in the SELECT statement used to generate the result set. For Sybase, Oracle, and ODBC stored procedures, you can refer to these properties by the name of the column in the virtual table. For Informix and DB2 stored procedures, the columns are not named. For these databases, you must use a numeric index to refer to the column.
Result set objects are 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:
database.beginTransaction();
spobj = database.storedProc("getcusts");
resobj = spobj.resultSet();
database.commitTransaction();
/* Illegal! Result set no longer valid! */
col1 = resobj[0];
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.
spobj = database.storedProc("getcusts");
resobj = spobj.resultSet();
retval = spobj.returnValue();
/* Illegal! Result set no longer valid! */
col1 = resobj[0];
Connection object's cursor or SQLTable method. For example, the following code is illegal:
spobj = database.storedProc("getcusts");
cursobj = database.cursor("SELECT * FROM ORDERS;");
/* Illegal! The result set is no longer available! */
resobj = spobj.resultSet();
col1 = resobj[0];
Resultset object, use the close method to close it and release the memory it uses. If you release a connection that has an open result set, the runtime engine waits until the result set is closed before actually releasing the connection.
If you do not explicitly close a result set with the close method, the JavaScript runtime engine on the server automatically tries to close all open result sets when the associated database or DbPool object goes out of scope. This can tie up system resources unnecessarily. It can also lead to unpredictable results.
You can use the prototype property of the Resultset class to add a property to all Resultset instances. If you do so, that addition applies to all Resultset objects running in all applications on your server, not just in the single application that made the change. This allows you to expand the capabilities of this object for your entire server.
|
Allows the addition of properties to a Resultset object.
|
Examples
Assume you have the following Oracle stored procedure:
create or replace package timpack
as type timcurtype is ref cursor return customer%rowtype;
type timrentype is ref cursor return rentals%rowtype;
end timpack; create or replace procedure timset4(timrows1 in out timpack.timcurtype, timrows in out timpack.timrentype)
Running this stored procedure creates two result sets you can access. In the following code fragment the
as begin
open timrows for select * from rentals;
open timrows1 for select * from customer;
end timset4; resobj1 result set has rows returned by the timrows ref cursor and the resobj2 result set has the rows returned by the timrows1 ref cursor.
spobj = database.storedProc("timset4");
resobj1 = spobj.resultSet();
resobj2 = spobj.resultSet(); Properties
prototype
Represents the prototype for this class. You can use the prototype to add properties or methods to all instances of a class. For information on prototypes, see Function.prototype.
| Property of |
Resultset
|
| Implemented in | LiveWire 1.0 |
Methods
close
Closes the result set and frees the allocated memory.
| Method of |
Resultset
|
| Implemented in | Netscape Server 3.0 |
Syntax
close()
Parameters
None.
Returns
0 if the call was successful; otherwise, a nonzero status code based on any error message passed by the database. If the method returns a nonzero status code, use the associated majorErrorCode and majorErrorMessage methods to interpret the cause of the error.
Description
The close method closes a cursor or result set and releases the memory it uses. If you do not explicitly close a cursor or result set with the close method, the JavaScript runtime engine on the server automatically closes all open cursors and result sets when the corresponding client object goes out of scope.
rentalSet cursor, performs certain operations on it, and then closes it with the close method.
// Create a Cursor object
rentalSet = database.cursor("SELECT * FROM rentals")
// Perform operations on the cursor
cursorOperations()
//Close the cursor
err = rentalSet.close()
Cursor
columnName
Returns the name of the column in the result set corresponding to the specified number.
| Method of |
Resultset
|
| Implemented in | Netscape Server 3.0 |
Syntax
columnName (n)
Parameters
n | Zero-based integer corresponding to the column in the query. The first column in the result set is 0, the second is 1, and so on. |
Returns
The name of the column. For Informix stored procedures, this method for the Resultset object always returns the string "Expression".
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:
resSet = stObj.resultSet("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 resSet.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:
resSet = stObj.resultSet("select ID, NAME, CITY from customer");
With this statement, resSet.columnName(0) is ID, resSet.columnName(1) is NAME, and resSet.columnName(2) is CITY.
Examples
The following example assigns the name of the first column in the customerSet cursor to the variable header:
customerSet=database.cursor(SELECT * FROM customer ORDER BY name)
header = customerSet.columnName(0)columns
Returns the number of columns in the result set.
| Method of |
Resultset
|
| Implemented in | Netscape Server 3.0 |
Syntax
columns()
Parameters
None.
Returns
The number of named and unnamed columns.
Examples
See Example 2 of Cursor for an example of using the columns method with the cursorColumn array.
The following example returns the number of columns in the
custs cursor:
custs.columns()
next
Moves the current row to the next row in the result set.
| Method of |
Resultset
|
| Implemented in | Netscape Server 3.0 |
Syntax
next()
Parameters
None.
Returns
False if the current row is the last row; otherwise, true.
Description
Initially, the pointer (or current row) for a cursor or result set is positioned before the first row returned. Use the next method to move the pointer through the records in the cursor or result set. This method moves the pointer to the next row and returns true as long as there is another row available. When the cursor or result set has reached the last row, the method returns false. Note that if the cursor is empty, this method always returns false.
Examples
Example 1. This example uses the next method to navigate to the last row in a cursor. The variable x is initialized to true. When the pointer is in the last row of the cursor, the next method returns false and terminates the while loop.
customerSet = database.cursor("select * from customer", true)
x = true
Example 2. In the following example, the
while (x) {
x = customerSet.next() }rentalSet cursor contains columns named videoId, rentalDate, and dueDate. The next method is called in a while loop that iterates over every row in the cursor. When the pointer is on the last row in the cursor, the next method returns false and terminates the while loop.
This example displays the three columns of the cursor in an HTML table:
<SERVER>
// Create a Cursor object
rentalSet = database.cursor("SELECT videoId, rentalDate, returnDate
FROM rentals")
</SERVER>// Create an HTML table
<TABLE BORDER>
<TR>
<TH>Video ID</TH>
<TD>Rental Date</TD>
<TD>Due Date</TD>
</TR><SERVER>
// Iterate through each row in the cursor
while (rentalSet.next()) {
</SERVER>// Display the cursor values in the HTML table
<TR>
<TH><SERVER>write(rentalSet.videoId)</SERVER></TH>
<TD><SERVER>write(rentalSet.rentalDate)</SERVER></TD>
<TD><SERVER>write(rentalSet.returnDate)</SERVER></TD>
</TR>// Terminate the while loop
<SERVER>
}
</SERVER>// End the table
</TABLE>
Last Updated: 10/31/97 16:36:13