Connection Sample

This sample is a simple extension of the framework code. Essentially, the only new element introduced is a query execution, which results in a Recordset. You can iterate through each element in the Recordset and move to the very start or end of the Recordset. To programmers familiar with ANSI cursors, the Recordset may be thought of as an elaboration of the cursor concept. It also includes scrolling and remoting (moving the entire result set to another process rather than one row at a time).

The code below only includes the run method, which you can insert into the framework code:

public boolean run()
{
   Connection c = new Connection();
   c.setConnectionString ("dsn=aDSNName;pwd=aName;pwd=aPWD;database=aDatabase");
   c.setCommandTimeout (10);
   c.setConnectionTimeout (10);
   c.setCursorLocation (AdoEnums.adUseClientBatch);

   c.open();
   c.setDefaultDatabase ("aSpecificDatabase");
   System.out.println ("Connection string = " + c.getConnectionString());

   // Verify that the propertiea on the connection 
   // were set correctly. If not, return a fail condition.
   if (c.getCommandTimeout() != 10)
      return false;

   if (c.getConnectionTimeout() != 10)
      return false;

   if (c.getCursorLocation() != AdoEnums.adUseClientBatch)
      return false;

   if (c.getMode() != AdoEnums.adModeUnknown)
      return false;

   // Create a new Recordset to contain the results 
   // of the SQL DML operation (select * ...). 
   Recordset rs= c.execute ("select * from authors");
   rs.moveFirst();
   rs.moveLast();

   // Delete all rows from the authors table. 
   c.executeUpdate ("delete from authors");

   // Close the Connection and Recordset. 
   rs.close();
   c.close();
   return true;
      
}

This sample introduces a number of options that were not discussed in the framework code. ADO/WFC can operate in “default” mode, where the developer doesn’t have to set all of the options possible. This is a trade-off with functionality, since the default behavior of ADO/WFC is to create server-side, forward-only cursors. This means that scrolling is not supported by default. To get the convenience of scrolling cursors and locally cached data, rather than performing a network roundtrip for each row fetched, the following options are set in the code:

c.setCommandTimeout (10);
c.setConnectionTimeout (10);
c.setCursorLocation (AdoEnums.adUseClientBatch);

The CursorLocation property tells the ADO/WFC run time to create a client-side cursor and to operate in batch mode. The advantage of this type of cursor is increased navigational speed and the ability to make changes all at once. The other two Connection properties specify a timeout for the connection. This is typically used to ensure that an application doesn’t enter a long wait when a server resource is not available. In this example, the attempt to connect will be aborted if the server/source does not acknowledge the connection request within 10 seconds. The default setting for both of these timeout properties is indefinite; this means the application will go into a wait mode until connected or until the application process is terminated.

The line, c.executeUpdate("delete from authors"), provides the major piece of functionality in this sample. Here, the application uses the open connection to issue a SQL DML statement to delete all rows in the authors table. There are other execute operations that return Recordset objects. These execute operations are typically select statements or stored procedure calls that return one or more Recordsets as “results.” Since this is a delete operation, there is no result data to process (aside from potential error messages), and the executeUpdate method is employed. The Recordset class offers multiple options for controlling the nature of the “result.”

Try experimenting with a few different queries and alter the cursor type and location properties. Depending on your network and machine, you should see some performance differences. ADO/WFC provides the convenience of server cursors or the capabilities of “remoting” your data to the client, where navigation and data access operations can be performed, and the changes sent back to the server, in a "batch.” This greatly reduces the overhead of data marshaling.