Developing a connector for Excel client

The following tutorial describes developing a connector for an Excel client.

Goal

In this tutorial, you will learn how to run Excel as a service in IBM® Spectrum Symphony and perform calculations on compute hosts in the cluster.

At a glance

Before you begin, ensure you have installed and started IBM Spectrum Symphony Developer Edition. You will do the following:

Review and understand the sample
Build the sample client and deploy the sample service
Run the sample client and service

Review and understand the sample

Review the C++ sample client code to learn how to link your Excel spreadsheets to IBM Spectrum Symphony.

The connector for Excel package also includes a VB client sample that connects to IBM Spectrum Symphony using the COM API; for more information, see the Cross-language tutorial.

Locate the code samples

Note: %SOAM_HOME% is an environment variable that represents the IBM Spectrum Symphony Developer Edition installation directory; for example, C:\Program Files\IBM\Platform SymphonyDE\.

Solution file (Visual Studio .NET): %SOAM_HOME%\version\Integrations\ConnectorForMsExcel\samples\CPP\AsyncClient\connector_for_ms_excel_sample_Visual_Studio_version.sln
Client: %SOAM_HOME%\version\Integrations\ConnectorForMsExcel\samples\CPP\AsyncClient\AsyncClient.cpp
Input, output, and data objects: %SOAM_HOME%\version\Integrations\ConnectorForMsExcel\src\
Service: %SOAM_HOME%\version\Integrations\ConnectorForMsExcel\service\ConnectorForMsExcel.zip
Spreadsheet: %SOAM_HOME%\version\Integrations\ConnectorForMsExcel\samples\spreadsheets\ConnectorForExcelDemo.xls
Application profile: The service required to compute the input data along with additional application parameters are defined in the application profile: %SOAM_HOME%\version\Integrations\ConnectorForMsExcel\samples\CPP\AsyncClient\connectorForExcelSampleApp.xml

What the sample does

The sample provides an asynchronous C++ client, Excel spreadsheet, and wrapper service for Excel. The Excel spreadsheet contains business logic implemented in the form of a macro (VBA function) that performs calculations.

When you run the sample, here is the sequence of events:

The client sends task input messages and common data to the ConnectorForMsExcel service. Each task input message contains:
- workbook name (ex. ConnectorForExcelDemo.xls) containing the macro
- macro name (ex. MyMacro)
- input string (data) passed to the macro.
The common data contains the path of the Excel spreadsheet.
The ConnectorForMsExcel service launches Excel, which opens the workbook and executes the VBA macro. The VBA macro processes the input data string that is sent with each task input message.
The macro formats the result message and passes it to the service. The service sends the message to the client, which displays it in the command prompt window. The service closes Excel upon completion of the tasks.

Step 1: Develop the spreadsheet macro

The spreadsheet contains the logic (VBA code) that performs the calculations on the input data. When we create the input message, we pass the name of the VBA macro and other data to the service.

In the ConnectorForExcelDemo.xls spreadsheet, we implement MyMacro() as the main function. It calls the Initialize() function, which parses the input data string to extract the values for <NumberIterations> and <Seed>.

The Sim_RunFunction() performs a cycle of calculations based on the number of iterations specified in the input data string and prints out the results in the spreadsheet.

Step 2: Initialize the client

In AsyncClient.cpp,when you initialize, you initialize the IBM Spectrum Symphony client infrastructure. You initialize once per client.

Important: Initialization is required. Otherwise, API calls will fail.

...
        SoamFactory::initialize();
...

Step 3: Implement the response handler (callback) method to retrieve output messages

With an asynchronous client, when a task is completed by the service, there must be a means of communicating this status back to the client. The response handler or callback is implemented for this purpose. It is called by the middleware each time a service completes a task.

In this sample, the OnResponse() method is the response handler. It is a member of the MySessionCallback class that inherits from the SessionCallback class. The method accepts the TaskOutputHandle as an input argument, which is passed to the method by the middleware whenever the respective task has completed.

First, we check if there is output to retrieve. If so, get the output message from the service and print out the task ID. Extract and print the message from the task result using the populateTaskOutput() method.

Increment the counter that records the number of task results received. The critical section object ensures that another thread does not try to increment the counter while it is being accessed.

    void onResponse(TaskOutputHandlePtr &output) throw()    
    {        
        cout << "onResponse handler called" << endl;
        try        
        {               
            cout << "Check for success of task" << endl; 
            // check for success of task            
            if (true == output->isSuccessful())
            {                                
                // get the message returned from the service                
                ExcelMessage outMsg;                
                output->getMessage(&outMsg);                
                // display content of reply                
                cout << "Task Succeeded [" <<  output->getId() << "]" << endl;
                string message = outMsg.GetResult(); 
                if( message.length() > 512 ) 
                {
                    string tmp = message.substr(0, 512); 
                    cout << tmp ;  
                    cout << "( to display the actual size " << (unsigned int)
                    message.length() << " is truncated to 512 )" << endl << endl;
                }  
                else  
                {   
                    cout << outMsg.GetResult() << endl << endl;
                }            
            }    
            else     
            {   
                // get the exception associated with this task
                SoamExceptionPtr ex = output->getException();
                cout << "Task Failed : " << ex->what() << endl << endl;
            }
        }        
        catch(SoamException &exception)
        {            
            cout << "Exception occured in OnResponse() : " << exception.what() << endl;
        }
        // Update counter used to synchronize the controlling thread
        // with this callback object        
        EnterCriticalSection(&m_criticalSection); 
        ++m_tasksReceived;        
        LeaveCriticalSection(&m_criticalSection);    
   }

Step 4: Connect to an application

To send data to be calculated in the form of input messages, you connect to an application.

You specify an application name, a user name, and password. The application name must match that defined in the application profile.

For IBM Spectrum Symphony Developer Edition , there is no security checking and logon credentials are ignored so you can specify any user name and password. Security checking is done however, when your client application submits workload to the actual grid.

The default security callback encapsulates the callback for the user name and password.

...
    const char appName[]="ConnectorForExcel";
...        
    // setup application authentication information using the default security
    //provider,        
    DefaultSecurityCallback securityCB("User1", "User1pw");
...        
    // connect to the specified application        
    ConnectionPtr conPtr = SoamFactory::connect(appName, &securityCB);
...        
    // retrieve and print our connection ID           
    cout << "get connection ID=" << conPtr->getId() << endl << endl;

Step 5: Create a session to group tasks:

In AsyncClient.cpp, perform this step after you have connected to the application.

When creating an asynchronous session, you need to specify the session attributes by using the SessionCreationAttributes object. In this sample, we create a SessionCreationAttributes object called attributes and set five parameters in the object.

The first parameter is the session name. This is optional. The session name can be any descriptive name you want to assign to your session. It is for information purposes, such as in the command line interface.

The second parameter is the session type. The session type is optional. You can leave this parameter blank and system default values are used for your session.

The third parameter is the session flag, which we specify as SF_RECEIVE_ASYNC. You must specify it as shown. This indicates to Symphony that this is an asynchronous session.

The fourth parameter is the common data object containing the path to the Excel spreadsheet that you want to send to the service.

The fifth parameter is the callback object.

We pass the attributes object to the createSession() method, which returns a pointer to the session.

...
 // create call back
        MySessionCallback  myCallback;

        std::vector<std::string> fileNames;
        std::string pathToWorkBookeFile="C:\\Program Files\\IBM\\Platform SymphonyDE\\DE\\.\\Integrations\\ 
        ConnectorForMsExcel\\samples\\spreadsheets\\ConnectorForExcelDemo.xls";

        fileNames.push_back(pathToWorkBookeFile);
        ExcelCommonData commonData( fileNames );

        cout << "Creating an asynchronous Session" << endl;

        // Create an asynchronous Session
        SessionCreationAttributes attributes;
        attributes.setSessionName("mySession");
        attributes.setSessionType("ShortRunningTasks");
        attributes.setSessionFlags(Session::ReceiveAsync);
        attributes.setCommonData(&commonData);
        attributes.setSessionCallback(&myCallback);

        // Create a synchronous session
        SessionPtr sesPtr = conPtr->createSession(attributes);
        cout << "    Session created" << endl;
        // retrieve and print session ID
        cout << "    Session ID:" << sesPtr->getId() << endl << endl;
...

Step 6: Send input data to be processed

In this step, we create 10 input messages to be processed by the service. We call the fillMessageWithExcelData() method. The fillMessageWithExcelData() method fills the inMsg object with (1) spreadsheet name, (2) macro name, and (3) data input string. When a message is sent with the sendTaskInput() method, a task input handle is returned. This task input handle contains the ID for the task that was created for this input message.

...
 int tasksToSend = 10;
        cout << "Send " << tasksToSend << " messages to Excel service" << endl;

        for (int taskCount = 0; taskCount < tasksToSend; taskCount++)
        {
            // Create a message        
            ExcelMessage inMsg;

            cout << "Preparing message #" << taskCount +1 << endl;
            fillMessageWithExcelData(inMsg);
            
            cout << "     " << inMsg.ToString() << endl;

            cout << "Trying to submitt task..." << endl;
            // send it
            TaskInputHandlePtr input = sesPtr->sendTaskInput(&inMsg);
            
            // retrieve and print task ID
            cout << "      Task submitted with ID : " << input->getId() << endl << endl;
        }
...

...
void fillMessageWithExcelData( ExcelMessage &inMsg )
{
    char cWorkbookName[]="ConnectorForExcelDemo.xls";
    inMsg.SetWorkbookName( cWorkbookName );

    char cMacroName[]="MyMacro";
    inMsg.SetMacroName(cMacroName);

    char sInputString[]="<NumberIterations>1</NumberIterations><Seed>55545</Seed>";
    inMsg.SetParam(sInputString);
}
...

Step 7: Wait for replies

After all 10 tasks (messages) have been sent to the service, the main client execution thread must wait for all tasks to be processed before uninitializing the client API. As each task is completed by the service, the m_tasksReceived variable is incremented; refer to Step 3. The myCallback.getReceived() method returns the value of m_tasksReceived. If m_tasksReceived is less than the total number of tasks sent and there are no exceptions thrown, the main thread waits 2 seconds before checking the value of m_tasksReceived again. This cycle continues until all the tasks results are received.

...
cout << "Wait till all replies have been received asynchronously by our callback ..." 
            << endl << endl;
        while ((myCallback.getReceived() < tasksToSend) && !myCallback.getDone())
        {
            ourSleep(2);
        }
...

...
void ourSleep(unsigned long sleepInSeconds)
{
    soam::Sleep(sleepInSeconds * 1000);
}
...

Step 8: Uninitialize

Always uninitialize the client API at the end of all API calls. If you do not call uninitialize(), the client API will be in an undefined state and resources used by the client will be held indefinitely.

Important:

Once you uninitialize, all objects become null. For example, you can no longer create a session or send an input message.

...
SoamFactory::uninitialize();
...

Building the sample client and add the application

Build the sample client:
1. Locate solution file connector_for_ms_excel_sample_Visual_Studio_version.sln in %SOAM_HOME%\version\Integrations\ConnectorForMsExcel\samples\CPP\AsyncClient.
2. Load the file into Visual Studio and build it. You can find the compiled client binary in %SOAM_HOME%\version\Integrations\ConnectorForMsExcel\samples\CPP\output\
Add the application:
When you add an application through the IBM Spectrum Symphony Developer Edition cluster management console, you must use the Add Application wizard. This wizard defines a consumer location to associate with your application, deploys your service package, and registers your application. After completing the steps with the wizard, your application should be ready to use.
1. Click Workload > Symphony > Application Profiles.
  The Application Profiles page displays.
2. Select Global Actions > Add/Remove Applications.
  The Add/Remove Application page displays.
3. Select Add a new application, then click Continue.
  The Adding an Application page displays.
4. Select Use existing profile and add application wizard. Click Browse and navigate to %SOAM_HOME%\version\Integrations\ConnectorForMsExcel\samples\CPP\AsyncClient.
5. Select application profile connectorForExcelSampleApp.xml, then click Continue.
  The Service Package location window displays.
6. Click Browse and navigate to %SOAM_HOME%\version\Integrations\ConnectorForMsExcel\service. Select service package ConnectorForMsExcel.zip, then click Continue.
  The Confirmation window displays.
7. Review your selections, then click Confirm.
  The project wizard creates your application and registers it within IBM Spectrum Symphony.
8. Click Close.
  The application is now enabled.
Run the sample client and service:
To run the service, you run the client application. The service a client application uses is specified in the application profile.
1. Go to the directory where the client executable is located:
```
cd %SOAM_HOME%\version\Integrations\ConnectorForMsExcel\samples\CPP\output
```
2. Run the client application:
```
excel_runner_client_vc100.exe
```
  The client starts and the system starts the corresponding service.