Execute, Requery, and Clear Methods Example (VJ++)

Execute, Requery, and Clear Methods Example (VJ++)

This example demonstrates the Execute method when run from both a Command object and a Connection object. It also uses the Requery method to retrieve current data in a recordset, and the Clear method to clear the contents of the Errors collection. The ExecuteCommand and PrintOutput procedures are required for this procedure to run.

// The WFC class includes the ADO objects.
import com.ms.wfc.data.*;

public class ExecuteX
{
// Main Function

public static void main (String[] args)
{
    ExecuteX();
}

// ExecuteX Function

static void ExecuteX()
{
    // Define string variables.
    String strSQLChange = "UPDATE Titles SET Type = "
            + "'self_help' WHERE Type = 'psychology'";
    String strSQLRestore = "UPDATE Titles SET Type = "
            + "'psychology' WHERE Type = 'self_help'";
    String strCnn = "Provider=sqloledb;Data Source=Server4;"
            + "Initial Catalog=Pubs;User Id=sa;Password=;";

    // Define ADO objects.
    Connection  cnConn1    = null;
    Command  cmdChange  = null;
    Recordset  rsTitles  = null;

    try
    {
        // Open connection.
        cnConn1 = new Connection ();
        cnConn1.open(strCnn, "", "", AdoEnums.CommandType.UNSPECIFIED);

        // Create command object.
        cmdChange = new Command();
        cmdChange.setActiveConnection (cnConn1);
        cmdChange.setCommandText (strSQLChange);

        // Open recordset with Titles table.
        rsTitles = new Recordset();
        rsTitles.open("Titles", cnConn1,
                      AdoEnums.CursorType.STATIC,
                      AdoEnums.LockType.OPTIMISTIC,
                      AdoEnums.CommandType.TABLE);

        // Print report of original data.
        System.out.println("\n\n\tData in Titles table "
                           + "before executing the query: \n");
        PrintOutput(rsTitles);

        // Clear extraneous errors from the Errors collection.
        cnConn1.getErrors().clear();

        // Call the ExecuteCommand subroutine to
        // execute cmdChange command.
        ExecuteCommand(cmdChange, rsTitles);

        // Print report of new data.
        System.out.println("\n\n\tData in Titles table after "
            + "executing the query: \n");
        PrintOutput(rsTitles);

        // Use the Connection object's execute method to
        // execute SQL statement to restore data.
        cnConn1.execute(strSQLRestore);

        // Print report of restored data.
        System.out.println("\n\n\tData after executing the query "
            + "to restore the original information: \n");
        PrintOutput(rsTitles);

        // Clean up objects before exit.
        rsTitles.close();
        cnConn1.close();
    }  // End Try statement.
    catch( AdoException ae )
    {
        // Notify user of any errors that result from ADO.

        // As passing a Recordset, check for null pointer first.
        if (rsTitles != null)
        {
            PrintProviderError(rsTitles.getActiveConnection());
        }
        else
        {
            System.out.println("Exception: " + ae.getMessage());
        }
    }
}

// ExecuteCommand Function

static void ExecuteCommand(Command cmdTemp, Recordset rstTemp)
{
    try
    {
        // CommandText property already set before function was called.
        cmdTemp.setCommandType(AdoEnums.CommandType.TEXT);
        cmdTemp.execute();

        // Retrieve the current data by requerying the recordset.
        rstTemp.requery(AdoEnums.CommandType.UNKNOWN);
    }
    catch( AdoException ae )
    {
        // Notify user of any errors that result from ADO.
        PrintProviderError(rstTemp.getActiveConnection());
    }
}

//  PrintOutput Function

static void PrintOutput(Recordset rstTemp)
{
    // Ensure at top of recordset.
    rstTemp.moveFirst();

    // If EOF is true, then no data and skip print loop.
    if( rstTemp.getEOF() )
    {
        System.out.println("\tRecordset empty\n");
    }
    else
    {
        // Enumerate Recordset and print data from each.
        while( !(rstTemp.getEOF()) )
        {
            // Convert variant string to convertable string type.
            System.out.println("\t"
                + rstTemp.getFields().getItem("Title").getValue() + " "
                + rstTemp.getFields().getItem("Type").getValue() + "\n");
            rstTemp.moveNext();
        }
    }
}

    // PrintProviderError Function

    static void PrintProviderError( Connection Cnn1 )
    {
        // Print Provider errors from Connection object.
        // ErrItem is an item object in the Connection’s Errors collection.
        com.ms.wfc.data.Error  ErrItem = null;
        long nCount = 0;
        int  i      = 0;

        nCount = Cnn1.getErrors().getCount();

        // If there are any errors in the collection, print them.
        if( nCount > 0);
        {
            // Collection ranges from 0 to nCount - 1
            for (i = 0; i< nCount; i++)
            {
                ErrItem = Cnn1.getErrors().getItem(i);
                System.out.println("\t Error number: " + ErrItem.getNumber()
                    + "\t" + ErrItem.getDescription() );
            }
        }

    }

    // PrintIOError Function

    static void PrintIOError( java.io.IOException je)
    {
        System.out.println("Error \n");
        System.out.println("\tSource = " + je.getClass() + "\n");
        System.out.println("\tDescription = " + je.getMessage() + "\n");
    }
}