SQLTable method of the database object or a Connection object. The SQLTable method takes an SQL SELECT statement and returns an HTML table. Each row and column in the query is a row and column of the table. The HTML table also has column headings for each column in the database table.
The SQLTable method does not give you control over formatting of the output. Furthermore, if that output contains a Blob object, that object does not display as an image. (For information on blobs, see "Working with Binary Data" on page 338.) If you want to customize the appearance of the output, use a database cursor to create your own display function. For more information, see "Manipulating Query Results with Cursors" on page 326.
As an example, if myconn is a Connection object, the following JavaScript statement displays the results of the database query in a table:
myconn.SQLTable("select * from videos");
The following is the first part of the table that could be generated by these statements:
| Title | ID | Year | Category | Quantity | On Hand |
Synopsis
|
|
| |
|---|
execute method of the database object or a Connection object enables an application to execute an arbitrary SQL statement. Using execute is referred to as performing passthrough SQL, because it passes SQL directly to the server.
You can use execute for any data definition language (DDL) or data manipulation language (DML) SQL statement supported by the database server. Examples include CREATE, ALTER, and DROP. While you can use it to execute any SQL statement, you cannot return data with the execute method.
Notice that execute is for performing standard SQL statements, not for performing extensions to SQL provided by a particular database vendor. For example, you cannot call the Oracle describe function or the Informix load function from the execute method.
To perform passthrough SQL statements, simply provide the SQL statement as the parameter to the execute method. For example, you might want to remove a table from the database that is referred to by the project object's oldtable property. To do so, you can use this method call:
connobj.execute("DROP TABLE " + project.oldtable);Important
When using execute, your SQL statement must strictly conform to the SQL
syntax requirements of the database server. For example, some servers require
each SQL statement to be terminated by a semicolon. For more information, see
your database server documentation.
If you have not explicitly started a transaction, the single statement is committed automatically. For more information on transaction control, see "Managing Transactions" on page 335.
To perform some actions, such as creating or deleting a table, you may need to have privileges granted by your database administrator. Refer to your database server documentation for more information, or ask your database administrator.
Cursor class, call the database object's or a Connection object's cursor method, passing an SQL SELECT statement as its parameter.
You can think of a cursor as a virtual table, with rows and columns specified by the query. A cursor also implies the 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.
When finished, close the database cursor by calling its close method. A database connection cannot be released until all associated cursors have been closed. For example, if you call a Connection object's release method and that connection has an associated cursor that has not been closed, the connection is not actually released until you close the cursor.
The following table summarizes the methods and properties of the Cursor class.
Table 16.1 Cursor properties and methods
| Method or Property |
Description
|
|
|
|
|
|
|
|
| |
|---|
Cursor class in the Server-Side JavaScript Reference.
cursor method of the associated database or Connection object. Creating the Cursor object also opens the cursor in the database. You do not need a separate open command. You can supply the following information when creating a Cursor object:
SELECT statement supported by the database server. To ensure database independence, use SQL 89/92-compliant syntax. The cursor is created as a virtual table of the results of this SQL statement.SELECT statement is "select count(*) from videos", you cannot create an updatable cursor.CUSTOMER table. The records contain the columns id, name, and city and are ordered by the value of the id column.
custs = connobj.cursor ("select id, name, city
from customer order by id");
This statement sets the variable custs to a Cursor object. The SQL query might return the following rows:
1 Sally Smith SuvaYou can then access this information using methods of the
2 Jane Doe Cupertino
3 John Brown Harper's Ferry
custs Cursor object. This object has id, name, and city properties, corresponding to the columns in the virtual table.
When you initially create a Cursor object, the pointer is positioned just before the first row in the virtual table. The following sections describe how you can get information from the virtual table.
You can also use the string concatenation operator (+) and string variables (such as client or request property values) when constructing a SELECT statement. For example, the following call uses a previously stored customer ID to further constrain the query:
custs = connobj.cursor ("select * from customer where id = "
+ client.customerID);
You can encounter various problems when you try to create a Cursor object. For example, if the SELECT statement in your call to the cursor method refers to a nonexistent table, the database returns an error and the cursor method returns null instead of a Cursor object. In this situation, you should use the majorErrorCode and majorErrorMessage methods to determine what error has occurred.
As a second example, suppose the SELECT statement refers to a table that exists but has no rows. In this case, the database may not return an error, and the cursor method returns a valid 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 possibility.
colName property for each named column in the virtual table (other than those corresponding to aggregate functions), as determined by the SELECT statement. You can access the values for the current row using these properties. In the example above, the cursor has properties for the columns id, name, and city. You could display the values of the first returned row using the following statements:
// Create the Cursor object.
custs = connobj.cursor ("select id, name, city
from customer order by id");
// Before continuing, make sure a real cursor was returned
// and there was no database error.
if ( custs && (connobj.majorErrorCode() == 0) ) {
// Get the first row
custs.next();
// Display the values
write ("<B>Customer Name:</B> " + custs.name + "<BR>");
write ("<B>City:</B> " + custs.city + "<BR>");
write ("<B>Customer ID:</B> " + custs.id);
//Close the cursorInitially, the current row is positioned before the first row in the table. The execution of the
custs.close();
}
next method moves the current row to the first row. For example, suppose this is the first row of the cursor:
1 Sally Smith SuvaIn this case, the preceding code displays the following: Customer Name: Sally Smith
Cursor object (or indeed any JavaScript object) as elements of an array. The zero-index array element corresponds to the first column, the one-index array element corresponds to the second column, and so on.
For example, you could use an index to display the same column values retrieved in the previous example:
write ("<B>Customer Name:</B> " + custs[1] + "<BR>");
write ("<B>City:</B> " + custs[2] + "<BR>");
write ("<B>Customer ID:</B> " + custs[0]);
This technique is particularly useful inside a loop. For example, you can create a Cursor object named custs and display its query results in an HTML table with the following code:
// Create the Cursor object.
custs = connobj.cursor ("select id, name, city
from customer order by id");
// Before continuing, make sure a real cursor was returned
// and there was no database error.
if ( custs && (connobj.majorErrorCode() == 0) ) {
write ("<TABLE BORDER=1>");
// Display column names as headers.
write("<TR>");
i = 0;
while ( i < custs.columns() ) {
write("<TH>", custs.columnName(i), "</TH>");
i++;
}
write("</TR>");
// Display each row in the virtual table.
while(custs.next()) {
write("<TR>");
i = 0;
while ( i < custs.columns() ) {
write("<TD>", custs[i], "</TD>");
i++;
}
write("</TR>");
}
write ("</TABLE>");
// Close the cursor.This code would display the following table:
custs.close();
}
| ID | NAME |
CITY
|
|
|
| |
|---|
SELECT statements can retrieve values that are not columns in the database, such as aggregate values and SQL expressions. For such values, the Cursor object does not have a named property. You can access these values only by using the Cursor object's property array index for the value.
The following example creates a cursor named empData, navigates to the row in that cursor, and then displays the value retrieved by the aggregate function MAX. It also checks to make sure the results from the database are valid before using them:
empData = connobj.cursor ("select min(salary), avg(salary),
max(salary) from employees");
if ( empData && (connobj.majorErrorCode() == 0) ) {
rowexists = empData.next();
if (rowexists) { write("Highest salary is ", empData[2]); }
}
This second example creates a cursor named empRows to count the number of rows in the table, navigates to the row in that cursor, and then displays the number of rows, once again checking validity of the data:
empRows = connobj.cursor ("select count(*) from employees");
if ( empRows && (connobj.majorErrorCode() == 0) ) {
rowexists = empRows.next();
if (rowexists) { write ("Number of rows in table: ", empRows[0]); }
}
next method to move the pointer through the records in the virtual table. This method moves the pointer to the next row and returns true as long it found another row in the virtual table. If there is not another row, next returns false.
For example, suppose a virtual table has columns named title, rentalDate, and dueDate. The following code uses next to iterate through the rows and display the column values in a table:
// Create the cursor.
custs = connobj.cursor ("select * from customer");
// Check for validity of the cursor and no database errors.
if ( custs && (connobj.majorErrorCode() == 0) ) {
write ("<TABLE>");// Iterate through rows, displaying values.
while (custs.next()) {
write ("<TR><TD>" + custs.title + "</TD>" +
"<TD>" + custs.rentalDate + "</TD>" +
"<TD>" + custs.dueDate + "</TD></TR>");
}
write ("</TABLE>");// Always close your cursors when finished!This code could produce output such as the following:
custs.close();
}
columns method of the Cursor class returns the number of columns in a cursor. This method takes no parameters:
custs.columns()You might use this method if you need to iterate over each column in a cursor. The
columnName method of the Cursor class returns the name of a column in the virtual table. This method takes an integer as a parameter, where the integer specifies the ordinal number of the column, starting with 0. The first column in the virtual table is 0, the second is 1, and so on.
For example, the following expression assigns the name of the first column in the custs cursor to the variable header:
header = custs.columnName(0)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.
true when creating the cursor, as in the following example:
custs = connobj.cursor ("select id, name, city from customer", true)
For a cursor to be updatable, the SELECT statement must be an updatable query (one that allows updating). For example, the statement cannot retrieve rows from more than one table or contain a GROUP BY clause, and generally it must retrieve key values from a table. For more information on constructing updatable queries, consult your database vendor's documentation.
When you use cursors to make changes to your database, you should always work inside an explicit transaction. You do so using the beginTransaction, commitTransaction, and rollbackTransaction methods, as described in "Managing Transactions" on page 335. If you do not use explicit transactions in these situations, you may get errors from your database.
For example, Informix and Oracle both return error messages if you use a cursor without an explicit transaction. Oracle returns Error ORA-01002: fetch out of sequence; Informix returns Error -206: There is no current row for UPDATE/DELETE cursor.
As mentioned in "Navigating with Cursors" on page 331, you cannot necessarily depend on your position in the cursor. For this reason, when making changes to the database, be sure to test that you're working on the correct row before changing it.
Also, remember that when you create a cursor, the pointer is positioned before any of the rows in the cursor. So, to update a row, you must call the next method at least once to establish the first row of the table as the current row. Once you have a row, you can assign values to columns in the cursor.
The following example uses an updatable cursor to compute the bonus for salespeople who met their quota. It then updates the database with this information:
connobj.beginTransaction ();
emps = connobj.cursor(
"select * from employees where dept='sales'", true);
// Before proceeding make sure the cursor was created and
// there was no database error.
if ( emps && (connobj.majorErrorCode() == 0) ) {
// Iterate over the rows of the cursor, updating information
// based on the return value of the metQuota function.
while ( emps.next() ) {
if (metQuota (request.quota, emps.sold)) {
emps.bonus = computeBonus (emps.sold);
}
else emps.bonus = 0;
emps.updateRow ("employees");
}
// When done, close the cursor and commit the transaction.This example creates an updatable cursor of all employees in the Sales department. It iterates over the rows of that cursor, using the user-defined JavaScript function
emps.close();
connobj.commitTransaction();
}
else {
// If there wasn't a cursor to work with, roll back the transaction.
connobj.rollbackTransaction();
}
metQuota to determine whether or not the employee met quota. This function uses the value of quota property of the request object (possibly set in a form on a client page) and the sold column of the cursor to make this determination. The code then sets the bonus appropriately and calls updateRow to modify the employees table. Once all rows in the cursor have been accessed, the code commits the transaction. If no cursor was returned by the call to the cursor method, the code rolls back the transaction.
In addition to the updateRow method, you can use the insertRow and deleteRow methods to insert a new row or delete the current row. You do not need to assign values when you use deleteRow, because it simply deletes an entire row.
When you use insertRow, the values you assign to columns are used for the new row. If you have previously called the cursor's next method, then the values of the current row are used for any columns without assigned values; otherwise, the unassigned columns are null. Also, if some columns in the table are not in the cursor, then insertRow inserts null in these columns. The location of the inserted row depends on the database vendor library. If you need to access the row after you call the insertRow method, you must first close the existing cursor and then open a new cursor.
NOTE: DB2 has aTimedata type. JavaScript does not have a corresponding data type. For this reason, you cannot update rows with values that use the DB2Timedata type
INSERT, UPDATE, and DELETE statements. Transactions can also be used to control the consistency of the data you refer to in your application.
For most databases, if you do not control transactions explicitly, the runtime engine uses the underlying database's autocommit feature to treat each database statement as a separate transaction. Each statement is either committed or rolled back immediately, based on the success or failure of the individual statement. Explicitly managing transactions overrides this default behavior.
In some databases, such as Oracle, autocommit is an explicit feature that LiveWire turns on for individual statements. In others, such as Informix, autocommit is the default behavior when you do not create a transaction. In general, LiveWire hides these differences and puts an application in autocommit mode whenever the application does not use beginTransaction to explicitly start a transaction.
For Informix ANSI databases, LiveWire does not use autocommit. For these databases, an application always uses transactions even if it never explicitly calls beginTransaction. The application must use commitTransaction or rollbackTransaction to finish the transaction.
NOTE: You are strongly encouraged to use explicit transaction control any time you make changes to a database. This ensures that the changes succeed or fail together. In addition, any time you use updatable cursors, you should use explicit transactions to control the consistency of your data between the time you read the data (withnext) and the time you change it (withinsertRow,updateRow, ordeleteRow). As described in "Changing Database Information" on page 333, using explicit transaction control with updatable cursors is necessary to avoid errors in some databases such as Oracle and Informix.
database object or a Connection object to explicitly manage transactions:
beginTransaction starts a new transaction. All actions that modify the database are grouped with this transaction, known as the current transaction.commitTransaction commits the current transaction. This method attempts to commit all the actions since the last call to beginTransaction.rollbackTransaction rolls back the current transaction. This method undoes all modifications since the last call to beginTransaction.NO LOG option does not
support transactions, and you will get an error if you use these methods.
beginTransaction multiple times before committing or rolling back the first transaction you opened, you'll get an error.
For the database object, the maximum scope of a transaction is limited to the current client request (HTML page) in the application. If the application exits the page before calling the commitTransaction or rollbackTransaction method, then the transaction is automatically either committed or rolled back, based on the setting of the commitflag parameter provided when you connected to the database.
For Connection objects, the scope of a transaction is limited to the lifetime of that object. If you release the connection or close the pool of connections before calling the commitTransaction or rollbackTransaction method, then the transaction is automatically either committed or rolled back, based on the setting of the commitflag parameter provided when you made the connection, either with the connect method or in the DbPool constructor.
If there is no current transaction (that is, if the application has not called beginTransaction), calls to commitTransaction and rollbackTransaction can result in an error from the database.
You can set your transaction to work at different levels of granularity. The example described in "Changing Database Information" on page 333 creates a single transaction for modifying all rows of the cursor. If your cursor has a small number of rows, this approach is sensible.
If, however, your cursor returns thousands of rows, you may want to process the cursor in multiple transactions. This approach can both cut down the transaction size and improve the concurrency of access to that information.
If you do break down your processing into multiple transactions, be certain that a call to next and an associated call to updateRow or deleteRow happen within the same transaction. If you get a row in one transaction, finish that transaction, and then attempt to either update or delete the row, you may get an error from your database.
How you choose to handle transactions depends on the goals of your application. You should refer to your database vendor documentation for more information on how to use transactions appropriately for that database type.
Blob class methods.imageFileName containing the name of the desired image file. You could then use this HTML expression to display the image for each row:
<IMG SRC=`mycursor.imageFileName`>As the cursor navigates through the table, the name of the file in the
IMG tag changes to refer to the appropriate file.
If you need to manipulate actual binary data in your database, the JavaScript runtime engine recognizes when the value in a column is BLOb data. That is, when the software creates a Cursor object, if one of the database columns contains BLOb data, the software creates a Blob object for the corresponding value in the Cursor object. You can then use the Blob object's methods to display that data. Also, if you want to insert BLOb data into a database, the software provides a global function for you to use. The following table outlines the methods and functions for working with BLOb data.
Table 16.2 Methods and functions for working with Blobs
| Method or Function |
Description
|
|
|
| |
|---|
blobImage method fetches a BLOb from the database, creates a temporary file of the specified format, and generates an HTML IMG tag that refers to the temporary file. The runtime engine removes the temporary file after the page is generated and sent to the client.
The blobLink method fetches BLOb data from the database, creates a temporary file, and generates an HTML hypertext link to the temporary file. The runtime engine removes the temporary file after the user clicks the link or 60 seconds after the request has been processed.
The following example illustrates using blobImage and blobLink to create temporary files. In this case, the FISHTBL table has four columns: an ID, a name, and two images. One of these is a small thumbnail image; the other is a larger image. The example code writes HTML for displaying the name, the thumbnail, and a link to the larger image.
cursor = connobj.cursor ("select * from fishtbl");if ( cursor && (connobj.majorErrorCode() == 0) ) {
while (cursor.next()) {
write (cursor.name);
write (cursor.picture.blobImage("gif"));
write (cursor.picture.blobLink("image\gif", "Link" + cursor.id));
write ("<BR>");
}
cursor.close();
}
If FISHTBL contains rows for four fish, the example could produce the following HTML:
Cod <IMG SRC="LIVEWIRE_TEMP9">If you want to add BLOb data to a database, use the
<A HREF="LIVEWIRE_TEMP10">Link1 </A> <BR>
Anthia <IMG SRC="LIVEWIRE_TEMP11">
<A HREF="LIVEWIRE_TEMP12">Link2 </A> <BR>
Scorpion <IMG SRC="LIVEWIRE_TEMP13">
<A HREF="LIVEWIRE_TEMP14">Link3 </A> <BR>
Surgeon <IMG SRC="LIVEWIRE_TEMP15">
<A HREF="LIVEWIRE_TEMP16">Link4 </A> <BR>
blob global function. This function assigns BLOb data to a column in an updatable cursor. As opposed to blobImage and blobLink, blob is a top-level function, not a method.
The following statements assign BLOb data to one of the columns in a row and then update that row in the FISHTBL table of the database. The cursor contains a single row.
// Begin a transaction.
database.beginTransaction();
// Create a cursor.
fishCursor = database.cursor ("select * from fishtbl where
name='Harlequin Ghost Pipefish'", true);
// Make sure cursor was created.
if ( fishCursor && (database.majorErrorCode() == 0) ) {
// Position the pointer on the row.
rowexists = fishCursor.next();
if ( rowexists ) {// Assign the blob data.
fishCursor.picture = blob ("c:\\data\\fish\\photo\\pipe.gif");
// Update the row.
fishCursor.updateRow ("fishtbl");
// Close the cursor and commit the changes.Remember that the backslash (\) is the escape character in JavaScript. For this reason, you must use two backslashes in NT filenames, as shown in the example.
fishCursor.close();
database.commitTransaction();
}
else {
// Close the cursor and roll back the transaction.
fishCursor.close();
database.rollbackTransaction();
}
}
else {
// Never got a cursor; rollback the transaction.
database.rollbackTransaction();
}
Stproc and Resultset. With the methods of these classes you can call a stored procedure and manipulate the results of that procedure.
SELECT statements, retrieving information from the database. You can think of this information as a virtual table, very similar to a read-only cursor. (For information on cursors, see "Manipulating Query Results with Cursors" on page 326.)
LiveWire uses an instance of the Resultset class to contain the rows returned by a single SELECT statement of a stored procedure. If the stored procedure allows multiple SELECT statements, you get a separate Resultset object for each SELECT statement. You use the resultSet method of the Stproc class to obtain a result set object and then you use that object's methods to manipulate the result set.
Different database vendors return a result set in these varying ways:
SELECT statements.RESUME feature, the stored procedure can have a set of these multiple return values. This set is like the rows of a table. LiveWire creates a single result set to contain this virtual table.SELECT statement. You can open multiple ref cursors in an Oracle stored procedure to contain rows returned by several SELECT statements. LiveWire creates a separate Resultset object for each ref cursor.outParamCount and outParameters methods of the Stproc class to access output and input/output parameters. However, Informix does not allow output or input/output parameters. Therefore, you should not use the outParamCount and outParameters methods with Informix stored procedures.
returnValue method of the Stproc class to access the return value. However, the return values for Informix stored procedures are used to generate its result set. For this reason, returnValue always returns null for Informix stored procedures. In addition, return values are not available for ODBC and DB2 stored procedures.
resultSet object and get the data from that object.DB2CLI.PROCEDURES system table and enter your DB2 stored procedures in it. DB2CLI.PROCEDURES is a pseudo-catalog table.
If your DB2 is for IBM MVS/EA version 4.1 or later, you must define the name of your stored procedures in the SYSIBM.SYSPROCEDURES catalog table.
Remember you use C, C++, or another source language to write a DB2 stored procedure. The data types you use with those languages do not match the data types available in DB2. Therefore, when you add the stored procedure to DB2CLI.PROCEDURES or SYSIBM.SYSPROCEDURES, be sure to record the corresponding DB2 data type for the stored procedure parameters and not the data types of the source language.
For information on DB2 data types and on how to make entries in these tables, see your DB2 documentation.
storedProcArgs method of the database or DbPool object.
You need exactly one prototype for each stored procedure in your application. The software ignores additional prototypes for the same stored procedure.
In the prototype, you provide the name of the stored procedure and the type of each of its parameters. A parameter must be for input (IN), output (OUT), or input and output (INOUT). For example, to create a prototype for a stored procedure called newhire that has two input parameters and one output parameter, you could use this method call:
poolobj.storedProcArgs("newhire", "IN", "IN", "OUT");
Stproc object using the database or Connection object's storedProc method. Creating the object automatically invokes the stored procedure. When creating a stored-procedure object, you specify the name of the procedure and any parameters to the procedure.
For example, assume you have a stored procedure called newhire that takes one string and one integer parameter. The following method call creates the spObj stored-procedure object and invokes the newhire stored procedure:
spObj = connobj.storedProc("newhire", "Fred Jones", 1996);
In general, you must provide values for all input and input/output parameters to the stored procedure. If a stored procedure has a default value defined for one of its parameters, you can use the "/Default/" directive to specify that default value. Similarly, if a stored procedure can take a null value for one of its parameters, you can specify the null value either with the "/Null/" directive or by passing in the null value itself.
For example, assume the demosp stored procedure takes two string parameters and one integer parameter. You could supply all the parameters as follows:
spobj = connobj.storedProc("demosp", "Param_1", "Param_2", 1);
Alternatively, to pass null for the second parameter and to use the default value for third parameter, you could use either of these statements:
spobj = connobj.storedProc("demosp", "Param_1", "/Null/", "/Default/");
spobj = connobj.storedProc("demosp", "Param_1", null, "/Default/");NOTE:
On Informix, default values must occur only after all specified values. For
example, you cannot use /Default/ for the second parameter of a stored
procedure and then specify a value for the third parameter.
You can also use the "/Default/" and "/Null/" directives for input/output parameters.
An Oracle stored procedure can take ref cursors as input/output or output parameters. For example, assume you have an Oracle stored procedure named proc1 that takes four parameters: a ref cursor, an integer value, another ref cursor, and another integer value. The call to that stored procedure from SQL Plus might look as follows:
execute proc1When you call this stored procedure from within a JavaScript application, however, you do not supply the ref cursor parameters. Instead, the equivalent call would be:(refcursor1, 3,refcursor2, 5);
spobj = connobj.storedProc("proc1", 3, 5);
For information on output parameters, see "Working with Output Parameters" on page 353. Output parameters cannot be null; however, you can assign a null value to input or input/output parameters.
The following table summarizes the methods of a stored-procedure object.
| Method |
Description
|
| Retrieves the return value of the stored procedure. For Informix, DB2, and ODBC, this method always returns null.
|
| |
|---|
CUSTINFO table with the columns id, city, and name. In Sybase, you could use this stored procedure to get the first 200 rows of the table:
create proc getcusts asIf
begin
select id, name, city from custinfo where custno < 200
end
CUSTINFO were an Informix table, the equivalent Informix stored procedure would be this:
create procedure getcusts returning int, char(15), char(15);
define rcity, rname char (15);
define i int;
foreach
select id, name, city into i, rname, rcity
from custinfo
where id < 200;
return i, rname, rcity with resume;If
end foreach;
end procedure;
CUSTINFO were an Oracle table, the equivalent Oracle stored procedure would be:
create or replace package orapack as
type custcurtype is ref cursor return custinfo%rowtype
end orapack;
create or replace custresultset (custcursor inout orapack.custcurtype)In all cases, you create a
as begin
open custcursor for select id, name, city from custinfo
where id < 200
end custresultset;
resultSet object to retrieve the information from the stored procedure. You do so by using the stored-procedure object's resultSet method, as follows:
resObj = spObj.resultSet();As for
Cursor objects, resultSet objects have a current row, which is simply the row being pointed to in the result set. Initially, the pointer is positioned before the first row of the result set. To see the values in the rows of the result set, you use the next method to move the pointer through the rows in the result set, as shown in the following example:
spobj = connobj.storedProc("getcusts");if ( spobj && (connobj.majorErrorCode() == 0) ) {// Creates a newresultSetobject.
resobj = spobj.resultSet();
// Make sure you got a result set before continuing.
if ( resobj && (connobj.majorErrorCode() == 0) ) {
// Initially moves theAs long as there is another row in the result set, theresultSetobject pointer to the first
// result set row and then loops through the rows.
while (resObj.next())
{
write("<TR><TD>" + resObj.name + "</TD>");
write("<TD>" + resObj.city + "</TD>");
write("<TD>" + resObj.id + "</TD></TR>");
}
resobj.close();
}
}
next method returns true and moves the pointer to the next row. When the pointer reaches the last row in the result set, the next method returns false.
The preceding example works for a Sybase stored procedure. In that case, the resultSet object contains a named property for each column in the result set. For Informix and DB2 stored procedures, by contrast, the object does not contain named columns. In this case, you can get the values by referencing the column position. So, for Informix and DB2, you would use this code to display the same information:
spobj = connobj.storedProc("getcusts");if ( spobj && (connobj.majorErrorCode() == 0) ) {// Creates a new resultSet object.
resobj = spobj.resultSet();
// Make sure you got a result set before continuing.
if ( resobj && (connobj.majorErrorCode() == 0) ) {
// Initially moves the resultSet object pointer to the firstYou can use the column position for result sets with any database, not just with Informix and DB2. You can use the column name for stored procedures for all database types other than Informix or DB2.
// result set row and then loops through the rows.
while (resObj.next())
{
write("<TR><TD>" + resObj[1] + "</TD>");
write("<TD>" + resObj[2] + "</TD>");
write("<TD>" + resObj[0] + "</TD></TR>");
}
resobj.close();
}
}
resultSet object for each. Suppose your stored procedure executes these SQL statements:
select name from customers where id = 6767You could use the multiple
select * from orders where id = 6767
resultSet objects generated by these statements as follows:
// This statement is needed for DB2, ODBC, and Sybase.
poolobj.storedProcArgs("GetCustOrderInfo","IN");
spobj = connobj.storedProc("GetCustOrderInfo",6767);if ( spobj && (connobj.majorErrorCode() == 0) ) {resobj1 = spobj.resultSet();
// Make sure result set exists before continuing.
if ( resobj1 && (connobj.majorErrorCode() == 0) ) {
// This first result set returns only one row.
// Make sure that row contains data.
rowexists = resobj1.next();
if ( rowexists )
write("<P>Customer " + resobj1.name +
" has the following orders:</P>");
resobj1.close();
// The second result set returns one row for each order placed
// by the customer. Make sure the rows have data.
resobj2 = spobj.resultSet();
var i = 0;
if ( resobj2 && (connobj.majorErrorCode() == 0) ) {
write("\nOrder# Quantity Total</P>");
while(resobj2.next()) {
write(resobj2.orderno + " " + resobj2.quantity
+ " " + resobj2.Totalamount + "</P>");
i++;
}
resobj2.close();
write("Customer has " + i + " orders.</P>");
}
else write("Customer has no orders.</P>");
}
}spobj.close();For an example of using multiple Oracle ref cursors in a stored procedure, see the description of the
Resultset class in the Server-Side JavaScript Reference.
Resultset class.
Table 16.4 Resultset methods and properties
| Method or Property |
Description
|
| Returns the number of columns in the result set. For Informix, this method returns the number of return values for a single row.
|
|
| |
|---|
resultSet object is a read-only, sequential-style object. For this reason, the class does not have the insertRow, deleteRow, and updateRow methods defined for Cursor objects.
resultSet object is 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];
resultSet 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. See "Working with Return Values" on page 353, for more information about these methods.spobj = database.storedProc("getcusts");
resobj = spobj.resultSet();
retval = spobj.returnValue();
// Illegal! Result set no longer valid!
col1 = resobj[0];resultSet objects before you call the cursor or SQLTable method of the associated connection. Once you call cursor or SQLTable, the result set is no longer available. For example, the following code is illegal:spobj = database.storedProc("getcusts");
resobj = spobj.resultSet();
curobj = database.cursor ("select * from orders");
// Illegal! The result set is no longer available!
col1 = resobj[0];resultSet objects before you call the associated connection's cursor or SQLTable method. For ODBC, if you get a cursor, then access the result set, and then use the cursor, the Cursor object is no longer available. For example, the following code is illegal:spbobj = database.storedProc("getcusts");
resobj = spobj.resulSet();
curobj = database.cursor ("select * from orders");
col1 = resobj[0];
// Illegal! The cursor is no longer available.
curobj.next();returnValue method always returns null.
If your stored procedure has a return value, you can access that value with the returnValue method.
On DB2, ODBC, and Sybase, you must use stored procedures and cursors sequentially. You cannot intermix them. For this reason, you must let the system know that you have finished using the stored procedure before you can work with a cursor. You do this by calling the returnValue method of the stored procedure object. This method provides the stored procedure's return value (if it has one) and completes the execution of the stored procedure. You should also close all objects related to stored procedures when you have finished using them.
NOTE: For DB2, ODBC, and Sybase, you must retrieveresultSetobjects before you call thereturnValuemethod. Once you callreturnValue, you can't get any more data from a result set, and you can't get any additional result sets. You should callreturnValueafter you have processed the result set and before you retrieve the output parameters.
outParamCount method. You can work with the output parameters of a stored procedure by using the object's outParameters method. If outParamCount returns 0, the stored procedure has no output parameters. In this situation, do not call outParameters.
For example, suppose you created a stored procedure that finds the name of an employee when given an ID. If there is an employee name associated with the given ID, the stored procedure returns 1, and its output parameter contains the employee name. Otherwise, the output parameter is empty. The following code either displays the employee name or a message indicating the name wasn't found:
id = 100;Assume a stored procedure has one input parameter, one input/output parameter, and one output parameter. Further, assume the call to the stored procedure sends a value for the input parameter and the input/output parameter as shown here:
getNameProc = connobj.storedProc("getName", id);
returnValue = getNameProc.returnValue();
if (returnValue == 1)
write ("Name of employee is " + getNameProc.outParameters(0));
else
write ("No employee with id = " + id);
spobj = connobj.storedProc("myinout", 34, 56);
The outParameters method returns any input/output parameters before it returns the first output parameter.
In the preceding example, if you call outParameters(1), it returns the value returned from the stored procedure. By contrast, if you call outParameters(0), the method returns 56. This is the value passed to the stored procedure in the input/output parameter position.
NOTE: Output parameters cannot be null; however, you can assign a null value to input or input/output parameters.For DB2, ODBC, and Sybase, you must retrieve
resultSet objects and use the returnValue method before you call outParameters. Once you call returnValue or outParameters, you can't get any more data from a result set, and you can't get any additional result sets. You should call outParameters after you have processed the result set and any return values.
majorErrorCode and majorErrorMessage methods of the associated database or Connection object.
For example, assume you have the following Informix stored procedure:
create procedure usercheck (user varchar(20))When you run this stored procedure, you could check whether an error occurred and then access the error code and message as follows:
if user = 'LiveWire' then
raise exception -746, 0, 'User not Allowed';
endif
end procedure
spobj = connobj.storedProc("usercheck");if ( connobj.majorErrorCode() ) {
write("The procedure returned this error code: " +
connobj.majorErrorCode());
write("The procedure returned this error message: " +
connobj.majorErrorMessage());
}
Last Updated: 11/12/98 15:29:40