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

[khandielas]

Another question is whether this is the right way to do pivot for MySQL table? The output from above code is something like:

total prod 1  prod 2 .....
585    10      15

I also use the same technique for percentage column too.

[poj]

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

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

[khandielas]

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

[poj]

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');

I could get 31.
Update ; Here is the code I tested it with
Read more... (2 kB)

poj

[khandielas]

It turns out the limit for the concat string is set by mySQL.

After set group_concat_max_len to a big number, the query works fine.

$sth = $dbh->do('SET @@group_concat_max_len = 320000')

Thank you all!