Cursor object represents a database cursor for a specified SQL SELECT statement. cursor method of a Connection object or of the database object. You do not call a Cursor constructor.
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:
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.
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 |
Description
An array of objects corresponding to the columns in a cursor.
| |
|---|
watch and unwatch methods from Object.
close()
majorErrorCode and majorErrorMessage methods to interpret the cause of the error.
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()
columnName (n)
n |
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.
customerSet cursor to the variable header:
customerSet=database.cursor(SELECT * FROM customer ORDER BY name)
header = customerSet.columnName(0)
columns()
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()
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 cursorThis query might return a virtual table containing the following rows:
customerSet.close()
1 John Smith AnytownExample 2: Iterating with the cursor properties. In this example, the
2 Fred Flintstone Bedrock
3 George Jetson Spacely
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 cursorBecause the
customerSet.close()
next statement moves the pointer to the first row, the preceding code displays values similar to the following:
1Example 3. Using the cursor properties with an aggregate expression. In this example, the
John Smith
Anytown
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 (table)
table | A string specifying the name of the table from which to delete a row. |
majorErrorCode and majorErrorMessage methods to interpret the cause of the error.
deleteRow method uses an updatable cursor to delete the current row from the specified table. See Cursor for information about creating an updatable cursor.
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()In this example, the
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() }
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 (table)
table | A string specifying the name of the table in which to insert a row. |
majorErrorCode and majorErrorMessage methods to interpret the cause of the error.
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:
insertRow method.next method, explicitly assigning values for some columns, and then calling the insertRow method. Columns that are not explicitly assigned values receive values from the row to which you navigated.insertRow method. If you do not issue a next method, columns that are not explicitly assigned values are null.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.
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.theNameIn this example, the
customerSet.insertRow("accounts")
customerSet.close()
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()
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.
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 = trueExample 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>
Function.prototype.updateRow (table)
table |
majorErrorCode and majorErrorMessage methods to interpret the cause of the error.
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.
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")
Last Updated: 11/13/98 10:22:54