Stored Procedure call returns Code 500 with Response Body: no content

REST API
1

I make a stored procedure call to a MySQL db using the REST API. It works fine when the result set has a small number of rows to return. However when the result dataset is larger I get Response Code 500 with Response Body: no content.
Testing the same stored procedure call via a db client, it works fine and I get the result of the stored procedure call.

I tested REST API’s stored procedure call with several different stored procedures (with and without parameters) and I get the same behavior (no content in the body even if the stored procedure works fine). What could be the reason for that?

I used both versions 1.7.8 and 1.8 and got the same results.

2

If you haven’t already, try calling the stored procedure from the API Docs in the DSP admin console. Just curious to see if you get the same result there.

3

I did try to call the stored procedures from API Docs in the DSP admin console and I am getting the same response (no content). I believe It is not specific to a single stored procedure (tested with a few different ones) and I am still getting “no content” when the procedure returns a few hundred rows back.
My configuration: Using the Bitnami distribution (1.7.8 and 1.8) and MySQL.

4

I created a stored procedure on my 1.8.0 OS X Bitnami install that does a select on a single table of 1300 records.

BEGIN
   SELECT *  FROM contact;
   END

It worked fine. Have you tried a really simple one like this? Care to post your stored procedure so we can see it?

5

I did try with a simple select:

CREATE PROCEDURE cube_survey.test()
BEGIN
select * FROM fact_aggreegates_view_stage;
END;

I get the same “no content” response. The table has about 700K rows. Here is the table description:

CREATE TABLE fact_aggreegates_view_stage (
id int(11) NOT NULL DEFAULT ‘0’,
DIM_SURVEY_id int(11) NOT NULL,
DIM_TIME_id int(11) NOT NULL,
DIM_STATE_id int(11) NOT NULL,
DIM_INDUSTRY_id int(11) NOT NULL,
DIM_SIZE int(11) DEFAULT NULL,
DIM_ANSWERS_id int(11) NOT NULL,
total_count int(11) DEFAULT NULL,
total_percent double DEFAULT NULL,
load_date datetime DEFAULT NULL,
time_year int(11) DEFAULT NULL,
time_quarter int(11) DEFAULT NULL,
time_quarter_short char(3) DEFAULT NULL,
time_quarter_long char(15) DEFAULT NULL,
time_month int(11) DEFAULT NULL,
time_month_short char(3) DEFAULT NULL,
time_month_long char(10) DEFAULT NULL,
state varchar(2) DEFAULT NULL,
state_region_id int(11) DEFAULT NULL,
state_region varchar(45) DEFAULT NULL,
industry_short varchar(100) DEFAULT NULL,
industry_long varchar(200) DEFAULT NULL,
employeesize varchar(45) DEFAULT NULL,
resp_qcode char(10) DEFAULT NULL,
resp_q_short varchar(100) DEFAULT NULL,
resp_q varchar(300) DEFAULT NULL,
resp_acode int(11) DEFAULT NULL,
resp_a_short varchar(100) DEFAULT NULL,
resp_a varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Calling the procedure from the API Docs is not working (returns Error 500 - no content). Calling getRecords() from the table directly works fine. Strange.

6

Do you really need to return 700K records to the client? That’s a lot of data. Based on your previous post I thought you were talking about hundreds of records not hundreds of thousands. getRecords in the API docs only returns the first page of 1000 records so that’s why it worked. Stored procedures return all the data in one response.

7

Even if I filter the table in the stored procedure the Rest API still returns “No content” using the callStoredProc(). The result set is just 1300 rows.In another stored procedure it would work with 96 rows but it will not work with 144 rows.

8

Hi. Were you able to solve this problem. If so, can you please post the solution. I am facing the same problem with Oracle 11g database. I am able to connect to it from a php code but not via rest api.

9

Hi @rakeshdebur, are you certain for your Oracle connection you’re receiving the same Response Code 500 with Response Body: no content as the topic in this thread?