MyApi Export Pagination

bill budwitz

• April 07, 2022 19:49

A very simple script that calls MSSQL to retreive all the records from a table.  I get the first 20 records (of 854),  how do i go about getting the remaining records (pages)?

thanks

bill

script below

import { exports } from 'integrator-api'

function _mainExport (options) {
  let invokeExportResponse;
  let response = {};

  let PORObject = return_POR_JobSites_Obj (options);

  //Execute the export
  try {
    invokeExportResponse = exports.runVirtual({export:PORObject});
    //invokeExportResponse = PORObject;
    response.statusCode = 200;
  } catch(e) {
    invokeExportResponse = JSON.stringify(e);
    response.statusCode = 400;
  }

  // Create body response
  response.body =  invokeExportResponse;
  
  return {
    statusCode: response.statusCode,
    headers: { },
    body: response.body
  }
}

function return_POR_JobSites_Obj (custId) {

  let PORObject = {
  "_connectionId": "60***************53",
  "apiIdentifier": "ea*****13",
  "asynchronous": true,
  "oneToMany": false,
  "parsers": [],
  "rdbms": {
    "query": "Select * from [POR99].[dbo].[CustomerJobSite]"
  },
  "adaptorType": "RDBMSExport"
}
  

  return PORObject;
}

0

• 

  Scott Henderson CTO

  • April 07, 2022 20:23

  

  

  

  1. You can configure the 'pageSize' property in the export definition to return more than 20 records at a time.  Depending on the size of the records in the database, and the client application processing the records, you can make the 'pageSize' property as high as 10000, and then you might not have to deal with paging at all.
  2. If paging is required, then you should use the function 'export.runVirtualWithPaging', and then it is really easy to incorporate paging into your function calls.

  0
• 

  bill budwitz

  • April 07, 2022 20:38

  

  

  Scott,

  thank you for the quick response.  Added pageSize to definition and still get back only 20 records.  Maybe wrong place or typo?  

  function return_POR_JobSites_Obj () {

    let PORObject = {
    "_connectionId": "60ecb92f850e251d51b9cb53",
    "apiIdentifier": "ea055d5f13",
    "asynchronous": true,
    "oneToMany": false,
    "pageSize":1000,
    "parsers": [],
    "rdbms": {
      "query": "Select * from [POR99].[dbo].[CustomerJobSite]"
    },
    "adaptorType": "RDBMSExport"
  }
      return PORObject;
  }

  0
• 

  Scott Henderson CTO

  • April 07, 2022 21:06

  

  

  

  It's very hard to say where the problem is.  The pageSize property is a very heavily used property in flows, and also in our REST API.  See here for an example in a REST API call.  The JavaScript functions use this same API behind the scenes, so in theory it should work, but maybe there is a bug somewhere related to this being a database, or being in the MyAPI context.  I would contact support to get some help at this point.

  0
• 

  bill budwitz

  • April 07, 2022 22:10

  

  

  Scott Henderson

  Thank again for the quick reply.   i will covert my direct SQL call to using a RESTful API that delivers the same results.   If that has an issue, i will run the issue up the flag pole with support.

  bill

   

  0

Please sign in to leave a comment.