Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

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

by khandielas (Acolyte)
on Jul 24, 2013 at 18:26 UTC ( #1046180=perlquestion: print w/replies, xml ) Need Help??
khandielas has asked for the wisdom of the Perl Monks concerning the following question:

Hi, all, I could not find the solution for my problem.

I try to pivot several mySQL tables for reporting, the row number is not fixed so what I am doing now is that grouping it first as a subquery and then pivot it. It works fine with small number of rows but does not work with large number rows.

Here is an example, their are many cols, but only prod matters to me.

prod xx xx ... 'prod 1' 'prod 2' 'prod 1' 'prod 3' 'Prod 4' ......
After grouping by prod, the table should be
prod count prod 1 10 prod 2 15 ......

Here is my code:

use DBI; my $dbh = connect(....); my $pvt_col = 'prod'; my $count = 'count'; my $table = 'prod' my $stmt = q{ SELECT CONCAT( '(SUM(CASE WHEN }. $pvt_col. q{ IS NULL THEN Count ELSE 0 END)) AS + Total, ', GROUP_CONCAT(DISTINCT CONCAT( '(SUM(CASE WHEN }. $pvt_col. q{ = ''', }. $pvt_col. q{, ''' T +HEN ', }. $count. q{, ' ELSE 0 END)) AS `', }. $pvt_col. q{, '`' ) ) ) FROM ( SELECT }. $pvt_col. q{, count(*) AS }. $count. q{ FROM }. $table. q{ GROUP BY }. $pvt_col. q{ WITH ROLLUP) as temptbl;}; my $sth = $dbh->prepare($stmt); $sth->execute(); my $tmp; while ( ($tmp) = $sth->fetchrow_array() ) { print"$tmp\n"; last; }

The expected output should be:

(SUM(CASE WHEN prod IS NULL THEN Count ELSE 0 END)) AS Total, (SUM(CASE WHEN prod = 'prod1' THEN 10 ELSE 0 END)) AS `prod1`, (SUM(CASE WHEN prod = 'prod2' THEN 15 ELSE 0 END)) AS `prod2`, (SUM(CASE WHEN prod = 'prod3' THEN 1 ELSE 0 END)) AS `prod3`, .....

After this, I can query again to produce pivot results:

my $statement = q{ SELECT }. $tmp. q{ FROM( SELECT }. $pvt_col. q{, count(*) AS }. $count. q{ FROM }. $table. q{ GROUP BY }. $pvt_col. q{ WITH ROLLUP ) as temptbl;}; $sth = $dbh->prepare($statement); ....
$statement is like:
SELECT ( (SUM(CASE WHEN prod IS NULL THEN Count ELSE 0 END)) AS Total, (SUM(CASE WHEN prod = 'prod1' THEN 10 ELSE 0 END)) AS `prod1`, (SUM(CASE WHEN prod = 'prod2' THEN 15 ELSE 0 END)) AS `prod2`, (SUM(CASE WHEN prod = ..... ... FROM( SELECT prod, count(*) AS Count FROM prod GROUP BY prod WITH ROLL +UP) as temptbl;

It works beautifully for tables with small number of rows, but failed for tables with larger number of rows. $tmp is trucated when I have large rows. So $statement cannot be excuted successfully.

Monks, any help is highly appreciated!

Environment: Perl 5.14.2, ubuntu 12.04.2, MySQL 5.5.31

  • Comment on return string from query (DBI, MySQL) got truncated when trying to generating statement dynamically for MySQL
  • Select or Download Code

Replies are listed 'Best First'.
Re: return string from query (DBI, MySQL) got truncated when trying to generating statement dynamically for MySQL
by khandielas (Acolyte) on Jul 24, 2013 at 18:34 UTC
    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.
      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

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

      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!

Re: return string from query (DBI, MySQL) got truncated when trying to generating statement dynamically for MySQL
by Anonymous Monk on Jul 25, 2013 at 07:26 UTC
Re: return string from query (DBI, MySQL) got truncated when trying to generating statement dynamically for MySQL
by Anonymous Monk on Jul 24, 2013 at 19:37 UTC

    Why are your SQL statements so complex, while you seem to perform a rather normal task?

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1046180]
Approved by marto
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (10)
As of 2017-12-15 20:06 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What programming language do you hate the most?




















    Results (443 votes). Check out past polls.

    Notices?