7. Executing Named SQL & Stored Procedures
Contents
7.1. Introduction
7.4. Executing Updates
7.1. Introduction
This section explains how to execute pre-defined named SQL commands. Named
SQL Commands are useful when you want retrieve arbitrary information from the
database not necessarily associated with business objects, or when you need to
specify query conditions that are beyond the scope of the OQuery
class. They are also useful if you need to perform arbitrary DML SQL or execute
a stored procedure.
For details on how to define named SQL commands using the Mapping Tool see section 7. Named SQL and Stored Procedures of the Mapping Tool Guide.
Object queries return results sets that match the attribute structure of a persistent
class, including the object ID. Object Queries are executed by invoking the getObjects method of the Database class. The
objects retrieved via Object Queries are not cached. Its method
signature is shown below:
public Object[] getObjects(String name, Variant[] params, int maxCount) throws BODBException;
The first argument is the name of command to execute. The command must have been defined
as an OBJECT QUERY. The second argument is an array of parameter values, or null if none.
The Variant class is a VBSF wrapper class provided
for parameters that can wrap a value of all the necessary data types. The last
argument is the maximum number of objects to return, or 0 for unlimited.
Below is an example of its usage from the vcontatcdemo sample application that
retrieves all contacts with last name equal to MOORE:
Variant[] params = new Variant[1];
params[0] = new Variant("MOORE");
Contact[] allContacts =
(Contact[])db.getObjects("GetContactsWithLastNameEqualTo", params, 0);
for (int i = 0; i < allContacts.length; i++) {
//do something with each contact
}
The GetContactsWithLastNameEqualTo command has been
defined in the mapping schema as:
SELECT * FROM CONTACT WHERE LastName = ?
Value queries retrieve arbitrary values from the database not associated with a
persistent class. A common case is when the value of a SQL aggregate function must be
retrieved. Value Queries are executed by invoking the getValues
method of the Database class. Its method signature is shown below:
public DBSReader getValues(String name, Variant[] params, int maxCount) throws BODBException;
The DBSReader return value represents the query
result set. The DBSReader class provides methods to
read the values of result set columns by ordinal number or name and to advance to the next
row in the result set.
Below is another example from the vcontactdemo sample application that retrieves the
largest amount owed by a contact:
BigDecimal amt = null;
DBSReader rdr = db.getValues("GetMaxAmtOwed", null, 0);
try {
if (rdr.nextRow())
amt = rdr.getBigDecimal(1, 2);
} catch ( DBSException e ) {
throw new BODBException(e);
}
System.out.println("Largest amt owed by a contact is "+amt);
The GetMaxAmtOwed command has been defined in the
mapping schema as:
SELECT MAX(AmtOwed) FROM CONTACT
7.4. Executing Updates
Update statements perform data manipulation and data definition functions that do not
return a result set. Update statements are executed by invoking the execute
method of the Database class. You must use explicit transaction control when using the execute method. Its method signature is shown below:
public int execute(String name, Variant[] params) throws BODBException;
Below is another example from the vcontactdemo sample application that changes the first
name of the contact with ID = 8:
Variant[] params = new Variant[2];
params[0] = new Variant("JOHN");
params[1] = new Variant(8L);
Transaction txn = new Transaction(db);
txn.begin();
int rowCount = db.execute("UpdateContactFirstName", params);
txn.commit();
The UpdateContactFirstName command has been defined
in the mapping schema as:
UPDATE CONTACT SET FirstName = ? WHERE ContactID = ?
7.5. Executing Stored Procedure Queries
Stored procedure queries are store procedures that return persistent objects. Stored procedures that return objects must be associated with a business class and must return a result set matching its attribute structure, including the object ID. The objects retrieved via Stored procedure queries are not cached.
Invoking a stored
procedure query is a two step process. First a reference to the BODBProcedure
object corresponding to the stored procedure to be executed must be obtained by passing
the name of the command to the getProcedure method
of the Database class. The procedure is then executed by passing the BODBProcedure
object to the executeRSProcedure method, which
returns an array of persistent objects. Both method signatures are shown below:
public BODBProcedure getProcedure(String name) throws
BODBException;
public Object[] executeRSProcedure(BODBProcedure proc) throws BODBException;
If the procedure accepts input parameters or returns output parameters, the parameters
must be set and/or declared using the appropriate methods of the BODBProcedure
object prior to executing the procedure. Input parameters are set using the set methods. Output parameters are registered using the registerOutParameter method. After the procedure is
executed, any output parameters can be retrieved using the get
methods of the BODBProcedure object.
Below is an example from the vcontactdemo2 sample application that demonstrates how to
invoke a stored procedure that requires an input parameter to retrieve a collection of
SUPERSET objects from the database. In this particular example, the stored procedure named
GetCatThinContacts is passed a category ID as input
parameter, and the result set returned is limited to objects who belong to the supplied
category.
//get the procedure object that will be executed
BODBProcedure proc = db.getProcedure("GetCatThinContacts");
//set input parameter as the category ID of business contacts(3)
proc.setLong(1, 3) ;
//execute the procedure
Contact[] allContacts = (Contact[])db.executeRSProcedure(proc);
for (int i = 0; i < allContacts.length; i++) {
//do something with each contact
}
Below is a possible implementation of how the GetCatThinContacts
named procedure would be defined under Microsoft SQL Server:
CREATE PROCEDURE getCatThinContacts @category_id INT AS
SELECT CONTACT.ContactID, CONTACT.LastName, CONTACT.FirstName, CONTACT.CompanyID,
CATEGORY.Description
FROM CONTACT, CATEGORY
WHERE CONTACT.CategoryID=CATEGORY.CategoryID AND CATEGORY.CategoryID=@category_id
7.6. Executing Stored Procedures
Executing named stored procedures that perform computations or data manipulation
functions is identical to executing stored procedure queries, except that the executeProcedure method must be used to execute the query.
Below is its method signature:
public int executeProcedure(BODBProcedure proc) throws BODBException;
The example below is also taken from the vcontactdemo2 sample application. It executes a
procedure named RaiseCommission that raises a
contact's commission percentage two percentage points by invoking a stored procedure that
takes as input parameter the object ID of the contact whose commission is to be raised,
and returns as output parameter the new commission percentage assigned to the contact row.
Transaction txn = new Transaction(db);
txn.begin();
//get procedure object in order to set/register parameters
proc = db.getProcedure("RaiseCommission");
//set input parameter as the ID of the business contact
//whose commission is going to be raised (in this case ID=3)
proc.setLong(1, 3);
//register the type of the output parameter
proc.registerOutParameter(2,
db.getSQLType(com.objectmatter.bsf.mapping.schema.ClassAttribute.FLOAT));
db.executeProcedure(proc); //execute the procedure
//retrieve output parameter
float newPctg = proc.getFloat(2);
//close procedure. IMPORTANT step to release db resources
proc.close();
txn.commit();
System.out.println("Business Contact #3 commission raised to: " + newPctg);
As shown in the example, these types of procedures should always be invoked in the context
of a transaction.
Below is a possible implementation of how the RaiseCommission
named procedure would be defined under Microsoft SQL Server:
CREATE PROCEDURE raise_com @contact_id INT, @new_com REAL
OUTPUT AS
DECLARE @curr_com REAL
SELECT @curr_com=CommissionPctg FROM CONTACT
WHERE ContactID=@contact_id
SELECT @new_com=@curr_com + 2
UPDATE CONTACT SET CommissionPctg=@new_com
WHERE ContactID=@contact_id