Re: SQL::Statement limit clause with placeholders

Replies are listed 'Best First'.
Re^2: SQL::Statement limit clause with placeholders by dragonchild (Archbishop) on Jun 03, 2005 at 14:32 UTC
AFACT, SQL::Statement can bind the placeholders prior to "generating an execution plan for the query." As each DBD differs slightly anyways (DBD::mysql and DBD::Pg anyone?!), you can advertise this as a "feature" with little developer cost. I've always thought that the execution plan argument against LIMIT placeholders was a bunch of hooey. The execution plan isn't be any worse because you're taking the 100th-119th records vs. full set. You still have to go retrieve the whole record set anyways. The only things I can see are If your LIMIT starts at 0, you might lose an optimization if your tables are split across disks. If your LIMIT starts at a higher number than your cardinality in one of the indices, you execute the query even though you can know you'll be returning an empty set. Those are all lost optimizations. They're not degradations of the base query's execution plan. Oracle's developers are just lazy. In general, if you think something isn't in Perl, try it out, because it usually is. :-) "What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against?"	[reply]
Re^2: SQL::Statement limit clause with placeholders by springm (Novice) on Jun 03, 2005 at 14:48 UTC
Just tried PostgreSQL `$dbh2->selectall_arrayref("SELECT * FROM sessions limit ? offset ?", { +}, 2,3);` [download] works like a charm	[reply] [d/l]
Re^2: SQL::Statement limit clause with placeholders by springm (Novice) on Jun 03, 2005 at 14:22 UTC
OK. Facts accepted. So what would be the best way to implement paging of records in chunks of, say, 20 per page?	[reply]
Re^3: SQL::Statement limit clause with placeholders by dragonchild (Archbishop) on Jun 03, 2005 at 14:26 UTC
The immediate solution is to dynamically generate the SQL. This could be as simple as: `my $sql = <<__END_SQL__; YOUR STATEMENT HERE __END_SQL__ $sql .= "LIMIT $start, $chunk_size\n"; my $sth = $dbh->prepare( $sql ); # etc.` [download] In general, if you think something isn't in Perl, try it out, because it usually is. :-) "What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against?"	[reply] [d/l]
Re^4: SQL::Statement limit clause with placeholders by springm (Novice) on Jun 03, 2005 at 14:37 UTC
Ok. Looks reasonable. Untainting offset und limit (coming from query params) via regex should be safe then. Thanks for the help!	[reply]
Re^3: SQL::Statement limit clause with placeholders by jZed (Prior) on Jun 03, 2005 at 14:27 UTC
Variable interpolation?	[reply]