| Previous Contents Index DocHome Next |
| iPlanet Application Server 6.0 Programmer's Guide (C++) |
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
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
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:
Supported Databases
iPlanet Application Server supports the following 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
Check the user's security level to see whether they have access to the tables and other database objects that the application will reference.
Open a database connection.
- For more information, see , "Writing Secure Applications."
Write the database command (an insert, update, delete, or query operation).
- For more information, see Opening a Database Connection.
Run the command.
- Several sections in this chapter describe in detail how to write database commands.
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 IGXDataConn 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( ) method in the GXAppLogic class. This method instantiates a data connection object. The connection parameters are placed in an IGXValList which is then passed as a parameter to CreateDataConn( ). For example:loginParams = GXCreateValList();
loginParams->SetValString("DSN", "salesDB");
loginParams->SetValString("DB", "salesDB");
loginParams->SetValString("USER", "steve");
loginParams->SetValString("PSWD", "pass7878");
CreateDataConn(0,GX_DA_DRIVER_ODBC,
In this example, the connection uses the following connection parameters:
Specifying Connection Parameters
In the CreateDataConn( ) 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.
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( ). For example:
HRESULT hr = CreateDataConn(0,GX_DA_DRIVER_ODBC,
HRESULT hr = CreateDataConn(0,GX_DA_DRIVER_ODBC,
HRESULT hr = CreateDataConn(0,GX_DA_DRIVER_ODBC,
// Code using the three connections...
// Finished using connection 1
// Continue using connections 2 and 3...
// Release them also when finished.
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( ) method in the IGXTable 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.For a flat result set, use the GetColumnByOrd( ) method in the IGXResultSet interface.
For a hierarchical result set, use the GetColumnByOrd( ) method in the IGXHierResultSet interface.
Alternatively, to walk through all the available columns, call 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( ). Before the start of the loop, call 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 IGXColumn interface: GetName( ), GetNullsAllowed( ), GetPrecision( ), GetScale( ), GetSize( ), and 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();
hr = table->EnumColumns(&column);
column->GetName(buffer, sizeof(buffer));
StreamResult("Column Name = ");
sprintf(buffer, "Column Type = %d", type);
// No more columns, exit loop.
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.
To insert a row by calling a series of methodsWrite a SQL INSERT statement and pass it to the 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 Using Pass-Through Database Commands.
Open a connection to a data source.
Get a table in the database by calling GetTable( ).
Create a temporary buffer for data by calling AllocRow( ).
Set the values of columns by calling the SetValue**( ) methods of the IGXTable interface. You must refer to columns by their ordinal position in the table, using GetColumnOrdinal( ) if necessary.
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:hr=pConn->GetTable("OBTransaction",
&pTable))==GXE_SUCCESS)&&pTable);
// Look up the column ordinals for the table
ULONG transTypeCol=0; pTable->GetColumnOrdinal("transType",
ULONG postDateCol=0; pTable->GetColumnOrdinal("postDate",
ULONG acctNumCol=0; pTable->GetColumnOrdinal("acctNum",
ULONG amountCol=0; pTable->GetColumnOrdinal("amount",
// Allocate a new row for the withdrawal
pTable->SetValueString(acctNumCol, pFromAcct);
pTable->SetValueInt(transTypeCol, OB_TRANSTYPE_WITHDRAWAL);
pTable->SetValueDateString(postDateCol, dateStr);
pTable->SetValueDouble(amountCol, amount*-1.0);
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.
To update table rows by calling a series of methodsWrite a SQL UPDATE statement and pass it to the 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 Using Pass-Through Database Commands.
Open a connection to a data source.
Get a table in the database by calling GetTable( ).
Create a temporary buffer for data by calling AllocRow( ).
Set the values of the columns you want to modify by calling SetValue**( ) methods of the IGXTable interface. You can refer to columns by name or by column number.
Specify which row(s) to update and actually update them by calling 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:conn_params = GXCreateValList();
conn_params->SetValString("DSN", "salesDB");
conn_params->SetValString("DB", "salesDB");
conn_params->SetValString("USER", "steve");
conn_params->SetValString("PSWD", "pass7878");
hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL,
hr = conn->GetTable("employees", &table);
// Set the new values in the row
table->GetColumnOrdinal("region", &col);
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.
To delete rows by calling a series of methodsWrite 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 Using Pass-Through Database Commands.
Open a connection to a data source.
Get a table in the database by calling GetTable( ).
Specify which row(s) to delete and actually remove them by calling 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:conn_params = GXCreateValList();
conn_params->SetValString("DSN", "salesDB");
conn_params->SetValString("DB", "salesDB");
conn_params->SetValString("USER", "steve");
conn_params->SetValString("PSWD", "pass7878");
hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL,
hr = conn->GetTable("employees", &table);
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
Instantiate an IGXQuery interface object.
To run a pass-through command
Write a SQL statement and pass it as a parameter to a 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 (;).
- Query objects are used for all types of pass-through database commands, not just queries.
- The statement must comply with the requirements of the database driver. For more information about this syntax, refer to your driver or SQL documentation.
Open a connection to a data source.
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:
Call the ExecuteQuery( ) method in the IGXDataConn interface. This method runs all types of database commands that are specified using SetSQL( ), not just queries. For example:
Examples
The following code passes a flat query (SELECT statement) to SetSQL( ):sprintf(sqlStr, "SELECT * FROM OBUser, OBCustomer WHERE OBUser.userName = OBCustomer.userName AND OBCustomer.ssn = '%s'", pSsn);
The following code passes an INSERT command to SetSQL( ):
pUserQuery->SetSQL("INSERT INTO OBUser(userName, password,
userType, eMail) VALUES (:userName, :password, :userType,
The following code passes an UPDATE command to SetSQL( ):
pUserQuery->SetSQL("UPDATE OBUser SET password = :password,
eMail = :eMail WHERE userName = :userName");
The following code passes a DELETE command to SetSQL( ):
pQuery->SetSQL("DELETE FROM OBUser WHERE 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 IGXPreparedQuery 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:
Open a connection to a data source.
Note that, when Sybase prepared statements are executed, either an empty result set is returned (through the Execute( ) method of IGXCallableStmt) 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.Pre-compile the command by declaring a variable of type IGXPreparedQuery and calling the PrepareQuery( ) method in the IGXDataConn 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);
If the command contains parameters, instantiate an IGXValList object and use SetValString( ) to set the parameter values you want to pass into the command. For example:
IGXValList *pList=GXCreateValList();
GXSetValListString(pList, ":userName", pUserName);
Run the command by calling the prepared query object's Execute( ) method. Pass the IGXValList object, if any, as a parameter. For example:
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( ).
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( ). You cannot use parameters when using method calls such as AddRow( ) and 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 Using Prepared Database Commands. 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.
You can include up to 1,024 parameters in a single database command, unless your database software allows fewer parameters than this.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.
Examples
Suppose the code for a DELETE command contains the following line: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");
The same IGXValList object could be used for the following DELETE command:
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.
LPSTR userName=GXGetValListString(m_pValIn, "userName");
LPSTR password=GXGetValListString(m_pValIn, "password");
pQuery->SetTables("OBUser, OBCustomer");
pQuery->SetFields("OBUser.userName, userType, ssn, lastName,
pQuery->SetWhere("OBUser.userName *= OBCustomer.userName AND
OBUser.userName= :userName AND password= :password");
IGXPreparedQuery *pPrepQuery=NULL;
hr=pConn->PrepareQuery(0, pQuery, NULL, NULL,
IGXValList *pList=GXCreateValList();
GXSetValListString(pList, ":userName", userName);
GXSetValListString(pList, ":password", password);
IGXResultSet *pResultSet=NULL;
hr=pPrepQuery->Execute(0, pList, 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.
For more information about the items referred to in this list, see your SQL documentation.Operand of a unary + or - 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.
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( ) method.
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 , "Types of Queries."
Example
The following code shows a flat query with one parameter, :1, which is a placeholder for a minimum salary value: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( ) method. This is the only technique in which you can use parameters.
Use a series of method calls. You cannot use parameters with this technique.
- For more information, see Using Pass-Through Database Commands.
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,
The following code shows an UPDATE command with three parameters:
pUserQuery->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 =
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 IGXCallableStmt 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( ) 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, ...)}");
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
Open a connection to the data source. For example:
conn_params = GXCreateValList();
conn_params->SetValString("DSN", "salesDB");
conn_params->SetValString("DB", "salesDB");
conn_params->SetValString("USER", "steve");
conn_params->SetValString("PSWD", "pass7878");
hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL, &conn);
Write a stored procedure and store it in the database using SetSQL( ). For example:
"(v1land in int, v2pop out int)"
" select COUNTIES.POP into v2pop from COUNTIES"
" where COUNTIES.LAND = v1land;"
Running a Stored Procedure
After writing and storing a procedure in the database, you can run it using the following steps:
Open a connection to the data source. For example:
conn_params = GXCreateValList();
conn_params->SetValString("DSN", "salesDB");
conn_params->SetValString("DB", "salesDB");
conn_params->SetValString("USER", "steve");
conn_params->SetValString("PSWD", "pass7878");
hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL, &conn);
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, ¶m1. 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.
Prepare a callable statement. For example:
- The following is an example of how you call a stored procedure:
qry->SetSQL("{:ret = call myFunction(¶m1)}");
HRESULT hr = conn->PrepareCall(0, qry, null, null, &s);
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:
params->SetValInt(":ret", 9999);
params->SetValInt("¶m1", 20);
To execute the stored procedure, run the callable statement object's Execute( ) method. For example:
hr = s->Execute(0, params, NULL, NULL, &rs);
If the stored procedure's output parameters are of interest to you, use the GetParams( ) method to retrieve them. For example:
hr = s->GetParams(0, ¶msOut);
Free the resources used. For example:
- For Informix stored procedures, output parameters are returned in the result set that Execute( ) returns.
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.
Retrieve output parameters through a result set returned by Execute( )
Note that the following types of DB2 stored procedures cannot be executed:
Where OUT parameters come before all IN 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 runs a stored procedure:conn_params = GXCreateValList();
conn_params->SetValString("DSN", "salesDB");
conn_params->SetValString("DB", "salesDB");
conn_params->SetValString("USER", "steve");
conn_params->SetValString("PSWD", "pass7878");
hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL,
"(v1land in int, v2pop out int)"
" select COUNTIES.POP into v2pop from COUNTIES"
" where COUNTIES.LAND = v1land;"
// Run qry to store the procedure in the database
hr = conn->ExecuteQuery(0, qry, NULL, NULL, &rs);
// Now write a new query to run the stored procedure
qry->SetSQL("{:ret = call myFunction(¶m1)}");
hr = conn->PrepareCall(0, qry, NULL, NULL, &s);
// Set up parameters for the stored procedure
params->SetValInt(":ret", 9999);
params->SetValInt("¶m1", 20);
hr = s->Execute(0, params, NULL, NULL, &rs);
// This output vallist contains the output values.
hr = s->GetParams(0, ¶msOut);// More code to use the results of the procedure ...
Using Triggers
A trigger is a stored block of SQL or programmatic SQL statements with the following characteristics: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
To create a triggerThe 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)
Open a connection to a data source. For example:
conn_params = GXCreateValList();
conn_params->SetValString("DSN", "salesDB");
conn_params->SetValString("DB", "salesDB");
conn_params->SetValString("USER", "steve");
conn_params->SetValString("PSWD", "pass7878");
hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL,
Set up the trigger by calling CreateTrigger( ). For example:
hr = conn->CreateTrigger("employees", "ProcessNew",
"FOR EACH ROW WHEN(title='Director')",
If your database requires it, call EnableTrigger( ). For example:
Disabling and Enabling Triggers
To temporarily stop the trigger from executing, call DisableTrigger( ). To re-enable the trigger, call EnableTrigger( ). For example: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( ). For example: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:
Creating a New Sequence
For each sequence, you specify the following characteristics:
A name
To create a new sequenceThe corresponding column in the database (if the database implements sequences as autoincrement fields, rather than as separate objects)
Open a connection to the data source. For example:
conn_params = GXCreateValList();
conn_params->SetValString("DSN", "salesDB");
conn_params->SetValString("DB", "salesDB");
conn_params->SetValString("USER", "steve");
conn_params->SetValString("PSWD", "pass7878");
hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL,
Set up the sequence by calling CreateSequence( ). For example:
hr = conn->QueryInterface(IID_IGXSequenceMgr,
hr = seqmgr->CreateSequence("mySeq", "orders.ID", 100, 1,
To start the sequence, call GetNext( ). For example:
- In databases that do not support autoincrement fields, the second parameter to CreateSequence( ) is null. For example, in an Oracle database, a sequence is implemented as an object rather than as a field in a table.
Use the sequence number to perform the task for which you created it. For example, use the sequence in an INSERT statement:
Using An Existing Sequence
After setting up a sequence, you can get access to it as follows.
Open a connection to the data source. For example:
conn_params = GXCreateValList();
conn_params->SetValString("DSN", "salesDB");
conn_params->SetValString("DB", "salesDB");
conn_params->SetValString("USER", "steve");
conn_params->SetValString("PSWD", "pass7878");
hr = CreateDataConn(0, GX_DA_DRIVER_ODBC, conn_params, NULL,
Retrieve a reference to the sequence by calling GetSequence( ) in the IGXSequenceMgr interface. For example:
hr = conn->QueryInterface(IID_IGXSequenceMgr,
hr = seqmgr->GetSequence("mySeq", "orders.ID", &seq);
If you need to find out the current value of the sequence for any reason, call GetCurrent( ). For example:
int seqVal = seq->GetCurrent();
To generate the next number in the sequence, call GetNext( ). For example:
Use the sequence number to perform the task for which you created it. For example, use the sequence in an INSERT statement:
Deleting a Sequence
To permanently remove a sequence from the database, call the Drop( ) method in the IGXSequence 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 Drop( ) 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 Drop( ) 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 Drop( ) 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: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
Open one or more connections to data sources.
Instantiate a transaction object. For example:
Start the transaction by calling Begin( ). For example:
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 ...
// Set up another INSERT command ...
To make the changes to the database permanent, call Commit( ). For example:
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.// Allocate a new row for the withdrawal half of the
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
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
// If the deposit command failed, roll back
HandleOBSystemError("Could not insert transaction");
// If the withdrawal command failed, roll back
HandleOBSystemError("Could not insert transaction");
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( ) method. For example:
// ... series of data operations ...
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( ) method.
Example
The following code rolls back a transaction if a test condition is not met:// ... series of data operations ...
Previous Contents Index DocHome Next
Copyright © 2000 Sun Microsystems, Inc. Some preexisting portions Copyright © 2000 Netscape Communications Corp. All rights reserved.
Last Updated April 26, 2000