Resultset

创建源

描述

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 表. 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:

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 rollback 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: database.beginTransaction();
   spobj = database.storedProc("getcusts");
   resobj = spobj.resultSet();
   database.commitTransaction();
   /* Illegal! Result set no longer valid! */
   col1 = resobj[0];

2. You must retrieve result set 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. spobj = database.storedProc("getcusts");
   resobj = spobj.resultSet();
   retval = spobj.returnValue();
   /* Illegal! Result set no longer valid! */
   col1 = resobj[0];

3. Similarly, you must retrieve result set objects before you call the associated 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];

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 unpredic表 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.

属性概览

方法概览

示例

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)
as begin
open timrows for select * from rentals;
open timrows1 for select * from customer;
end timset4; Running this stored procedure creates two result sets you can access. In the following code fragment the 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();

属性

方法

语法

返回

描述

示例

// Create a Cursor object
rentalSet = database.cursor("SELECT * FROM rentals") // Perform operations on the cursor
cursorOperations() //Close the cursor
err = rentalSet.close()

参看

columnName

语法

返回

If your SELECT statement uses a wildcard (*) to select all the columns in a 表, 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 表 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.

示例

customerSet=database.cursor(SELECT * FROM customer ORDER BY name)
header = customerSet.columnName(0)

columns

语法

返回

示例

The following example returns the number of columns in the custs cursor:

custs.columns()

next

语法

返回

描述

示例

customerSet = database.cursor("select * from customer", true) x = true
while (x) {
   x = customerSet.next() } 示例 2. In the following example, the 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 表:

<SERVER>
// Create a Cursor object
rentalSet = database.cursor("SELECT videoId, rentalDate, returnDate
   FROM rentals")
</SERVER> // Create an HTML 表
<表 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 表
   <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 表
</表>