Reputation: 2167
We created the Web API for querying the Oracle DB returning the result in the JSON in the below format.So the API will be getting the array of input parameters. As the query result is so huge like for the below query it returns 313568 when we try in the SQL Developer.
SELECT * from STCD_PRIO_CATEGORY where STPR_STUDY.STD_REF IN("BL001,TM002")
Below is the code we are using
public HttpResponseMessage Getdetails([FromUri] string[] id)
{
string connStr = ConfigurationManager.ConnectionStrings["ProDataConnection"].ConnectionString;
using (OracleConnection dbconn = new OracleConnection(connStr))
{
var inconditions = id.Distinct().ToArray();
var srtcon = string.Join(",", inconditions);
DataSet userDataset = new DataSet();
var strQuery = @"SELECT * from STCD_PRIO_CATEGORY where STPR_STUDY.STD_REF IN(" + srtcon + ")";
using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn))
{
using (OracleDataAdapter adapter = new OracleDataAdapter(selectCommand))
{
DataTable selectResults = new DataTable();
adapter.Fill(selectResults);
var returnObject = new { data = selectResults };
var response = Request.CreateResponse(HttpStatusCode.OK, returnObject, MediaTypeHeaderValue.Parse("application/json"));
ContentDispositionHeaderValue contentDisposition = null;
if (ContentDispositionHeaderValue.TryParse("inline; filename=ProvantisStudyData.json", out contentDisposition))
{
response.Content.Headers.ContentDisposition = contentDisposition;
}
return response;
When I am using the URL https://bhbl.abc.org/api/Sample?id='BL001'&id='TM002'
for querying the DB as
But when I am using the URL https://bhbl.abc.org/api/Sample?id='BL001' it returns result which is just 41552 records
How to create API which will return huge amount of data. Any help is greatly appreciated
Upvotes: 0
Views: 3002
Reputation: 417
This is because, api returned data is too large, the process to produce memory overflow. You need to use the paging sql:
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT *
FROM table_name) a
WHERE ROWNUM <= 40)
WHERE rn >= 21
Client proccess once, multiple calls api, complete all data acquisition.
The client core code below:
// ids: 'BL001' 'TM002', next_key: paging record index
while (next_key != null)
{
next_key = GetDetails(ids, next_key);
}
private string GetDetails(stirng[] ids, string next_key)
{
// call api
var result = ...;
// parse api reponse result
object key = result.next_key;
if (key == null) return null;
string s = key.ToString();
return string.IsNullOrEmpty(s) ? null : s;
}
The server core code below:
public HttpResponseMessage Getdetails([FromUri] string[] id, [FromUri] string next_key)
{
// use paging sql
// excute sql return record count
var count = ...
// return next_key
if (count < 20)
{
result.next_key = null;
}
else
{
result.next_key = (int.Parse(next_key) + 20).ToString();
}
return result;
}
Upvotes: 2