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
  • 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)
      )
    )
  • 0
    A Score Level 2
    in reply to davidb545

    Hi
    You need to change the way you pass the body of the response

  • 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 :-)

Reply
  • 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 :-)

Children
No Data