Table of Contents | Previous | Next | Index


Cursor

Server-side object. A Cursor object represents a database cursor for a specified SQL SELECT statement.

Server-side object

Implemented in

NES 2.0

Created by

The cursor method of a Connection object or of the database object. You do not call a Cursor constructor.

Description

A database query is said to return a Cursor. You can think of a Cursor as a virtual table, with rows and columns specified by the query. A cursor also has a 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.

You can perform the following tasks with the Cursor object:

You can use a Cursor object to customize the display of the virtual table by specifying which columns and rows to display and how to display them. The Cursor object does not automatically display the data returned in the virtual table. To display this data, you must create HTML code such as that shown in Example 4 for the cursor method.

A pointer indicates the current row in a Cursor. When you create a Cursor, the pointer is initially positioned before the first row of the cursor. The next method makes the following row in the cursor the current row. If the SELECT statement used in the call to the cursor method does not return any rows, the method still creates a 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 condition.

Important

A database cursor does not guarantee the order or positioning of its rows. For example, if you have an updatable cursor and add a row to the cursor, you have no way of knowing where that row appears in the cursor. When finished with a Cursor object, use the close method to close it and release the memory it uses. If you release a connection that has an open cursor, the runtime engine waits until the cursor is closed before actually releasing the connection.

If you do not explicitly close a cursor with the close method, the JavaScript runtime engine on the server automatically tries to close all open cursors 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 Cursor class to add a property to all Cursor instances. If you do so, that addition applies to all Cursor instances 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.

NOTE: Every Sybase table you use with a cursor must have a unique index.
Properties. The properties of cursor objects vary from instance to instance. Each Cursor object has a property for each named column in the cursor. In other words, when you create a cursor, it acquires a property for each column in the virtual table, as determined by the SELECT statement.

NOTE: Unlike other properties in JavaScript, cursor properties corresponding to column names are not case sensitive, because SQL is not case sensitive and some databases are not case sensitive.
You can also refer to properties of a Cursor object as elements of an array. The 0-index array element corresponds to the first column, the 1-index array element corresponds to the second column, and so on.

SELECT statements can retrieve values that are not columns in the database, such as aggregate values and SQL expressions. You can display these values by using the cursor's property array index for the value.

Property Summary

Property Description
cursorColumn

An array of objects corresponding to the columns in a cursor.

prototype

Allows the addition of properties to the Cursor object.

Method Summary

Method Description
close

Closes the cursor and frees the allocated memory.

columnName

the name of the column in the cursor corresponding to the specified number.

columns

Returns the number of columns in the cursor.

deleteRow

Deletes the current row in the specified table.

insertRow

Inserts a new row in the specified table.

next

Moves the current row to the next row in the cursor.

updateRow

Updates records in the current row of the specified table in the cursor.

In addition, this object inherits the watch and unwatch methods from Object.


close

Closes the cursor and frees the allocated memory.

Method of

Cursor

Implemented in

NES 2.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.

Examples

The following example creates the 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()

columnName

Returns the name of the column in the cursor corresponding to the specified number.

Method of

Cursor

Implemented in

NES 2.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.

The result sets for Informix and DB2 stored procedures do not have named columns. Do not use this method when connecting to those databases. In those cases you should always refer to the result set columns by the index number.

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.

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 cursor.

Method of

Cursor

Implemented in

NES 2.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()

cursorColumn

An array of objects corresponding to the columns in a cursor.

Property of

Cursor

Implemented in

NES 2.0

Examples

Example 1: Using column titles as cursor properties. The following example creates the customerSet Cursor object containing the id, name, and city rows from the customer table. The next method moves the pointer to the first row of the cursor. The id, name, and city columns become the cursor properties customer.id, customerSet.name, and customerSet.city. Because the pointer is in the first row of the cursor, the write method displays the values of these properties for the first row.

// Create a Cursor object
customerSet = database.cursor("SELECT id, name, city FROM customer")
// Navigate to the first row
customerSet.next()
write(customerSet.id + "<BR>")
write(customerSet.name + "<BR>")
write(customerSet.city + "<BR>")
// Close the cursor
customerSet.close()
This query might return a virtual table containing the following rows:

1 John Smith Anytown 
2 Fred Flintstone Bedrock
3 George Jetson Spacely
Example 2: Iterating with the cursor properties. In this example, the cursor property array is used in a for statement to iterate over each column in the customerSet cursor.

// Create a Cursor object
customerSet = database.cursor("SELECT id, name, city FROM customer")
// Navigate to the first row
customerSet.next()
// Start a for loop
for ( var i = 0; i < customerSet.columns(); i++) {
write(customerSet[i] + "<BR>") }
// Close the cursor
customerSet.close()
Because the next statement moves the pointer to the first row, the preceding code displays values similar to the following:

1
John Smith
Anytown
Example 3. Using the cursor properties with an aggregate expression. In this example, the salarySet cursor contains a column created by the aggregate function MAX.

salarySet = database.cursor("SELECT name, MAX(salary) FROM employee")
Because the aggregate column does not have a name, you must use the refer to it by its index number, as follows:

write(salarySet[1])

deleteRow

Deletes the current row in the specified table.

Method of

Cursor

Implemented in

NES 2.0

Syntax

deleteRow (table)

Parameters

table

A string specifying the name of the table from which to delete a row.

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 deleteRow method uses an updatable cursor to delete the current row from the specified table. See Cursor for information about creating an updatable cursor.

Examples

In the following example, the deleteRow method removes a customer from the customer database. The cursor method creates the customerSet cursor containing a single row; the value for customer.ID is passed in as a request object property. The next method moves the pointer to the only row in the cursor, and the deleteRow method deletes the row.

database.beginTransaction()
   customerSet = database.cursor("select * from customer where
      customer.ID = " + request.ID, true)
   customerSet.next()
   statusCode = customerSet.deleteRow("customer")
   customerSet.close()
   if (statusCode == 0) {
      database.commitTransaction() }
   else {
      database.rollbackTransaction() }
In this example, the deleteRow method sets the value of statusCode to indicate whether deleteRow succeeds or fails. If statusCode is 0, the method has succeeded and the transaction is committed; otherwise, the transaction is rolled back.


insertRow

Inserts a new row in the specified table.

Method of

Cursor

Implemented in

NES 2.0

Syntax

insertRow (table)

Parameters

table

A string specifying the name of the table in which to insert a row.

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 insertRow method uses an updatable cursor to insert a row in the specified table. See the cursor method for information about creating an updatable cursor.

The location of the inserted row depends on the database vendor library. If you need to get at the row after calling the insertRow method, you must first close the existing cursor and then open a new cursor.

You can specify values for the row you are inserting as follows:

The insertRow method inserts a null value in any table columns that do not appear in the cursor.

The insertRow method returns a status code based on a database server message to indicate whether the method completed successfully. If successful, the method returns a 0; otherwise, it returns a nonzero integer to indicate the reason it failed. See the Server-Side JavaScript Guide for an explanation of status codes.

Examples

In some applications, such as a video-rental application, a husband, wife, and children could all share the same account number but be listed under different names. In this example, a user has just added a name to the accounts table and wants to add a spouse's name to the same account.

customerSet = database.cursor("select * from customer", true)
x=true
while (x) {
   x = customerSet.next() }
customerSet.name = request.theName
customerSet.insertRow("accounts")
customerSet.close()
In this example, the next method navigates to the last row in the table, which contains the most recently added account. The value of theName is passed in by the request object and assigned to the name column in the customerSet cursor. The insertRow method inserts a new row at the end of the table. The value of the name column in the new row is the value of theName. Because the application used the next method to navigate, the value of every other column in the new row is the same as the value in the previous row.


next

Moves the current row to the next row in the cursor.

Method of

Cursor

Implemented in

NES 2.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
while (x) {
   x = customerSet.next() }
Example 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 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>

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

Cursor

Implemented in

NES 2.0


updateRow

Updates records in the current row of the specified table in the cursor.

Method of

Cursor

Implemented in

NES 2.0

Syntax

updateRow (table)

Parameters

table

String specifying the name of the table to update.

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 updateRow method lets you use values in the current row of an updatable cursor to modify a table. See the cursor method for information about creating an updatable cursor. Before performing an updateRow, you must perform at least one next with the cursor so the current row is set to a row.

Assign values to columns in the current row of the cursor, and then use the updateRow method to update the current row of the table specified by the table parameter. Column values that are not explicitly assigned are not changed by the updateRow method.

The updateRow method returns a status code based on a database server message to indicate whether the method completed successfully. If successful, the method returns a 0; otherwise, it returns a nonzero integer to indicate the reason it failed. See the Server-Side JavaScript Guide for an explanation of the individual status codes.

Examples

This example uses updateRow to update the returndate column of the rentals table. The values of customerID and videoID are passed into the cursor method as properties of the request object. When the videoReturn Cursor object opens, the next method navigates to the only record returned and updates the value in the returnDate field.

// Create a cursor containing the rented video
videoReturn = database.cursor("SELECT * FROM rentals WHERE
   customerId = " + request.customerID + " AND
   videoId = " + request.videoID, true)
// Position the pointer on the first row of the cursor
videoReturn.next()
// Assign today's date to the returndate column
videoReturn.returndate = today
// Update the row
videoReturn.updateRow("rentals")

Table of Contents | Previous | Next | Index

Last Updated: 11/13/98 10:22:54

Copyright (c) 1998 Netscape Communications Corporation