Forum Stats

  • 3,824,874 Users
  • 2,260,435 Discussions
  • 7,896,336 Comments

Discussions

Form on REST Data Source occasionally returns no data found error (but there's no local data)

Carey A
Carey A Member Posts: 6 Green Ribbon

I created an interactive report and form on a REST data source using APEX's page builder tools. On some rows, I will get a no data found error. Normally this would mean the PK was not found in the local table, but I'm not using any tables. Or it could mean the PK isn't getting passed to the form page, but I've confirmed that it is being passed over via the debug tools. The vast majority of rows work as expected, but a handful of rows consistently return this error. In Postman, these records work as expected, so I don't think there's anything wrong with the source data, which is a Shopify website. These records also work fine on Shopify itself. I've manually compared the rows that work to the rows that don't, and can't see anything wrong. The backtrace on the no data found error sends me to APEX code (see pic).

Has anyone seen this before and solved it? This is version 22.1.0.


Answers

  • Carey A
    Carey A Member Posts: 6 Green Ribbon

    To be clear, this error happens when I click the edit button on the interactive report. Usually it loads the data in the form as expected, but for a few rows it returns no data found. Using Chrome 102.0.5005.61 on a Mac running Monterey 12.2.1.

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,329 Employee

    Hi,

    can you turn on debug mode, and then reproduce the No Data Found error ...? The debug output will contain a detailed error stack which will help to diagnose the cause of the issue.

    regards

    Carsten

  • Carey A
    Carey A Member Posts: 6 Green Ribbon

    Hi Carsten, I've been watching your videos to try and figure this out.

    Here's a screenshot of the debug output. I'd be happy to get you a login if you have a few minutes to look at it. Thanks!

    -Carey


  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,329 Employee

    Hi Carey,

    based on the error stack, the NO Data Found is indeed thrown as the form region does not see any row in the data from the REST Data Source.

    As you're only seeing this "for a few rows": Are the primary keys pure numeric values ...? Or is there some formatting / escaping involved?

    Did APEX invoke the URL for the REST Data Source correctly? You should see that in the debug log as well - there should be a line saying "Making .... request to ..." with the URL being invoked. Are all parameters as you'd expect these?

    To say more, I would indeed need to have a closer look into this.

    regards

    -Carsten

  • Carey A
    Carey A Member Posts: 6 Green Ribbon

    It uses the GET request for multiple records, not the GET for a single record, which surprised me. But otherwise it looked as I expected it to look.

    making GET request to https://stash-marketplace.myshopify.com/admin/api/2022-04/products.json?created_at_max=&created_at_min=&limit=250&product_type=&status=&updated_at_min=&vendor=, using request body:
    

    They are all numeric IDs.

  • Carey A
    Carey A Member Posts: 6 Green Ribbon

    I just sent you a message with login info if you have a few minutes to check it out. I totally understand if you don't.


    Carey

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,329 Employee

    Hi Carey,

    I now had a look into your application, and I can indeed see that the Interactive Grid (on page 3) and the Form (on page 4) actually invoke different URLs.

    Interactive Grid invokes ...

    https://{host}/{path}/2022-04/products.json?created_at_max=&created_at_min=&limit=250&product_type=Apparel%20Fabric&status=&updated_at_min=&vendor=Destashify,
    

    while the Form region invokes:

    https://{host}/{path}/2022-04/products.json?created_at_max=&created_at_min=&limit=250&product_type=&status=&updated_at_min=&vendor=
    

    My suspicion now is that (because of the different parameters), the Interactive Grid works on a different result set than accessible to the Form. When we now try to open the form for the specific row (which was part of the Interactive Grid result set), the request for the Form region does not contain this row. The REST API might also cut off results after some amount of rows (limit parameter), which can also lead to the report getting a different result set than the Form.

    So I would align the REST Source parameters for the report and the form, so that they both work on the same set of data.

    BTW: As there is a limit parameter, I suspect that the REST API uses some sort of result pagination. You might be able to set that pagination up for the REST Data Source also (Pagination attributes).

    Does that help?

    regards

    -Carsten

  • Carey A
    Carey A Member Posts: 6 Green Ribbon

    Everything is working now, and I found out a few things.

    1) The Shopify API uses the same parameter name as different types of parameters in different operations. So the ID parameter might be used as a query string variable in one operation, a URL Pattern variable in another operation, and a Request Body variable in another operation. I was trying to put ID in the list of parameters associated with the entire Data Source, but APEX does not accept multiple parameters with the same name but different types. I moved most variables into their respective operations, even though the same variable and type combo might be used both in PUT and POST, for instance. That removed most of my problems. (However, there is one operation which uses ID in two places in the HTTP as different parameter types and I don't have a solution for that yet.)

    2) I found that some parameters weren't being populated when the HTTP call was made. That turned out to be a case issue. I had experimented with both lower case and upper case parameter names, and only lower case parameter names work when it came to populating the POST call.

    3) I'm also having pagination problems. Shopify uses a type of pagination which isn't currently supported by APEX, so I'm using the 'no pagination' option and synchronization steps as a way to get around that. It's kludgy and I'm looking for a better way without writing a bunch of code by hand to deal with pagination. Very possible that this is user error, and I'll be looking into it again soon.

    4) I'm surprised to see that I don't ever see the GET call for a single record in View Debug; it's always the GET call for multiple records, even when I click the Edit button for one record. I have both GET calls set up in my REST Data Source. Will be looking into this again too.

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,329 Employee

    Hi Carey,

    for such cases, APEX allows to use REST Data Source Plug-Ins.

    So far, you're using the Simple HTTP REST Data Source type. This type supports a few pagination schemes. The URL is built bases on configured parameters, and for POST or PUT requests, the JSON request body is built based on a few standard assumptions.

    To fully leverage a specific REST API, i.e. support a specific pagination scheme, built URLs in a specific way and so on, a REST Source Plug-In can be used.

    The Plug-In architecture provides a set of interfaces, which are implemented by the Plug-In developer. For a REST Data Source Plug-In these are the "fetch", "discovery", "dml" or "execute" callbacks. When a Plug-In is in place, APEX calls your implementation when fetching data from the REST API, instead of using its built-in logic.

    An example for a REST Source Plug-In is here:

    https://github.com/oracle/apex/tree/21.2/plugins/rest-source/fixed-page-size

    It only implements the "fetch" function, but that illustrates how you can follow a specific pagination schema which the REST API uses. Of course, the Plug-In implementation is custom PL/SQL code, i.e. some effort which has to be taken.

    I hope this helps

    -Carsten