Re: PrepareAndExecute statement return only 100 rows

[Josh Elser]

Great! Glad to hear it, Puneeth.

I was digging around in the code yesterday for a different reason, and I 
think the documentation may be wrong depending on the version you're using.

In the changes introduced by 
https://issues.apache.org/jira/browse/CALCITE-1243, you'll notice that 
max_row_count was removed in favor of max_rows_total and 
max_rows_in_first_frame, which limit the number of records returned by a 
query in total and the number of records returned in the 
ExecuteResponse, respectively.

Prior to this change (I'm guessing in the version you are using), -1 
would not limit the total number of results for a query, but you would 
still be limited by the number of results in one frame (as described). 
It appears that you could also provide maxRowCount=-2 which would return 
all results in the first Frame (but this is ill-advised for the 
aforementioned reasons).

Aside, I would love to see the PHP driver you are working on published 
somewhere and licensed permissively for others to use :)

Puneeth Prasad wrote:
Hi Josh,

Thanks a lot for the inputs. We have made the necessary changes and it works
like a charm.

Regards,
puneeth

-----Original Message-----
From: Josh Elser [mailto:josh.el...@gmail.com]
Sent: 13 October 2016 14:58
To: user@phoenix.apache.org
Subject: Re: PrepareAndExecute statement return only 100 rows

Hi Puneeth,

What version of Phoenix are you using?

Indeed per [1], maxRowCount should control the number of rows returned
in the ExecuteResponse. However, given that you see 100 rows (which is
the default), it sounds like the value is not being respected. The most
recent docs may not align with the version of code you're running.

Unless you can guarantee that you never see more than a few hundred
rows, it is likely not a good idea to request all of the rows in one
request (use the FetchRequest to get subsequent batches).

- Josh

[1]
http://calcite.apache.org/avatica/docs/json_reference.html#prepareandexecute
request

Puneeth Prasad wrote:
Hi,

PrepareAndExecute statement has a default limit of returning 100 rows.
To avoid that, we use maxRowCount = -1, but it still gives only 100 rows.

I've copied the PHP code below, the highlighted part is the necessary
change to fetch all the rows possible. Can you please suggest where
we've gone wrong and how to correct it? Is there something pretty
obvious we missed out here?

curl_setopt($ch, CURLOPT_URL, "http://ip.address.of.phoenix.server:8765/
<http://120.138.8.208:8765/>");

curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

curl_setopt($ch, CURLOPT_POST, 1);

$headers[] = "Request:

{\"request\":\"openConnection\",\"connectionId\":\"000000-0000-0000-00000000
\"}";
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);

$result = curl_exec($ch);

$headers[] = "Request:

{\"request\":\"createStatement\",\"connectionId\":\"000000-0000-0000-0000000
0\"}";
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);

$result = curl_exec($ch);

$headers[] = "Request:

{\"request\":\"prepareAndExecute\",\"connectionId\":\"000000-0000-0000-00000
000\",\"statementId\":
".$a.",\"sql\": \"SELECT * FROM TABLE_NAME\",*\"maxRowCount\":-1*}";

curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);

$result = curl_exec($ch);

$headers[] = "Request:

{\"request\":\"closeStatement\",\"connectionId\":\"000000-0000-0000-00000000
\",\"statementId\":
1}";

curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);

$result = curl_exec($ch);

$headers[] = "Request:

{\"request\":\"closeConnection\",\"connectionId\":\"000000-0000-0000-0000000
0\"}";
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);

$result = curl_exec($ch);

Thanks!

Puneeth