Tag Archives: MCP

Reading Excel Spreadsheets from MCP

I am currently working on an EAE project where we have some data in Excel spreadsheets that need to go into a DMSII database. In this case the spreadsheet data are only read once and after that maintained in DMSII. Here it is easy enough to save the spreadsheet as a CSV file, copy the file to the MCP platform and read the file with a one time EAE report. But what if you have data coming in on Excel sheets on a regular basis? Do you still want to go through the hassle of opening the file and saving it as a CSV? No need, the Apache Software Foundation (who else?) has a Java API that knows how to read Excel spreadsheets.

The Apache POI project provides a Java API that can read, and write, XLS files (in the HSSF/Horrible Spread Sheet Format as they call it) and XLSX files. To read a spreadsheet from ALGOL, all I needed to do was wrap a little Java code around the POI interface to make it easily accessible from an ALGOL program.

The code presented here was just an excercise for me, to see how complicated it is to read Excel. I can imagine the ALGOL program to be replaced by a library, which can be called from an EAE report. You can copy the Excel file to the MCP platform using FTP or Windows copy. Alternatively, users could be allowed to upload Excel data using their web browser. In that case the Tomcat web server running on your JProcessor can implement a servlet that uses the Apache (again) Commons FileUpload package to accept Excel file uploads from authorized clients. The uploaded files can be written to an MCP pack for batch processing, or they can be processed right away using, for example, JDBC.

You’ll need to download the Apache POI API from http://poi.apache.org/download.html to compile the ExcelReaderImpl class. Documentation of the POI API and examples can also be found on the Apache site.

The method openSpreadsheet() is not very complicated. It instantiates some POI objects and if an exception is thrown, it returns a non zero result.

The getRow() method is a bit more involved because the type of cell contents dictates the method with which to retrieve the cell data. I find the handling of cell of type CELL_TYPE_FORMULA a bit clumsy. First, I used a FormulaEvaluator class to calculate the result of the formala in a cell. However, since the FormulaEvaluator runs on Java, there is no guarantee that the result is exactly the same as the result Excel originally produced. That is why I commented out that bit and opted for the approach where I try and catch until a valid result is returned.

SpreadsheetReader.java This defines a generic interface to read spreadsheets.
ExcelReaderImpl.java This class implements an Excel xls-type file reader
ExcelReaderLibrary.java This class contains static methods to make calls from ALGOL easy

The ALGOL include file was, as usual, generated using the JifGen utility. But, since I’m running Java 7 on my PC and the Java version of my MCP is still version 6, some more path info was needed in the JifGen arguments:

\"program files"\java\jdk1.6.0_25\jre\bin\java -cp /libs/mcp/mcp.jar;/libs/mcp/mcptools.jar;/"program files"/java/jdk1.6.0_25/lib/tools.jar com.unisys.mcp.tools.JifGen -algol -classpath dist/excel2mcp.jar -d mcp/algol  -output EXCEL_ -verbose com.mcpjava.mcp.spreadsheet.ExcelReaderLibrary

The generated ALGOL file and the test driver are nothing special:


Java as a library, part 2

In the previuos blog I’ve made a Java class Grep that exports a method named doGrep(). I also supplied a Java test driver to test the Grep class. In this blog I will write the ALGOL program that takes the search text and directory as a parameter and calls the Java doGrep() method to do the actual work.

The utility JifGen has been run to generate the ALGOL include file that holds the definitions of the Java methods (GREP_INCL.ALG_M). I copied this ALGOL symbol to my MCP system as

My ALGOL program looks like this:

NEXT+   ....*....1....*....2....*....3....*....4....*....5....*....6....*....7..
00002000% grep                                                                  
00003000% Parameters:                                                           
00004000%   text:    String parameter of the text to search for.                
00005000%   path:    String parameter of file or directory to search.           
00006000%   recurse: Boolean, if TRUE all subdirectories will also be           
00007000%            searched.                                                  
00009000% Note: The Apache Software Licence applies to this code.               
00010000% (http://www.apache.org/licenses/LICENSE-2.0.html)                     
00012000$ tads                                                                  
00013000$$ set level 2                                                          
00014000procedure grep(arguments);                                              
00015000array arguments[*];                                                     
00020000    ARRAY JNIEnv[0] = envEtc;                                           
00021000    ARRAY JavaVM[-JNI_MIN_ENV_ARRAY_SIZE] = envEtc;                     
00022000    ebcdic value array jvmOptions ("-cp ./grep.jar " 48"00");           
00023000    ebcdic value array mcpOptions (48"00");                             
00024000    integer jvmRslt;                                                    
00025000    double returnArray;                                                 
00026000    ebcdic array param[0:299];                                          
00027000    ebcdic array ax[0:119];                                             
00028000    integer errorCode;                                                  
00029000    pointer pparam;                                                     
00029200    file lp(kind = printer);                                            
00029400    ebcdic array line[0:132];                                           
00031000$$  INCLUDE GREPJNI="GREP/SRC/INCLUDE/GREP_INCL"                        
00033000    procedure listHits(returnArray);                                    
00034000    value returnArray;                                                  
00035000    double returnArray;                                                 
00036000    begin                                                               
00038000        ebcdic array found[0:300];                                      
00039000        integer foundLen;                                               
00040000        double arrayElement;                                            
00041000        integer returnLength;                                           
00042000        integer i;                                                      
00044000        returnLength := JNI_GET_ARRAY_LENGTH(JNIEnv, returnArray);      
00046000        for i := 0 step 1 until (returnLength - 1) do                   
00047000        begin                                                           
00048000            replace found by " " for 300;                               
00049000            foundLen := 300;                                            
00050000            arrayElement := JNI_GET_OBJECT_ARRAY_ELEMENT                
00051000                (JNIEnv, returnArray, i);                               
00052000            JNI_GET_STRING_EBCDIC_CHARS                                 
00053000                (JNIEnv, arrayElement, found, 0, foundLen);             
00054000            if foundLen > 0 then                                        
00055000            begin                                                       
00056000                write(lp, *, found);                                    
00057000            end;                                                        
00058000        end;                                                            
00059000    end of listHits;                                                    
00061000    if (size(arguments) * 6) > 300 then                                 
00062000    begin                                                               
00063000        resize(param, size(arguments) * 6, DISCARD);                    
00064000    end;                                                                
00065000    replace pparam:param[0] by pointer(arguments)                       
00066000        for size(arguments) * 6 until = 48"00";                         
00068000    % Initialize the Java Virtual Machine                               
00069000    jvmRslt := JNI_CREATE_JAVA_VM_STAR(JavaVM, JNIEnv,                  
00070000                                       JNI_VERSION_1_6,                 
00071000                                       jvmOptions,                      
00072000                                       mcpOptions);                     
00072100    if jvmRslt < 0 then                                                 
00072200    begin                                                               
00072300        replace line by " " for 132;                                    
00072400        replace line by "Result from JNI_CREATE_JAVA_VM_STAR: ",        
00072500            jvmRslt for * digits;                                       
00072600        write(lp, 132, line);                                           
00072700    end                                                                 
00072800    else                                                                
00072900    begin                                                               
00073000        INITIALIZE_GREP_;                                               
00074000        returnArray := GREP__GREP_DO_GREP(param,                        
00075000            0, offset(pparam));                                         
00076000        if JNI_Check_Exception(JNIEnv) then                             
00077000        begin                                                           
00078000            errorCode := JNI_GET_LAST_ERROR(JNIEnv);                    
00079000            replace ax by "JNI Exception: Error code = ",               
00080000                errorCode for * digits, 48"00";                         
00081000            display(ax);                                                
00082000        end;                                                            
00083000        listHits(returnArray);                                          
00083500        JNI_DESTROY_JAVA_VM(JavaVM);                                    
00084000    end;                                                                
00085000end of grep.                                                            

Line 17000 includes the variables and procedure declarations that are necessary to communicate via JNI. It needs to be included before the include of the generated GREP/SRC/INCLUDE/GREP_INCL. The lines 19000 – 21000 declare some arrays that the generated ALGOL file needs. The comments at the top of the generated include file remind you of this.

Lines 22000 and 23000 define parameter strings that need to be supplied to the JNI_CREATE_JAVA_VM_STAR procedure (line 69000). This procedure will start a Java Virtual Machine on the JProcessor. The array jvmOptions sets the CLASSPATH of the JVM. In this program I have hardcoded the CLASSPATH relative to the CURRENTDIRECTORY attribute of the program. I a production system you would probably get these parameters from a configuration file.

The INIALIZE_GREP_ in line 73000 results in a call (via GREP/SRC/INCLUDE/GREP_INCL) of the JAVAJNILIB library procdeure JNI_FIND_CLASS_E. This procedure returns a handle to the Grep class.

Since I've made the doGrep() method a static method, I don't need to create an instance of the Grep class. I find this makes the ALGOL code less complicated. In a production system that I built, I defined a single class to interface between Java and ALGOL. A call to a static method in this class creates a single instance of a Manager class, the Manager class in turn, instantiates other objects as needed. This scheme can work because a JVM is dedicated to one program stack.

In line 74000, the actual doGrep method is called. The parameter param is an EBCDIC array that contains the arguments for doGrep. The next parameter, 0, is the offset into the param array where the actual arguments start. The last parameter gives the length, in characters, of the arguments string. The GREP__GREP_DO_GREP procedure in the generated include file wraps the actual, more complex, JNI call.

I started a test using the WFL below:

NEXT+   ....*....1....*....2....*....3....*....4....*....5....*....6....*....7..
00000100BEGIN JOB WFL/GREP;                                                     
00000300RUN GREP/GREPDRIVER(                                                    
00000350        "-r grep  /-/user/usercode/ruud/grep/src");                     
00000400    LIBRARY JAVAVM(LIBACCESS = BYTITLE,                                 
00000500        TITLE=*DIR/JRE6/BIN/JAVAPROXYLIB ON DISK);                      
00000600    CURRENTDIRECTORY="/-/USER/USERCODE/RUUD/GREP";                      
00000800END JOB 

The CURRENTDIRECTORY equation is, apparently, just for the GREP/GREPDRIVER program, it is not propagated to the Java Virtual Machine. That means that the path to directory to be searched by Grep must be an absolute path, it cannot be relative to the CURRENTDIRECTORY.

I also got a message “JAVAHOMEPATH not provided; JRE location extracted from proxy name (/-/DISK/DIR/JRE6).”. This message comes from the program *SYSTEM/JAVAFILE, which is used by the JVM to access the MCP file system. Adding a file equation FILE JAVAHOMEPATH(PATHNAME="/-/DISK/DIR/JRE6"); did not get rid of the message. But using the path from the PROXYLIB is just fine.

When I ran my test WFL, much to my surprise, I didn't get any results back. I figured the JNI_CREATE_JAVA_VM_STAR failed, that's why I added the code in lines 72300 – 72600. But there was no problem there, nor was there a problem in the GREP__GREP_DO_GREP call. Wouldn't it be nice if I could debug my Java code now. Well, I could, the way to do that was obvious: Just insert the debug arguments
-Xdebug -Xrunjdwp:transport=dt_socket,address=9000,server=y,suspend=y
at the beginning of the jvmOptions array and I could connect my Netbeans to the JVM under test. Stepping through the doGrep() Java code, the problem jumped right at me. The param.split(“ “) method splits the parameter string using a single space as a token separator, but my test job has two spaces between “grep” and “/-/user/....”. I have now changed the regular expression in the param.split to “ +”, telling the split to use one or more spaces to separate the string.

Check for JNI exceptions!

Recently, I had a problem on a production system with an ALGOL to Java JNI (Java Native Interface) connection. Since this problem is still fresh in my head, I will post about it now. In a later blog I’ll go through the steps I took to set up the JNI connection in more detail.

At this customer site, a separate database is run for each brand they are marketing. Each brand has a connection to Websphere-MQ. A Java program on the JProcessor connects to MQ, the ALGOL program calls methods in the Java program. The whole Java environment (JVM) is like a library to the ALGOL program.

The ALGOL program contains the controlling loop. It calls a Java method to fetch a message from the Message Queue. The ALGOL program processeses the message and calls Java to put a reply message on the queue. It then fetches the next message etc. If are no messages in the queue, the Java method blocks until a message arrives.

But, when we setting up a new brand, and consequently another copy of the ALGOL-Java program, we could not get it to work. The new copy of the ALGOL program kept failing with a “REQUESTED MEMORY SIZE GREATER THAN 65535 WORDS” message. I usually put an ON ANYFAULT, PROGRAMDUMP(ALL) statement in my ALGOL programs, so the fault produced a program dump. The fault appeared to come from the SYSTEM/JAVAJNILIB. This librray tried to resize an array beyond the 65535 words limit as soon as the new environment tried to call a Java method. To test if we had hit a limit somewhere, we stopped one of the running environments and started the new one. Now it worked and restarting the stopped, existing environment failed.

At this point I contacted Unisys. After all, the dump showed that it was the SYSTEM/JAVAJNILIB that had all of it’s 65535 data buffers filled up with parameters from ALGOL to Java. The Unisys people jumped onto the problem right away and after some mails back and forth they came up with three problem areas: The JAVAJNILIB could do with some better failure detection/recovery, the generated include file should call JNI_CANCEL_PARAMETER_LIST if creating a parameter list to Java fails and, most importantly, my ALGOL program should check for a JVM exception after each call to a Java method. Because the ALGOL program did not check for Java exceptions, it kept firing off method calls to a JVM that was no longer functional, thus, at high speed, eating up all the data buffers in the JAVAJNILIB.
Here is the code snippet that does a method call from ALGOL to Java:

00105900    JNILIB_MCPJNICONNECTION_COMMAND(command,                            
00106000        0, offset(pc), result, rsltOffset, rsltSz);                     
00106010    if JNI_Check_Exception(JNIEnv) then                                 
00106020    begin                                                               
00106030      errorCode := JNI_GET_LAST_ERROR(JNIEnv);                          
00106052      replace axmsg by "JNI Exception: Error code = ",                  
00106054         errorCode for * digits, NULL;                                  
00106056      display(axmsg);                                                   
00106058      myself.status := value(TERMINATED);                               
00106060    end;

After the call to the Java method in line 105900, JNI_Check_Exception is called in line 106010. If that call returns a true, the JNI error code is determined. The exception is displayed and the program is terminated. In my case, I could leave it to COMS to restart the program.
A Java utility “JifGen” is provided to generate wrapper procedures that do the low level JNI calls. The current JifGen does not generate the JNI_CANCEL_PARAMETER_LIST in, but you can add that bit of code yourself. This the procedure JNILIB_MCPJNICONNECTION_COMMAND that is called from the user program:

00016600% Class:     nl_acme_mcp_mq_MCPJNIConnection                          
00016700% Method:    command                                                    
00016800% Signature: (Ljava/lang/String;)Ljava/lang/String;                     
00016900% ID: 2                                                                 
00017100PROCEDURE JNILIB_MCPJNICONNECTION_COMMAND                               
00017300  VALUE         ARG0_OFFSET, ARG0_SIZE, RESULT_OFFSET ;                 
00017400  EBCDIC ARRAY  ARG0[0];                % Input                         
00017500  INTEGER       ARG0_OFFSET;                                            
00017600  INTEGER       ARG0_SIZE;              % Input                         
00017700  EBCDIC ARRAY  RESULT[0];              % Output                        
00017800  INTEGER       RESULT_OFFSET;                                          
00017900  INTEGER       RESULT_SIZE;            % Output                        
00018100  LABEL xit;                                                            
00018200  OWN DOUBLE methodID;                                                  
00018300  REAL hParams;                                                         
00018500  IF JNI_IsNull(methodID) THEN                                          
00018600  BEGIN                                                                 
00018700    methodID := JNI_GET_STATIC_METHOD_ID_E(                             
00018800            JNIEnv,                                                     
00018900            CLASS_MCPJNIConnection_REF,                                 
00019000            JNILIB_StringPool, 50,       % command                      
00019100            JNILIB_StringPool, 58 );     %                              
00019200    %  (Ljava/lang/String;)Ljava/lang/String;                           
00019300    IF JNI_Check_Exception( JNIenv ) THEN GO TO xit;                    
00019400  END IsNull;                                                           
00019600  hParams := JNI_CREATE_PARAMETER_LIST( JNIEnv, methodID,               
00019700   JNILIB_StringPool, 58, 600 );           %                            
00019800  %  (Ljava/lang/String;)Ljava/lang/String;                             
00019900  IF hParams EQL 0 THEN GO TO xit;                                      
00020100   ARG0_SIZE);                                                          
00020200  IF JNI_Check_Exception( JNIenv ) THEN                                 
00020220  begin                                                                 
00020240   JNI_CANCEL_PARAMETER_LIST(JNIEnv, hParams);                          
00020260   GO TO xit;                                                           
00020280  end;                                                                  
00020300  JNI_CALL_STATIC_STRING_EBCDIC_METHOD_P(                               
00020400    JNIEnv, CLASS_MCPJNIConnection_REF, methodID, hParams, RESULT,      
00020500     RESULT_OFFSET, RESULT_SIZE);                                       
00020700  xit:                                                                  

The code generated by GifGen just does the GO TO xit. I added the JNI_CANCEL_PARAMETER_LIST in line 20240.

Allthough a UCF was submitted for the JAVAJNILIB and the JifGen, these two points are not really an issue, as long as you make sure you check for exceptions after each call to the JVM.


  • The Java Native Interface Programmer’s Guide and Specification, Sheng Liang, 1999
  • The Unisys Javadoc for JNI on the MCP system in *JRE/jni