Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

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

by poj (Priest)
on Jul 24, 2013 at 19:38 UTC ( #1046188=note: print w/ replies, xml ) Need Help??


in reply to Re: return string from query (DBI, MySQL) got truncated when trying to generating statement dynamically for MySQL
in thread 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";
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
Re^3: return string from query (DBI, MySQL) got truncated when trying to generating statement dynamically for MySQL
by khandielas (Novice) 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; }
      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
      poj

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (7)
As of 2014-08-20 06:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (105 votes), past polls