IBM Support

PH13222: REST API DOESN?T ALLOW FOR THE COMMA TO BE ESCAPED IN QUERY

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error description

  • Description:
    
    If a field within OpenPages has a comma in it and you try to use
    the REST API and/or the query-test.jsp to query the system it
    returns an error.  If you try to escape the comma as outlined in
    this documentation on PDF Page 6 it doesn?t work either:
    http://public.dhe.ibm.com/software/data/cognos/documentation/ope
    npages/en/8.0.0/GRC_REST_API.pdf
    
    Testing has been done on Enum Field values as well as Simple
    String Values.
    
    Prerequisites:
    
    OpenPages 8.0.0.2 with Solutions
    
    1.	Copy the query-test.jsp to the proper location on the server
    a)	Go to this link and click on ?OpenPages GCR SDK REST Samples?
    and save the file.
    https://www-01.ibm.com/support/docview.wss?uid=swg27051151#opv8r
    0m0en
    b)	Extract the zip file
    c)	Copy the query-test.jsp to
    <op_home>/profiles/<Appserver_Nodename>/installedApps/OpenPagesC
    ell/op-apps.ear/sosa.war
    i.	where
    ii.	<OpenPages_Home> is the installation location for OpenPages
    (e.g., C:\OpenPages)
    iii.	<Appserver_Nodename> is the name of the WebSphere
    application server node (e.g., qe-vm18-OPNode1)
    2.	Create a Field Group
    a.	Log into OpenPages as the Administrator
    b.	Switch to the Standard UI if necessary
    c.	Navigate to Administration -> Field Groups
    d.	Click ?Add? and provide a name, then click ?Create?
    3.	Create two fields, one Enum and on Simple String, within the
    field group
    a.	Click on the field group added above
    b.	Click ?Add? next to Field Definitions
    c.	Provide a name and select ?Enumerated String? as the Data
    Type, click the >>
    d.	Add two values, one with a comma and one without
    e.	Click ?Create?
    f.	Click ?Add? again to create a second field
    g.	Provide a name and select ?Simple String? as the Data Type
    h.	Click ?Create?
    4.	Add the Field group to the Object Type
    a.	Navigate to Administration -> Object Types
    b.	Select the Object Type (for this example we will use
    SOXProcess)
    c.	Enable System Admin Mode
    d.	Next to ?Included Field Groups? click ?Include?
    e.	Select the Field Group created above and click ?Add?
    f.	Disable System Admin Mode
    5.	Add the fields to the Profile and Detail view of the profile
    a.	Navigate to Administration -> Profiles
    b.	Select ?OpenPages Modules 7.4.0 Master?
    c.	Select SOXProcess under ?Object Types?
    d.	Next to ?Object Fields? click ?Include?
    e.	Select the two fields you create and click ?Include?
    f.	Scroll down to ?Object Views? and click on ?Detail?
    g.	Click on ?Choose Fields?
    h.	Select your two fields and click ?Apply?
    i.	Click ?Save?
    6.	Create the Hierarchy (Business Entity -> Process)
    a.	Navigate to Organization -> Business Entities
    b.	Create a new Business Entity, fill in all required fields and
    click ?Save?
    c.	Click the Business Entity to go to the Detail View
    d.	On the left under ?Associations? click on ?Processes?
    e.	Click ?Actions? and then ?Add a New Process?
    f.	Fill in all the required fields and click ?Save?
    g.	Next to the process click ?Actions -> Edit this Process?
    h.	Update your two fields to select the enum WITHOUT commas and
    put in a string WITHOUT commas
    i.	Click ?Save?
    j.	Repeat the steps to create another process but this time set
    the two fields to the enum value WITH the comma and the string
    WITH a comma
    
    
    Steps to Reproduce:
    
    1.	Log into OpenPages as the Administrator
    2.	Ensure the OpenPages Modules 7.4.0 Master profile is being
    used
    3.	Change the url to be this
    http://<servername>:<port>/openpages/query-test.jsp
    4.	Create the query to return the Enum Value WITHOUT the comma
    a.	For example: SELECT * FROM [SOXProcess] where
    [SOXProcess].[1:Amy_Test_Enum] IN ('Value1')
    i.	Where ?1? is the field group name, ?Amy_Test_Enum? is the
    field name and ?Value1? is the enum value
    b.	Click ?Execute? and note it returns a row
    5.	Update the query to look for the enum value WITH a comma in
    it
    a.	For example: SELECT * FROM [SOXProcess] where
    [SOXProcess].[1:Amy_Test_Enum] IN ('Value2, Value3')
    i.	Where ?1? is the field group name, ?Amy_Test_Enum? is the
    field name and ?Value2, Value3? is the enum value
    b.	Click ?Execute?
    c.	Note the Error
    6.	Update the query to look for the simple string value WITHOUT
    the comma
    a.	For example: SELECT * FROM [SOXProcess] where
    [SOXProcess].[1:Amy_Test_String] IN ('no comma')
    i.	Where ?1? is the field group name, ?Amy_Test_String? is the
    field name and ?no comma? is the string value
    b.	Click ?Execute? and note the results are returned
    7.	Update the query to look for the string WITH a comma in it
    a.	For example: SELECT * FROM [SOXProcess] where
    [SOXProcess].[1:Amy_Test_String] IN ('string, comma')
    i.	Where ?1? is the field group name, ?Amy_Test_String? is the
    field name and ?string, comma? is the string value
    b.	Click ?Execute? and note the error is returned
    8.	Try escaping the comma by replacing it with %2C
    a.	For example: SELECT * FROM [SOXProcess] where
    [SOXProcess].[1:Amy_Test_String] IN ('string%2C comma')
    b.	Click ?Execute?
    c.	Note there is no error, but it also returns no results
    

Local fix

  • Don?t use commas in your field values if you plan to use REST
    API/query-test.jsp to retrieve values.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * OpenPages Users                                              *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * REST API DOESN?T ALLOW FOR THE COMMA TO BE ESCAPED IN QUERY  *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Customers should download OpenPages with Watson 8.2 Fix Pack *
    * 1 (8.2.0.1). See the following document for details on       *
    * obtaining OpenPages 8.2.0.1:                                 *
    * https://www.ibm.com/support/pages/openpages-watson-82-fix-pa *
    * ck-1                                                         *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    PH13222

  • Reported component name

    OPENPAGES GRC

  • Reported component ID

    5725D5100

  • Reported release

    800

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-06-11

  • Closed date

    2020-11-23

  • Last modified date

    2020-11-23

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    OPENPAGES GRC

  • Fixed component ID

    5725D5100

Applicable component levels

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSFUEU","label":"IBM OpenPages with Watson"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"800"}]

Document Information

Modified date:
24 November 2020