Command Sample

This sample extends the framework code by introducing an important feature of ADO/WFC: the parametrically varying Command. Command objects provide a performance gain for applications by caching execution plans for repetitive operations, which only differ in the value of the parameters plugged in at a given point. The prime example of this is a stored procedure that takes an input parameter ([in]) and returns a result set (Recordset) to the caller. The execution time saved by the program through reuse of the cached execution plan is substantial.

Note   The following code assumes that you have access to the Microsoft® SQL Server™ sample database, pubs. If you do not, you can substitute your table of choice.

public boolean run()
{
   Connection c = new Connection();
   c.setIsolationLevel (AdoEnums.adXactReadCommitted);
   c.setCursorLocation (AdoEnums.adUseClientBatch);

   c.open ("dsn=aDSN;database=pubs", "aName", "aPWD");

   Command cmd = new Command();
   cmd.setName ("MyFirstCommand");
   cmd.setCommandText ("select * from authors where au_lname = ?");
   cmd.setCommandTimeout (10);
   cmd.setPrepared (true);
   cmd.setCommandType (AdoEnums.adCmdText);
   cmd.setActiveConnection ("dsn=aDSN;database=pubs ");
   cmd.setActiveConnection (c);

   Parameters p = cmd.getParameters();

   Parameter param = new Parameter();
   param.setName ("MyFirstParam");
   param.setType (AdoEnums.adVarChar);
   param.setDirection (AdoEnums.adParamInput);
   param.setSize (5);
   param.setString ("Smith");
   p.append (param);

   Recordset rs = cmd.execute();
   rs.moveFirst();
   
   if (rs.getFields().getItem("au_lname").getString().compareTo("Smith") != 0)
      System.out.println("The author is not a Smith.");
   
   rs.close();
   c.close();

   return true;
} 

This sample instantiates and configures a Command object. Like all ADO/WFC components, Command objects are configured through their properties:

cmd.setName ("MyFavoriteSQLQuery");
cmd.setCommandText ("select * from authors where au_lname = ?");
cmd.setCommandTimeout (10);
cmd.setPrepared (true);
cmd.setCommandType (AdoEnums.adCmdText);

The Name property defines a friendly name for the Command. The CommandText property specifies the invariant portion of the Command; this is the portion that will be “prepared,” meaning generated and cached, by the sample code. Placeholders for parameters in the command are specified by the “?” character. The Prepared boolean property is used to compile the execution plan for the command. The CommandTimeout property is identical to the ConnectionTimeout property — it denotes an upper bound on duration for the command to successfully complete execution. The final property, CommandType, is used to specify the type of Command. This property can be used to differentiate parametric SQL statements from other types of commands, such as stored procedures.

Now, the profile of the input parameter must be delineated. Each Command has a collection of Parameter objects. You may add, remove, and edit these Parameter objects. This is precisely what the following lines of code do — specify the parameter’s name, data type, “direction” of the parameter (in, out, in/out), data size, and the value to plug into the Parameter.

Parameters p = cmd.getParameters();
Parameter param = new Parameter();
param.setName ("MyFirstParam");
param.setType (AdoEnums.adVarChar);
param.setDirection (AdoEnums.adParamInput);
param.setSize (5);
param.setString ("Smith");
p.append (param);

Once the Parameter has been defined, and a value loaded into it, the Command is ready to be invoked. In this example, the return value of the Command will be a set of rows matching the predicate of the select statement — a Recordset to be marshaled to the client. The Command is executed by invoking the execute method. The resulting Recordset is a full-fledged cursor, capable of navigation and data modifications. Data in a Recordset may be accessed through several methods, but the preferred methods are enumerated as follows:

rs.getFields().getItem("<column_name>").get<JavaIntrinsicOrStringorObjectAccessor>();
rs.getFields("<column_name>").get<JavaIntrinsicOrStringorObjectAccessor>();
rs.getItem("<column_name>").get<JavaIntrinsicOrStringorObjectAccessor>();

The <column_name> parameter should be replaced with a valid field name, such as “au_lname” in this sample. The ADO/WFC Field component provides a Java-friendly set of accessor methods for pulling the value out of the Field into a Java intrinsic type, or a Java Object. There is no need to utilize Variant types to access data within Java programs. Some data coercion operations are not supported, and will result in AdoExceptions being thrown by the Field object.

The remaining methods in this sample demonstrate the pattern of closing objects (such as Connections and Recordsets) to ensure proper release of the underlying resources.