Table of Contents | Previous | Next | Index


Connection

Represents a single database connection from a pool of connections.

Server-side object

Implemented in

NES 3.0

Created by

The DbPool.connection method. You do not call a connection constructor directly. Once you have a Connection object, you use it for your interactions with the database.

Description

You can use the prototype property of the Connection class to add a property to all Connection instances. If you do so, that addition applies to all Connection 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

Property Description
prototype

Allows the addition of properties to the connection object.

Method Summary

Method Description
beginTransaction

Begins a new SQL transaction.

commitTransaction

Commits the current transaction.

connected

Tests whether the database pool (and hence this connection) is connected to a database.

cursor

Creates a database cursor for the specified SQL SELECT statement.

execute

Performs the specified SQL statement. Use for SQL statements other than queries.

majorErrorCode

Major error code returned by the database server or ODBC.

majorErrorMessage

Major error message returned by database server or ODBC.

minorErrorCode

Secondary error code returned by database vendor library.

minorErrorMessage

Secondary message returned by database vendor library.

release

Releases the connection back to the database pool.

rollbackTransaction

Rolls back the current transaction.

SQLTable

Displays query results. Creates an HTML table for results of an SQL SELECT statement.

storedProc

Creates a stored-procedure object and runs the specified stored procedure.

toString

Returns a string representing the specified object.

In addition, this object inherits the watch and unwatch methods from Object.


beginTransaction

Begins a new SQL transaction.

Method of

Connection

Implemented in

NES 3.0

Syntax

beginTransaction()

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

All subsequent actions that modify the database are grouped with this transaction, known as the current transaction.

For the database object, the scope of a transaction is limited to the current 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 when the connection was established. This parameter is provided when you make the connection by calling database.connect.

For Connection objects, the scope of a transaction is limited to the lifetime of that object. If the connection is released or the pool of connections is closed 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 when the connection was established. This parameter is provided when you make the connection by calling 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 are ignored.

The LiveWire Database Service does not support nested transactions. If you call beginTransaction when a transaction is already open (that is, you've called beginTransaction and have yet to commit or roll back that transaction), you'll get an error message.

Examples

This example updates the rentals table within a transaction. The values of customerID and videoID are passed into the cursor method as properties of the request object. When the videoReturn Cursor object opens, the next method navigates to the only record in the answer set and updates the value in the returnDate field.

The variable x is assigned a database status code to indicate if the updateRow method is successful. If updateRow succeeds, the value of x is 0, and the transaction is committed; otherwise, the transaction is rolled back.

// Begin a transaction
database.beginTransaction();
// Create a Date object with the value of today's date
today = new Date();
// Create a Cursor with the rented video in the answer set
videoReturn = database.Cursor("SELECT * FROM rentals WHERE
   customerId = " + request.customerID + " AND
   videoId = " + request.videoID, true);
// Position the pointer on the first row of the Cursor
// and update the row
videoReturn.next()
videoReturn.returndate = today;
x = videoReturn.updateRow("rentals");
// End the transaction by committing or rolling back
if (x == 0) {
   database.commitTransaction() }
else {
   database.rollbackTransaction() }
// Close the Cursor
videoReturn.close();

commitTransaction

Commits the current transaction

Method of

Connection

Implemented in

NES 3.0

Syntax

commitTransaction()

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

This method attempts to commit all actions since the last call to beginTransaction.

For the database object, the scope of a transaction is limited to the current 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 when the connection was established. This parameter is provided when you make the connection with the database or DbPool object.

For Connection objects, the scope of a transaction is limited to the lifetime of that object. If the connection is released or the pool of connections is closed before calling the commitTransaction or rollbackTransaction method, then the transaction is automatically either committed or rolled back, based on the commitFlag value.

If there is no current transaction (that is, if the application has not called beginTransaction), calls to commitTransaction and rollbackTransaction are ignored.

The LiveWire Database Service does not support nested transactions. If you call beginTransaction when a transaction is already open (that is, you've called beginTransaction and have yet to commit or roll back that transaction), you'll get an error message.


connected

Tests whether the database pool and all of its connections are connected to a database.

Method of

Connection

Implemented in

NES 3.0

Syntax

connected()

Parameters

None.

Returns

True if the pool (and hence a particular connection in the pool) is currently connected to a database; otherwise, false.

Description

The connected method indicates whether this object is currently connected to a database.

If this method returns false for a Connection object, you cannot use any other methods of that object. You must reconnect to the database, using the DbPool object, and then get a new Connection object. Similarly, if this method returns false for the database object, you must reconnect before using other methods of that object.

Example

Example 1: The following code fragment checks to see if the connection is currently open. If it's not, it reconnects the pool and reassigns a new value to the myconn variable.

if (!myconn.connected()) {
  mypool.connect("INFORMIX", "myserv", "SYSTEM", "MANAGER", "mydb", 4);
  myconn = mypool.connection;
}
Example 2: The following example uses an if condition to determine if an application is connected to a database server. If the application is connected, the isConnectedRoutine function runs; if the application is not connected, the isNotConnected routine runs.

if(database.connected()) {
   isConnectedRoutine() }
else {
   isNotConnectedRoutine() }

cursor

Creates a Cursor object.

Method of

Connection

Implemented in

NES 3.0

Syntax

cursor(sqlStatement [,updatable])

Parameters

sqlStatement

A JavaScript string representing a SQL SELECT statement supported by the database server.

updatable

A Boolean parameter indicating whether or not the cursor is updatable.

Returns

A new Cursor object.

Description

The cursor method creates a Cursor object that contains the rows returned by a SQL SELECT statement. The SELECT statement is passed to the cursor method as the sqlStatement argument. If the SELECT statement does not return any rows, the resulting Cursor object has no rows. The first time you use the next method on the object, it returns false.

You can perform the following tasks with the Cursor object:

The cursor method does not automatically display the returned data. To display this data, you must create custom HTML code. This HTML code may display the rows in an HTML table, as shown in Example 3. The SQLTable method is an easier way to display the output of a database query, but you cannot navigate, modify data, or control the format of the output.

The optional parameter updatable specifies whether you can modify the Cursor object you create with the cursor method. To create a Cursor object you can modify, specify updatable as true. If you do not specify a value for the updatable parameter, it is false by default.

If you create an updatable Cursor object, the answer set returned by the sqlStatement parameter must be updatable. For example, the SELECT statement in the sqlStatement parameter cannot contain a GROUP BY clause; in addition, the query usually must retrieve key values from a table. For more information on constructing updatable queries, consult your database vendor's documentation.

Examples

Example 1. The following example creates the updatable cursor custs and returns the columns ID, CUST_NAME, and CITY from the customer table:

custs = database.Cursor("select id, cust_name, city from customer", true)
Example 2. You can construct the SELECT statement with the string concatenation operator (+) and string variables such as client or request property values, as shown in the following example:

custs = database.Cursor("select * from customer
   where customerID = " + request.customerID);
Example 3. The following example demonstrates how to format the answer set returned by the cursor method as an HTML table. This example first creates Cursor object named videoSet and then displays two columns of its data (videoSet.title and videoSet.synopsis).

// Create the videoSet Cursor
<SERVER>
videoSet = database.cursor("select * from videos
   where videos.numonhand > 0 order by title");
</SERVER>
// Begin creating an HTML table to contain the answer set
// Specify titles for the two columns in the answer set
<TABLE BORDER>
<CAPTION> Videos on Hand </CAPTION>
<TR>
   <TH>Title</TH>
   <TH>Synopsis</TH>
</TR>
// Use a while loop to iterate over each row in the cursor
<SERVER>
while(videoSet.next()) {
</SERVER>
// Use write statements to display the data in both columns
<TR>
   <TH><A HREF=\Q"rent.html?videoID="+videoSet.id\Q>
       <SERVER>write(videoSet.title)</SERVER></A></TH>
   <TD><SERVER>write(videoSet.synopsis)</SERVER></TD>
</TR>
// End the while loop
<SERVER>
}
</SERVER>
// End the HTML table
</TABLE>
The values in the videoSet.title column are displayed within the A tag so a user can click them as links. When a user clicks a title, the rent.html page opens and the column value videoSet.id is passed to it as the value of request.videoID.

See also

Connection.SQLTable, Connection.cursor


execute

Performs the specified SQL statement. Use for SQL statements other than queries.

Method of

Connection

Implemented in

NES 3.0

Syntax

execute (stmt)

Parameters

stmt

A string representing the SQL statement to execute.

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

This method enables an application to execute any data definition language (DDL) or data manipulation language (DML) SQL statement supported by the database server that does not return a Cursor, such as CREATE, ALTER, or DROP.

Each database supports a standard core of DDL and DML statements. In addition, they may each also support DDL and DML statements specific to that database vendor. You can use execute to call any of those statements. However, each database vendor may also provide functions you can use with the database that are not DDL or DML statements. You cannot use execute to call those functions. For example, you cannot call the Oracle describe function or the Informix load function from the execute method.

Although technically you can use execute to perform data modification (INSERT, UPDATE, and DELETE statements), you should instead use Cursor objects. This makes your application more database-independent. Cursors also provide support for binary large object (BLOb) data.

When using the execute method, your SQL statement must strictly conform to the syntax requirements of the database server. For example, some servers require each SQL statement to be terminated by a semicolon. See your server documentation for more information.

If you have not explicitly started a transaction, the single statement is automatically committed.

Examples

In the following example, the execute method is used to delete a customer from the customer table. customer.ID represents the unique ID of a customer that is in the ID column of the customer table. The value for customer.ID is passed into the DELETE statement as the value of the ID property of the request object.

if(request.ID != null) {
   database.execute("delete from customer
      where customer.ID = " + request.ID)
}

majorErrorCode

Major error code returned by the database server or ODBC.

Method of

Connection

Implemented in

NES 3.0

Syntax

majorErrorCode()

Parameters

None.

Returns

The result returned by this method depends on the database server being used:

Description

SQL statements can fail for a variety of reasons, including referential integrity constraints, lack of user privileges, record or table locking in a multiuser database, and so on. When an action fails, the database server returns an error message indicating the reason for failure. The LiveWire Database Service provides two ways of getting error information: from the status code returned by various methods or from special properties containing error messages and codes.

Status codes are integers between 0 and 27, with 0 indicating a successful execution of the statement and other numbers indicating an error, as shown in the following table.

Table 1.1 Database status codes.
Status code Explanation Status code Explanation

0

No error

14

Null reference parameter

1

Out of memory

15

Connection object not found

2

Object never initialized

16

Required information is missing

3

Type conversion error

17

Object cannot support multiple readers

4

Database not registered

18

Object cannot support deletions

5

Error reported by server

19

Object cannot support insertions

6

Message from server

20

Object cannot support updates

7

Error from vendor's library

21

Object cannot support updates

8

Lost connection

22

Object cannot support indices

9

End of fetch

23

Object cannot be dropped

10

Invalid use of object

24

Incorrect connection supplied

11

Column does not exist

25

Object cannot support privileges

12

Invalid positioning within object (bounds error)

26

Object cannot support cursors

13

Unsupported feature

27

Unable to open

Examples

This example updates the rentals table within a transaction. The updateRow method assigns a database status code to the statusCode variable to indicate whether the method is successful.

If updateRow succeeds, the value of statusCode is 0, and the transaction is committed. If updateRow returns a statusCode value of either five or seven, the values of majorErrorCode, majorErrorMessage, minorErrorCode, and minorErrorMessage are displayed. If statusCode is set to any other value, the errorRoutine function is called.

database.beginTransaction()
statusCode = cursor.updateRow("rentals")
if (statusCode == 0) {
   database.commitTransaction()
   }
if (statusCode == 5 || statusCode == 7) {
   write("The operation failed to complete.<BR>"
   write("Contact your system administrator with the following:<P>"
   write("The value of statusCode is " + statusCode + "<BR>")
   write("The value of majorErrorCode is " +
      database.majorErrorCode() + "<BR>")
   write("The value of majorErrorMessage is " +
      database.majorErrorMessage() + "<BR>")
   write("The value of minorErrorCode is " +
      database.minorErrorCode() + "<BR>")
   write("The value of minorErrorMessage is " +
      database.minorErrorMessage() + "<BR>")
   database.rollbackTransaction()
   }
else {
   errorRoutine()
   }

majorErrorMessage

Major error message returned by database server or ODBC. For server errors, this typically corresponds to the server's SQLCODE.

Method of

Connection

Implemented in

NES 3.0

Syntax

majorErrorMessage()

Parameters

None.

Returns

A string describing that depends on the database server:

Description

SQL statements can fail for a variety of reasons, including referential integrity constraints, lack of user privileges, record or table locking in a multiuser database, and so on. When an action fails, the database server returns an error message indicating the reason for failure. The LiveWire Database Service provides two ways of getting error information: from the status code returned by connection and DbPool methods or from special connection or DbPool properties containing error messages and codes.

Examples

See Connection.majorErrorCode.


minorErrorCode

Secondary error code returned by database vendor library.

Method of

Connection

Implemented in

NES 3.0

Syntax

minorErrorCode()

Parameters

None.

Returns

The result returned by this method depends on the database server:


minorErrorMessage

Secondary message returned by database vendor library.

Method of

Connection

Implemented in

NES 3.0

Syntax

minorErrorMessage()

Parameters

None.

Returns

The string returned by this method depends on the database server:


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

Connection

Implemented in

NES 2.0


release

Releases the connection back to the database pool.

Method of

Connection

Implemented in

NES 3.0

Syntax

release()

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

Before calling the release method, you should close all open cursors. When you call the release method, the runtime engine waits until all cursors have been closed and then returns the connection to the database pool. The connection is then available to the next user.

If you don't call the release method, the connection remains unavailable until the object goes out of scope. Assuming the object has been assigned to a variable, it can go out of scope at different times:

You must call the release method for all connections in a database pool before you can call the DbPool object's disconnect method. Otherwise, the connection is still considered in use by the runtime engine, so the disconnect waits until all connections are released.


rollbackTransaction

Rolls back the current transaction.

Method of

Connection

Implemented in

NES 3.0

Syntax

rollbackTransaction()

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

This method will undo all modifications since the last call to beginTransaction.

For the database object, the scope of a transaction is limited to the current 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 when the connection was established. This parameter is provided when you make the connection with the database or DbPool object.

For Connection objects, the scope of a transaction is limited to the lifetime of that object. If the connection is released or the pool of connections is closed before calling the commitTransaction or rollbackTransaction method, then the transaction is automatically either committed or rolled back, based on the commitFlag value.

If there is no current transaction (that is, if the application has not called beginTransaction), calls to commitTransaction and rollbackTransaction are ignored.

The LiveWire Database Service does not support nested transactions. If you call beginTransaction when a transaction is already open (that is, you've called beginTransaction and have yet to commit or roll back that transaction), you'll get an error message.


SQLTable

Displays query results. Creates an HTML table for results of an SQL SELECT statement.

Method of

Connection

Implemented in

NES 3.0

Syntax

SQLTable (stmt)

Parameters

stmt

A string representing an SQL SELECT statement.

Returns

A string representing an HTML table, with each row and column in the query as a row and column of the table.

Description

Although SQLTable does not give explicit control over how the output is formatted, it is the easiest way to display query results. If you want to customize the appearance of the output, use a Cursor object to create your own display function.

NOTE: Every Sybase table you use with a cursor must have a unique index.

Example

If connobj is a Connection object and request.sql contains an SQL query, then the following JavaScript statements display the result of the query in a table:

write(request.sql)
connobj.SQLTable(request.sql)
The first line simply displays the SELECT statement, and the second line displays the results of the query. This is the first part of the HTML generated by these statements:

select * from videos
<TABLE BORDER>
<TR>
<TH>title</TH>
<TH>id</TH>
<TH>year</TH>
<TH>category</TH>
<TH>quantity</TH>
<TH>numonhand</TH>
<TH>synopsis</TH>
</TR>
<TR>
<TD>A Clockwork Orange</TD>
<TD>1</TD>
<TD>1975</TD>
<TD>Science Fiction</TD>
<TD>5</TD>
<TD>3</TD>
<TD> Little Alex, played by Malcolm Macdowell,
and his droogies stop by the Miloko bar for a
refreshing libation before a wild night on the town.
</TD>
</TR>
<TR>
<TD>Sleepless In Seattle</TD>
...
As this example illustrates, SQLTable generates an HTML table, with column headings for each column in the database table and a row in the table for each row in the database table.


storedProc

Creates a stored procedure object and runs the specified stored procedure.

Method of

Connection

Implemented in

NES 3.0

Syntax

storedProc (procName [, inarg1 [, inarg2 [, ... inargN]]])

Parameters

procName

A string specifying the name of the stored procedure to run.

inarg1, ..., inargN

The input parameters to be passed to the procedure, separated by commas.

Returns

A new Stproc object.

Description

The scope of the stored procedure object is a single page of the application. In other words, all methods to be executed for any instance of storedProc must be invoked on the same application page as the page on which the object is created.

When you create a stored procedure, you can specify default values for any of the parameters. Then, if a parameter is not included when the stored procedure is executed, the procedure uses the default value. However, when you call a stored procedure from a server-side JavaScript application, you must indicate that you want to use the default value by typing "/Default/" in place of the parameter. (Remember that JavaScript is case sensitive.) For example:
spObj = connobj.storedProc ("newhire", "/Default/", 3)

toString

Returns a string representing the specified object.

Method of

Connection

Implemented in

NES 3.0

Syntax

toString()

Parameters

None.

Description

Every object has a toString method that is automatically called when it is to be represented as a text value or when an object is referred to in a string concatenation.

You can use toString within your own code to convert an object into a string, and you can create your own function to be called in place of the default toString method.

This method returns a string of the following format:

db "name" "userName" "dbtype" "serverName"
where

name

The name of the database.

userName

The name of the user connected to the database.

dbType

One of ORACLE, SYBASE, INFORMIX, DB2, or ODBC.

serverName

The name of the database server.

The method displays an empty string for any of attributes whose value is unknown.

For information on defining your own toString method, see the Object.toString method.


Table of Contents | Previous | Next | Index

Last Updated: 11/13/98 10:22:51

Copyright 1998 Netscape Communications Corporation