|
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 |
- |
|
|