On Java Development

All things related to Java development, from the perspective of a caveman.

Calling Stored Procedure SECGETUSERROLES

without comments


This post shows how to use the stored procedure SECGETUSERROLES. This stored proc is designed to return records created by a join of 4 files. The record provides information about a discrete user’s role for a given application. This information can then be used to control the behavior of the application.


The SQL used by the Stored Procedure

For information purposes, this is the SQL statement that the stored procedure executes. Execution can result in returning single or multiple records. Determining how the records can govern the application’s behavior is the responsibility of the developer.

As hinted by the above statement, there are two parameters to be passed to the stored procedure. One parm is for the field USUSERID , which identifies the user, and the other is for the field APAPPCOD, which is the application for which the user’s role must be determined. The records that are returned will allow the application to make that determination.


The POJO for the Returned Records

The first thing to do is to create a simple POJO designed to hold the field values of the records to be returned by the stored proc. Using the SQL statement presented above, an example of a returned record is shown.

The field names reflect those from the files used by the SQL statement, above.

Shown below is the POJO that holds the information returned from the SQL statement executed by the stored procedure. The field names reflect those returned by stored procedure itself.

Shown below is the DDS source on the iSeries used to define the names and type of data to be returned. The POJO’s property names reflect the names defined here.

The source above is presented simply as background information to illustrate why the stored procedure returns a record consisting of these field names.


The Service Class

As shown by the class level comments, this class is designed to support calls to various stored procedures that reside on the iSeries. It contains any and all methods needed to call various stored procedures as required by the application. So far, there is only one method and it will call our stored procedure. Since it is I/O oriented, this class is placed in the project’s db.storedproc package.


The Stored Procedure Class

This class, SecGetUserRoles, is responsible for calling the stored procedure “SECGETUSERROLES” and extends Spring’s StoredProcedure class. This classed is used by StoredProcService, presented above.

You can see the logic that isolates the data values by field name and when necessary, casts them to the appropriate type before setting them in the POJO, SECVL08S1ServiceRecord.


Calling the Stored Procedure

Presented here is the Java code that calls the stored procedure using the StoredProcService class.



This post showed how to use the stored procedure SECGETUSERROLES so that the application can determine the user’s role as it relates to the application being used by that user. The post also showed how the classes StoredProcService and SecGetUserRoles work together to call the procedure.

The class relationships between the classes is shown below.

Written by admin

October 3rd, 2014 at 8:03 am

Leave a Reply

You must be logged in to post a comment.