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.") 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."
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."
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 9.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, cityThis statement sets the variable
from customer order by id");
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 = "You can encounter various problems when you try to create a
+ client.customerID);
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>");This technique is particularly useful inside a loop. For example, you can create a
write ("<B>City:</B> " + custs[2] + "<BR>");
write ("<B>Customer ID:</B> " + custs[0]);
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),This second example creates a cursor named
max(salary) from employees");
if ( empData && (connobj.majorErrorCode() == 0) ) {
rowexists = empData.next();
if (rowexists) { write("Highest salary is ", empData[2]); }
}
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." 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," 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 aTime
data type. JavaScript does not have a corresponding data type. For this reason, you cannot update rows with values that use the DB2Time
data 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 225, 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" 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 9.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) ) {If
while (cursor.next()) {
write (cursor.name);
write (cursor.picture.blobImage("gif"));
write (cursor.picture.blobLink("image\gif", "Link" + cursor.id));
write ("<BR>");
}
cursor.close();
}
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.")
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." 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 newresultSet
object.
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, theresultSet
object 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 9.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," 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 retrieveresultSet
objects before you call thereturnValue
method. 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 callreturnValue
after 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: 09/29/99 18:01:57