phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Josh Elser <josh.el...@gmail.com>
Subject Re: PrepareAndExecute statement return only 100 rows
Date Sun, 16 Oct 2016 21:39:15 GMT
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.elser@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
>>
>

Mime
View raw message