Return a User Profile Listing to a .NET Client
October 19, 2011 Hey, Mike:
I am a VB.NET programmer and am looking to retrieve a list of all users and their expiration dates on our AS/400 system. I stumbled across the cwbx library and have been able to connect to our system, but not much else from there.
Can you suggest how this info can be retrieved from VB.NET? Thanks.
The easiest way to retrieve this user info is to use the i/OS Display User Profile (DSPUSRPRF) command and dump the results to a table (also known as an outfile):
DSPUSRPRF USRPRF(*ALL) OUTPUT(*OUTFILE) OUTFILE(QTEMP/LSTUSRPRFP)
The above command (assuming you have the authority) will dump all of the user profile information (including expiration date) to a temporary table that can be queried by SQL.
To access this data from a remote client, it would probably be best to wrap this code in something called an external stored procedure. An external procedure is written in a High Level Language (HLL), such as C, COBOL or RPG, that is made accessible to SQL. The next piece of code below contains an embedded SQL RPG program that will run the above DSPUSRPRF command, query the temporary table for relevant information and return the results to the caller. The query does a few minor things, such as exclude IBM system profiles and pick a few columns out of the many available.
// // List User Profile Stored Procedure Registration // ====================================================== // CREATE PROCEDURE DEV/LSTUSRPRFR // RESULT SETS 1 // LANGUAGE RPGLE // EXTERNAL // PARAMETER STYLE GENERAL // READS SQL DATA // HOption(*NoDebugIO:*SrcStmt) UsrPrf(*Owner) DQCmdExc PR ExtPgm('QCMDEXC') D Command 32000 Const Options(*VarSize) D CommandLen 15 5 Const /Free CallP(E) QCmdExc('DLTF QTEMP/LSTUSRPRFP':21); QCmdExc( 'DSPUSRPRF USRPRF(*ALL) OUTPUT(*OUTFILE) OUTFILE(QTEMP/LSTUSRPRFP)': 66); Exec SQL SET OPTION COMMIT=*NONE, DATFMT=*ISO; Exec SQL DECLARE USER_LIST CURSOR FOR SELECT UPUPRF AS PROFILE, UPUSCL AS USER_CLASS, UPTEXT AS DESCRIPTION, CASE WHEN UPEXPC=' ' THEN NULL ELSE CAST( CASE UPEXPC WHEN '0' THEN '19' WHEN '1' THEN '20' WHEN '2' THEN '21' END || LEFT(UPEXPD,2)||'-'|| SUBSTR(UPEXPD,3,2)||'-'|| SUBSTR(UPEXPD,5,2) AS DATE) END AS EXP_DATE FROM QTEMP/LSTUSRPRFP WHERE UPUPRF NOT LIKE 'Q%' /* Exclude System Profiles */ ORDER BY UPUPRF; Exec SQL OPEN USER_LIST; *InLR=*On; Return; /End-Free
The results sent to the client will look something like this:
You will need to register the RPG program as a stored procedure for use with SQL by using the CREATE PROCEDURE statement listed in the program header. My sample assumes that both the RPG program and the procedure will be placed in the DEV schema (a.k.a., library).
As for consuming the results with VB.NET, I wouldn’t bother using the cwbx ActiveX controls. From VB.NET, it’s probably best to call the external stored procedure using ODBC, OLE DB, or the DB2 for i managed provider that comes with the System i Access product.
The following piece of code contains VB.NET sample code that invokes the procedure and retrieves the results into a DataTable. The IBM managed provider (iDB2Connection, etc.) is used to make the connection.
Imports IBM.Data.DB2.iSeries Module Module1 Sub Main() Dim DB2Connection As iDB2Connection = New iDB2Connection ("DataSource=AS400;UserId=MIKE;Password=123;DefaultCollection=dev") Dim DB2Command As iDB2Command = New iDB2Command("LSTUSRPRFR", CommandType.StoredProcedure, DB2Connection) Dim ProfileData As DataTable = New DataTable() DB2Connection.Open() ProfileData.Load(DB2Command.ExecuteReader(CommandBehavior.CloseConnection)) For Each r As DataRow In ProfileData.Rows Console.WriteLine(r("PROFILE")) Console.WriteLine(r("USER_CLASS")) Console.WriteLine(r("DESCRIPTION")) Console.WriteLine(r("EXP_DATE")) Console.WriteLine("-----") Next DB2Command.Dispose() DB2Connection.Dispose() Console.ReadLine() End Sub End Module
If you have an HLL developer at your disposal, a fancier alternative option to achieve the same results would be as follows (in RPG, C, or COBOL):