Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Re^2: I need outputting the results of a query in a formatted way to a scalar, so that I can email the results

by gandolf989 (Scribe)
on Sep 20, 2018 at 20:05 UTC ( #1222744=note: print w/replies, xml ) Need Help??


in reply to Re: I need outputting the results of a query in a formatted way to a scalar, so that I can email the results
in thread I need outputting the results of a query in a formatted way to a scalar, so that I can email the results

That helped. Unfortunately I am not getting buffer overflow errors. The total size of the email is 40k. So I don't see why I would get a page overflow error. Any ideas?
INFO - Opening DBI connection page overflow at /home/oracle/scripts/findMostExpensiveSQL.pl line 126 +. at /home/oracle/scripts/findMostExpensiveSQL.pl line 126. findMostExpensiveSQL::print_query_results(DBI::db=HASH(0x2c0d7 +00), "\x{a}SELECT * FROM (\x{a}SELECT sql_id, TO_CHAR(MIN(last_loa +d_time"...) called at /home/oracle/scripts/findMostExpensiveSQL.pl li +ne 252 page overflow at /home/oracle/scripts/findMostExpensiveSQL.pl line 126 +. at /home/oracle/scripts/findMostExpensiveSQL.pl line 126. findMostExpensiveSQL::print_query_results(DBI::db=HASH(0x2c0d7 +00), "\x{a}SELECT * FROM (\x{a}SELECT sql_id, TO_CHAR(MIN(last_loa +d_time"...) called at /home/oracle/scripts/findMostExpensiveSQL.pl li +ne 252 page overflow at /home/oracle/scripts/findMostExpensiveSQL.pl line 126 +. at /home/oracle/scripts/findMostExpensiveSQL.pl line 126. findMostExpensiveSQL::print_query_results(DBI::db=HASH(0x2c0d7 +00), "\x{a}SELECT * FROM (\x{a}SELECT t.sql_id, TO_CHAR(MIN(t.last_lo +ad_tim"...) called at /home/oracle/scripts/findMostExpensiveSQL.pl li +ne 260 page overflow at /home/oracle/scripts/findMostExpensiveSQL.pl line 126 +. at /home/oracle/scripts/findMostExpensiveSQL.pl line 126. findMostExpensiveSQL::print_query_results(DBI::db=HASH(0x2c0d7 +00), "\x{a}SELECT * FROM (\x{a}SELECT t.sql_id, TO_CHAR(MIN(t.last_lo +ad_tim"...) called at /home/oracle/scripts/findMostExpensiveSQL.pl li +ne 260 page overflow at /home/oracle/scripts/findMostExpensiveSQL.pl line 126 +. at /home/oracle/scripts/findMostExpensiveSQL.pl line 126. findMostExpensiveSQL::print_query_results(DBI::db=HASH(0x2c0d7 +00), "\x{a}SELECT * FROM (\x{a}SELECT t.sql_id, TO_CHAR(MIN(t.last_lo +ad_tim"...) called at /home/oracle/scripts/findMostExpensiveSQL.pl li +ne 260 page overflow at /home/oracle/scripts/findMostExpensiveSQL.pl line 126 +. at /home/oracle/scripts/findMostExpensiveSQL.pl line 126. findMostExpensiveSQL::print_query_results(DBI::db=HASH(0x2c0d7 +00), "\x{a}SELECT * FROM (\x{a}SELECT t.sql_id, TO_CHAR(MIN(t.last_lo +ad_tim"...) called at /home/oracle/scripts/findMostExpensiveSQL.pl li +ne 260
sub print_query_results { my $dbh = shift; my $sql_query = shift; my $sql = SQL::Beautify->new; my ( $sql_id, $min_last_load_time, $buffer_gets, $disk_reads, $exec +utions, $sorts, $parse_calls, $sql_fulltext ); my $return_scalar = qq{ }; my $sth = $dbh->prepare( $sql_query ); my $formatted_sql; $sth->execute(); $sth->bind_columns( undef, \$sql_id, \$min_last_load_time, \$buffer +_gets, \$disk_reads, \$executions, \$sorts, \$parse_calls, \$sql_full +text ); format OUTPUT = @|||||||||||||||@||||||||||||||||||||@|||||||||||||||@|||||||||||||||@ +|||||||||||||||@|||||||||||||||@||||||||||||||| 'SQL_ID', 'min_last_load_time','buffer_gets', 'disk_reads', ' +executions', 'sorts', 'parse_calls', @<<<<<<<<<<<<<<<@>>>>>>>>>>>>>>>>>>>>@>>>>>>>>>>>>>>>@>>>>>>>>>>>>>>>@ +>>>>>>>>>>>>>>>@>>>>>>>>>>>>>>>@>>>>>>>>>>>>>>> $sql_id, $min_last_load_time, $buffer_gets, $disk_reads, $ +executions, $sorts, $parse_calls, @* $sql_fulltext . open OUTPUT, '>', \$return_scalar; while( $sth->fetch() ) { $formatted_sql = SQL::Beautify->new; $formatted_sql->query( $sql_fulltext ); $sql_fulltext = $formatted_sql->beautify;; OUTPUT->flush(); write OUTPUT; } close OUTPUT; return $return_scalar; }

2018-09-22 Athanasius added code tags

  • Comment on Re^2: I need outputting the results of a query in a formatted way to a scalar, so that I can email the results
  • Select or Download Code

Replies are listed 'Best First'.
Re^3: I need outputting the results of a query in a formatted way to a scalar, so that I can email the results
by poj (Abbot) on Sep 20, 2018 at 21:12 UTC

    Consider separating the data extraction from the formatting so you can prove each part individually. Also, maybe sprintf is all you need.

    my $result = get_query_results($dbh,$sql); print format_query_results($result); sub get_query_results { my ($dbh,$sql_query) = @_; my $sth = $dbh->prepare( $sql_query ); $sth->execute(); my $ar = $sth->fetchall_arrayref; return $ar; } sub format_query_results { my $ar = shift; my $fmt = "%-15s %20s %15s %15s %15s %15s %15s\n"; my $heading = sprintf $fmt, qw(SQL_ID min_last_load_time buffer_gets disk_reads executions sorts parse_calls ); my $report; for my $rec ( @$ar ){ $report .= $heading; $report .= sprintf $fmt, @$rec[0..6]; my $sql = SQL::Beautify->new( query => $rec->[7] ); $report .= "\n".$sql->beautify."\n"; } return $report; }
    poj
      Hi Poj, That was exactly what I needed. I am no longer getting page overflows. Thanks!
Re^3: I need outputting the results of a query in a formatted way to a scalar, so that I can email the results
by hippo (Chancellor) on Sep 20, 2018 at 21:15 UTC
    The total size of the email is 40k. So I don't see why I would get a page overflow error. Any ideas?

    From perldiag:

    page overflow

    (W io) A single call to write() produced more lines than can fit on a page. See perlform.

    Either split the write up or increase the page size.

Re^3: I need outputting the results of a query in a formatted way to a scalar, so that I can email the results
by BillKSmith (Prior) on Sep 20, 2018 at 20:48 UTC
    Be warned, this is the reference to consult when all else fails. It will answer any floating point question, but at the expense of being "more than you care to know". Its math is not difficult, but the large amount of it can be frustrating. I do recommend that you skim through it to at least learn what it covers.
    Bill

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1222744]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (3)
As of 2019-11-11 22:42 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Strict and warnings: which comes first?



    Results (63 votes). Check out past polls.

    Notices?