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
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) ) )
Hi davidb545You 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,
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 :-)