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

Question

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?

[Added extra detail]

I have [also] tried writing a seperate api which [only] returns the row-count for my entity, but it [also] returns error 500...

a!localVariables(
  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(value: local!entities)
  )
)

thanks heaps,

David.

David · Accepted Answer · 2020-01-30T01:15:45.833

with(
  local!entities: a!queryEntity(
    entity: cons!MY_DATABASE_TABLE_DS,
    query: a!query(
      pagingInfo: a!pagingInfo(
        startIndex: 1,
        batchSize: 0
      )
    ),
    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...

I got the [original] code working as well, by similarly changing the batch size to a smaller number [rather than -1 to retrieve all records]. It turns out that retrieving all records is not necessary to get a correct value for this totalCount field:

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

In actual fact, setting the batchsize to 0 [for this application] is the best option since this gets the metadata (including the totalCount) without actually wasting any processing time retrieving the rows of data (which aren't used in this instance) - thereby increasing the performance (Thanks Mike Schmitt for the tip on this one).

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

1 Answers1