View | Details | Raw Unified | Return to bug 9915
Collapse All | Expand All

(-)a/C4/Reports/Guided.pm (-14 / +19 lines)
Lines 400-421 sub nb_rows { Link Here
400
400
401
=head2 execute_query
401
=head2 execute_query
402
402
403
  ($results, $error) = execute_query($sql, $offset, $limit)
403
  ($sth, $error) = execute_query($sql, $offset, $limit[, \@sql_params])
404
404
405
405
406
When passed C<$sql>, this function returns an array ref containing a result set
406
This function returns a DBI statement handler from which the caller can
407
suitably formatted for display in html or for output as a flat file when passed in
407
fetch the results of the SQL passed via C<$sql>.
408
C<$format> and C<$id>. It also returns the C<$total> records available for the
408
409
supplied query. If passed any query other than a SELECT, or if there is a db error,
409
If passed any query other than a SELECT, or if there is a db error,
410
C<$errors> an array ref is returned containing the error after this manner:
410
C<$errors> an array ref is returned containing the error after this
411
manner:
411
412
412
C<$error->{'sqlerr'}> contains the offending SQL keyword.
413
C<$error->{'sqlerr'}> contains the offending SQL keyword.
413
C<$error->{'queryerr'}> contains the native db engine error returned for the query.
414
C<$error->{'queryerr'}> contains the native db engine error returned
415
for the query.
416
417
C<$offset>, and C<$limit> are required parameters.
414
418
415
Valid values for C<$format> are 'text,' 'tab,' 'csv,' or 'url. C<$sql>, C<$type>,
419
C<\@sql_params> is an optional list of parameter values to paste in.
416
C<$offset>, and C<$limit> are required parameters. If a valid C<$format> is passed
420
The caller is reponsible for making sure that C<$sql> has placeholders
417
in, C<$offset> and C<$limit> are ignored for obvious reasons. A LIMIT specified by
421
and that the number placeholders matches the number of parameters.
418
the user in a user-supplied SQL query WILL apply in any case.
419
422
420
=cut
423
=cut
421
424
Lines 470-478 sub strip_limit { Link Here
470
    }
473
    }
471
}
474
}
472
475
473
sub execute_query ($;$$$) {
476
sub execute_query {
477
478
    my ( $sql, $offset, $limit, $sql_params ) = @_;
474
479
475
    my ( $sql, $offset, $limit, $no_count ) = @_;
480
    $sql_params = [] unless defined $sql_params;
476
481
477
    # check parameters
482
    # check parameters
478
    unless ($sql) {
483
    unless ($sql) {
Lines 506-512 sub execute_query ($;$$$) { Link Here
506
    $sql .= " LIMIT ?, ?";
511
    $sql .= " LIMIT ?, ?";
507
512
508
    my $sth = C4::Context->dbh->prepare($sql);
513
    my $sth = C4::Context->dbh->prepare($sql);
509
    $sth->execute($offset, $limit);
514
    $sth->execute(@$sql_params, $offset, $limit);
510
    return ( $sth );
515
    return ( $sth );
511
    # my @xmlarray = ... ;
516
    # my @xmlarray = ... ;
512
    # my $url = "/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=$id";
517
    # my $url = "/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=$id";
(-)a/opac/svc/report (-12 / +3 lines)
Lines 56-76 unless ($json_text) { Link Here
56
    my $offset = 0;
56
    my $offset = 0;
57
    my $limit = C4::Context->preference("SvcMaxReportRows") || 10;
57
    my $limit = C4::Context->preference("SvcMaxReportRows") || 10;
58
    my $sql = $report_rec->{savedsql};
58
    my $sql = $report_rec->{savedsql};
59
    if (@sql_params) {
60
59
61
        # we have sql params need to fix the sql
60
    # convert SQL parameters to placeholders
62
        my @split = split /<<|>>/, $sql;
61
    $sql =~ s/(<<.*?>>)/\?/g;
63
        my @tmpl_parameters;
64
        for ( my $i = 0 ; $i < $#split / 2 ; $i++ ) {
65
            my $quoted = C4::Context->dbh->quote( $sql_params[$i] );
66
62
67
            # if there are special regexp chars, we must \ them
68
            $split[ $i * 2 + 1 ] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
69
            $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
70
        }
71
    }
72
    my ( $sth, $errors ) =
63
    my ( $sth, $errors ) =
73
      execute_query( $sql, $offset, $limit );
64
      execute_query( $sql, $offset, $limit, \@sql_params );
74
    if ($sth) {
65
    if ($sth) {
75
        my $lines;
66
        my $lines;
76
        if ($report_annotation) {
67
        if ($report_annotation) {
(-)a/t/db_dependent/Reports_Guided.t (-4 / +20 lines)
Lines 5-11 Link Here
5
5
6
use Modern::Perl;
6
use Modern::Perl;
7
7
8
use Test::More tests => 12;
8
use Test::More tests => 14;
9
9
10
use C4::Context;
10
use C4::Context;
11
11
Lines 14-21 BEGIN { Link Here
14
}
14
}
15
can_ok(
15
can_ok(
16
    'C4::Reports::Guided',
16
    'C4::Reports::Guided',
17
    qw(save_report
17
    qw(save_report delete_report execute_query)
18
      delete_report)
19
);
18
);
20
19
21
#Start transaction
20
#Start transaction
Lines 68-73 $count -= 2; Link Here
68
is( scalar( @{ get_saved_reports() } ),
67
is( scalar( @{ get_saved_reports() } ),
69
    $count, "Report2 and report3 have been deleted" );
68
    $count, "Report2 and report3 have been deleted" );
70
69
70
my $sth = execute_query('SELECT COUNT(*) FROM systempreferences', 0, 10);
71
my $results = $sth->fetchall_arrayref;
72
is(scalar(@$results), 1, 'running a query returned a result');
73
74
my $version = C4::Context->preference('Version');
75
$sth = execute_query(
76
    'SELECT value FROM systempreferences WHERE variable = ?',
77
    0,
78
    10,
79
    [ 'Version' ],
80
);
81
$results = $sth->fetchall_arrayref;
82
is_deeply(
83
    $results,
84
    [ [ $version ] ],
85
    'running a query with a parameter returned the expected result'
86
);
87
71
#End transaction
88
#End transaction
72
$dbh->rollback;
89
$dbh->rollback;
73
90
74
- 

Return to bug 9915