• State Suggested Answer
  • Replies 6 replies
  • Answers 2 answers
  • Subscribers 6 subscribers
  • Views 4716 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Integrations

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

davidb545

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
  • 0

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

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

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

  • 0 in reply to ekanshj

    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.

  • 0 in reply to ekanshj

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

© 2024 Appian. All rights reserved.