Attachment #27330 for bug #9915

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





=head2 execute_query

  ($sth, $error) = execute_query($sql, $offset, $limit[, \@sql_params])


This function returns a DBI statement handler from which the caller can
fetch the results of the SQL passed via C<$sql>.

If passed any query other than a SELECT, or if there is a db error,
C<$errors> an array ref is returned containing the error after this
manner:

C<$error->{'sqlerr'}> contains the offending SQL keyword.
C<$error->{'queryerr'}> contains the native db engine error returned
for the query.

C<$offset>, and C<$limit> are required parameters.

C<\@sql_params> is an optional list of parameter values to paste in.
The caller is reponsible for making sure that C<$sql> has placeholders
and that the number placeholders matches the number of parameters.
the user in a user-supplied SQL query WILL apply in any case.

=cut


    }
}

sub execute_query {

    my ( $sql, $offset, $limit, $sql_params ) = @_;

    $sql_params = [] unless defined $sql_params;

    # check parameters
    unless ($sql) {

    $sql .= " LIMIT ?, ?";

    my $sth = C4::Context->dbh->prepare($sql);
    $sth->execute(@$sql_params, $offset, $limit);
    return ( $sth );
    # my @xmlarray = ... ;
    # my $url = "/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=$id";




    my $offset = 0;
    my $limit = C4::Context->preference("SvcMaxReportRows") || 10;
    my $sql = $report_rec->{savedsql};
    if (@sql_params) {

    # convert SQL parameters to placeholders
    $sql =~ s/(<<.*?>>)/\?/g;
        my @tmpl_parameters;
        for ( my $i = 0 ; $i < $#split / 2 ; $i++ ) {
            my $quoted = C4::Context->dbh->quote( $sql_params[$i] );

            # if there are special regexp chars, we must \ them
            $split[ $i * 2 + 1 ] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
            $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
        }
    }
    my ( $sth, $errors ) =
      execute_query( $sql, $offset, $limit, \@sql_params );
    if ($sth) {
        my $lines;
        if ($report_annotation) {





use Modern::Perl;

use Test::More tests => 14;

use C4::Context;


}
can_ok(
    'C4::Reports::Guided',
    qw(save_report delete_report execute_query)
      delete_report)
);

#Start transaction

is( scalar( @{ get_saved_reports() } ),
    $count, "Report2 and report3 have been deleted" );

my $sth = execute_query('SELECT COUNT(*) FROM systempreferences', 0, 10);
my $results = $sth->fetchall_arrayref;
is(scalar(@$results), 1, 'running a query returned a result');

my $version = C4::Context->preference('Version');
$sth = execute_query(
    'SELECT value FROM systempreferences WHERE variable = ?',
    0,
    10,
    [ 'Version' ],
);
$results = $sth->fetchall_arrayref;
is_deeply(
    $results,
    [ [ $version ] ],
    'running a query with a parameter returned the expected result'
);

#End transaction
$dbh->rollback;

- 

Return to bug 9915

Lines 400-421 sub nb_rows { Link Here

(-)a/C4/Reports/Guided.pm (-14 / +19 lines)
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";

Lines 56-76 unless ($json_text) { Link Here

(-)a/opac/svc/report (-12 / +3 lines)
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) {

Lines 5-11 Link Here

(-)a/t/db_dependent/Reports_Guided.t (-4 / +20 lines)
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	-