Chapter 5   Working with Databases


This chapter describes iPlanet Application Server's interaction with databases, which are used for a wide variety of purposes, such as storing information about users or customers, tracking inventory levels, and recording sales or banking transactions.

The following topics are included in this chapter:

• Introduction to Working with Databases

• About Database Connections

• Getting Information About Columns or Fields

• Inserting Records in a Database

• Updating Records in a Database

• Deleting Records From a Database

• Using Pass-Through Database Commands

• Using Prepared Database Commands

• Using Parameters in Database Commands

• Using Stored Procedures

• Using Triggers

• Using Sequences

• Managing Database Transactions

• Using Java Database Connectivity (JDBC)

Introduction to Working with Databases

---

iPlanet Application Server applications typically interact with databases. For example, an application might perform a database query to retrieve data for a report.

In addition to querying for data, you can perform several other types of database commands. The following list summarizes the operations you can include in an iPlanet Application Server application:

• Queries

• INSERT

• UPDATE

• DELETE

• Any other database command supported by the database server (through the setSQL( )SetSQL( ) method)

Supported Databases

iPlanet Application Server supports the following databases:

• Oracle

• Sybase

• Informix CLI

• Microsoft SQL Server

• DB2

• ODBC 1.0 and ODBC 2.0 compliant databases

Summary of Database Interaction

The following list summarizes the steps involved in adding database interaction to an iPlanet Application Server application:

To add database interaction to an iPlanet Application Server application

1. Check the user's security level to see whether they have access to the tables and other database objects that the application will reference.

   For more information, see

2. Open a database connection.

   For more information, see

3. Write the database command (an insert, update, delete, or query operation).

   Several sections in this chapter describe in detail how to write database commands.

4. Run the command.

5. Retrieve and process the results of the command (if any).

About Database Connections

---

A database connection is a communication link with a database or other data source. Your code can create and manipulate several database connections simultaneously to access data.

Each database connection is represented by a database connection object, which is an instance of the IDataConn interfaceIGXDataConn interface. Each IGXDataConn object contains information such as the user name, password, and any other parameters that are necessary to establish the connection to a particular database.

Opening a Database Connection

Before running a query or another database command, you must open a connection to a data source. To do this, call the createDataConn( )CreateDataConn( ) method in the AppLogic classGXAppLogic class. This method instantiates a data connection object. The connection parameters are placed in an IGXValList which is then passed as a parameters to cCreateDataConn( ). For example:

IGXValList *loginParams;

loginParams = GXCreateValList();

loginParams->SetValString("DSN", "salesDB");

loginParams->SetValString("DB", "salesDB");

loginParams->SetValString("USER", "steve");

loginParams->SetValString("PSWD", "pass7878");

IGXDataConn *conn;

CreateDataConn(0,GX_DA_DRIVER_ODBC,

               loginParams, NULL, &conn);

IDataConn conn;

conn = createDataConn(0, GX_DA_DAD_DRIVERS.GX_DA_DRIVER_ODBC,

"salesDB", "salesDB", "steve", "pass7878");

In this example, the connection uses the following connection parameters:

• Type of driver is ODBC.

• Data source name is salesDB.

• Database name is also salesDB.

• User name is steve.

• Password is pass7878.

Specifying Connection Parameters

In the cCreateDataConn( ) method call, the AppLogic specifies which data access driver should be used and lists the connection parameters required by that type of driver. In the example given earlier, the ODBC data access driver is specified. This type of driver requires connection parameters, such as a data source name, user name, and password.

One of the connection parameters is the name of a data source. Before using an ODBC connection, you must use the ODBC administration utility, supplied with your database software, to define and name a data source.

For more information about how to do this, refer to your ODBC documentation.

Passing Connection Parameters in an IValList Object

Instead of specifying each connection parameter separately in the createDataConn( ) method's parameter list, you can place the parameters in an IValList object and pass the object to createDataConn( ). This technique is useful for drivers that require more connection parameters than you can list in the createDataConn( ) parameter list.

To pass connection parameters in an IValList object

1. Instantiate the IValList object.

2. Use setValString( ) to specify the connection parameter names and values. For ODBC connections, the connection parameter names are DSN, DB, USER, and PSWD.

3. Open the connection, passing the IValList object as a parameter to createDataConn( ).

Example
The following code uses an IValList object to set connection parameters:

IValList loginParams;

loginParams = GX.CreateValList();

loginParams.setValString("DSN", "salesDB");

loginParams.setValString("DB", "salesDB");

loginParams.setValString("USER", "steve");

loginParams.setValString("PSWD", "pass7878");

IDataConn conn;

conn = createDataConn(0,GX_DA_DAD_DRIVERS.GX_DA_DRIVER_ODBC,

   loginParams);

Closing a Database Connection

It is not necessary to close a connection object. The Data Access Engine service of iPlanet Application Server manages these connections for you. It will perform any shutdown and cleanup that is necessary.

If you are opening many connections simultaneously, you might want to close one or more of them explicitly in order to free up the connection for others who might be waiting to use it. You can explicitly close a database connection by using Release( )the Release( ) method in the GX class. For example:

IGXDataConn *conn1;

HRESULT hr = CreateDataConn(0,GX_DA_DRIVER_ODBC,

loginParams1, NULL, &conn1);

IGXDataConn *conn2;

HRESULT hr = CreateDataConn(0,GX_DA_DRIVER_ODBC,

loginParams2, NULL, &conn2);

IGXDataConn *conn3;

HRESULT hr = CreateDataConn(0,GX_DA_DRIVER_ODBC,

loginParams3, NULL, &conn3);

// ...

// Code using the three connections...

// Finished using connection 1

conn1->Release();

// Continue using connections 2 and 3...

// Release them also when finished.

IDataConn conn1;

conn1 = createDataConn(0,GX_DA_DAD_DRIVERS.GX_DA_DRIVER_ODBC,

loginParams1);

IDataConn conn2;

conn2 = createDataConn(0,GX_DA_DAD_DRIVERS.GX_DA_DRIVER_ODBC,

loginParams2);

IDataConn conn3;

conn3 = createDataConn(0,GX_DA_DAD_DRIVERS.GX_DA_DRIVER_ODBC,

loginParams3);

// ...

// Code using the three connections...

// Finished using connection 1

GX.Release(conn1);

// Continue using connections 2 and 3...

Getting Information About Columns or Fields

---

When you are working with databases, you often need to refer to columns in a database table or fields in a result set. If you do not know the names of the columns or fields, use one of the following methods:

• For a table, use the getColumnByOrd( )GetColumnByOrd( ) method in the ITable interfaceIGXTable interface.

• For a flat result set, use the getColumnByOrd( )GetColumnByOrd( ) method in the IResultSet interfaceIGXResultSet interface.

• For a hierarchical result set, use the getColumnByOrd( )GetColumnByOrd( ) method in the IHierResultSet interfaceIGXHierResultSet interface.

Each of these methods returns one column object at a time. The column object describes the name, data type, size, and other properties of a column. You can use a loop and an incrementing counter to retrieve all the available columns in a table or result set.

Alternatively, to walk through all the available columns, call enumColumns( )EnumColumns( ) inside a loop to iterate through all the columns in a table or all the fields in a result set's row structure. To find out how many columns or fields there are, call getNumColumns( )GetNumColumns( ). Before the start of the loop, call enumColumnReset( )EnumColumnReset( ) so that you start at the first column.

Once you have retrieved a column object, you can get more information about it by using the following methods in the IColumn interfaceIGXColumn interface: getName( )GetName( ), getNullsAllowed( )GetNullsAllowed( ), getPrecision( )GetPrecision( ), getScale( )GetScale( ), getSize( )GetSize( ), and getType( )GetType( ). For more information about these methods, see the iPlanet Application Server Foundation Class Reference.

Example
The following code enumerates columns and constructs a segment of HTML that displays the name and type code of all columns in a database table:

hr = table->EnumColumnReset();

while (TRUE)

{

   IGXColumn *column = NULL;

   hr = table->EnumColumns(&column);

   if (hr == NOERROR &&

      column)

   {

      char buffer[256];

      buffer[0] = '\0';

      column->GetName(buffer, sizeof(buffer));

      StreamResult("Column Name = ");

      StreamResult(buffer);

      StreamResult(", ");

      DWORD type;

      type = 0;

      column->GetType(&type);

      sprintf(buffer, "Column Type = %d", type);

      StreamResult(buffer);

      StreamResult("<br>");

      column->Release();

   }

   else

   {

   // No more columns, exit loop.

   //

   break;

   }

}

ITable table;

IColumn col;

String htmlString;

table = conn.getTable("Products");

htmlString += "<h2>Products Table</h2>";

table.enumColumnsReset();

while ((col = table.enumColumns()) != null) {

   htmlString += "Column Name = ";

   htmlString += col.getName();

   htmlString += ", Column Type = ";

   htmlString += col.getType();

   htmlString += "<br>";

};

return result(htmlString);

Inserting Records in a Database

---

You can write code to insert records, or rows, into tables. To do so, write an INSERT command. An INSERT command is a database command that adds a new row to an existing table.

You can use two techniques to write an INSERT command programmatically:

• Use a series of method calls to write the command. The rest of this section describes how to use this technique.

• Write a SQL INSERT statement and pass it to the setSQL( )SetSQL( ) method. Use this technique only if you are very familiar with SQL syntax. With this technique, you can allow part of the INSERT command to be set through runtime parameters. For more information, see

To insert a row by calling a series of methods

1. Open a connection to a data source.

2. Get a table in the database by calling getTable( )GetTable( ).

3. Create a temporary buffer for data by calling allocRow( )AllocRow( ).

4. Set the values of columns by calling the sSetValue**( ) methods of the ITable interfaceIGXTable interface. You must refer to columns by their ordinal position in the table, using getColumnOrdinal( )GetColumnOrdinal( ) if necessary.

5. Add the row to the database by calling addRow( )AddRow( ).

Example
The following code, from the Transfer AppLogic in the Online Bank sample application, adds a row to the OBTransaction table for a new transaction:

// Get table

IGXTable *pTable=NULL;

hr=pConn->GetTable("OBTransaction",

   &pTable))==GXE_SUCCESS)&&pTable);

// Look up the column ordinals for the table

ULONG transTypeCol=0; pTable->GetColumnOrdinal("transType",

   &transTypeCol);

ULONG postDateCol=0; pTable->GetColumnOrdinal("postDate",

   &postDateCol);

ULONG acctNumCol=0; pTable->GetColumnOrdinal("acctNum",

   &acctNumCol);

ULONG amountCol=0; pTable->GetColumnOrdinal("amount",

   &amountCol);

// Allocate a new row for the withdrawal

pTable->AllocRow();

// Set values in the row

pTable->SetValueString(acctNumCol, pFromAcct);

pTable->SetValueInt(transTypeCol, OB_TRANSTYPE_WITHDRAWAL);

pTable->SetValueDateString(postDateCol, dateStr);

pTable->SetValueDouble(amountCol, amount*-1.0);

// Perform the insert

pTable->AddRow(0, pTx);

// Get table

ITable table = conn.getTable("OBTransaction");

//* Allocate row in table for withdrawal transaction

//*

table.allocRow();

// Set the contents of the row

table.setValueString(acctNumCol, fromAcct);

table.setValueInt(transTypeCol,

   OBDBDefs.TRANSTYPE_WITHDRAWAL);

table.setValueDateString(postDateCol, transDateString);

table.setValueDouble(amountCol, amount.doubleValue() * -1.0);

// Perform the insert

rc = table.addRow(0, transferTrans);

Updating Records in a Database

---

You can write code to modify records, or rows, in tables. To do so, write an UPDATE command. An UPDATE command is a database command that changes the values of one or more columns in one or more existing rows in a table.

You can use two techniques to write an UPDATE command programmatically:

• Use a series of method calls to write the command. The rest of this section describes how to use this technique.

• Write a SQL UPDATE statement and pass it to the setSQL( )SetSQL( ) method. Use this technique only if you are very familiar with SQL syntax. With this technique, you can allow part of the UPDATE command to be set through runtime parameters. For more information, see

To update table rows by calling a series of methods

1. Open a connection to a data source.

2. Get a table in the database by calling getTable( )GetTable( ).

3. Create a temporary buffer for data by calling allocRow( )AllocRow( ).

4. Set the values of the columns you want to modify by calling sSetValue**( ) methods of the ITable interfaceIGXTable interface. You can refer to columns by name or by column number.

5. Specify which row(s) to update and actually update them by calling updateRow( )UpdateRow( ). Use the same syntax as a SQL WHERE clause to specify which row(s) to update.

   For more information about SQL syntax, see your SQL documentation.

Example
The following code changes the value of the region column from West to East, where the current region is West:

// Create a connection

IGXValList *conn_params;

conn_params = GXCreateValList();

conn_params->SetValString("DSN", "salesDB");

conn_params->SetValString("DB", "salesDB");

conn_params->SetValString("USER", "steve");

conn_params->SetValString("PSWD", "pass7878");

IGXDataConn *conn = NULL;

HRESULT hr;

hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL,

   &conn);

// Get a table

IGXTable *table = NULL;

hr = conn->GetTable("employees", &table);

// Allocate the row

hr = table->AllocRow();

// Set the new values in the row

ULONG col;

table->GetColumnOrdinal("region", &col);

table->SetValueString(col, "East");

// Perform the update

table->UpdateRow(0, "region='West'", NULL);

// Release resources

table->Release();

conn->Release();

conn_params->Release();

// Create a connection

IDataConn conn;

conn = createDataConn(0, GX_DA_DAD_DRIVERS.GX_DA_DRIVER_ODBC,

   "salesDB", "salesDB", "steve", "pass7878");

ITable table;

table = conn.getTable("employees");

table.allocRow();

int col;

col = table.getColumnOrdinal("region");

table.setValueString(col, "East");

table.updateRow(0, "region='West'", null);

Deleting Records From a Database

---

You can write code to delete records, or rows, from tables. To do so, write a DELETE command. A DELETE command is a database command that removes one or more existing rows from a table.

You can use two techniques to write a DELETE command programmatically:

• Use a series of method calls to write the command. The rest of this section describes how to use this technique.

• Write a SQL DELETE statement and pass it to the SetSQL( ) method. Use this technique only if you are very familiar with SQL syntax. For more information, see

To delete rows by calling a series of methods

1. Open a connection to a data source.

2. Get a table in the database by calling getTable( )GetTable( ).

3. Specify which row(s) to delete and actually remove them by calling deleteRow( )DeleteRow( ). Use the same syntax as a SQL WHERE clause to specify which row(s) to delete.

   For more information about SQL syntax, see your SQL documentation.

Example
The following code deletes all rows that contain data about sales employees whose last name is Smith:

// Create a connection

IGXValList *conn_params;

conn_params = GXCreateValList();

conn_params->SetValString("DSN", "salesDB");

conn_params->SetValString("DB", "salesDB");

conn_params->SetValString("USER", "steve");

conn_params->SetValString("PSWD", "pass7878");

IGXDataConn *conn = NULL;

HRESULT hr;

hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL,

   &conn);

// Get the table

IGXTable *table = NULL;

hr = conn->GetTable("employees", &table);

// Perform the delete

table->DeleteRow(0, "lastname='Smith'", NULL);

// Release resources used

table->Release();

conn->Release();

conn_params->Release();

// Create a connection

IDataConn conn;

conn = createDataConn(0, GX_DA_DAD_DRIVERS.GX_DA_DRIVER_ODBC,

"salesDB", "salesDB", "steve", "pass7878");

// Get the table

ITable table;

table = conn.getTable("employees");

// Perform the delete

table.deleteRow(0, "lastname = 'Smith'", null);

Using Pass-Through Database Commands

---

A pass-through database command is a statement you write using SQL syntax and pass directly to the database. Use pass-through commands anytime you want to send literal SQL or database server-specific commands to the database. Use this technique only if you are familiar with SQL syntax.

To write a pass-through command

1. Instantiate an IQuery interfaceIGXQuery interface object.

   Query objects are used for all types of pass-through database commands, not just queries.

2. Write a SQL statement and pass it as a parameter to a setSQL( )SetSQL( ) method call. The statement must not contain a statement termination character at the end. For example, commands passed to Oracle databases must not contain semicolons (;).

   The statement must comply with the requirements of the database driver. For more information about this syntax, refer to your driver or SQL documentation.

To run a pass-through command

1. Open a connection to a data source.

2. If necessary, declare a variable to refer to the output from the command. For example, if the command is a query, you will need a way to refer to the result set:    IGXResultSet *rs;

   IResultSet rs;

3. Call the executeQuery( )ExecuteQuery( ) method in the IDataConn interfaceIGXDataConn interface. This method runs all types of database commands that are specified using sSetSQL( ), not just queries. For example:    HRESULT hr = conn->ExecuteQuery(0, qry, NULL, NULL, &rs);

   rs = conn.executeQuery(0, qry, null, null);

Examples
The following code passes a flat query (SELECT statement) to sSetSQL( ):

// Set the sql string

char sqlStr[400];

sprintf(sqlStr, "SELECT * FROM OBUser, OBCustomer WHERE OBUser.userName = OBCustomer.userName AND OBCustomer.ssn = '%s'", pSsn);

// Pass to SetSQL()

pQuery->SetSQL(sqlStr);

// Set up SQL string

String sqlString = "SELECT * FROM OBUser, OBCustomer " +

   "WHERE OBUser.userName = OBCustomer.userName AND " +

   "OBCustomer.ssn = '" + ssn + "'";

// Pass to setSQL()

selCustQuery.setSQL(sqlString);

The following code passes an INSERT command to sSetSQL( ):

pUserQuery->SetSQL("INSERT INTO OBUser(userName, password,

   userType, eMail) VALUES (:userName, :password, :userType,

   :eMail)");

insUserQuery.setSQL("INSERT INTO OBUser(userName, " +

   "password, userType, eMail) VALUES (:userName," +

   ":password, :userType, :eMail)");

The following code passes an UPDATE command to sSetSQL( ):

pUserQuery->SetSQL("UPDATE OBUser SET password = :password,

   eMail = :eMail WHERE userName = :userName");

updUserQuery.setSQL("UPDATE OBUser SET password = " +

   ":password, eMail = :eMail WHERE userName = :userName");

The following code passes a DELETE command to sSetSQL( ):

pQuery->SetSQL("DELETE FROM OBUser WHERE userName =

   :userName");

delUserQuery.setSQL("DELETE FROM OBUser WHERE userName =" +

   ":userName");

Using Prepared Database Commands

---

When you run a database command, the database engine performs certain routine tasks such as determining efficient paths to the data referenced in the command. These tasks become repetitive when running the same command multiple times.

A prepared command is a database command that is compiled by the database. By preparing the command, you specify that the database need only perform these compilation tasks once for the command. The state of the command is saved after these tasks are done. From this point on, whenever the command runs, it has a head start. Without prepared commands, the database engine must recompile each command every time you run it, which is less efficient.

The iPlanet Application Server supports prepared commands through the IPreparedQuery interfaceIGXPreparedQuery interface.

To run a prepared database command

Before preparing a database command, you must write it using the techniques described elsewhere in this chapter. Then perform the following steps:

1. Open a connection to a data source.

2. Pre-compile the command by declaring a variable of type IGXPreparedQuery and calling the prepareQuery( )PrepareQuery( ) method in the IDataConn interfaceIGXDataConn interface. This method prepares all types of database commands, not just queries. For example:    IGXPreparedQuery *pPQuery=NULL;

      hr=pConn->PrepareQuery(0, pQuery, NULL, NULL, &pPQuery);

   IPreparedQuery delUserPQuery;

   delUserPQuery = conn.prepareQuery(0, delUserQuery, null,

      null);

3. If the command contains parameters, instantiate an IGXValList object and use setValString( )SetValString( ) to set the parameter values you want to pass into the command. For example:    IGXValList *pList=GXCreateValList();

      GXSetValListString(pList, ":userName", pUserName);

   IValList userValList = GX.CreateValList();

   userValList.setValString(":userName", userName);

4. Run the command by calling the prepared query object's execute( )Execute( ) method. Pass the IGXValList object, if any, as a parameter. For example:    IGXResultSet *pRset=NULL;

      hr=pPQuery->Execute(0, pList, NULL, NULL, &pRset);

      pRset->Release();

   IResultSet rs;

   rs = delUserPQuery.execute(0, userValList, null, null);

Note that, when Sybase prepared statements are executed, either an empty result set is returned (through the execute( )Execute( ) method of ICallableStmtIGXCallableStmt) or a null pointer for a result set is returned. The application writer is required to test for both cases. This behavior is different from that of other databases in that they return an empty result set.

Also, when using prepared queries with the Sybase native driver, problems might be experienced when inserting a column of type money. The money type is not supported by the Sybase native driver. As a workaround, use a fixed insert statement with the method executeQuery( )ExecuteQuery( ).

Using Parameters in Database Commands

---

When you are writing a database command, you can use parameter markers instead of values for parts of the command. This technique is useful for making commands more flexible and reusable.

For example, a static INSERT command is of limited usefulness. More typically, you use parameters to set up a prepared INSERT, then run the command in a loop, passing in a different IGXValList object each time.

You can use parameters in flat queries, query files, or SQL commands that you pass to setSQL( )SetSQL( ). You cannot use parameters when using method calls such as addRow( )AddRow( ) and deleteRow( )DeleteRow( ) to specify INSERT, UPDATE, or DELETE commands.

Before you run a database command that contains parameters, you must first prepare it, as described in Then, when you run the command, you pass an IGXValList object to it. Each item in the list corresponds to one of the parameters. iPlanet Application Server replaces each parameter name with a value from the IGXValList object.

To place a parameter in a database command, use one of the following types of parameter markers:

• A question mark (?). When you use this technique, the names (also called keys) of the items in the IGXValList object must consist of numbers, which can optionally be preceded by colons ("1", "2", or ":1", ":2", etc.). When you run the command, iPlanet Application Server substitutes values into the command in the order in which they are numbered in the IGXValList object.

• A name or number preceded by a colon (":1", ":2", ":city", ":max", etc.). When you run the command, iPlanet Application Server matches the parameter names to the names of the items in the IGXValList object. This technique gives you more control over the order in which the parameters appear in the command. Even if you use numbers in this technique, they need not appear in order in the command.

You can include up to 1,024 parameters in a single database command, unless your database software allows fewer parameters than this.

Examples
Suppose the code for a DELETE command contains the following line:

deleteCmd->SetSQL("DELETE FROM products WHERE color = :1");

deleteCmd.setSQL("DELETE FROM products " +

"WHERE color = :1");

The following IGXValList object sets the parameter value to be passed into the command:

IGXValList *pList=GXCreateValList();

GXSetValListString(pList, ":1", "green");

dbParams = GX.CreateValList();

dbParams.setValString(":1", "green");

The same IGXValList object could be used for the following DELETE command:

deleteCmd->SetSQL("DELETE FROM products WHERE color = ?");

deleteCmd.setSQL("DELETE FROM products WHERE color = ?");

The next example uses code from the OBLogin AppLogic in the Online Bank sample application. It shows a prepared flat query with two parameters. The parameter values for the query are obtained from input parameters, which are passed into the AppLogic from user input on the login form.

// Get input parameters

LPSTR userName=GXGetValListString(m_pValIn, "userName");

LPSTR password=GXGetValListString(m_pValIn, "password");

// Write query

IGXQuery *pQuery=NULL;

hr=CreateQuery(&pQuery);

pQuery->SetTables("OBUser, OBCustomer");

pQuery->SetFields("OBUser.userName, userType, ssn, lastName,

   firstName");

pQuery->SetWhere("OBUser.userName *= OBCustomer.userName AND

   OBUser.userName= :userName AND password= :password");

// Prepare the query

IGXPreparedQuery *pPrepQuery=NULL;

hr=pConn->PrepareQuery(0, pQuery, NULL, NULL,

   &pPrepQuery);

IGXValList *pList=GXCreateValList();

GXSetValListString(pList, ":userName", userName);

GXSetValListString(pList, ":password", password);

// Run the query

IGXResultSet *pResultSet=NULL;

hr=pPrepQuery->Execute(0, pList, NULL, NULL,

   &pResultSet);

// Get input parameters

String userName = valIn.getValString("userName");

String password = valIn.getValString("password");

// Write query

IQuery selUserQuery = createQuery();

selUserQuery.setTables("OBUser");

selUserQuery.setFields("userName, userType");

selUserQuery.setWhere("userName = :userName AND " +

   "password = :password");

// Prepare the query

IPreparedQuery selUserPQuery =

   conn.prepareQuery(0, selUserQuery, null, null);

IValList inputValList = GX.CreateValList();

inputValList.setValString(":userName", userName);

inputValList.setValString(":password", password);

// Execute query

IResultSet selUserResultSet = selUserPQuery.execute(0,

   inputValList, null, null);

Parts of Syntax in Which Parameters are Not Allowed

You cannot use parameters in a hierarchical query that is written using method calls. That is, none of the flat queries that make up the hierarchical query can contain parameters. However, you can use parameters in a hierarchical query that is written in a query file (.gxq).

Outside of hierarchical queries, you can use parameters to replace any single word or value in a database command, except for the following parts of SQL command syntax:

• Field name in a SELECT list.

• Operand of a unary + or - operation.

• Argument of a SET operation.

• Both expressions in a comparison predicate. Only one expression in a comparison can be a parameter.

• Both operands of a binary operator. Only one operand can be a parameter.

• Both the first and second operands of a BETWEEN operation. Either the first or second operand can be a parameter, but not both.

• Both the first and third operands of a BETWEEN operation. Either the first or third operand can be a parameter, but not both.

• Both the expression and the first value of an IN operation. Either the expression or the first value can be a parameter, but not both.

For more information about the items referred to in this list, see your SQL documentation.

Using Parameters in a Flat Query

iPlanet Application Server provides three techniques for writing flat queries:

• Write the query in SQL and pass it to the setSQL( )SetSQL( ) method.

• Use a series of method calls to set up the query clauses.

• Write the query in a query file.

You can use parameters no matter which technique you use to write the query. However, if the flat query is to be included in a hierarchical query, it cannot contain parameters.

For more information about hierarchical queries, see

Example
The following code shows a flat query with one parameter, :1, which is a placeholder for a minimum salary value:

IGXQuery *qry;

CreateQuery(&qry);

qry->SetTables("employee");

qry->SetFields("empSalary, empName");

qry->SetWhere("empSalary > :1");

IQuery qry;

qry = createQuery();

qry.setTables("employee");

qry.setFields("empSalary, empName");

qry.setWhere("empSalary > :1");

Using Parameters in an INSERT, UPDATE, or DELETE Command

iPlanet Application Server provides two techniques for writing INSERT, UPDATE, and DELETE commands:

• Write the command in SQL and pass it to the setSQL( )SetSQL( ) method. This is the only technique in which you can use parameters.

  For more information, see

• Use a series of method calls. You cannot use parameters with this technique.

Examples
The following code shows an INSERT command with four parameters for the values to be placed in a new row:

pUserQuery->SetSQL("INSERT INTO OBUser(userName, password,

   userType, eMail) VALUES (:userName, :password, :userType,

   :eMail)");

insUserQuery.setSQL("INSERT INTO OBUser(userName, " +

   "password, userType, eMail) VALUES (:userName," +

   ":password, :userType, :eMail)");

The following code shows an UPDATE command with three parameters:

pUserQuery->SetSQL("UPDATE OBUser SET password = :password,

   eMail = :eMail WHERE userName = :userName");

updUserQuery.setSQL("UPDATE OBUser SET password = " +

":password, eMail = :eMail WHERE userName = :userName");

The following code shows a DELETE command with a parameter for the user name:

pQuery->SetSQL("DELETE FROM OBUser WHERE userName =

   :userName");

delUserQuery.setSQL("DELETE FROM OBUser WHERE userName =" +

   ":userName");

Using Stored Procedures

---

A stored procedure is a block of statements written in SQL or programmatic SQL and stored in a database. You can use stored procedures to perform any type of database operation, such as modifying, inserting, or deleting records. The use of stored procedures improves database performance by reducing the amount of information that is sent over a network.

AppLogics can call stored procedures by using the ICallableStmt interfaceIGXCallableStmt interface. The IGXCallableStmt interface provides a standard way to call stored procedures in any database server. Methods in the IGXCallableStmt interface let you

• execute a stored procedure

• pass parameter values to the stored procedure, if required

• retrieve values of the stored procedure's out parameters and return value, if any

Getting the Return Value of a Stored Function

Some stored procedures are functions with return values, and others are procedures with no return value. You run these two types of stored procedures using slightly different syntax with the setSQL( )SetSQL( ) method. When the stored procedure is a function, you can use the following syntax to get the return value of the function:

q->SetSQL("{:ret = call func(:arg1, :arg2, ...)}");

q.setSQL("{:ret = call func(:arg1, :arg2, ...)}");

When the stored procedure is not a function, or when you do not care about the return value, omit the :ret = portion of the syntax. If the return value syntax is used, the space between the return-value parameter and the equal sign is required.

Be careful to use this syntax only with functions. If the stored procedure is not defined as a function in the database, and has no return value, a runtime error occurs.

Creating a Stored Procedure

To write a stored procedure and store it in your database, you can use the techniques supported by your database software. You can also write the stored procedure from code in your application.

To create a stored procedure in code

1. Open a connection to the data source. For example:    IGXValList *conn_params;

      conn_params = GXCreateValList();

      conn_params->SetValString("DSN", "salesDB");

      conn_params->SetValString("DB", "salesDB");

      conn_params->SetValString("USER", "steve");

      conn_params->SetValString("PSWD", "pass7878");

      IGXDataConn *conn = NULL;

      HRESULT hr;

      hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL, &conn);

   IDataConn conn;

   conn = createDataConn(0, GX_DA_DAD_DRIVERS.GX_DA_DRIVER_ODBC,

   "salesDB", "salesDB", "steve", "pass7878");

2. Write a stored procedure and store it in the database using sSetSQL( ). For example:    LPSTR myStoreP;

      myStoreP =

      "create procedure myProc1 "

      "(v1land in int, v2pop out int)"

      " as"

      " begin"

      " select COUNTIES.POP into v2pop from COUNTIES"

      " where COUNTIES.LAND = v1land;"

      " end;";

      

      IGXQuery *qry = NULL;

      hr = CreateQuery(&qry);

      qry->SetSQL(myStoreP);

      

      // Run the query to store the procedure in the database

      IGXResultSet *rs = NULL;

      hr = conn->ExecuteQuery(0, qry, NULL, NULL, &rs);

   String myStoreP = "";

   myStoreP = "create procedure myProc1";

   myStoreP += "(v1land in int, v2pop out int)";

   myStoreP += " as";

   myStoreP += " begin";

   myStoreP += " select COUNTIES.POP into v2pop from COUNTIES"; myStoreP += " where COUNTIES.LAND = v1land;";

   myStoreP += "end;";

   IQuery query;

   query = createQuery();

   query.setSQL(myStoreP);

   // Run the query to store the procedure in the database

   conn_rtest.executeQuery(0,query,null,null);

Running a Stored Procedure

After writing and storing a procedure in the database, you can run it using the following steps:

To run a stored procedure

1. Open a connection to the data source. For example:    IGXValList *conn_params;

      conn_params = GXCreateValList();

      conn_params->SetValString("DSN", "salesDB");

      conn_params->SetValString("DB", "salesDB");

      conn_params->SetValString("USER", "steve");

      conn_params->SetValString("PSWD", "pass7878");

      IGXDataConn *conn = NULL;

      HRESULT hr;

      hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL, &conn);

   IDataConn conn;

   conn = createDataConn(0, GX_DA_DAD_DRIVERS.GX_DA_DRIVER_ODBC,

   "salesDB", "salesDB", "steve", "pass7878");

2. Write a pass-through database command that calls the stored procedure. In the SQL statement, use the following syntax:

   •    The call keyword to call the stored procedure.

   • The :ret keyword to get the stored function's return value, if needed.

   • If the stored procedure accepts parameters, use the following conventions: When accessing a stored procedure on Sybase or MS SQL Server, input parameter names specified in the call must be prefixed with the ampersand (&) character, for example, &param1. Other database drivers accept the ampersand, as well as, the colon (:) character. For all database drivers, input/output and output parameter names are prefixed with the colon (:) character, for example, :param2.

   The following is an example of how you call a stored procedure:

      IGXQuery *qry = NULL;

      hr = CreateQuery(&qry);

      qry->SetSQL("{:ret = call myFunction(&param1)}");

   IQuery qry = createQuery();

   qry.setSQL("{:ret = call myFunction(&param1)}");

3. Prepare a callable statement. For example:    IGXCallableStmt *s;

      HRESULT hr = conn->PrepareCall(0, qry, null, null, &s);

   ICallableStmt s;

   s = conn.prepareCall(0, qry, null, null);

4. If the stored procedure requires parameters, set up an IGXValList object with the parameters. If the stored procedure is a function, you must set the return-value parameter :ret to some initial value in this IGXValList object. For example:    IGXValList *params;

      params = GXCreateValList();

      params->SetValInt(":ret", 9999);

      params->SetValInt("&param1", 20);

   IValList params = GX.CreateValList();

   params.setValInt(":ret", 9999);

   params.setValInt("&param1", 20);

5. To execute the stored procedure, run the callable statement object's execute( )Execute( ) method. For example:    IGXResultSet *rs = NULL;

      hr = s->Execute(0, params, NULL, NULL, &rs);

   IResultSet resultSetOut = s.execute(0, params, null, null);

6. If the stored procedure's output parameters are of interest to you, use the getParams( )GetParams( ) method to retrieve them. For example:    IGXValList *paramsOut = NULL;

      hr = s->GetParams(0, &paramsOut);

   IValList paramsOut = s.getParams(0);

   For Informix stored procedures, output parameters are returned in the result set that execute( )Execute( ) returns.

7. Free the resources used. For example:    qry->Release();

      s->Release();

      params->Release();

      paramsOut->Release();

      resultSetOut->Release();

Supported Stored Procedure Operations

Different databases provide different support for stored procedure operations, such as retrieving output parameters, and getting the return value of a stored function. Similarly, the iPlanet Application Server supports some of these operations differently for different databases.

The following table lists some of the common operations and indicates if the operation is supported for each database. If an operation is not supported, either the database does not provide the support or the iPlanet Application Server does not.

Operation	Oracle	Informix	DB2	Sybase & MS SQL Server
Retrieve output parameters using getParams( )GetParams( )	Yes	No	Yes	Yes
Retrieve output parameters through a result set returned by execute( )Execute( )	No	Yes	Yes	Yes
Retrieve multiple result sets	No	No	No	No
Retrieve a single return value from a stored function	Yes	No	No	No
Get the stored procedure's code execution status	Yes	Yes	Yes	Yes
Get the error code returned by the stored procedure	Yes	Yes	Yes	Yes

Note that the following types of DB2 stored procedures cannot be executed:

• Where OUT parameters come before all IN parameters

• Where INOUT parameters come before OUT parameters

The stored procedures need to pass parameters in the correct sequence. To preserve preexisting stored procedures, write wrapper stored procedures that take these parameters in the correct order. Note that this parameter sequence problem is not seen with MS SQL server, but can happen with the other ODBC-compliant data sources.

Sample Stored Procedure

The following code writes and runswrites, runs, and removes a stored procedure:

IGXValList *conn_params;

conn_params = GXCreateValList();

conn_params->SetValString("DSN", "salesDB");

conn_params->SetValString("DB", "salesDB");

conn_params->SetValString("USER", "steve");

conn_params->SetValString("PSWD", "pass7878");

IGXDataConn *conn = NULL;

HRESULT hr;

hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL,

   &conn);

LPSTR myStoreP;

myStoreP =

"create procedure myProc1 "

"(v1land in int, v2pop out int)"

" as"

" begin"

" select COUNTIES.POP into v2pop from COUNTIES"

" where COUNTIES.LAND = v1land;"

" end;";

IGXQuery *qry = NULL;

hr = CreateQuery(&qry);

qry->SetSQL(myStoreP);

// Run qry to store the procedure in the database

IGXResultSet *rs = NULL;

hr = conn->ExecuteQuery(0, qry, NULL, NULL, &rs);

rs->Release();

qry->Release();

conn->Release();

conn_params->Release();

// Now write a new query to run the stored procedure

IGXQuery *qry = NULL;

hr = CreateQuery(&qry);

qry->SetSQL("{:ret = call myFunction(&param1)}");

IGXCallableStmt *s = NULL;

hr = conn->PrepareCall(0, qry, NULL, NULL, &s);

// Set up parameters for the stored procedure

IGXValList *params;

params = GXCreateValList();

params->SetValInt(":ret", 9999);

params->SetValInt("&param1", 20);

// Run the stored procedure

IGXResultSet *rs = NULL;

hr = s->Execute(0, params, NULL, NULL, &rs);

// This output vallist contains the output values.

IGXValList *paramsOut = NULL;

hr = s->GetParams(0, &paramsOut);// More code to use the results of the procedure ...

qry->Release();

s->Release();

params->Release();

paramsOut->Release();

resultSetOut->Release();

try{

   IValList outParams = GX.CreateValList();

   IValList inParams = GX.CreateValList();

   ICallableStmt myStmt = null;

   String firstVal = "";

   String secondVal = "";

   // Create the database connection(s).

   IDataConn conn_rtest = createDataConn(0,

      GX_DA_DAD_DRIVERS.GX_DA_DRIVER_ODBC,

      /* Datasource name */ "ksample",

      /* Database name */ "",

      /* userName */ "kdemo",

      /* password */ "kdemo");

   if (conn_rtest == null)

   {

      result("<HTML><BODY>Unable to create connection." +

         "</BODY></HTML>");

      return -1;

   }

   //Write the stored procedure

   String myStoreP = "";

   myStoreP = "create procedure myProc1";

   myStoreP += "(v1land in int, v2pop out int)";

   myStoreP += " as";

   myStoreP += " begin";

   myStoreP += " select COUNTIES.POP into v2pop from " +

      "COUNTIES where COUNTIES.LAND = v1land;";

   myStoreP += "end;";

   IQuery query;

   query = createQuery();

   query.setSQL(myStoreP);

   conn_rtest.executeQuery(0,query,null,null);

   //Write command to call stored procedure

   String theProc = "{call myProc1(&p1, :p2)}";

   IQuery myquery;

   myquery = createQuery();

   myquery.setSQL(theProc);

   //Prepare the callable statement for execution

   myStmt = conn_rtest.prepareCall(0, myquery, null, null);

   //Set the in parameter value

   inParams.setValInt("&p1",6210603);

   //Set the out parameter value to any integer

   inParams.setValInt(":p2", 0);

   //Run the callable statement

   myStmt.execute(0, inParams, null, null);

   //Get the stored procedure's output value

   outParams = myStmt.getParams(0);

   //Display the results

   String result = "";

   String key = "";

   while((key = outParams.getNextKey())!= null)

   {

      result += "the value " + outParams.getVal(key);

      log(" the value==> "+ firstVal);

   }

   result("<html><body>the result :"+result+"</body>" +

      "</html>");

   //Drop stored procedure

   IQuery qrydrop = createQuery();

   qrydrop.setSQL("drop procedure myProc1");

   conn_rtest.executeQuery(0, qrydrop, null, null);

}

catch(Exception e){result("my program");}

Using Triggers

---

A trigger is a stored block of SQL or programmatic SQL statements with the following characteristics:

• It is associated with a table.

• It runs in response to an INSERT, UPDATE, or DELETE operation.

• It runs only under certain specified conditions.

For example, you can set a trigger that runs whenever an UPDATE command is executed in a particular table, with the additional condition that the data being written into a certain field is NULL. If the user attempts to insert a NULL value in the field, the trigger runs and displays an error message or takes other remedial action.

Creating a Trigger

For each trigger, you specify the following characteristics:

• The database table with which it is associated

• The name of the trigger

• The condition that determines when the trigger is executed

• Which type of command activates the trigger (INSERT, UPDATE, or DELETE)

• What action occurs when the trigger is activated (specified in SQL)

To create a trigger

1. Open a connection to a data source. For example:    IGXValList *conn_params;

      conn_params = GXCreateValList();

      conn_params->SetValString("DSN", "salesDB");

      conn_params->SetValString("DB", "salesDB");

      conn_params->SetValString("USER", "steve");

      conn_params->SetValString("PSWD", "pass7878");

      IGXDataConn *conn = NULL;

      HRESULT hr;

      hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL,

         &conn);

   IDataConn conn;

   conn = createDataConn(0, GX_DA_DAD_DRIVERS.GX_DA_DRIVER_ODBC,

      "personnelDB", "personnelDB", "sandra", "pass7878");

2. Set up the trigger by calling createTrigger( )CreateTrigger( ). For example:    hr = conn->CreateTrigger("employees", "ProcessNew",

   "FOR EACH ROW WHEN(title='Director')",

   "AFTER INSERT", sqlString);

   conn.createTrigger("employees", "ProcessNew",

   "FOR EACH ROW WHEN(title='Director')",

   "AFTER INSERT", sqlString;

3. If your database requires it, call enableTrigger( )EnableTrigger( ). For example: conn->EnableTrigger("employees", "ProcessNew");

   conn.enableTrigger("employees", "ProcessNew");

Disabling and Enabling Triggers

To temporarily stop the trigger from executing, call disableTrigger( )DisableTrigger( ). To re-enable the trigger, call enableTrigger( )EnableTrigger( ). For example:

conn->DisableTrigger("employees", "ProcessNew");

// Perform tasks without risk of executing the trigger.

// ...

conn->EnableTrigger("employees", "ProcessNew");

conn->Release();

conn.disableTrigger("employees", "ProcessNew");

// Perform tasks without risk of executing the trigger

// ...

conn.enableTrigger("employees", "ProcessNew");

In some cases the enable and disable commands might enable or disable all triggers that are defined on a certain table, not just the named trigger. For example, Oracle databases behave in this manner.

Deleting a Trigger

To remove a trigger from the database permanently, call dropTrigger( )DropTrigger( ). For example:

conn->DropTrigger("employees", "ProcessNew");

conn.dropTrigger("employees", "ProcessNew");

Using Sequences

---

A sequence is a sequential number generator which exists in a database. Some database vendors refer to a sequence as a serial, identity, or autoincrement.

A sequence is useful for generating transaction-safe numbers for database transaction applications. A single application can use several sequences to generate incremental numbers for various purposes. In some cases, you use sequences to generate numbers that are guaranteed to be unique, rather than being concerned with the order of the numbers as such.

For example, you might have an online catalog application through which customers can purchase products. When customers access the application for the first time, you assign each customer a unique, incremental ID number. You can use a sequence to generate this number. In addition, you might want to generate a unique, incremental purchase order number for each customer order. You would create another sequence to generate this number.

iPlanet Application Server supports creating and using sequences with applications through the following parts of the iPlanet Application Server Foundation Class Library:

• ISequence interfaceIGXSequence interface

• Sequence methods in the IDataConn interfaceIGXDataConn interface

Creating a New Sequence

For each sequence, you specify the following characteristics:

• A name

• The corresponding column in the database (if the database implements sequences as autoincrement fields, rather than as separate objects)

• Starting value

• Increment interval

• Additional database-specific options, if any

To create a new sequence

1. Open a connection to the data source. For example:    IGXValList *conn_params;

      conn_params = GXCreateValList();

      conn_params->SetValString("DSN", "salesDB");

      conn_params->SetValString("DB", "salesDB");

      conn_params->SetValString("USER", "steve");

      conn_params->SetValString("PSWD", "pass7878");

      IGXDataConn *conn = NULL;

      HRESULT hr;

      hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL,

         &conn);

   IDataConn conn;

   conn = createDataConn(0, GX_DA_DAD_DRIVERS.GX_DA_DRIVER_ODBC,

      "salesDB", "salesDB", "steve", "pass7878");

2. Set up the sequence by calling createSequence( )CreateSequence( ). For example:    IGXSequenceMgr *seqmgr;

      hr = conn->QueryInterface(IID_IGXSequenceMgr,

         (LPVOID *) &seqmgr);

      IGXSequence *seq = NULL;

      hr = seqmgr->CreateSequence("mySeq", "orders.ID", 100, 1,

         NULL, &seq);

   ISequence seq;

   seq = ((ISequenceMgr)conn).createSequence("mySeq",

      "orders.ID", 100, 1, null)

   In databases that do not support autoincrement fields, the second parameter to cCreateSequence( ) is null. For example, in an Oracle database, a sequence is implemented as an object rather than as a field in a table.

3. To start the sequence, call getNext( )GetNext( ). For example:    DWORD seqVal = 0;

      hr = seq->GetNext(&seqVal);

   int seqVal;

   seqVal = seq.getNext();

4. Use the sequence number to perform the task for which you created it. For example, use the sequence in an INSERT statement:    IGXQuery *qry;

      CreateQuery(&qry);

      char tmp[512];

      sprintf(tmp, "INSERT into orders (ID) values (%d), (cust)"

         "values (%s)",

         seqVal,

         custName);

      qry->SetSQL(tmp);

   IQuery qry = createQuery();

   qry.setSQL("INSERT into orders (ID) values ("+ seqVal +")," +

   "(cust) values ("+ custName +")");

Using An Existing Sequence

After setting up a sequence, you can get access to it as follows.

To use an existing sequence

1. Open a connection to the data source. For example:    IGXValList *conn_params;

      conn_params = GXCreateValList();

      conn_params->SetValString("DSN", "salesDB");

      conn_params->SetValString("DB", "salesDB");

      conn_params->SetValString("USER", "steve");

      conn_params->SetValString("PSWD", "pass7878");

      IGXDataConn *conn = NULL;

      HRESULT hr;

      hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL,

         &conn);

   IDataConn conn;

   conn = createDataConn(0, GX_DA_DAD_DRIVERS.GX_DA_DRIVER_ODBC,

      "salesDB", "salesDB", "steve", "pass7878");

2. Retrieve a reference to the sequence by calling getSequence( )GetSequence( ) in the ISequenceMgr interfaceIGXSequenceMgr interface. For example:    IGXSequenceMgr *seqmgr;

      hr = conn->QueryInterface(IID_IGXSequenceMgr,

         (LPVOID *) &seqmgr);

      IGXSequence *seq = NULL;

      hr = seqmgr->GetSequence("mySeq", "orders.ID", &seq);

   ISequence seq = ((ISequenceMgr)conn).getSequence(

                     "mySeq","orders.ID");

3. If you need to find out the current value of the sequence for any reason, call getCurrent( )GetCurrent( ). For example:    int seqVal = seq->GetCurrent();

   int seqVal = seq.getCurrent();

4. To generate the next number in the sequence, call getNext( )GetNext( ). For example:    int seqVal = seq->GetNext();

   int seqVal;

   seqVal = seq.getNext();

5. Use the sequence number to perform the task for which you created it. For example, use the sequence in an INSERT statement:    IGXQuery *qry;

      CreateQuery(&qry);

      char tmp[512];

      sprintf(tmp, "INSERT into orders (ID) values (%d), (cust)"

         "values (%s)",

         seqVal,

         custName);

      qry->SetSQL(tmp);

   IQuery qry = createQuery();

   qry.setSQL("INSERT into orders (ID) values ("+ seqVal +")," +

   "(cust) values ("+ custName +")");

Deleting a Sequence

To permanently remove a sequence from the database, call the drop( )Drop( ) method in the ISequence interfaceIGXSequence interface. However, you should exercise caution when using this method. If the database implements the sequence as a field in a table, the call to dDrop( ) will delete the entire table, not just the sequence field. If the database implements the sequence as an object, as does Oracle for example, the call to dDrop( ) deletes only the sequence object.

Typically, once you start a sequence there is no reason to delete it. The sequence is normally used to create a permanent, unique numbering system for data in a database. However, you might use dDrop( ) if you are using the sequence mechanism to generate unique sequential numbers for some short-lived programmatic purpose.

Managing Database Transactions

---

A database transaction is a set of database commands that succeed or fail as a group. The necessity of grouping commands into a transaction is determined by business logic. For example, when a bank customer moves money from a savings account to a checking account, two operations are involved:

• Deduct the money from the savings account.

• Add the money to the checking account.

If one of these operations is performed without the other, the transaction is not complete and the accounts will not balance. Both operations must succeed for the entire transaction to be correct.

An iPlanet Application Server application can process several transactions simultaneously. Each transaction works with one or more different database connection objects. Each transaction is made up of several method calls, each of which runs a database command. Each method call is associated with its own connection object, so that the transaction can include commands on more than one database.

Transactions are supported through the IGXTrans interface.

Setting Up a Transaction

A transaction is represented by a transaction object, which is passed to several database commands. The commands in a transaction are united by the fact that they all have the same transaction object as a parameter.

To group several database commands into a transaction

1. Open one or more connections to data sources.

2. Instantiate a transaction object. For example:    IGXTrans *pTx=NULL;

      hr=CreateTrans(&pTx);

   ITrans transferTrans = createTrans();

3. Start the transaction by calling begin( )Begin( ). For example:    pTx->Begin();

   transferTrans.begin();

4. Call the methods that are involved in the transaction. To identify which transaction the commands belong to, you pass the transaction object as a parameter to each method. For example:    // Set up an INSERT command ...

      // Then call AddRow()

      pTable->AddRow(0, pTx);

      // Set up another INSERT command ...

      // Then call AddRow()

      pTable->AddRow(0, pTx);

   // Set up an INSERT command ...

   // Then call addRow()

   rc = table.addRow(0, transferTrans);

   // Set up another INSERT command ...

   // Then call addRow()

   rc = table.addRow(0, transferTrans);

5. To make the changes to the database permanent, call commit( )Commit( ). For example:    pTx->Commit(0, NULL);

      pTx->Release();

   transferTrans.commit(0);

Example
The following code, from the Transfer AppLogic in the Online Bank sample application, sets up a transaction that transfers funds between accounts. The first command in the transaction withdraws funds from one account, and the second commands adds the funds to another account.

// Create a transaction

IGXTrans *pTx=NULL;

hr=CreateTrans(&pTx);

pTx->Begin();

// Allocate a new row for the withdrawal half of the

// transaction

pTable->AllocRow();

pTable->SetValueString(acctNumCol, pFromAcct);

pTable->SetValueInt(transTypeCol, OB_TRANSTYPE_WITHDRAWAL);

pTable->SetValueDateString(postDateCol, dateStr);

pTable->SetValueDouble(amountCol, amount*-1.0);

// Add the row using the transaction

if(pTable->AddRow(0, pTx)==GXE_SUCCESS) {

   // Allocate a new row for the deposit half of the

   //transaction

   pTable->AllocRow();

   pTable->SetValueString(acctNumCol, pToAcct);

   pTable->SetValueInt(transTypeCol, OB_TRANSTYPE_DEPOSIT);

   pTable->SetValueDateString(postDateCol, dateStr);

   pTable->SetValueDouble(amountCol, amount);

   // Add the second row using the transaction

   if(pTable->AddRow(0, pTx)==GXE_SUCCESS)

      // If both commands succeeded, commit all changes

      pTx->Commit(0, NULL);

   else {

   // If the deposit command failed, roll back

      pTx->Rollback();

      HandleOBSystemError("Could not insert transaction");

   }

else {

// If the withdrawal command failed, roll back

   pTx->Rollback();

   HandleOBSystemError("Could not insert transaction");

}

pTx->Release();

ITrans transferTrans = createTrans();

transferTrans.begin();

int rc;

// Allocate row in table for withdrawal portion of

// transaction

table.allocRow();

table.setValueString(acctNumCol, fromAcct);

table.setValueInt(transTypeCol,

   OBDBDefs.TRANSTYPE_WITHDRAWAL);

table.setValueDateString(postDateCol, transDateString);

table.setValueDouble(amountCol, amount.doubleValue() * -1.0);

// Add the first row, using the transaction

rc = table.addRow(0, transferTrans);

//**

//* If addition of row fails, roll back transaction

//*

if (rc != 0)

   transferTrans.rollback();

// Allocate row in table for deposit portion of transaction

table.allocRow();

table.setValueString(acctNumCol, toAcct);

table.setValueInt(transTypeCol, OBDBDefs.TRANSTYPE_DEPOSIT);

table.setValueDateString(postDateCol, transDateString);

table.setValueDouble(amountCol, amount.doubleValue());

// Add the second row using the transaction

rc = table.addRow(0, transferTrans);

// If addition of row fails, roll back transaction

if (rc != 0)

   transferTrans.rollback();

// If both parts of transaction succeeded, commit transaction

transferTrans.commit(0);

Committing a Transaction

When a transaction is committed, all the database commands in the transaction are finalized and changes are saved in the database. The transaction overwrites or deletes the data that was in the database previously and was affected by the commands in the transaction.

To commit a transaction, call the commit( )Commit( ) method. For example:

IGXTrans *trx;

CreateTrans(&trx);

// ... series of data operations ...

trx->Commit(0, NULL);

trx->Release();

ITrans trx;

trx = createTrans();

//. . . series of data operations . . .

trx.commit();

Rolling Back aTransaction

When a transaction is rolled back, all the database commands in the transaction are discarded, and any changes are abandoned. The actual data stored in the database remains unchanged by any of the commands in the transaction.

If a database server is interrupted in the middle of a transaction, such as by a power outage, all uncompleted transactions are automatically rolled back by the database engine. You can also roll back a transaction programmatically if you want to abandon the changes that were proposed by the commands in the transaction.

To roll back a transaction, call the rollback( )Rollback( ) method.

Example
The following code rolls back a transaction if a test condition is not met:

IGXTrans *trx;

CreateTrans(&trx);

// ... series of data operations ...

if (testCondition == 0)

   trx->Commit(0, NULL);

else

   trx->Rollback();

trx->Release();

ITrans trx;

trx = createTrans();

//. . . series of data operations . . .

if (testCondition == 0)

trx.commit();

else

trx.rollback();

Using Java Database Connectivity (JDBC)

---

You can call iPlanet Application Server services using JDBC method calls. Both iPlanet Application Server Foundation Class Library methods and JDBC methods can be used interchangeably to access the same underlying objects in the iPlanet Application Server.

JDBC is an ODBC-like database connectivity API developed to allow access to SQL databases through Java. The JDBC API was developed by JavaSoft and is quickly becoming the standard API for web-based database access through Java.

iPlanet Application Server supports all JDBC types and objects, and supports the core methods. To see whether a particular method is supported, consult the table at the end of this section. If your code calls a JDBC method that is not supported by iPlanet Application Server, the method returns failure.

The following interfaces and methods are supported according to the JDBC 1.20 specification:

Interface	Methods and Variables Supported
CallableStatement	Methods: getBigDecimal(int, int), getBoolean(int), getByte(int), getBytes(int), getDate(int), getDouble(int), getFloat(int), getInt(int), getLong(int), getObject(int), getShort(int), getString(int), getTime(int), getTimestamp(int), registerOutParameter(int, int), registerOutParameter(int, int, int), wasNull( )
Connection	Methods: close( ), commit( ), createStatement( ), getAutoCommit( ), getCatalog( ), getMetaData( ), getTransactionIsolation( ), isClosed( ), prepareCall(String), prepareStatement(String), rollback( ), setAutoCommit(boolean), setCatalog(String), setTransactionIsolation(int) Variables: TRANSACTION_NONE, TRANSACTION_READ_COMMITTED, TRANSACTION_READ_UNCOMMITTED, TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE
DatabaseMetaData	All
Driver	Methods: acceptsURL(String), connect(String, Properties), getMajorVersion( ), getMinorVersion( ), getPropertyInfo(String, Properties), jdbcCompliant( )
PreparedStatement	Methods: clearParameters( ), executeQuery( ), executeUpdate( ), setBigDecimal(int, BigDecimal), setBoolean(int, boolean), setByte(int, byte), setBytes(int, byte[]), setDate(int, Date), setDouble(int, double), setFloat(int, float), setInt(int, int), setLong(int, long), setNull(int, int), setObject(int, Object), setObject(int, Object, int), setObject(int, Object, int, int), setShort(int, short), setString(int, String), setTime(int, Time), setTimestamp(int, Timestamp)
ResultSet	Methods: close( ), findColumn(String), getBigDecimal(int, int), getBigDecimal(String, int), getBoolean(int), getBoolean(String), getByte(int), getByte(String), getBytes(int), getBytes(String), getCursorName( ), getDate(int), getDate(String), getDouble(int), getDouble(String), getFloat(int), getFloat(String), getInt(int), getInt(String), getLong(int), getLong(String), getMetaData( ), getObject(int), getObject(String), getShort(int), getShort(String), getString(int), getString(String), getTime(int), getTime(String), getTimestamp(int), getTimestamp(String), next( ), wasNull( )
ResultSetMetaData	Methods: getCatalogName(int), getColumnCount( ), getColumnDisplaySize(int), getColumnLabel(int), getColumnName(int), getColumnType(int), getColumnTypeName(int), getPrecision(int), getScale(int), getSchemaName(int), getTableName(int), isAutoIncrement(int), isCaseSensitive(int), isCurrency(int), isNullable(int), isSigned(int) Variables: columnNoNulls, columnNullable, columnNullableUnknown
Statement	Methods: close( ), executeQuery(String), executeUpdate(String), getMaxFieldSize( ), getMaxRows( ), getQueryTimeout( ), setCursorName(String), setMaxFieldSize(int), setMaxRows(int), setQueryTimeout(int)

Using JDBC URLs to Connect NAS to a Database

Use the following JDBC URL syntax to connect NAS to a database:

jdbc:kiva:[DRIVER[:DSN]]

Note that additional properties can be passed to the Driver.connect() call in a java.util.Properties argument. The above JDBC URL syntax is a name-value pair list with the following possible keys:

• DRIVER: the name of the driver constant. For example, to use GX_DA_DRIVER_INFORMIX_CLI as the driver, specify this part of the URL ("DRIVER") as INFORMIX_CLI. Note that this key information is always capitalized.

• DSN: the datasource name exactly as it would appear in the createDataConn() call.

• DB: the database name exactly as it would appear in the createDataConn() call.

• USER: the username to connect to the database.

• PSWD: the password to connect to the database. Note that the DRIVER and DSN specified in the URL always take precedence over the DRIVER and DSN keys specified in the properties list.

For OCL JDBC connections, use the following keys:

• ocl_driver: if this key is set to "true" in the properties list, KES assumes that an OCL client connection is being made rather than a NAS server-side connection.

• ocl_flags: the stringified flags that will be passed to GX.CreateClient() call.

• ocl_uri: the URI passed into OCL's createConnection() call.

• ocl_USER: the OCL client login name.

• ocl_PSWD: the OCL client login password.

Registering an AppLogic with a JDBC Driver

The following sequence must be used for NAS to successfully register an AppLogic with the JDBC driver:

Applogic al;

Connection conn;

java.util.Properties props;

al = this;

drv = new com.kivasoft.jdbc.Driver(al);

/* Set up property list of connection info */

....

conn = drv.connect(url, props);