Cursor object represents a database cursor for a specified SQL SELECT statement.
| Server-side object | |
| Implemented in | LiveWire 1.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:
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.
| An array of objects corresponding to the columns in a cursor. |
|
Allows the addition of properties to a Cursor object.
|
Properties
This section describes the properties of cursor objects.
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 of |
Cursor
|
| Implemented in | LiveWire 1.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
This query might return a virtual table containing the following rows:
customerSet.close()1 John Smith Anytown
Example 2: Iterating with the cursor properties. In this example, the
2 Fred Flintstone Bedrock
3 George Jetson Spacelycursor 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
Because the
customerSet.close()next statement moves the pointer to the first row, the preceding code displays values similar to the following:
1
Example 3. Using the cursor properties with an aggregate expression. In this example, the
John Smith
AnytownsalarySet 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])
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 | LiveWire 1.0 |
Methods
close
Closes the cursor and frees the allocated memory.
| Method of |
Cursor
|
| Implemented in | LiveWire 1.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 | LiveWire 1.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 | LiveWire 1.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()
deleteRow
| Method of |
Cursor
|
| Implemented in | LiveWire 1.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()
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
Inserts a new row in the specified table.
| Method of |
Cursor
|
| Implemented in | LiveWire 1.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:
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 Writing Server-Side JavaScript Applications
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
In 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
Moves the current row to the next row in the cursor.
| Method of |
Cursor
|
| Implemented in | LiveWire 1.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>updateRow
Updates records in the current row of the specified table in the cursor.
| Method of |
Cursor
|
| Implemented in | LiveWire 1.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 Writing Server-Side JavaScript Applications 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")
Last Updated: 10/31/97 16:36:13