MyApi Export Pagination

Avatar
bill budwitz
Celigo University Level 1: Skilled
Engaged

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

Comments

4 comments
Date Votes
  • Avatar
    Scott Henderson
    CTO
    Celigo University Level 1: Skilled
    Answer Pro
    Top Contributor
    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
  • Avatar
    bill budwitz
    Celigo University Level 1: Skilled
    Engaged

    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
  • Avatar
    Scott Henderson
    CTO
    Celigo University Level 1: Skilled
    Answer Pro
    Top Contributor

    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
  • Avatar
    bill budwitz
    Celigo University Level 1: Skilled
    Engaged

    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.

 

Didn't find what you were looking for?

New post