Re^2: return string from query (DBI, MySQL) got truncated when trying to generating statement dynamically for MySQL

It looks easier with a hash

#!perl
use strict;
use DBI;
my $dbh = connect(..);

my %pivot=();
my $total;

my $sql = 'SELECT prod,count(*) FROM prod GROUP BY prod';
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my @f = $sth->fetchrow_array){
  $pivot{$f[0] || 'null' } = $f[1];
  $total       += $f[1];
}     
my @cols = sort keys %pivot;
unshift @cols,'total';
$pivot{'total'} = $total;

print sprintf "| %5s ",$_ for @cols;
print "|\n";
print sprintf "| %5d ",$pivot{$_} for @cols;
print "|\n";
[download]

Note ; This assumes you don't have a prod called 'total'.
poj

Comment on Re^2: return string from query (DBI, MySQL) got truncated when trying to generating statement dynamically for MySQL Download Code

Replies are listed 'Best First'.
Re^3: return string from query (DBI, MySQL) got truncated when trying to generating statement dynamically for MySQL by khandielas (Sexton) on Jul 24, 2013 at 21:18 UTC
Thank you. Using perl code to process the result sets is a solution, but I really want to know what is the reason for the failure and what parameter in DBI or MySQL control the result string length. I like to let Database engine to do all the work if possible, esp. for any type of 'set' calculation, so that I don't need to process row by row. Your code can be simplified a bit with 'ROLLUP' in the query': `my $stmt = "SELECT prod, count(*) from prod GROUP BY prod WITH ROLLUP; +"; $sth = $dbh->prepare( $stmt ); $sth->execute(); my %pivot; while ( my ( $k, $v ) = $sth->fetchrow_array() ) { $k = 'Total' unless defined $k; $k = 'Unknown' if $k eq ''; $pivot{$k} = $v; }` [download]	[reply] [d/l]
Re^4: return string from query (DBI, MySQL) got truncated when trying to generating statement dynamically for MySQL by poj (Abbot) on Jul 24, 2013 at 22:19 UTC
The limit is on GROUP_CONCAT. From the docs The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. With default limit the maximum results I could get was prod1 to prod15. After this `$dbh->do('SET SESSION group_concat_max_len = 2048');` [download] I could get 31. Update ; Here is the code I tested it with Read more... (2 kB) poj	[reply] [d/l] [select]