Lines 464-498
sub nb_rows {
Link Here
|
464 |
return $results ? $results->[0] : 0; |
464 |
return $results ? $results->[0] : 0; |
465 |
} |
465 |
} |
466 |
|
466 |
|
467 |
=head2 execute_query |
467 |
=head2 select_2_select_count |
468 |
|
|
|
469 |
($sth, $error) = execute_query($sql, $offset, $limit[, \@sql_params]) |
470 |
|
471 |
|
472 |
This function returns a DBI statement handler from which the caller can |
473 |
fetch the results of the SQL passed via C<$sql>. |
474 |
|
475 |
If passed any query other than a SELECT, or if there is a DB error, |
476 |
C<$errors> is returned, and is a hashref containing the error after this |
477 |
manner: |
478 |
|
479 |
C<$error->{'sqlerr'}> contains the offending SQL keyword. |
480 |
C<$error->{'queryerr'}> contains the native db engine error returned |
481 |
for the query. |
482 |
|
483 |
C<$offset>, and C<$limit> are required parameters. |
484 |
|
468 |
|
485 |
C<\@sql_params> is an optional list of parameter values to paste in. |
469 |
returns $sql, $offset, $limit |
486 |
The caller is responsible for making sure that C<$sql> has placeholders |
470 |
$sql returned will be transformed to: |
487 |
and that the number placeholders matches the number of parameters. |
471 |
~ remove any LIMIT clause |
|
|
472 |
~ replace SELECT clause w/ SELECT count(*) |
488 |
|
473 |
|
489 |
=cut |
474 |
=cut |
490 |
|
475 |
|
491 |
# returns $sql, $offset, $limit |
|
|
492 |
# $sql returned will be transformed to: |
493 |
# ~ remove any LIMIT clause |
494 |
# ~ repace SELECT clause w/ SELECT count(*) |
495 |
|
496 |
sub select_2_select_count { |
476 |
sub select_2_select_count { |
497 |
# Modify the query passed in to create a count query... (I think this covers all cases -crn) |
477 |
# Modify the query passed in to create a count query... (I think this covers all cases -crn) |
498 |
my ($sql) = strip_limit(shift) or return; |
478 |
my ($sql) = strip_limit(shift) or return; |
Lines 500-524
sub select_2_select_count {
Link Here
|
500 |
return $sql; |
480 |
return $sql; |
501 |
} |
481 |
} |
502 |
|
482 |
|
503 |
# This removes the LIMIT from the query so that a custom one can be specified. |
483 |
=head2 strip_limit |
504 |
# Usage: |
484 |
This removes the LIMIT from the query so that a custom one can be specified. |
505 |
# ($new_sql, $offset, $limit) = strip_limit($sql); |
485 |
Usage: |
506 |
# |
486 |
($new_sql, $offset, $limit) = strip_limit($sql); |
507 |
# Where: |
487 |
|
508 |
# $sql is the query to modify |
488 |
Where: |
509 |
# $new_sql is the resulting query |
489 |
$sql is the query to modify |
510 |
# $offset is the offset value, if the LIMIT was the two-argument form, |
490 |
$new_sql is the resulting query |
511 |
# 0 if it wasn't otherwise given. |
491 |
$offset is the offset value, if the LIMIT was the two-argument form, |
512 |
# $limit is the limit value |
492 |
0 if it wasn't otherwise given. |
513 |
# |
493 |
$limit is the limit value |
514 |
# Notes: |
494 |
|
515 |
# * This makes an effort to not break subqueries that have their own |
495 |
Notes: |
516 |
# LIMIT specified. It does that by only removing a LIMIT if it comes after |
496 |
* This makes an effort to not break subqueries that have their own |
517 |
# a WHERE clause (which isn't perfect, but at least should make more cases |
497 |
LIMIT specified. It does that by only removing a LIMIT if it comes after |
518 |
# work - subqueries with a limit in the WHERE will still break.) |
498 |
a WHERE clause (which isn't perfect, but at least should make more cases |
519 |
# * If your query doesn't have a WHERE clause then all LIMITs will be |
499 |
work - subqueries with a limit in the WHERE will still break.) |
520 |
# removed. This may break some subqueries, but is hopefully rare enough |
500 |
* If your query doesn't have a WHERE clause then all LIMITs will be |
521 |
# to not be a big issue. |
501 |
removed. This may break some subqueries, but is hopefully rare enough |
|
|
502 |
to not be a big issue. |
503 |
|
504 |
=cut |
505 |
|
522 |
sub strip_limit { |
506 |
sub strip_limit { |
523 |
my ($sql) = @_; |
507 |
my ($sql) = @_; |
524 |
|
508 |
|
Lines 539-544
sub strip_limit {
Link Here
|
539 |
} |
523 |
} |
540 |
} |
524 |
} |
541 |
|
525 |
|
|
|
526 |
=head2 execute_query |
527 |
|
528 |
($sth, $error) = execute_query({ |
529 |
sql => $sql, |
530 |
offset => $offset, |
531 |
limit => $limit |
532 |
sql_params => \@sql_params], |
533 |
report_id => $report_id |
534 |
}) |
535 |
|
536 |
|
537 |
This function returns a DBI statement handler from which the caller can |
538 |
fetch the results of the SQL passed via C<$sql>. |
539 |
|
540 |
If passed any query other than a SELECT, or if there is a DB error, |
541 |
C<$errors> is returned, and is a hashref containing the error after this |
542 |
manner: |
543 |
|
544 |
C<$error->{'sqlerr'}> contains the offending SQL keyword. |
545 |
C<$error->{'queryerr'}> contains the native db engine error returned |
546 |
for the query. |
547 |
|
548 |
C<$offset>, and C<$limit> are required parameters. |
549 |
|
550 |
C<\@sql_params> is an optional list of parameter values to paste in. |
551 |
The caller is responsible for making sure that C<$sql> has placeholders |
552 |
and that the number placeholders matches the number of parameters. |
553 |
|
554 |
=cut |
555 |
|
542 |
sub execute_query { |
556 |
sub execute_query { |
543 |
|
557 |
|
544 |
my $params = shift; |
558 |
my $params = shift; |
545 |
- |
|
|