Example needed of using $filter parameter ODATA queries. - Forum - DataDirect Cloud - Progress Community

Example needed of using $filter parameter ODATA queries.

 Forum

Example needed of using $filter parameter ODATA queries.

  • I been playing around with ODATA on-off for the last few weeks and one thing I can't get working is using the $filter= URL parameter. The result comes back but is ignoring the specified $filter parameters.

    Do the field names specified in the $filter syntax needs to be indexed in the Schema Map for $filters to work?

    I'm using the documentations from here:

    http://www.odata.org/documentation/odata-version-2-0/uri-conventions/#FilterSystemQueryOption

  • Say I have a table Employees that I can query with the OData URL

    https://service.datadirectcloud.com/api/odata/OpenEdgeOPSports2000/Employees

    This returns results like the following

    {

     "d": {

       "results": [

         {

           "__metadata": {

             "uri": "service.datadirectcloud.com/.../Employees(1)",

             "type": "OpenEdgeOPSports2000.Employee"

           },

           "EmpNum": 1,

           "LastName": "Koberlein",

           "FirstName": "Kelly",

           "Address": "4500 Main Street",

           "Address2": "Apt 22",

           "City": "Houston",

           "State": "TX",

           "PostalCode": "75024",

           "DeptCode": "100",

           "Position": "Consultant",

           "HomePhone": "(214) 234-4657",

           "WorkPhone": "(617) 234-5748",

           "VacationDaysLeft": 8,

           "SickDaysLeft": 5,

           "StartDate": "\\/Date(862876800000)\\/",

           "Birthdate": "\\/Date(-37152000000)\\/"

         },

         {

           "__metadata": {

             "uri": "service.datadirectcloud.com/.../Employees(2)",

             "type": "OpenEdgeOPSports2000.Employee"

           },

           "EmpNum": 2,

           "LastName": "Andrews",

           "FirstName": "Anita",

           "Address": "10 Smith St",

           "Address2": "",

           "City": "Bedford",

           "State": "GA",

           "PostalCode": "66766",

           "DeptCode": "100",

           "Position": "Consultant",

           "HomePhone": "(617) 555-1111",

           "WorkPhone": "(617) 280-4599",

           "VacationDaysLeft": 0,

           "SickDaysLeft": 0,

           "StartDate": "\\/Date(878515200000)\\/",

           "Birthdate": "\\/Date(-127958400000)\\/"

         },

         {

           "__metadata": {

             "uri": "service.datadirectcloud.com/.../Employees(3)",

             "type": "OpenEdgeOPSports2000.Employee"

           },

           "EmpNum": 3,

           "LastName": "Smith",

           "FirstName": "Justine",

           "Address": "1342 Atlantic Ave",

           "Address2": "Apt 345b",

           "City": "Boston",

           "State": "MA",

           "PostalCode": "01834",

           "DeptCode": "400",

           "Position": "Sales Manager",

           "HomePhone": "617 333-3334",

           "WorkPhone": "800 787-8484",

           "VacationDaysLeft": 5,

           "SickDaysLeft": 4,

           "StartDate": "\\/Date(866678400000)\\/",

           "Birthdate": "\\/Date(-315014400000)\\/"

         },

         . . .

    next let me limit the number of columns so we can see more rows at a time

    https://service.datadirectcloud.com/api/odata/OpenEdgeOPSports2000/Employees?$select=FirstName,LastName,City,State

    This returns the following results

    {

     "d": {

       "results": [

         {

           "__metadata": {

             "uri": "service.datadirectcloud.com/.../Employees(1)",

             "type": "OpenEdgeOPSports2000.Employee"

           },

           "FirstName": "Kelly",

           "LastName": "Koberlein",

           "City": "Houston",

           "State": "TX"

         },

         {

           "__metadata": {

             "uri": "service.datadirectcloud.com/.../Employees(2)",

             "type": "OpenEdgeOPSports2000.Employee"

           },

           "FirstName": "Anita",

           "LastName": "Andrews",

           "City": "Bedford",

           "State": "GA"

         },

         {

           "__metadata": {

             "uri": "service.datadirectcloud.com/.../Employees(3)",

             "type": "OpenEdgeOPSports2000.Employee"

           },

           "FirstName": "Justine",

           "LastName": "Smith",

           "City": "Boston",

           "State": "MA"

         },

         {

           "__metadata": {

             "uri": "service.datadirectcloud.com/.../Employees(4)",

             "type": "OpenEdgeOPSports2000.Employee"

           },

           "FirstName": "Andrew",

           "LastName": "Shaw",

           "City": "Princeton",

           "State": "NJ"

         },

         {

           "__metadata": {

             "uri": "service.datadirectcloud.com/.../Employees(5)",

             "type": "OpenEdgeOPSports2000.Employee"

           },

           "FirstName": "Kelley",

           "LastName": "Bradford",

           "City": "New York",

           "State": "NY"

         },

         . . .

    next lets filter the result to show only the employees that live in North Carolina

    https://service.datadirectcloud.com/api/odata/OpenEdgeOPSports2000/Employees?$select=FirstName,LastName,City,State&$filter=State eq 'NC'

    This returns the following results

    {

     "d": {

       "results": [

         {

           "__metadata": {

             "uri": "service.datadirectcloud.com/.../Employees(13)",

             "type": "OpenEdgeOPSports2000.Employee"

           },

           "FirstName": "Luke",

           "LastName": "Sanders",

           "City": "Chapel Hill",

           "State": "NC"

         },

         {

           "__metadata": {

             "uri": "service.datadirectcloud.com/.../Employees(38)",

             "type": "OpenEdgeOPSports2000.Employee"

           },

           "FirstName": "Harold",

           "LastName": "Tedford",

           "City": "Charlotte",

           "State": "NC"

         },

         {

           "__metadata": {

             "uri": "service.datadirectcloud.com/.../Employees(41)",

             "type": "OpenEdgeOPSports2000.Employee"

           },

           "FirstName": "Dawn",

           "LastName": "Weston",

           "City": "Charlotte",

           "State": "NC"

         }

       ]

     }

    }

    Finally a little more complex filter that returns Employees that live in North Carolina, but do not live in Chapel Hill

    https://service.datadirectcloud.com/api/odata/OpenEdgeOPSports2000/Employees?$select=FirstName,LastName,City,State&$filter=State eq 'NC' and City ne 'Chapel Hill'

     

    This returns the following results

    {

       "d": {
          "results": [
             {
                "__metadata": {
                   "uri": "https://service.datadirectcloud.com/api/odata/OpenEdgeOPSports2000/Employees(38)",
                   "type": "OpenEdgeOPSports2000.Employee"
                },
                "FirstName": "Harold",
                "LastName": "Tedford",
                "City": "Charlotte",
                "State": "NC"
             },
             {
                "__metadata": {
                   "uri": "https://service.datadirectcloud.com/api/odata/OpenEdgeOPSports2000/Employees(41)",
                   "type": "OpenEdgeOPSports2000.Employee"
                },
                "FirstName": "Dawn",
                "LastName": "Weston",
                "City": "Charlotte",
                "State": "NC"
             }
          ]
       }
    }

    I hope this helps.  One thing to note is that the Entity names and Property names used in the OData URL are case sensitive.  They must match the case returned by the Service Document or MetaData Document..  If you are still having problems with the $filter, send some examples of the OData query you are trying.

  • Okay. Slowly narrowing it down.

    Now I want to get a next dataset in the query but I get Error 500. I get the url/uri from the __next property. Could this be something to do with cookies?

    This will cause an error 500

    Example:

    service.datadirectcloud.com/.../TR_DAILY_RESULTS_DISHES$orderby=DAYS%20desc&$filter=CAMPAIGN_CODE%20eq%20'SJCSCAN01'&$skiptoken=ryYzJXGpvibXLHiV-1-0-0-0-"

  • I don't know if this is a typo in the forum post or not, but the double quote at the end of the URL above should not be there, but I tried adding the extra quote at the end in one of the tests I ran and it seems to be ignored.

    What is the message that is returned with the 500 return code?  How long was it between when the initial query was submitted and when the next token URL was submitted.  There is a time limit for how long the response for the initial query is retained.  For OData queries, the results are maintained for 10 minutes.  If I submit a query that returns a next URL and then execute that next URL more than 10 minutes after the original query, the service returns an error response with a 500 status code and the message "Your session has timed out".

    {

     "error": {

       "code": "222206013",

       "message": {

         "lang": "en-US",

         "value": "Your session has timed out."

       }

     }

    }

    If that is not the cause the we will need to get some more info about the query executed.  In particular what user it was executed by, what data source or URL it was executed against and the approximate time it was executed.

  • Yes, the double is needed because I'm call ODATA via cURL & wget. The '&'  gets interpreted on the command line hence the reason for double quotes. The 'next ' request is happing well within the 10 minutes. When the server does return Error 500, it does not output any reason.

    Also I today the ODBC & DDC trial expired today so I'm unable to continue with my testing.

  • Looking at the logs, it looks like the results expired before the query with the skip token was executed. I will send details in an email