When finished with a Stproc object, use the close method to close it and release the memory it uses. If you release a connection that has an open stored procedure, the runtime engine waits until the stored procedure is closed before actually releasing the connection.

If you do not explicitly close a stored procedure with the close method, the JavaScript runtime engine on the server automatically tries to close all open stored procedures when the associated database or Connection object goes out of scope. This can tie up system resources unnecessarily. It can also lead to unpredictable results.

You can use the prototype property of the Stproc class to add a property to all Stproc instances. If you do so, that addition applies to all Stproc objects running in all applications on your server, not just in the single application that made the change. This allows you to expand the capabilities of this object for your entire server.

Property Summary

prototype

Allows the addition of properties to a Stproc object.

Method Summary

`close`	Closes a stored-procedure object.
`outParamCount`	Returns the number of output parameters returned by a stored procedure.
`outParameters`	Returns the value of the specified output parameter.
`resultSet`	Returns a new result set object.
`returnValue`	Returns the return value for the stored procedure.

Properties

prototype

Represents the prototype for this class. You can use the prototype to add properties or methods to all instances of a class. For information on prototypes, see Function.prototype.

Property of	`Stproc`
Implemented in	LiveWire 1.0

Methods

close

Closes the stored procedure and frees the allocated memory.

Method of	`Stproc`
Implemented in	Netscape Server 3.0

Syntax

close()

Parameters

None.

Returns

0 if the call was successful; otherwise, a nonzero status code based on any error message passed by the database. If the method returns a nonzero status code, use the associated majorErrorCode and majorErrorMessage methods to interpret the cause of the error.

Description

The close method closes a stored procedure and releases the memory it uses. If you do not explicitly close a stored procedure with the close method, the JavaScript runtime engine on the server automatically closes it when the corresponding client object goes out of scope.

outParamCount

Returns the number of output parameters returned by a stored procedure.

Method of	`Stproc`
Implemented in	Netscape Server 3.0

Syntax

outParamCount()

Parameters

None.

Returns

The number of output parameters for the stored procedure. Informix stored procedures do not have output parameters. Therefore for Informix, this method always returns 0. You should always call this method before calling outParameters, to ensure that the stored procedure has output parameters.

outParameters

Returns the value of the specified output parameter.

Method of	`Stproc`
Implemented in	Netscape Server 3.0

Syntax

outParameters (n)

Parameters

n	Zero-based ordinal for the output parameter to return.

Returns

The value of the specified output parameter. This can be a string, number, double, or object.

Description

Do not use this method for Informix stored procedures, because they do not have output parameters.

You should always call the outParamCount method before you call this method. If outParamCount returns 0, the stored procedure has no output parameters. In this situation, do not call this method.

You must retrieve result set objects before you call this method. Once you call this method, you can't get any more data from a result set, and you can't get any additional result sets.

resultSet

Returns a new result set object.

Method of	`Stproc`
Implemented in	Netscape Server 3.0

Syntax

resultSet ()

Parameters

None.

Description

Running a stored procedure can create 0 or more result sets. You access the result sets in turn by repeated calls to the resultSet method. See the description of the Resultset for restrictions on when you can use this method access the result sets for a stored procedure.

spobj = connobj.storedProc("getcusts");

// Creates a new result set object
resobj = spobj.resultSet();

returnValue

Returns the return value for the stored procedure.

Method of	`Stproc`
Implemented in	Netscape Server 3.0

Syntax

returnValue()

Parameters

None.

Returns

For Sybase, this method always returns the return value of the stored procedure.

For Oracle, this method returns null if the stored procedure did not return a value or the return value of the stored procedure.

For Informix, DB2, and ODBC, this method always returns null.

Description

You must retrieve result set objects before you call this method. Once you call this method, you can't get any more data from a result set, and you can't get any additional result sets.

Resultset

Represents a virtual table created by executing a stored procedure.

Server-side object
Implemented in	Netscape Server 3.0

Created by

The resultSet method of a Stproc object. The Resultset object does not have a constructor.

Description

For Sybase, Oracle, ODBC, and DB2 stored procedures, the stored-procedure object has one result set object for each SELECT statement executed by the stored procedure. For Informix stored procedures, the stored-procedure object always has one result set object.

A result set has a property for each column in the SELECT statement used to generate the result set. For Sybase, Oracle, and ODBC stored procedures, you can refer to these properties by the name of the column in the virtual table. For Informix and DB2 stored procedures, the columns are not named. For these databases, you must use a numeric index to refer to the column.

Result set objects are 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 rollback 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:
```
database.beginTransaction();
spobj = database.storedProc("getcusts");
resobj = spobj.resultSet();
database.commitTransaction();
/* Illegal! Result set no longer valid! */
col1 = resobj[0];
```
You must retrieve result set 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.
```
spobj = database.storedProc("getcusts");
resobj = spobj.resultSet();
retval = spobj.returnValue();
/* Illegal! Result set no longer valid! */
col1 = resobj[0];
```

Similarly, you must retrieve result set objects before you call the associated Connection object's cursor or SQLTable method. For example, the following code is illegal:

spobj = database.storedProc("getcusts");
cursobj = database.cursor("SELECT * FROM ORDERS;");
/* Illegal! The result set is no longer available! */
resobj = spobj.resultSet();
col1 = resobj[0];

When finished with a Resultset object, use the close method to close it and release the memory it uses. If you release a connection that has an open result set, the runtime engine waits until the result set is closed before actually releasing the connection.

If you do not explicitly close a result set with the close method, the JavaScript runtime engine on the server automatically tries to close all open result sets when the associated database or DbPool object goes out of scope. This can tie up system resources unnecessarily. It can also lead to unpredictable results.

You can use the prototype property of the Resultset class to add a property to all Resultset instances. If you do so, that addition applies to all Resultset objects running in all applications on your server, not just in the single application that made the change. This allows you to expand the capabilities of this object for your entire server.

Property Summary

prototype

Allows the addition of properties to a Resultset object.

Method Summary

`close`	Closes a result set object.
`columnName`	Returns the name of a column in the result set.
`columns`	Returns the number of columns in the result set.
`next`	Moves the current row to the next row in the result set.

Examples

Assume you have the following Oracle stored procedure:

create or replace package timpack 
as type timcurtype is ref cursor return customer%rowtype; 
type timrentype is ref cursor return rentals%rowtype; 
end timpack;

create or replace procedure timset4(timrows1 in out timpack.timcurtype, timrows in out
 timpack.timrentype) 
as begin 
open timrows for select * from rentals; 
open timrows1 for select * from customer; 
end timset4;

Running this stored procedure creates two result sets you can access. In the following code fragment the resobj1 result set has rows returned by the timrows ref cursor and the resobj2 result set has the rows returned by the timrows1 ref cursor.

spobj = database.storedProc("timset4");
resobj1 = spobj.resultSet();
resobj2 = spobj.resultSet();

Properties

prototype

Represents the prototype for this class. You can use the prototype to add properties or methods to all instances of a class. For information on prototypes, see Function.prototype.

Property of	`Resultset`
Implemented in	LiveWire 1.0

Methods

close

Closes the result set and frees the allocated memory.

Method of	`Resultset`
Implemented in	Netscape Server 3.0

Syntax

close()

Parameters

None.

Returns

Description

The close method closes a cursor or result set and releases the memory it uses. If you do not explicitly close a cursor or result set with the close method, the JavaScript runtime engine on the server automatically closes all open cursors and result sets when the corresponding client object goes out of scope.

Examples

The following example creates the rentalSet cursor, performs certain operations on it, and then closes it with the close method.

// Create a Cursor object
rentalSet = database.cursor("SELECT * FROM rentals")

// Perform operations on the cursor
cursorOperations()

//Close the cursor
err = rentalSet.close()

columnName

Returns the name of the column in the result set corresponding to the specified number.

Method of	`Resultset`
Implemented in	Netscape Server 3.0

Syntax

columnName (n)

Parameters

n	Zero-based integer corresponding to the column in the query. The first column in the result set is 0, the second is 1, and so on.

Returns

The name of the column. For Informix stored procedures, this method for the Resultset object always returns the string "Expression".

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:

resSet = stObj.resultSet("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 resSet.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:

resSet = stObj.resultSet("select ID, NAME, CITY from customer");

With this statement, resSet.columnName(0) is ID, resSet.columnName(1) is NAME, and resSet.columnName(2) is CITY.

Examples

The following example assigns the name of the first column in the customerSet cursor to the variable header:

customerSet=database.cursor(SELECT * FROM customer ORDER BY name)
header = customerSet.columnName(0)

columns

Returns the number of columns in the result set.

Method of	`Resultset`
Implemented in	Netscape Server 3.0

Syntax

columns()

Parameters

None.

Returns

The number of named and unnamed columns.

Examples

See Example 2 of Cursor for an example of using the columns method with the cursorColumn array.

The following example returns the number of columns in the custs cursor:

custs.columns()

Moves the current row to the next row in the result set.

Method of	`Resultset`
Implemented in	Netscape Server 3.0

Syntax

next()

Parameters

None.

Returns

False if the current row is the last row; otherwise, true.

Description

Initially, the pointer (or current row) for a cursor or result set is positioned before the first row returned. Use the next method to move the pointer through the records in the cursor or result set. This method moves the pointer to the next row and returns true as long as there is another row available. When the cursor or result set has reached the last row, the method returns false. Note that if the cursor is empty, this method always returns false.

Examples

Example 1. This example uses the next method to navigate to the last row in a cursor. The variable x is initialized to true. When the pointer is in the last row of the cursor, the next method returns false and terminates the while loop.

customerSet = database.cursor("select * from customer", true)

x = true
while (x) {
   x = customerSet.next() }

Example 2. In the following example, the rentalSet cursor contains columns named videoId, rentalDate, and dueDate. The next method is called in a while loop that iterates over every row in the cursor. When the pointer is on the last row in the cursor, the next method returns false and terminates the while loop.

This example displays the three columns of the cursor in an HTML table:

<SERVER>
// Create a Cursor object
rentalSet = database.cursor("SELECT videoId, rentalDate, returnDate
   FROM rentals")
</SERVER>

// Create an HTML table
<TABLE BORDER>
<TR>
<TH>Video ID</TH>
<TD>Rental Date</TD>
<TD>Due Date</TD>
</TR>

<SERVER>
// Iterate through each row in the cursor
while (rentalSet.next()) {
</SERVER>

// Display the cursor values in the HTML table
   <TR>
   <TH><SERVER>write(rentalSet.videoId)</SERVER></TH>
   <TD><SERVER>write(rentalSet.rentalDate)</SERVER></TD>
   <TD><SERVER>write(rentalSet.returnDate)</SERVER></TD>
   </TR>

// Terminate the while loop
<SERVER>
}
</SERVER>

// End the table
</TABLE>

blob

Server-side object. Provides functionality for displaying and linking to BLOb data.

Server-side object
Implemented in	LiveWire 1.0

Created by

You do not create a separate blob object. Instead, if you know that the value of a cursor property contains BLOb data, you use these methods to access that data:

`blobImage`	Displays BLOb data stored in a database.
`blobLink`	Displays a link that references BLOb data with a link.

Conversely, to store BLOb data in a database, use the blob function.

Methods

blobImage

Displays BLOb data stored in a database.

Method of	`blob`
Implemented in	LiveWire 1.0

Syntax

cursorName.colName.blobImage (format, altText, align, widthPixels, heightPixels,
 borderPixels, ismap)

Parameters

format	The image format. This can be GIF, JPEG, or any other MIME image format. The acceptable formats are specified in the `type=image` section of the file `$nshomehttpd-80configmime.types`, where `$nshome` is the directory in which you installed your server. The client browser must also be able to display the image format.
altText	(Optional) The value of the ALT attribute of the `image` tag. This indicates text to display if the client browser does not display images.
align	(Optional) The value of the `ALIGN` attribute of the `image` tag. This can be `"left"`, `"right"`, or any other value supported by the client browser.
widthPixels	(Optional) The width of the image in pixels.
heightPixels	(Optional) The height of the image in pixels.
borderPixels	(Optional) The size of the outline border in pixels if the image is a link.
ismap	(Optional) True if the image is a clickable map. If this parameter is true, the `image` tag has an ISMAP attribute; otherwise it does not.

Returns

An HTML IMG tag for the specified image type.

Description

Use blobImage to create an HTML image tag for a graphic image in a standard format such as GIF or JPEG.

The blobImage method fetches a BLOb from the database, creates a temporary file (in memory) of the specified format, and generates an HTML image tag that refers to the temporary file. The JavaScript runtime engine removes the temporary file after the page is generated and sent to the client.

While creating the page, the runtime engine keeps the binary data that blobImage fetches from the database in active memory, so requests that fetch a large amount of data can exceed dynamic memory on the server. Generally it is good practice to limit the number of rows retrieved at one time using blobImage to stay within the server's dynamic memory limits.

Examples

Example 1. The following example extracts a row containing a small image and a name. It writes HTML containing the name and a link to the image:

cursor = connobj.cursor("SELECT NAME, THUMB FROM FISHTBL WHERE ID=2")
write(cursor.name + " ")
write(cursor.thumb.blobImage("gif")) 
write("<BR>") 
cursor.close()

These statements produce this HTML:

Anthia <IMG SRC="LIVEWIRE_TEMP11"><BR>

Example 2. The following example creates a cursor from the rockStarBios table and uses blobImage to display an image retrieved from the photos column:

cursor = database.cursor("SELECT * FROM rockStarBios
   WHERE starID = 23")
while(cursor.next()) {
   write(cursor.photos.blobImage("gif", "Picture", "left",
      30, 30, 0,false))
}
cursor.close()

This example displays an image as if it were created by the following HTML:

<IMG SRC="livewire_temp.gif" ALT="Picture" ALIGN=LEFT 
   WIDTH=30 HEIGHT=30 BORDER=0>

The livewire_temp.gif file in this example is the file in which the rockStarBios table stores the BLOb data.

blobLink

Returns a link tag that references BLOb data with a link. Creates an HTML link to the BLOb.

Method of	`blob`
Implemented in	LiveWire 1.0

Syntax

cursorName.colName.blobLink (mimeType, linkText)

Parameters

mimeType	The MIME type of the binary data. This can be image/gif or any other acceptable MIME type, as specified in the Netscape server configuration file $nshomehttpd-80configmime.types, where `$nshome` is the directory in which you installed your server.
linkText	The text to display in the link. This can be any JavaScript string expression.

Returns

An HTML link tag.

Description

Use blobLink if you do not want to display graphics (to reduce bandwidth requirements) or if you want to provide a link to an audio clip or other multimedia content not viewable inline.

The blobLink method fetches BLOb data from the database, creates a temporary file in memory, and generates a hypertext link to the temporary file. The JavaScript runtime engine on the server removes the temporary files that blobLink creates after the user clicks the link or sixty seconds after the request has been processed.

The runtime engine keeps the binary data that blobLink fetches from the database in active memory, so requests that fetch a large amount of data can exceed dynamic memory on the server. Generally it is good practice to limit the number of rows retrieved at one time using blobLink to stay within the server's dynamic memory limits.

Example

Example 1. The following statements extract a row containing a large image and a name. It writes HTML containing the name and a link to the image:

cursor = connobj.cursor("SELECT NAME, PICTURE FROM FISHTBL WHERE ID=2")
write(cursor.name + " ")
write(cursor.picture.blobLink("image/gif", "Link" + cursor.id)) 
write("<BR>") 
cursor.close()

These statements produce this HTML:

Anthia <A HREF="LIVEWIRE_TEMP2">Link2</A><BR>

Example 2. The following example creates a cursor from the rockStarBios table and uses blobLink to create links to images retrieved from the photos column:

write("Click a link to display an image:<P>")
cursor = database.cursor("select * from rockStarBios")
while(cursor.next()) {
   write(cursor.photos.blobLink("image/gif", "Image " + cursor.id))
   write("<BR>")
}
cursor.close()

This example generates the following HTML:

Click a link to display an image:<P>
<A HREF="LIVEWIRE_TEMP1">Image 1</A><BR>
<A HREF="LIVEWIRE_TEMP2">Image 2</A><BR>
<A HREF="LIVEWIRE_TEMP3">Image 3</A><BR>
<A HREF="LIVEWIRE_TEMP4">Image 4</A><BR>

The LIVEWIRE_TEMP files in this example are temporary files created in memory by the blobLink method.

(Sebelumnya) Cursor

Chapter 11. Session Management ... (Berikutnya)

Link Bermanfaat

✺ Kuliah Online ✺ Kode Pos ✺ Kuliah Umum ✺ Kuliah Kelas Ekstensi ✺ Kuliah Jarak Jauh ✺ Kuliah Gratis Beasiswa S1 S2 ✺ Kampus Merdeka Belajar

eduNitas • Konsultan Pendidikan Tinggi (KPT) • PT. Gilland Ganesha • Gilland Group

Stproc

Created by

Description

Property Summary

Method Summary

Properties

prototype

Methods

close

Syntax

Parameters

Returns

Description

outParamCount

Syntax

Parameters

Returns

outParameters

Syntax

Parameters

Returns

Description

resultSet

Syntax

Parameters

Description

returnValue

Syntax

Parameters

Returns

Description

Resultset

Created by

Description

Property Summary

Method Summary

Examples

Properties

prototype

Methods

close

Syntax

Parameters

Returns

Description

Examples

See also

columnName

Syntax

Parameters

Returns

Examples

columns

Syntax

Parameters

Returns

Examples

next

Syntax

Parameters

Returns

Description

Examples

blob

Created by

Methods

blobImage

Syntax

Parameters

Returns

Description

Examples

blobLink

Syntax

Parameters

Returns

Description

Example