| Previous Contents Index Next |
| iPlanet Web Server, Enterprise Edition Server-Side JavaScript Guide |
Chapter 9 Working with a Database
This chapter discusses working with DB2, Informix, ODBC, Oracle, or Sybase relational databases. It describes how to retrieve information from the database and use it in your application, how to work with database transactions, and how to execute database stored procedures.Remember that if your application runs on Netscape FastTrack Server instead of iPlanet Web Server, it can access only databases on servers using the ODBC standard.
This chapter contains the following sections:
Interacting with a Relational Database
Automatically Displaying Query Results
Executing Arbitrary SQL Statements
Interacting with a Relational Database
The LiveWire Database Service allows you to interact with a relational database in many ways. You can do all of the following:
Perform database queries and have the runtime engine automatically format the results for you.
For information on how to set up and manage your database connections, see Chapter 8 "Connecting to a Database."Use cursors to perform database queries and present the results in an application-specific way or use the results in performing calculations.
Use cursors to change information in your database.
Use transactions to manage your database interactions.
Automatically Displaying Query Results
The simplest and quickest way to display the results of database queries is to use the SQLTable method of 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:
Executing Arbitrary SQL Statements
The execute method of 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);
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.
Manipulating Query Results with Cursors
In many situations, you do not simply want to display a table of query results. You may want to change the formatting of the result or even do arbitrary processing, rather than displaying it at all. To manipulate query results, you work with a database cursor returned by a database query. To create an instance of the Cursor class, call 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.
The following table summarizes the methods and properties of the Cursor class.
For complete information on these methods, see the description of the Cursor class in the Server-Side JavaScript Reference.
Creating a Cursor
Once an application is connected to a database, you can create a cursor by calling the 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:
An SQL 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.
For example, the following statement creates a cursor for records from the CUSTOMER table. The records contain the columns id, name, and city and are ordered by the value of the id column.An optional Boolean parameter indicating whether you want an updatable cursor. Use this parameter only if you want to change the content of the database, as described in "Changing Database Information." It is not always possible to create an updatable cursor for every SQL statement; this is controlled by the database. For example, if the SELECT statement is select count(*) from videos, you cannot create an updatable cursor.
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 Suva
2 Jane Doe Cupertino
3 John Brown Harper's FerryYou can then access this information using methods of the 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.
Displaying Record Values
When you create a cursor, it acquires a 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:
Initially, the current row is positioned before the first row in the table. The execution of the next method moves the current row to the first row. For example, suppose this is the first row of the cursor:
In this case, the preceding code displays the following:
Customer Name: Sally Smith
City: Suva
Customer ID: 1You can also refer to properties of a 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:
This code would display the following table:
This example uses methods discussed in the following sections.
Displaying Expressions and Aggregate Functions
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:
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:
Navigating with Cursors
Initially, the pointer for a cursor is positioned before the first row in the virtual table. Use the 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:
This code could produce output such as the following:
You cannot necessarily depend on your place in the cursor. For example, suppose you create a cursor and, while you're working with it, someone else adds a row to the table. Depending on the settings of the database, that row may appear in your cursor. For this reason, when appropriate (such as when updating rows) you may want your code to have tests to ensure it's working on the appropriate row.
Working with Columns
The columns method of the Cursor class returns the number of columns in a cursor. This method takes no parameters: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:
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.
Changing Database Information
You can use an updatable cursor to modify a table based on the cursor's current row. To request an updatable cursor, add an additional parameter of 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:
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 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 a Time data type. JavaScript does not have a corresponding data type. For this reason, you cannot update rows with values that use the DB2 Time data type
Managing Transactions
A transaction is a group of database actions that are performed together. Either all the actions succeed together or all fail together. When you apply all actions, making permanent changes to the database, you are said to commit a transaction. You can also roll back a transaction that you have not committed; this cancels all the actions.Transactions are important for maintaining data integrity and consistency. Although the various database servers implement transactions slightly differently, the LiveWire Database Service provides the same methods for transaction management with all databases. Refer to the database vendor documentation for information on data consistency and isolation levels in transactions.
You can use explicit transaction control for any set of actions. For example, actions that modify a database should come under transaction control. These actions correspond to SQL 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 (with next) and the time you change it (with insertRow, updateRow, or deleteRow). As described in "Changing Database Information," using explicit transaction control with updatable cursors is necessary to avoid errors in some databases such as Oracle and Informix.
Using the Transaction-Control Methods
Use the following methods of 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.
Of course, if your database does not support transactions, you cannot use them. For example, an Informix database created using the NO LOG option does not support transactions, and you will get an error if you use these methods.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.
The LiveWire Database Service does not support nested transactions. If you call beginTransaction multiple times before committing or rolling back the first transaction you opened, you'll get an error.
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.
Working with Binary Data
Binary data for multimedia content such as an image or sound is stored in a database as a binary large object (BLOb). You can use one of two techniques to handle binary data in JavaScript applications:
Store filenames in the database and keep the data in separate files.
If you do not need to keep BLOb data in a database, you can store the filenames in the database and access them in your application with standard HTML tags. For example, if you want to display an image for each row in a database table, you could have a column in the table called imageFileName containing the name of the desired image file. You could then use this HTML expression to display the image for each row:Store the data in the database as BLObs and access it with Blob class methods.
<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.
The 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.
If FISHTBL contains rows for four fish, the example could produce the following HTML:
If you want to add BLOb data to a database, use the 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.
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.
Calling Stored Procedures
Stored procedures are an integral part of operating and maintaining a relational database. They offer convenience by giving you a way to automate processes that you do often, but they offer other benefits as well:
Limited access. You can limit access to a sensitive database by giving users access only through a stored procedure. A user has access to the data, but only within the stored procedure. Any other access is denied.
The LiveWire Database Service provides two classes for working with stored procedures, Stproc and Resultset. With the methods of these classes you can call a stored procedure and manipulate the results of that procedure.Data integrity. Stored procedures help you make sure that information is provided and entered in a consistent way. By automating complicated transactions, you can reduce the possibility of user error.
Efficiency. A stored procedure is compiled once, when executed for the first time. Later executions run faster because they skip the compilation step. This also helps lighten the load on your network, because the stored procedure code is downloaded only once.
Exchanging Information
Stored procedures work differently for the various databases supported by the LiveWire Database Service. The most important distinction for LiveWire is how you pass information to and from the stored procedure in a JavaScript application. You always use input parameters to the stored procedure to pass information into a stored procedure.However, conceptually there are several distinct ways you might want to retrieve information from a stored procedure. Not every database vendor lets you retrieve information in all of these ways.
Result Sets
A stored procedure can execute one or more 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:
Sybase stored procedures can directly return the result of executing one or more SELECT statements.
Informix stored procedures can have multiple return values. Multiple return values are like the columns in a single row of a table, except that these columns are not named. In addition, if you use the 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.
Oracle stored procedures use ref cursors to contain the rows returned by a 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.
DB2 stored procedures use open cursors to return result sets.
Output and Input/Output Parameters
In addition to standard input parameters, some database vendors allow other types of parameters for their stored procedures. Output parameters store information on return from the procedure and input/output parameters both pass in information and return information.For most databases, you use the 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.
Return Values
Seen as a simple function call, a stored procedure can have a return value. For Oracle and Sybase, this return value is in addition to any result sets it returns.You use the 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.
Steps for Using Stored Procedures
Once you have a database connection, the steps for using a stored procedure in your application vary slightly for the different databases:
(DB2 only) Register the stored procedure in the appropriate system tables. (You do this outside of JavaScript.)
Notice that for several databases you can complete execution of your stored procedure either by getting the return value or by accessing the output parameters. Once you have done either of these things, you can no longer work with any result sets created by execution of the stored procedure.(DB2, ODBC, and Sybase) Define a prototype for your stored procedure.
(All databases) Execute the stored procedure.
(All databases) Create a resultSet object and get the data from that object.
(DB2, ODBC, and Sybase) Complete the execution by accessing the return value.
(DB2, ODBC, Oracle, and Sybase) Complete the execution by getting the output parameters.
(All databases) When finished, close the stored procedure by calling its close method.
The following sections describe each of these steps in more detail.
Registering the Stored Procedure
This step applies only to DB2.DB2 has various system tables in which you can record your stored procedure. In general, entering a stored procedure in these tables is optional. However, to use your stored procedure with LiveWire, you must make entries in these tables. You perform this step outside of the JavaScript application.
For DB2 common server, you must create the 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.
Defining a Prototype for a Stored Procedure
This step is relevant only for DB2, ODBC, and Sybase stored procedures, both user-defined and system stored procedures. You do not need to define a prototype for stored procedures for Oracle or Informix databases.For DB2, ODBC, and Sybase, the software cannot determine at runtime whether a particular parameter is for input, for output, or for both. Consequently, after you connect to the database, you must create a prototype providing information about the stored procedure you want to use, using the 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");
Executing the Stored Procedure
This step is relevant to all stored procedures.To execute a stored procedure, you create a 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/");
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 proc1 (refcursor1, 3, refcursor2, 5);
When 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:
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.
Working with Result Sets
This step is relevant for all stored procedures.As described in "Result Sets," different databases returns result sets in different ways. For example, assume you have the 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 as
begin
select id, name, city from custinfo where id < 200
endIf CUSTINFO were an Informix table, the equivalent Informix stored procedure would be this:
If CUSTINFO were an Oracle table, the equivalent Oracle stored procedure would be:
In all cases, you create a resultSet object to retrieve the information from the stored procedure. You do so by using the stored-procedure object's resultSet method, as follows:
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:
As long as there is another row in the result set, the 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:
You 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.
Multiple Result Sets
A Sybase, Oracle, DB2, or ODBC stored procedure can create multiple result sets. If it does, the stored procedure provides one resultSet object for each. Suppose your stored procedure executes these SQL statements:select name from customers where id = 6767
select * from orders where id = 6767You could use the multiple resultSet objects generated by these statements as follows:
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.
Result Set Methods and Properties
The following table summarizes the methods and properties of the Resultset class.
A 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.
When You Can Use Result Sets
A 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.
If you create a result set as part of a transaction, you must finish using the result set during that transaction. Once you either commit or roll back the transaction, you can't get any more data from a result set, and you can't get any additional result sets. For example, the following code is illegal:
connobj.beginTransaction();
spobj = connobj.storedProc("getcusts");
resobj = spobj.resultSet();
connobj.commitTransaction();
// Illegal! Result set no longer valid!
col1 = resobj[0];For Sybase, ODBC, and DB2, you must retrieve 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 = connobj.storedProc("getcusts");
resobj = spobj.resultSet();
retval = spobj.returnValue();
// Illegal! Result set no longer valid!
col1 = resobj[0];For Sybase, you must retrieve 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 = connobj.storedProc("getcusts");
resobj = spobj.resultSet();
curobj = connobj.cursor ("select * from orders");
// Illegal! The result set is no longer available!
col1 = resobj[0];For ODBC, a slightly different restriction holds. Again, you must work with the 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 = connobj.storedProc("getcusts");
resobj = spobj.resulSet();
curobj = connobj.cursor ("select * from orders");
col1 = resobj[0];
// Illegal! The cursor is no longer available.
curobj.next();
Closing Result Sets
When finished, close the result set by calling its close method. A database connection cannot be released until all associated result sets have been closed. For example, if you call a Connection object's release method and that connection has an associated result set that has not been closed, the connection is not actually released until you close the result set.
Working with Return Values
This step is relevant to Sybase and Oracle stored procedures. For Informix, ODBC, and DB2 stored procedures, the 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.
Working with Output Parameters
This step is relevant to Sybase, Oracle, DB2, or ODBC stored procedures. For Informix stored procedures, the methods discussed here are not applicable.To determine how many output parameters the procedure has (including both output and input/output parameters), you use the 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:
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:
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.
Closing Stored Procedures
When finished, close the stored procedure by calling its close method. A database connection cannot be released until all associated stored procedures have been closed. For example, if you call a Connection object's release method and that connection has an associated stored procedure that has not been closed, the connection is not actually released until you close the stored procedure.
Informix and Sybase Exceptions
Informix and Sybase stored procedures can return error codes using exceptions. After you run the stored procedure, you can retrieve these error codes and error messages using the 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))
if user = 'LiveWire' then
raise exception -746, 0, 'User not Allowed';
endif
end procedureWhen you run this stored procedure, you could check whether an error occurred and then access the error code and message as follows:
Previous Contents Index Next
Copyright © 2000 Sun Microsystems, Inc. Some preexisting portions Copyright © 2000 Netscape Communications Corp. All rights reserved.
Last Updated August 09, 2000