Queries

Objective of a query resource is to allow user to query OIPA using ad-hoc, predefined, user defined queries. Ad-hoc and predefined queries are out of scope for this phase. Only SELECT statements are supported.

POST and GET Methods

The user can use POST / GET requests to perform resource queries.

• POST operation should be used to create a SQL query in OIPA
• POST should be used only using development phase.
• During run time GET can be used to run the query already stored in OIPA.
• Bad SQLs that server cannot process will receive HTTP 400 error . This error is thrown when there is a client error (e.g., malformed request syntax, size too large, invalid request message framing, or deceptive request routing and also if INSERTS or DELETEs are added in SQL). The structure of this POST is given below.

• If successful, service layer will respond with HTTP 200 OK for the POST. This means that the query is recorded in the table. It does NOT mean query is a working without errors.
• If same applicationName and same queryName is sent in POST, service layer will record it as a new version number.

• Users can specify version number in URI if they want to execute an old version as GET /queries/getPendingPolicies? VersionNumber=1
• If VersionNumber is not specified, service layer will assume it as the latest version
• User can also specify applicationName in URI as GET/queries/getPendingPolicies?ApplicationName=AlamereMobileApp, it is recommended to use applicationName in GET/queries
• During run time GET is used to execute the query. Query parameters can be send in the GET request.
• The syntax of the query in queryValue will be same as SQL syntax in OIPA configuration.
• Raw data from database will be sent back in JSON format in GET.
• Only allow client to create new queries during development. The mode will be set in properties file. Based on that mode, POST will be allowed or disallowed.
• In production, only queries already defined server-side can be used.
• If the query GET results are more that 100, only 100 results will be sent.
• If the request is too long, HTTP protocol does not place any apriori limit on the length of a URI. If URI length is more than what server can handle , server should return 414 (Request-URI Too Long) status.

POST

POST request allows the user to create or define a query in OIPA, which can be used later.

Example:

Post ……/queries {

            "applicationName": "AlamereModileApp1",

            "queries": [{

                        "queryName": "pendingPoliciesByPlan",

                        "queryValue": "select policyguid, policynumber, 

 			Status from Aspolicy where status=02 and 

			planguid=[planGuid]"

            }]

}

GET

The user can use GET request to "Get the results of the query" using the below syntax:

/queries/queryname? parameter 1=value & parameter2=value 

Example: 

{

    "count" = 10,

    "results": [{

            "policyguid": "59028388-E882-47AB-8654-1301C8E9E604",

            "policynumber": "COATest1",

            "policyname": "COATest",

            "creationdate": "2014-03-01T23:46:11-05:00",

            "issuestatecode": "38",

            "plandate": "2014-03-01T23:46:11-05:00"

            "statuscode": "08",

            "companyguid": "05711222-7DB3-4EBD-A821-57A30A699B88",

            "planguid": "C5EE0DA4-3003-42C1-9468-D91DA546B79F",

            "updatedgmt": ""2014-03-01T23:46:11-05:00",

            "systemcode": "01"

        },

        {

            "policyguid": "70363535-D87E-4644-8F0F-375576A39B79",

            "policynumber": "SGTest01",

            "policyname": "SGTest",

            "creationdate": "31-JUL-15 12.00.00.000000000 AM",

            "issuestatecode": "05",

            "plandate": "31-JUL-15 12.00.00.000000000 AM",

            "statuscode": "08",

            "companyguid": "05711222-7DB3-4EBD-A821-57A30A699B88",

            "planguid": "C5EE0DA4-3003-42C1-9468-D91DA546B79F",

            "updatedDateTime": "2014-03-01T23:46:11-05:00",

            "systemcode": "01"

        }

    ]

}