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:
  1. WebDocs user - Leave this parameter blank to retrieve all valid folders, Document Types, search keys, or look-up list values.
  2. 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.
  3. Program library - This will almost always be RJSIMAGE. This library is added to the library list when the stored procedure is called.
  4. 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:
  1. All folders - This list has no user access information.
  2. Folders to which a user has access
  3. Users that have access to a folder - This option ignores the ''sort by description'' parameter.
  4. 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:
  1. All Document Types - This list has no user access information.
  2. Document Types to which a user has access
  3. Users that have access to a Document Type - This option ignores the ''sort by description'' parameter.
  4. 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:
  • Is displayed in browser document lists.
  • Is required.
  • Is read-only.
  • Uses look-up lists for its values.
  • Is a join or data field.

    Note: This feature was not implemented at the time this article was written.

There are 6 ways to use the GetKeyList stored procedure:
  1. All search keys
  2. Search keys for types to which a user has access
  3. Search keys for a Document Type - This option ignores the ''sort by description'' parameter.
  4. Search keys for a Document Type to which a user has access - This option ignores the ''sort by description'' parameter.
  5. Individual search key for a Document Type - This option ignores the ''sort by description'' parameter.
  6. 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:
  1. All look-up lists
  2. Look-up lists for Document Types to which a user has access
  3. Look-up lists for a Document Type - This option ignores the ''sort by description'' parameter.
  4. Look-up lists for a Document Type to which a user has access - This option ignores the ''sort by description'' parameter.
  5. The look-up list for the title or an individual search key for a Document Type - This option ignores the ''sort by description'' parameter.
  6. 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:
  1. Folder level 1 (DOCFLR1) - This should never be blank, due to editing when folders are created.
  2. Folder level 2
  3. Folder level 3
  4. Folder level 4
  5. Folder level 5
  6. Program library - This is almost always RJSIMAGE. This library is added to the library list when the stored procedure is called.
  7. 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.          

 


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: December 10, 2016