Stored Procedures
Stored procedures are like program calls that you can use in an SQL environment. You can call them using ODBC, JDBC, or CLI (call level interface) on the iSeries.
If stored procedures return result sets, as these do, then they cannot be tested in interactive SQL, nor can they be used with embedded SQL. You can, however, test them using the iSeries Navigator SQL Script runner, which uses JDBC. RJS Software recommends that you use the library name when calling these stored procedures.
The 4 stored procedures have these 4 parameters in common:
- WebDocs user - Leave this parameter blank to retrieve all valid folders, Document Types, search keys, or look-up list values.
- Sort by description - Type Y to sort the results first by the description, then by the regular sort. The sort is case-insensitive for the description and provides an alphabetical listing by description. Leave this parameter blank to leave the description out of the sort.
- Program library - This will almost always be RJSIMAGE. This library is added to the library list when the stored procedure is called.
- Data library - Leave this parameter blank for data that is in RJSIMAGE. If not blank and not the same as the program library, this library is added to the library list when the stored procedure is called.
GetFolderList
GetFolderList returns a list of folders. The lists may include user access information.
There are 4 ways to use the GetFolderList stored procedure:
- All folders - This list has no user access information.
- Folders to which a user has access
- Users that have access to a folder - This option ignores the ''sort by description'' parameter.
- Access to a folder for a user - This option ignores the ''sort by description'' parameter.
Examples
- To retrieve all folders, sorted by folder levels only, use:
call rjsimage/getfolderlist('', '', '', '', '', '', '', 'RJSIMAGE', '')
- To retrieve all folders, sorted first by level-1 description and then by folder levels, use:
call rjsimage/getfolderlist('', '', '', '', '', '', 'Y', 'RJSIMAGE', '')
- To retrieve all folders to which the user TEST has access, sorted by folder levels only, use:
call rjsimage/getfolderlist('TEST', '', '', '', '', '', '', 'RJSIMAGE', '')
- To retrieve all folders to which the user TEST has access, sorted first by level-1 description and then by folder levels, use:
call rjsimage/getfolderlist('TEST', '', '', '', '', '', 'Y', 'RJSIMAGE', '')
- To retrieve the access that user TEST has to the folder BANK/LOANS and ignore sorting, use:
call rjsimage/getfolderlist('TEST', 'BANK', 'LOANS', '', '', '', '', 'RJSIMAGE', '')
- To retrieve a list of all users that have access to the folder BANK/LOANS and ignore sorting, use:
call rjsimage/getfolderlist('', 'BANK', 'LOANS', '', '', '', '', 'RJSIMAGE', '')
GetTypeList
GetTypeList returns a list of Document Types. The lists include whether the document uses look-up lists for the title.
There are 4 ways to use the GetTypeList stored procedure:
- All Document Types - This list has no user access information.
- Document Types to which a user has access
- Users that have access to a Document Type - This option ignores the ''sort by description'' parameter.
- Access that user has to a Document Type - This option ignores the ''sort by description'' parameter.
Examples
- To retrieve all Document Types, sorted by Document Type only, use:
call rjsimage/gettypelist('', '', '', 'RJSIMAGE', '')
- To retrieve all Document Types, sorted first by Document Type description and then by Document Type, use:
call rjsimage/gettypelist('', '', 'Y', 'RJSIMAGE', '')
- To retrieve all Document Types to which the user TEST has access, sorted by Document Type only, use:
call rjsimage/gettypelist('TEST', '', '', 'RJSIMAGE', '')
- To retrieve all Document Types to which the user TEST has access, sorted first by Document Type description and then by Document Type, use:
call rjsimage/gettypelist('TEST', '', 'Y', 'RJSIMAGE', '')
- To retrieve the access that the user TEST has to the Document Type AP and ignore sorting, use:
call rjsimage/gettypelist('TEST', 'AP', '', 'RJSIMAGE', '')
- To retrieve all users that have access to Document Type AP and ignore sorting, use:
call rjsimage/gettypelist('', 'AP', '', 'RJSIMAGE', '')
GetKeyList
GetKeyList returns a list of document search keys. The lists include whether the search key:
There are 6 ways to use the GetKeyList stored procedure:
- All search keys
- Search keys for types to which a user has access
- Search keys for a Document Type - This option ignores the ''sort by description'' parameter.
- Search keys for a Document Type to which a user has access - This option ignores the ''sort by description'' parameter.
- Individual search key for a Document Type - This option ignores the ''sort by description'' parameter.
- Individual search key for a Document Type to which a user has access - This option ignores the ''sort by description'' parameter.
Examples
- To retrieve all search keys, sorted by Document Type only, use:
call rjsimage/getkeylist('', '', '', '', 'RJSIMAGE', '')
- To retrieve all search keys, sorted first by Document Type description and then by Document Type, use:
call rjsimage/gettypelist('', '', '', 'Y', 'RJSIMAGE', '')
- To retrieve the search keys for all Document Types to which the user TEST has access, sorted by Document Type only, use:
call rjsimage/gettypelist('TEST', '', '', '', 'RJSIMAGE', '')
- To retrieve the search keys for all Document Types to which the user TEST has access, sorted first by Document Type description and then by Document Type, use:
call rjsimage/gettypelist('TEST', '', '', 'Y', 'RJSIMAGE', '')
- To retrieve the search keys for the Document Type AP and ignore sorting, use:
call rjsimage/gettypelist('', 'AP', '', '', 'RJSIMAGE', '')
- To retrieve the search keys for the Document Type AP for the user TEST and ignore sorting, use:
call rjsimage/gettypelist('TEST', 'AP', '', '', 'RJSIMAGE', '')
- To retrieve the search key USERKEY1 for the Document Type AP and ignore sorting, use:
call rjsimage/gettypelist('', 'AP', 'USERKEY1', '', 'RJSIMAGE', '')
- To retrieve the search key USERKEY1 for the Document Type AP for the user TEST and ignore sorting, use:
call rjsimage/gettypelist('TEST', 'AP', 'USERKEY1', '', 'RJSIMAGE', '')
GetLookupList
GetLookupList returns look-up lists for document titles and search keys.
There are 6 ways to use the GetLookupList stored procedure:
- All look-up lists
- Look-up lists for Document Types to which a user has access
- Look-up lists for a Document Type - This option ignores the ''sort by description'' parameter.
- Look-up lists for a Document Type to which a user has access - This option ignores the ''sort by description'' parameter.
- The look-up list for the title or an individual search key for a Document Type - This option ignores the ''sort by description'' parameter.
- The look-up list for the title or an individual search key for a Document Type to which a user has access - This option ignores the ''sort by description'' parameter.
Examples
- To retrieve all look-up lists, sorted by Document Type only, use:
call rjsimage/getlookuplist('', '', '', '', 'RJSIMAGE', '')
- To retrieve all look-up lists, sorted first by Document Type description and then by Document Type, use:
call rjsimage/getlookuplist('', '', '', 'Y', 'RJSIMAGE', '')
- To retrieve look-up lists for all Document Types to which the user TEST has access, sorted by Document Type only, use:
call rjsimage/getlookuplist('TEST', '', '', '', 'RJSIMAGE', '')
- To retrieve look-up lists for all Document Types to which the user TEST has access, sorted first by Document Type description and then by Document Type, use:
call rjsimage/getlookuplist('TEST', '', '', 'Y', 'RJSIMAGE', '')
- To retrieve look-up lists for the Document Type SHIPPING and ignore sorting, use:
call rjsimage/getlookuplist('', 'SHIPPING', '', '', 'RJSIMAGE', '')
- To retrieve look-up lists for the Document Type SHIPPING for the user TEST and ignore sorting, use:
call rjsimage/getlookuplist('TEST', 'SHIPPING', '', '', 'RJSIMAGE', '')
- To retrieve the look-up list for the document title TITLE for Document Type SHIPPING and ignore sorting, use:
call rjsimage/getlookuplist('', 'SHIPPING', 'TITLE', '', 'RJSIMAGE', '')
- To retrieve the look-up list for the document title TITLE for the Document Type SHIPPING for the user TEST and ignore sorting, use:
call rjsimage/getlookuplist('TEST', 'SHIPPING', 'TITLE', '', 'RJSIMAGE', '')
User-defined Functions
User-defined functions are similar to the built-in functions of SQL and can be used in the same way. If you are using system naming, RJSIMAGE must be in the library list, because you cannot qualify functions with the library when using sytem naming. If you are using SQL naming, you can qualify the function with the library name. For example:
RJSIMAGE.function_name
There is one user-defined function at this time:
FolderIsValid
This function returns the validity status of a folder, that is, whether there are gaps in the levels (a higher level has a blank code) or the level has missing parent levels. This function has the following parameters:
- Folder level 1 (DOCFLR1) - This should never be blank, due to editing when folders are created.
- Folder level 2
- Folder level 3
- Folder level 4
- Folder level 5
- Program library - This is almost always RJSIMAGE. This library is added to the library list when the stored procedure is called.
- Data library - Leave this parameter blank for data that is in RJSIMAGE.If not blank and not the same as the program library, this library is added to the library list when the stored procedure is called.
Examples
- To return folders that are not valid, use:
select * from docflr00 where folderisvalid(docflr1,docflr2,docflr3,docflr4,docflr5,'RJSIMAGE','') <> 0
For example:
Folder 1 Folder 2 Folder 3 Folder 4 Folder 5
NATHAN NATHAN NATHAN NATHAN /RJSIMAGEDOC
VERN1 VERN2 Vern 2 /RJSIMAGEDOC
VERN1 VERN2 VERN4 Vern 4 /RJSIMAGEDOC
VERN1 VERN2 VERN3 Vern 3 /RJSIMAGEDOC
The first one and the last two are invalid because they do not have all the parents. The second folder is invalid because one of the levels is skipped - there is a gap.
- To return folders that are valid, use:
select * from docflr00 where folderisvalid(docflr1,docflr2,docflr3,docflr4,docflr5,'RJSIMAGE','') = 0
For example:
| Folder 1 |
Folder 2 |
Folder 3 |
Folder 4 |
Folder 5 |
Text |
| ACCOUNTING |
|
|
|
|
Accounting |
| ACCOUNTING |
AP |
|
|
|
A/P |
| ACCOUNTING |
AP |
SERVICES |
|
|
Services |
| ACCOUNTING |
AR |
|
|
|
A/R |
| ACCOUNTING |
GL |
|
|
|
G/L |
| AP |
|
|
|
|
WA Accounts Payable |
| AP |
INVOICES |
|
|
|
WA AP Invoices |
| A1 |
|
|
|
|
a1 |
| A1 |
A2 |
|
|
|
a2 |
| BAKERAR |
|
|
|
|
Baker AR |
| BAKERAR |
BAKERINVOICE |
|
|
|
Baker Sales Orders |
| BJSREPORTLEVEL1 |
|
|
|
|
L1 Desc |
| BJSREPORTLEVEL1 |
BJSREPORTLEVEL2 |
|
|
|
L2 Desc |
| CERTTRANS |
|
|
|
|
Transportation and Physical Development |
| etc. |
|
|
|
|
|
