How to get a count of database rows in Appian web api

I am trying to write a custom web-api in Appian that will [among other things] return a count of the number of rows in a database table. To that end, I have added this local variable in my api code.

local!countOfRows: a!queryEntity(
    entity: cons!MY_DATABASE_TABLE_DS,
    query: a!query(
      pagingInfo: a!pagingInfo(
        startIndex: 1,
        batchSize: -1
      )
    ),
    fetchTotalCount: true
  ).totalCount,

The idea is that I will then include this value as one of the output's in the json. For example:

local!dataBaseCasesWithDocs: {
    numRecs: local!countOfRows,
    recList: local!listOfRecords
}

So far the recList item works just fine - producing a nice json list of rows of data from my table [albeit 10 at a time]. But when i add the code for the countOfRows using the numRecs field, the function fails with an error 500.

Any thoughts?

thanks heaps,

David.

  Discussion posts and replies are publicly visible

Parents Reply Children
  • Thanks Ekansh - I have updated the code to this:


    with(
    local!entities: a!queryEntity(
    entity: cons!MY_DATABASE_TABLE_DS,
    query: a!query(
    pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: -1
    )
    ),
    fetchTotalCount: true
    ).totalCount,
    a!httpResponse(
    headers: {
    a!httpHeader(name: "Content-Type", value: "application/json")
    },
    body: a!toJson({count: local!entities})
    )
    )

    However, it still shows error 500.  I am wondering if there is too much data? There are about 7000 rows in the relevant database table - so I wouldn't have thought it was too big - do you think it is?

    cheers,

    David.

  • Ok - I have got the code working now - the final code is this:

    with(
    local!entities: a!queryEntity(
    entity: cons!MY_DATABASE_TABLE_DS,
    query: a!query(
    pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 10
    )
    ),
    fetchTotalCount: true
    ).totalCount,
    a!httpResponse(
    headers: {
    a!httpHeader(name: "Content-Type", value: "application/json")
    },
    body: a!toJson({count: local!entities})
    )
    )

    The only difference was that I added a batch size of 10.  It [nevertheless] returns the correct number of rows in the database...

    thanks heaps for your help.

    David :-)