Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

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

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


in reply to Re^3: 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

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
#!perl use strict; use DBI; my $dbh = connect(...); $dbh->do('SET SESSION group_concat_max_len = 2048');; my $pvt_col = 'prod'; my $count = 'count'; my $table = 'prod_test'; #test data create_test_db($ARGV[0] || 10); my $s1 =<<S1; 'SUM(CASE WHEN $pvt_col IS NULL THEN $count ELSE 0 END) AS Total,' S1 my $s2 =<<S2; CONCAT( '(SUM(CASE WHEN $pvt_col = ''',$pvt_col,''' THEN $count ELSE 0 END)) AS ',$pvt_col ) S2 my $sql1 = <<SQL1; SELECT CONCAT($s1,GROUP_CONCAT(DISTINCT $s2)) FROM ( SELECT $pvt_col,count(*) AS count FROM $table GROUP BY $pvt_col WITH ROLLUP) as temptbl SQL1 my $tmp = $dbh->selectrow_array($sql1); my $sql2 = <<SQL2; SELECT $tmp FROM ( SELECT $pvt_col, count(*) AS $count FROM $table GROUP BY $pvt_col WITH ROLLUP) as temptbl SQL2 my @f = $dbh->selectrow_array($sql2); print "@f\n"; sub create_test_db { my $no = shift; #$dbh->do("DROP TABLE $table"); $dbh->do("CREATE TABLE IF NOT EXISTS $table ( id int(11) NOT NULL AUTO_INCREMENT, $pvt_col varchar(20) NOT NULL, PRIMARY KEY (id))"); $dbh->do("DELETE FROM $table"); for my $n (1..$no){ for (1..$n){ $dbh->do("INSERT INTO $table VALUES (?,?)",undef,undef,sprintf " +prod%02d",$n); } } }

poj


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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (5)
As of 2014-07-12 20:56 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (241 votes), past polls