$d->select(-sql=$sql) has limit of 50?

classic Classic list List threaded Threaded
23 messages Options
12
Reply | Threaded
Open this post in threaded view
|

$d->select(-sql=$sql) has limit of 50?

stevepiercy
// knop method
     $d->select(-sql=$sql);
     log_critical($d->action_statement);
     log_critical($d->maxrecords_value);
// inline method
     inline(cnxn,-sql=$sql);
         log_critical(action_statement);
         log_critical(maxrecords_value);
         log_critical(found_count);
     /inline;

=>
[12/24/10 05:20:20] SELECT * FROM d.t
WHERE
     `c` LIKE '%foo%'
[12/24/10 05:20:20] 50
[12/24/10 05:20:20] 107
[12/24/10 05:20:20] SELECT * FROM d.t
WHERE
     `c` LIKE '%foo%'
[12/24/10 05:20:20] -1
[12/24/10 05:20:20] 107

WTF?  There is no limit clause in my SQL statement, but Knop
says the maxrecords_value is 50, whereas in the inline it is
-1?  How is that even possible?  How can I force Knop to use maxrecords=all?

--steve

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Steve Piercy               Web Site Builder              
Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>


--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

Bil Corry-3
Maybe -1 == all?  It used to be 'all' was mapped to a large integer, but it could have changed.

- Bil

On Dec 24, 2010, at 5:33 AM, Steve Piercy - Web Site Builder <[hidden email]> wrote:

> // knop method
>    $d->select(-sql=$sql);
>    log_critical($d->action_statement);
>    log_critical($d->maxrecords_value);
> // inline method
>    inline(cnxn,-sql=$sql);
>        log_critical(action_statement);
>        log_critical(maxrecords_value);
>        log_critical(found_count);
>    /inline;
>
> =>
> [12/24/10 05:20:20] SELECT * FROM d.t
> WHERE
>    `c` LIKE '%foo%'
> [12/24/10 05:20:20] 50
> [12/24/10 05:20:20] 107
> [12/24/10 05:20:20] SELECT * FROM d.t
> WHERE
>    `c` LIKE '%foo%'
> [12/24/10 05:20:20] -1
> [12/24/10 05:20:20] 107
>
> WTF?  There is no limit clause in my SQL statement, but Knop says the maxrecords_value is 50, whereas in the inline it is -1?  How is that even possible?  How can I force Knop to use maxrecords=all?
>
> --steve
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> Steve Piercy               Web Site Builder               Soquel, CA
> <[hidden email]>                  <http://www.StevePiercy.com/>
>
>
> --
> #############################################################
> This message is sent to you because you are subscribed to
> the mailing list <[hidden email]>.
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
> List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
> Project homepage http://montania.se/projects/knop/
> Google Code has the latest downloads at http://code.google.com/p/knop/

--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

stevepiercy
I tried appending to the SQL statement:

     $sql+=" LIMIT -1";

but that returned an error:

[12/24/10 14:28:41] MySQLDS: error from mysql: 1064, You have an
error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '-1'
at line 8

So I think that maxrecords is something internal to Lasso:

-1 == Omit a LIMIT clause.

--steve


On 12/24/10 at 9:13 AM, [hidden email] (Bil Corry) pronounced:

>Maybe -1 == all?  It used to be 'all' was mapped to a large
>integer, but it could have changed.
>
>- Bil
>
>On Dec 24, 2010, at 5:33 AM, Steve Piercy - Web Site Builder
><[hidden email]> wrote:
>
>>// knop method
>>$d->select(-sql=$sql);
>>log_critical($d->action_statement);
>>log_critical($d->maxrecords_value);
>>// inline method
>>inline(cnxn,-sql=$sql);
>>log_critical(action_statement);
>>log_critical(maxrecords_value);
>>log_critical(found_count);
>>/inline;
>>
>>=>
>>[12/24/10 05:20:20] SELECT * FROM d.t
>>WHERE
>>`c` LIKE '%foo%'
>>[12/24/10 05:20:20] 50
>>[12/24/10 05:20:20] 107
>>[12/24/10 05:20:20] SELECT * FROM d.t
>>WHERE
>>`c` LIKE '%foo%'
>>[12/24/10 05:20:20] -1
>>[12/24/10 05:20:20] 107
>>
>>WTF?  There is no limit clause in my SQL statement, but Knop says the
>maxrecords_value is 50, whereas in the inline it is -1?  How is
>that even possible?  How can I force Knop to use maxrecords=all?
>>
>>--steve
>>
>>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>>Steve Piercy               Web Site Builder               Soquel, CA
>><[hidden email]>                  <http://www.StevePiercy.com/>
>>
>>
>>--
>>#############################################################
>>This message is sent to you because you are subscribed to
>>the mailing list <[hidden email]>.
>>To unsubscribe, E-mail to: <[hidden email]>
>>Send administrative queries to  <[hidden email]>
>>List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
>>Project homepage http://montania.se/projects/knop/
>>Google Code has the latest downloads at http://code.google.com/p/knop/
>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Steve Piercy               Web Site Builder              
Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>


--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

Bil Corry-3
-maxrecords is a Lasso construct.  It tells the connector how many records to return, independent of how many records were returned from the datasource.  The only time it matches LIMIT is when using -uselimit in non-SQL inlines.

- Bil

On Dec 24, 2010, at 2:43 PM, Steve Piercy - Web Site Builder <[hidden email]> wrote:

> I tried appending to the SQL statement:
>
>    $sql+=" LIMIT -1";
>
> but that returned an error:
>
> [12/24/10 14:28:41] MySQLDS: error from mysql: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 8
>
> So I think that maxrecords is something internal to Lasso:
>
> -1 == Omit a LIMIT clause.
>
> --steve
>
>
> On 12/24/10 at 9:13 AM, [hidden email] (Bil Corry) pronounced:
>
>> Maybe -1 == all?  It used to be 'all' was mapped to a large integer, but it could have changed.
>>
>> - Bil
>>
>> On Dec 24, 2010, at 5:33 AM, Steve Piercy - Web Site Builder <[hidden email]> wrote:
>>
>>> // knop method
>>> $d->select(-sql=$sql);
>>> log_critical($d->action_statement);
>>> log_critical($d->maxrecords_value);
>>> // inline method
>>> inline(cnxn,-sql=$sql);
>>> log_critical(action_statement);
>>> log_critical(maxrecords_value);
>>> log_critical(found_count);
>>> /inline;
>>>
>>> =>
>>> [12/24/10 05:20:20] SELECT * FROM d.t
>>> WHERE
>>> `c` LIKE '%foo%'
>>> [12/24/10 05:20:20] 50
>>> [12/24/10 05:20:20] 107
>>> [12/24/10 05:20:20] SELECT * FROM d.t
>>> WHERE
>>> `c` LIKE '%foo%'
>>> [12/24/10 05:20:20] -1
>>> [12/24/10 05:20:20] 107
>>>
>>> WTF?  There is no limit clause in my SQL statement, but Knop says the
>> maxrecords_value is 50, whereas in the inline it is -1?  How is that even possible?  How can I force Knop to use maxrecords=all?
>>>
>>> --steve
>>>
>>> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>>> Steve Piercy               Web Site Builder               Soquel, CA
>>> <[hidden email]>                  <http://www.StevePiercy.com/>
>>>
>>>
>>> --
>>> #############################################################
>>> This message is sent to you because you are subscribed to
>>> the mailing list <[hidden email]>.
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>> List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
>>> Project homepage http://montania.se/projects/knop/
>>> Google Code has the latest downloads at http://code.google.com/p/knop/
>>
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> Steve Piercy               Web Site Builder               Soquel, CA
> <[hidden email]>                  <http://www.StevePiercy.com/>
>
>
> --
> #############################################################
> This message is sent to you because you are subscribed to
> the mailing list <[hidden email]>.
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
> List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
> Project homepage http://montania.se/projects/knop/
> Google Code has the latest downloads at http://code.google.com/p/knop/

--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

list
In reply to this post by stevepiercy
Without an explicit maxrecords set kno_database -> select will use Lassos default maxrecords setting of 50 records. Here's an example from recent code:

var('maxrecords' = (lp_client_param('-export') -> size > 0 || lp_client_param('display') == 'all' ? 2155410 | integer(lp_client_param('display'))));

var('skiprecords' = (lp_client_param('-export') -> size > 0 ? 0 | $lGrid -> page_skiprecords( $maxrecords)));

// build search params
var( 'searchparams' = array( -maxrecords = $maxrecords, -skiprecords = $skiprecords, -uselimit));

$dBase -> select( -sql = $sql, -search = $searchparams);

HDB
Jolle

24 dec 2010 kl. 14.33 skrev Steve Piercy - Web Site Builder:

> // knop method
>    $d->select(-sql=$sql);
>    log_critical($d->action_statement);
>    log_critical($d->maxrecords_value);
> // inline method
>    inline(cnxn,-sql=$sql);
>        log_critical(action_statement);
>        log_critical(maxrecords_value);
>        log_critical(found_count);
>    /inline;
>
> =>
> [12/24/10 05:20:20] SELECT * FROM d.t
> WHERE
>    `c` LIKE '%foo%'
> [12/24/10 05:20:20] 50
> [12/24/10 05:20:20] 107
> [12/24/10 05:20:20] SELECT * FROM d.t
> WHERE
>    `c` LIKE '%foo%'
> [12/24/10 05:20:20] -1
> [12/24/10 05:20:20] 107
>
> WTF?  There is no limit clause in my SQL statement, but Knop says the maxrecords_value is 50, whereas in the inline it is -1?  How is that even possible?  How can I force Knop to use maxrecords=all?
>
> --steve
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> Steve Piercy               Web Site Builder               Soquel, CA
> <[hidden email]>                  <http://www.StevePiercy.com/>
>
>
> --
> #############################################################
> This message is sent to you because you are subscribed to
> the mailing list <[hidden email]>.
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
> List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
> Project homepage http://montania.se/projects/knop/
> Google Code has the latest downloads at http://code.google.com/p/knop/
>
>


--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

stevepiercy
Rats.  I was afraid of that.  I scanned database.inc for some
kind of limit and did not find one, so I assumed that that was
what was happening.  Thanks for confirming and the workaround.

Outside of Knop, I prefer to remove Lasso's arbitrary limit of
50 records and override it with a limit when I need one.

     local('cnxn') = array(
         -database   = 'd2',
         -table      = 't2',
         -username   = 'u2',
         -password   = 'p2',
         -maxrecords = 'all',
         -nothing);

     // inline where LIMIT clause is either omitted or specified
in the SQL statement
     inline(#cnxn,-sql=#sql);

     // inline with LIMIT specified for FM datasources
     inline(#cnxn, ...classic inline params for FM..., -maxrecords=($optionally_override_all_with_an_integer));

That way I never run into this annoying idiot-syncrasy of Lasso.

Am I the only person who does something like this outside of Knop?

Would folks like to see this in Knop?

--steve


On 12/25/10 at 8:35 AM, [hidden email] (Jolle Carlestam) pronounced:

>Without an explicit maxrecords set kno_database -> select will
>use Lassos default maxrecords setting of 50 records. Here's an
>example from recent code:
>
>var('maxrecords' = (lp_client_param('-export') -> size > 0 ||
>lp_client_param('display') == 'all' ? 2155410 | integer(lp_client_param('display'))));
>
>var('skiprecords' = (lp_client_param('-export') -> size > 0 ? 0
>| $lGrid -> page_skiprecords( $maxrecords)));
>
>// build search params
>var( 'searchparams' = array( -maxrecords = $maxrecords,
>-skiprecords = $skiprecords, -uselimit));
>
>$dBase -> select( -sql = $sql, -search = $searchparams);
>
>HDB
>Jolle
>
>24 dec 2010 kl. 14.33 skrev Steve Piercy - Web Site Builder:
>
>>// knop method
>>$d->select(-sql=$sql);
>>log_critical($d->action_statement);
>>log_critical($d->maxrecords_value);
>>// inline method
>>inline(cnxn,-sql=$sql);
>>log_critical(action_statement);
>>log_critical(maxrecords_value);
>>log_critical(found_count);
>>/inline;
>>
>>=>
>>[12/24/10 05:20:20] SELECT * FROM d.t
>>WHERE
>>`c` LIKE '%foo%'
>>[12/24/10 05:20:20] 50
>>[12/24/10 05:20:20] 107
>>[12/24/10 05:20:20] SELECT * FROM d.t
>>WHERE
>>`c` LIKE '%foo%'
>>[12/24/10 05:20:20] -1
>>[12/24/10 05:20:20] 107
>>
>>WTF?  There is no limit clause in my SQL statement, but Knop says the
>maxrecords_value is 50, whereas in the inline it is -1?  How is
>that even possible?  How can I force Knop to use maxrecords=all?
>>
>>--steve
>>
>>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>>Steve Piercy               Web Site Builder               Soquel, CA
>><[hidden email]>                  <http://www.StevePiercy.com/>
>>
>>
>>--
>>#############################################################
>>This message is sent to you because you are subscribed to
>>the mailing list <[hidden email]>.
>>To unsubscribe, E-mail to: <[hidden email]>
>>Send administrative queries to  <[hidden email]>
>>List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
>>Project homepage http://montania.se/projects/knop/
>>Google Code has the latest downloads at http://code.google.com/p/knop/
>>
>>
>
>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Steve Piercy               Web Site Builder              
Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>


--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

Bil Corry-3
Steve Piercy - Web Site Builder wrote on 12/25/2010 3:51 PM:
> Outside of Knop, I prefer to remove Lasso's arbitrary limit of 50 records and override it with a limit when I need one.

I always set maxrecords to 'all' then use LIMIT within the SQL query as needed.  Or -uselimit with -maxrecords when doing a regular inline.

I haven't tried it in many years, but it used to be that specifying -maxrecords with SQL inline could result in the query returning 100k rows, but the connector only sending 50 or whatever to Lasso, wasting cycles, memory and bandwidth to transfer records that are merely discarded.   Avoid that if it's still the case.


- Bil

--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

stevepiercy
On 12/25/10 at 4:49 PM, [hidden email] (Bil Corry) pronounced:

>I haven't tried it in many years, but it used to be that
>specifying -maxrecords with SQL inline could result in the
>query returning 100k rows, but the connector only sending 50 or
>whatever to Lasso, wasting cycles, memory and bandwidth to
>transfer records that are merely discarded.   Avoid that if
>it's still the case.

I have not noticed that situation in 8.5, but then I don't have
tables with at least that many rows to confirm it one way or the other.

Still that sounds like a very poor implementation of a
connector.  IMO a connector's job should be to connect to a
datasource, then pass through statements and return results
without modifying them.  It would make more sense if the
connector passed all the records to Lasso, which in turn dropped
all but 50.  That's not much more sensible, but at least I can
understand that Lasso may have been implemented to do that to
prevent newbs from shooting themselves in the foot.

--steve

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Steve Piercy               Web Site Builder              
Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>


--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

Johan Solve-2
In reply to this post by stevepiercy
Maybe suitable with an option for the database object to make it default to all instead of the implicit 50.

At 15.51 -0800 2010-12-25, Steve Piercy - Web Site Builder wrote:

>Rats.  I was afraid of that.  I scanned database.inc for some kind of limit and did not find one, so I assumed that that was what was happening.  Thanks for confirming and the workaround.
>
>Outside of Knop, I prefer to remove Lasso's arbitrary limit of 50 records and override it with a limit when I need one.
>
>    local('cnxn') = array(
>        -database   = 'd2',
>        -table      = 't2',
>        -username   = 'u2',
>        -password   = 'p2',
>        -maxrecords = 'all',
>        -nothing);
>
>    // inline where LIMIT clause is either omitted or specified in the SQL statement
>    inline(#cnxn,-sql=#sql);
>
>    // inline with LIMIT specified for FM datasources
>    inline(#cnxn, ...classic inline params for FM..., -maxrecords=($optionally_override_all_with_an_integer));
>
>That way I never run into this annoying idiot-syncrasy of Lasso.
>
>Am I the only person who does something like this outside of Knop?
>
>Would folks like to see this in Knop?
>
>--steve
>
>
>On 12/25/10 at 8:35 AM, [hidden email] (Jolle Carlestam) pronounced:
>
>>Without an explicit maxrecords set kno_database -> select will use Lassos default maxrecords setting of 50 records. Here's an example from recent code:
>>
>>var('maxrecords' = (lp_client_param('-export') -> size > 0 || lp_client_param('display') == 'all' ? 2155410 | integer(lp_client_param('display'))));
>>
>>var('skiprecords' = (lp_client_param('-export') -> size > 0 ? 0 | $lGrid -> page_skiprecords( $maxrecords)));
>>
>>// build search params
>>var( 'searchparams' = array( -maxrecords = $maxrecords, -skiprecords = $skiprecords, -uselimit));
>>
>>$dBase -> select( -sql = $sql, -search = $searchparams);
>>
>>HDB
>>Jolle
>>
>>24 dec 2010 kl. 14.33 skrev Steve Piercy - Web Site Builder:
>>
>>>// knop method
>>>$d->select(-sql=$sql);
>>>log_critical($d->action_statement);
>>>log_critical($d->maxrecords_value);
>>>// inline method
>>>inline(cnxn,-sql=$sql);
>>>log_critical(action_statement);
>>>log_critical(maxrecords_value);
>>>log_critical(found_count);
>>>/inline;
>>>
>>>=>
>>>[12/24/10 05:20:20] SELECT * FROM d.t
>>>WHERE
>>>`c` LIKE '%foo%'
>>>[12/24/10 05:20:20] 50
>>>[12/24/10 05:20:20] 107
>>>[12/24/10 05:20:20] SELECT * FROM d.t
>>>WHERE
>>>`c` LIKE '%foo%'
>>>[12/24/10 05:20:20] -1
>>>[12/24/10 05:20:20] 107
>>>
>>>WTF?  There is no limit clause in my SQL statement, but Knop says the
>>maxrecords_value is 50, whereas in the inline it is -1?  How is that even possible?  How can I force Knop to use maxrecords=all?
>>>
>>>--steve
>>>
>>>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>>>Steve Piercy               Web Site Builder               Soquel, CA
>>><[hidden email]>                  <http://www.StevePiercy.com/>
>>>
>>>
>>>--
>>>#############################################################
>>>This message is sent to you because you are subscribed to
>>>the mailing list <[hidden email]>.
>>>To unsubscribe, E-mail to: <[hidden email]>
>>>Send administrative queries to  <[hidden email]>
>>>List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
>>>Project homepage http://montania.se/projects/knop/
>>>Google Code has the latest downloads at http://code.google.com/p/knop/
>>>
>>
>>
>
>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>Steve Piercy               Web Site Builder
>Soquel, CA
><[hidden email]>                  <http://www.StevePiercy.com/>
>
>
>--
>#############################################################
>This message is sent to you because you are subscribed to
>the mailing list <[hidden email]>.
>To unsubscribe, E-mail to: <[hidden email]>
>Send administrative queries to  <[hidden email]>
>List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
>Project homepage http://montania.se/projects/knop/
>Google Code has the latest downloads at http://code.google.com/p/knop/


--
     Johan Sölve    [FSA Member, Lasso Partner]
     Web Application/Lasso/FileMaker Developer
     MONTANIA SOFTWARE & SOLUTIONS
http://www.montania.se   mailto:[hidden email]
 (spam-safe email address, replace '-' with 'a')

--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

stevepiercy
I tried a quick modification to database.inc which worked for
exporting the grid results, but then that caused all records to
display in the grid, overriding the defaults set in the global
config or any other -maxrecords or -skiprecords value.  It's
more complicated than I expected and the goal can be achieved
with far less effort.

So there are two workarounds:
* Use Knop with Jolle's workaround.
* Bypass Knop and use an inline.

     inline(cnxn,-sql=$sql);
     #exp += field_names->join('\t');
     iterate(records_array,local('i'));
         #exp += '\n';
         iterate(#i,local('j'));
             (test for textareas that allow double quotes so we
escape them properly)
             ? #exp += '"'+(string_replaceregexp(#j,-find='"',-replace='""'))+'"\t'
             | #exp +=#j+'\t';
         /iterate;
     /iterate;
     /inline;

--steve


On 12/26/10 at 6:24 PM, [hidden email] (Johan Solve) pronounced:

>Maybe suitable with an option for the database object to make
>it default to all instead of the implicit 50.
>
>At 15.51 -0800 2010-12-25, Steve Piercy - Web Site Builder wrote:
>>Rats.  I was afraid of that.  I scanned database.inc for some kind of limit and did
>not find one, so I assumed that that was what was happening.  
>Thanks for confirming and the workaround.
>>
>>Outside of Knop, I prefer to remove Lasso's arbitrary limit of 50 records and
>override it with a limit when I need one.
>>
>>local('cnxn') = array(
>>-database   = 'd2',
>>-table      = 't2',
>>-username   = 'u2',
>>-password   = 'p2',
>>-maxrecords = 'all',
>>-nothing);
>>
>>// inline where LIMIT clause is either omitted or specified in the SQL statement
>>inline(#cnxn,-sql=#sql);
>>
>>// inline with LIMIT specified for FM datasources
>>inline(#cnxn, ...classic inline params for FM...,
>-maxrecords=($optionally_override_all_with_an_integer));
>>
>>That way I never run into this annoying idiot-syncrasy of Lasso.
>>
>>Am I the only person who does something like this outside of Knop?
>>
>>Would folks like to see this in Knop?
>>
>>--steve
>>
>>
>>On 12/25/10 at 8:35 AM, [hidden email] (Jolle Carlestam) pronounced:
>>
>>>Without an explicit maxrecords set kno_database -> select will use Lassos default
>maxrecords setting of 50 records. Here's an example from recent code:
>>>
>>>var('maxrecords' = (lp_client_param('-export') -> size > 0 ||
>lp_client_param('display') == 'all' ? 2155410 | integer(lp_client_param('display'))));
>>>
>>>var('skiprecords' = (lp_client_param('-export') -> size > 0 ? 0 | $lGrid ->
>page_skiprecords( $maxrecords)));
>>>
>>>// build search params
>>>var( 'searchparams' = array( -maxrecords = $maxrecords, -skiprecords = $skiprecords,
>-uselimit));
>>>
>>>$dBase -> select( -sql = $sql, -search = $searchparams);
>>>
>>>HDB
>>>Jolle
>>>
>>>24 dec 2010 kl. 14.33 skrev Steve Piercy - Web Site Builder:
>>>
>>>>// knop method
>>>>$d->select(-sql=$sql);
>>>>log_critical($d->action_statement);
>>>>log_critical($d->maxrecords_value);
>>>>// inline method
>>>>inline(cnxn,-sql=$sql);
>>>>log_critical(action_statement);
>>>>log_critical(maxrecords_value);
>>>>log_critical(found_count);
>>>>/inline;
>>>>
>>>>=>
>>>>[12/24/10 05:20:20] SELECT * FROM d.t
>>>>WHERE
>>>>`c` LIKE '%foo%'
>>>>[12/24/10 05:20:20] 50
>>>>[12/24/10 05:20:20] 107
>>>>[12/24/10 05:20:20] SELECT * FROM d.t
>>>>WHERE
>>>>`c` LIKE '%foo%'
>>>>[12/24/10 05:20:20] -1
>>>>[12/24/10 05:20:20] 107
>>>>
>>>>WTF?  There is no limit clause in my SQL statement, but Knop says the
>>>maxrecords_value is 50, whereas in the inline it is -1?  How is that even possible?
>How can I force Knop to use maxrecords=all?
>>>>
>>>>--steve
>>>>
>>>>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>>>>Steve Piercy               Web Site Builder               Soquel, CA
>>>><[hidden email]>                  <http://www.StevePiercy.com/>
>>>>
>>>>
>>>>--
>>>>#############################################################
>>>>This message is sent to you because you are subscribed to
>>>>the mailing list <[hidden email]>.
>>>>To unsubscribe, E-mail to: <[hidden email]>
>>>>Send administrative queries to  <[hidden email]>
>>>>List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
>>>>Project homepage http://montania.se/projects/knop/
>>>>Google Code has the latest downloads at http://code.google.com/p/knop/
>>>>
>>>
>>>
>>
>>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>>Steve Piercy               Web Site Builder
>>Soquel, CA
>><[hidden email]>                  <http://www.StevePiercy.com/>
>>
>>
>>--
>>#############################################################
>>This message is sent to you because you are subscribed to
>>the mailing list <[hidden email]>.
>>To unsubscribe, E-mail to: <[hidden email]>
>>Send administrative queries to  <[hidden email]>
>>List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
>>Project homepage http://montania.se/projects/knop/
>>Google Code has the latest downloads at http://code.google.com/p/knop/
>
>
>--
>Johan Sölve    [FSA Member, Lasso Partner]
>Web Application/Lasso/FileMaker Developer
>MONTANIA SOFTWARE & SOLUTIONS
>http://www.montania.se   mailto:[hidden email]
>(spam-safe email address, replace '-' with 'a')
>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Steve Piercy               Web Site Builder              
Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>


--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

Bil Corry-3
In reply to this post by stevepiercy
Steve Piercy - Web Site Builder wrote on 12/26/2010 2:11 AM:

> On 12/25/10 at 4:49 PM, [hidden email] (Bil Corry) pronounced:
>
>> I haven't tried it in many years, but it used to be that specifying
>> -maxrecords with SQL inline could result in the query returning
>> 100k rows, but the connector only sending 50 or whatever to Lasso,
>> wasting cycles, memory and bandwidth to transfer records that are
>> merely discarded. Avoid that if it's still the case.
>
> I have not noticed that situation in 8.5, but then I don't have
> tables with at least that many rows to confirm it one way or the
> other.

Even if you do a SQL inline with LIMIT 2, but maxrecords = 1, I believe MySQL sends two records and Lasso trims it to just 1.


> Still that sounds like a very poor implementation of a connector. IMO
> a connector's job should be to connect to a datasource, then pass
> through statements and return results without modifying them. It
> would make more sense if the connector passed all the records to
> Lasso, which in turn dropped all but 50. That's not much more
> sensible, but at least I can understand that Lasso may have been
> implemented to do that to prevent newbs from shooting themselves in
> the foot.

The behavior, like many in Lasso, are artifacts from the Filemaker beginnings.  I don't recall, but maybe Filemaker at one time sent all records and it was up to -maxrecords to trim them down.  I don't recall anymore.


- Bil

--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

stevepiercy
On 12/26/10 at 4:34 PM, [hidden email] (Bil Corry) pronounced:

>Steve Piercy - Web Site Builder wrote on 12/26/2010 2:11 AM:
>>On 12/25/10 at 4:49 PM, [hidden email] (Bil Corry) pronounced:
>>
>>>I haven't tried it in many years, but it used to be that specifying
>>>-maxrecords with SQL inline could result in the query returning
>>>100k rows, but the connector only sending 50 or whatever to Lasso,
>>>wasting cycles, memory and bandwidth to transfer records that are
>>>merely discarded. Avoid that if it's still the case.
>>
>>I have not noticed that situation in 8.5, but then I don't have
>>tables with at least that many rows to confirm it one way or the
>>other.
>
>Even if you do a SQL inline with LIMIT 2, but maxrecords = 1, I
>believe MySQL sends two records and Lasso trims it to just 1.

I should clarify.

Here are some tests where there are 3 options for -maxrecords
(-maxrecords='all', -maxrecords=20, and omitted), 2 options for
a LIMIT clause in the SQL statement (present or not), and where
I use -maxrecords='all' in my #cnxn parameters for all 3x2=6 permutations.

// When not using a LIMIT clause in the SQL statement
inline(#cnxn,-sql=$sql,-maxrecords='all');
action_statement: SELECT * FROM d.t
maxrecords_value: -1
found_count: 212
records_array->size: 212

inline(#cnxn,-sql=$sql,-maxrecords=20);
action_statement: SELECT * FROM d.t
maxrecords_value: 20
found_count: 212
records_array->size: 20

inline(#cnxn,-sql=$sql);
action_statement: SELECT * FROM d.t
maxrecords_value: -1
found_count: 212
records_array->size: 212



// When using a LIMIT 100 clause
inline(#cnxn,-sql=$sql,-maxrecords='all');
action_statement: SELECT * FROM d.t LIMIT 100;
maxrecords_value: -1
found_count: 100
records_array->size: 100

inline(#cnxn,-sql=$sql,-maxrecords=20);
action_statement: SELECT * FROM d.t LIMIT 100;
maxrecords_value: 20
found_count: 100
records_array->size: 20

inline(#cnxn,-sql=$sql);
action_statement: SELECT * FROM d.t LIMIT 100;
maxrecords_value: -1
found_count: 100
records_array->size: 100


Conclusions
-----------------------
Provided that one uses -maxrecords='all' in the array of inline
parameters, then the LIMIT clause in an SQL statement will be
honored.  This is what I always do.

If one overrides the initial -maxrecords='all' parameter with a
subsequent -maxrecords=integer parameter, then Lasso will trim
any LIMIT clause greater than the -maxrecords value.  This is
consistent with what you wrote above.

--steve

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Steve Piercy               Web Site Builder              
Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>


--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

Johan Solve-2
In reply to this post by Bil Corry-3
At 16.34 -0800 2010-12-26, Bil Corry wrote:
>The behavior, like many in Lasso, are artifacts from the Filemaker beginnings.  I don't recall, but maybe Filemaker at one time sent all records and it was up to -maxrecords to trim them down.  I don't recall anymore.

Yes for FileMaker requests, -maxrecords limits the number of records returned by FileMaker to Lasso. In this case Lasso doesn't have to trim any more.

Using LIMIT in SQL statements creates problems for found_count, but knop_database tries to work around that using the technique in http://tagswap.net/found_rows.



--
     Johan Sölve    [FSA Member, Lasso Partner]
     Web Application/Lasso/FileMaker Developer
     MONTANIA SOFTWARE & SOLUTIONS
http://www.montania.se   mailto:[hidden email]
 (spam-safe email address, replace '-' with 'a')

--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

list
In reply to this post by stevepiercy
27 dec 2010 kl. 00.37 skrev Steve Piercy - Web Site Builder:

> So there are two workarounds:
> * Use Knop with Jolle's workaround.

I kinda object to my method being called a workaround. It's the preferred and official way of dealing with maxrecords in the knop_database object.
knop_database acts just like any inline when used with sql queries. If -maxrecords is not stated then Lasso will return no more than 50 records regardless of how many Mysql finds. Inlines works that way, as does knop_database.
To deal with that you specify a -maxrecords value. In knop_database the official way is by using the param -search together with the -sql param. It accepts an array and sends that array on to the inline. It's not a workaround.

HDB
Jolle
--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

stevepiercy
In reply to this post by Johan Solve-2
On 12/27/10 at 9:00 AM, [hidden email] (Johan Solve) pronounced:

>At 16.34 -0800 2010-12-26, Bil Corry wrote:
>>The behavior, like many in Lasso, are artifacts from the Filemaker beginnings.  I
>don't recall, but maybe Filemaker at one time sent all records
>and it was up to -maxrecords to trim them down.  I don't recall anymore.
>
>Yes for FileMaker requests, -maxrecords limits the number of
>records returned by FileMaker to Lasso. In this case Lasso
>doesn't have to trim any more.
>
>Using LIMIT in SQL statements creates problems for found_count,
>but knop_database tries to work around that using the technique
>in http://tagswap.net/found_rows.

What are the problems that LIMIT in SQL statements creates for
found_count?  My tests using 8.5.6 and MySQL 5.1.x indicate that
the values for LIMIT, found_count, and "rows in set" via the
command line are always equivalent.  When there is no LIMIT
clause, values for found_count and "rows in set" via the command
line are equivalent.  That's either with or without any value
for -maxrecords.  Perhaps things have changed since you updated found_rows?

Separately there is an inconsistency between SQL statements and
Lasso's database abstraction layer across -maxrecords,
maxrecords_value and records_array->size.  Shenanigans!

action_statement: SELECT * FROM d.t
----------------------------------
-maxrecords='all'
maxrecords_value: -1
records_array->size: 212

-maxrecords=20
maxrecords_value: 20
records_array->size: 20

No -maxrecords
maxrecords_value: 50
records_array->size: 50


action_statement: SELECT * FROM d.t LIMIT 100;
---------------------------------------------
-maxrecords='all'
maxrecords_value: -1
records_array->size: 100

-maxrecords=20
maxrecords_value: 20
records_array->size: 20

No -maxrecords
maxrecords_value: 50
records_array->size: 50

--steve

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Steve Piercy               Web Site Builder              
Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>


--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

stevepiercy
In reply to this post by list
On 12/27/10 at 9:46 AM, [hidden email] pronounced:

>27 dec 2010 kl. 00.37 skrev Steve Piercy - Web Site Builder:
>
>>So there are two workarounds:
>>* Use Knop with Jolle's workaround.
>
>I kinda object to my method being called a workaround.

To clarify, I meant a workaround in the context of making Lasso
(and therefore Knop) be consistent with SQL statements via the
command line and "rows in set".

But then your method would not technically be a "workaround"
because it imposes a LIMIT clause with an arbitrarily high
value, whereas the desired SQL statement has none.  The end
result would be the same until that value is exceeded.

--steve

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Steve Piercy               Web Site Builder              
Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>


--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

Johan Solve-2
In reply to this post by stevepiercy
At 04.54 -0800 2010-12-27, Steve Piercy - Web Site Builder wrote:
>What are the problems that LIMIT in SQL statements creates for found_count?  My tests using 8.5.6 and MySQL 5.1.x indicate that the values for LIMIT, found_count, and "rows in set" via the command line are always equivalent.  When there is no LIMIT clause, values for found_count and "rows in set" via the command line are equivalent.  That's either with or without any value for -maxrecords.  Perhaps things have changed since you updated found_rows?

Not sure what you really mean here.

LIMIT=100
gives found_count=100 or found_count=212?

As far as I knew found_count would return 100 in that case, while it should (imo) return 212.

If that has changed, it would be interesting to find out more about how and when.


>Separately there is an inconsistency between SQL statements and Lasso's database abstraction layer across -maxrecords, maxrecords_value and records_array->size.  Shenanigans!
>
>action_statement: SELECT * FROM d.t
>----------------------------------
>-maxrecords='all'
>maxrecords_value: -1
>records_array->size: 212
...
>action_statement: SELECT * FROM d.t LIMIT 100;
>---------------------------------------------
>-maxrecords='all'
>maxrecords_value: -1
>records_array->size: 100

Where's the inconsistency?

When you use LIMIT, Lasso (and records_array) only sees 100 records no matter how much -maxrecords=all you use. Your results are as expected.



--
     Johan Sölve    [FSA Member, Lasso Partner]
     Web Application/Lasso/FileMaker Developer
     MONTANIA SOFTWARE & SOLUTIONS
http://www.montania.se   mailto:[hidden email]
 (spam-safe email address, replace '-' with 'a')

--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://www.nabble.com/Knop-Framework-Discussion-f29076.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

list
I've done some inline testing myself. Here're my findings:

1/ Using -findall with no -maxrecords value set
Found_Count: 702 <- correct
Maxrecords_value: 50
Records_array -> size: 50
Action_statement: SELECT SQL_CALC_FOUND_ROWS * FROM c196301.marker LIMIT 0,50

2/ Using -findall with -maxrecords set to all
Found_Count: 702 <- correct
Maxrecords_value: -1
Records_array -> size: 702
Action_statement: SELECT SQL_CALC_FOUND_ROWS * FROM c196301.marker LIMIT 0,2147483647

3/ Using -findall with -maxrecords set to 100
Found_Count: 702 <- correct
Maxrecords_value: 100
Records_array -> size: 100
Action_statement: SELECT SQL_CALC_FOUND_ROWS * FROM c196301.marker LIMIT 0,100

4/ Using -findall with -maxrecords set to 100 and -uselimit
Found_Count: 100 <- not correct
Maxrecords_value: 100
Records_array -> size: 100
Action_statement: SELECT * FROM c196301.marker LIMIT 0,100

5/ Using -sql with no -maxrecords value set and no LIMIT clause
Found_Count: 702 <- correct
Maxrecords_value: 50
Records_array -> size: 50
Action_statement: SELECT SQL_CALC_FOUND_ROWS * FROM c196301.marker

6/ Using -sql with no -maxrecords value set and LIMIT clause set less than total found records
Found_Count: 100 <- not correct
Maxrecords_value: 50
Records_array -> size: 50
Action_statement: SELECT SQL_CALC_FOUND_ROWS * FROM c196301.marker LIMIT 0,100

7/ Using -sql with -maxrecords set to all
Found_Count: 702 <- correct
Maxrecords_value: -1
Records_array -> size: 702
Action_statement: SELECT SQL_CALC_FOUND_ROWS * FROM c196301.marker LIMIT 0,100000

Conclusions. The problem seems to be that found_count won't always return the correct value. Especially when used with sql queries. Interesting is that test case 3/ Using -findall with -maxrecords set to 100 does return the correct value even if Lasso uses a LIMIT clause in the action statement. Whereas test case 6 won't despite very similar action statement.

HDB
Jolle
--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://lasso.2283332.n4.nabble.com/Knop-Framework-Discussion-f3157831.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

stevepiercy
In reply to this post by Johan Solve-2
On 12/27/10 at 2:33 PM, [hidden email] (Johan Solve) pronounced:

>At 04.54 -0800 2010-12-27, Steve Piercy - Web Site Builder wrote:
>>What are the problems that LIMIT in SQL statements creates for found_count?  My tests
>using 8.5.6 and MySQL 5.1.x indicate that the values for LIMIT,
>found_count, and "rows in set" via the command line are always
>equivalent.  When there is no LIMIT clause, values for
>found_count and "rows in set" via the command line are
>equivalent.  That's either with or without any value for
>-maxrecords.  Perhaps things have changed since you updated found_rows?
>
>Not sure what you really mean here.
>
>LIMIT=100
>gives found_count=100 or found_count=212?
>
>As far as I knew found_count would return 100 in that case,
>while it should (imo) return 212.

It does return 100, but we differ in our opinions of what
found_count should return.  I think it should return the count
of records actually found (its current behavior), not what would
have been found disregarding any LIMIT clause.

It bugs me that there is no separate SQL_CALC_FOUND_ROWS in
Lasso, so your found_rows ctag is a good addition.  However it
should not replace found_count.

>>Separately there is an inconsistency between SQL statements
>>and Lasso's database abstraction layer across -maxrecords,
>>maxrecords_value and records_array->size.  Shenanigans!
>>
>>action_statement: SELECT * FROM d.t
>>----------------------------------
>>-maxrecords='all'
>>maxrecords_value: -1
>>records_array->size: 212
>...
>>action_statement: SELECT * FROM d.t LIMIT 100;
>>---------------------------------------------
>>-maxrecords='all'
>>maxrecords_value: -1
>>records_array->size: 100
>
>Where's the inconsistency?
>
>When you use LIMIT, Lasso (and records_array) only sees 100
>records no matter how much -maxrecords=all you use. Your
>results are as expected.

The inconsistency is within each action_statement.  As you
change the value of -maxrecords, the value of maxrecords_value
is inconsistent with -maxrecords.

action_statement: SELECT * FROM d.t
----------------------------------
-maxrecords='all'
maxrecords_value: -1        // 'all' != -1
records_array->size: 212

-maxrecords=20
maxrecords_value: 20        // 20 == 20
records_array->size: 20

No -maxrecords
maxrecords_value: 50        // NULL != 50
records_array->size: 50

--steve

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Steve Piercy               Web Site Builder              
Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>


--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://lasso.2283332.n4.nabble.com/Knop-Framework-Discussion-f3157831.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
Reply | Threaded
Open this post in threaded view
|

Re: $d->select(-sql=$sql) has limit of 50?

stevepiercy
In reply to this post by list
On 12/27/10 at 3:00 PM, [hidden email] pronounced:

>I've done some inline testing myself. Here're my findings:
>
>1/ Using -findall with no -maxrecords value set
>Found_Count: 702 <- correct
>Maxrecords_value: 50
>Records_array -> size: 50
>Action_statement: SELECT SQL_CALC_FOUND_ROWS * FROM c196301.marker LIMIT 0,50
>
>2/ Using -findall with -maxrecords set to all
>Found_Count: 702 <- correct
>Maxrecords_value: -1
>Records_array -> size: 702
>Action_statement: SELECT SQL_CALC_FOUND_ROWS * FROM c196301.marker LIMIT 0,2147483647
>
>3/ Using -findall with -maxrecords set to 100
>Found_Count: 702 <- correct
>Maxrecords_value: 100
>Records_array -> size: 100
>Action_statement: SELECT SQL_CALC_FOUND_ROWS * FROM c196301.marker LIMIT 0,100
>
>4/ Using -findall with -maxrecords set to 100 and -uselimit
>Found_Count: 100 <- not correct
>Maxrecords_value: 100
>Records_array -> size: 100
>Action_statement: SELECT * FROM c196301.marker LIMIT 0,100
>
>5/ Using -sql with no -maxrecords value set and no LIMIT clause
>Found_Count: 702 <- correct
>Maxrecords_value: 50
>Records_array -> size: 50
>Action_statement: SELECT SQL_CALC_FOUND_ROWS * FROM c196301.marker
>
>6/ Using -sql with no -maxrecords value set and LIMIT clause
>set less than total found records
>Found_Count: 100 <- not correct
>Maxrecords_value: 50
>Records_array -> size: 50
>Action_statement: SELECT SQL_CALC_FOUND_ROWS * FROM c196301.marker LIMIT 0,100
>
>7/ Using -sql with -maxrecords set to all
>Found_Count: 702 <- correct
>Maxrecords_value: -1
>Records_array -> size: 702
>Action_statement: SELECT SQL_CALC_FOUND_ROWS * FROM c196301.marker LIMIT 0,100000
>
>Conclusions. The problem seems to be that found_count won't
>always return the correct value. Especially when used with sql
>queries. Interesting is that test case 3/ Using -findall with
>-maxrecords set to 100 does return the correct value even if
>Lasso uses a LIMIT clause in the action statement. Whereas test
>case 6 won't despite very similar action statement.

We're just setting ourselves up for trouble by mixing SQL
statements with any other non-connection related inline
parameter.  Adding -findall into this discussion adds more
confusion, especially when you combine it with -uselimit!  I
mean, how can you use a limit and find all records?

FWIW, I don't know what to expect for tests 1-4, I agree with
you on 5 and 7, but disagree on 6 as explained in my other email
regarding what I think found_count should represent.

--steve

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Steve Piercy               Web Site Builder              
Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>


--
#############################################################
This message is sent to you because you are subscribed to
the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
List archive http://lasso.2283332.n4.nabble.com/Knop-Framework-Discussion-f3157831.html
Project homepage http://montania.se/projects/knop/
Google Code has the latest downloads at http://code.google.com/p/knop/
12