http://www.perlmonks.org?node_id=632366


in reply to JSON module

Using fetchrow_hashref you can do something like:

use strict;
use warnings;
use DBI;
use JSON;

my @output;
my $dbh = DBI->connect('dbi:Pg:dbname=foo','bar','baz');
my $sth = $dbh->prepare('select * from qux');
$sth->execute;

while ( my $row = $sth->fetchrow_hashref ){
    push @output, $row;
}

print objToJson( { myData => \@output } );

[download]

It'd be killer on RAM with a lot of rows, though...

--
"Go up to the next female stranger you see and tell her that her "body is a wonderland."
My hypothesis is that she’ll be too busy laughing at you to even bother slapping you." (src)

Replies are listed 'Best First'.
Re^2: JSON module
by hallikpapa (Scribe) on Aug 14, 2007 at 05:54 UTC
    This works great from a command line thanks, but I was getting a Premature end of script headers error when trying to call it from the same place I was calling the PHP script. Still trying to figure out what that is all about. Do you think pushing a dynamic XML pushing it back to the browser instead of printing a file would be better than JSON for big data formats? I have never done any XML formatting in perl. Maybe I should use the print option to send it back to the browser? The reporting table and everything comes back to the screen great, and if I switch the call back to the php script, everything works great. So what should I do instead of just a normal print to get it to pass it back as a response to the browser? Thank you monks.
[reply]

      The only difference would be that you need to send back the proper HTTP headers first before the JSON data:

      use strict;
use warnings;
use CGI;
use DBI;
use JSON;

my @output;
my $dbh = DBI->connect('dbi:Pg:dbname=foo','bar','baz');
my $sth = $dbh->prepare('select * from qux');
$sth->execute;

while ( my $row = $sth->fetchrow_hashref ){
    push @output, $row;
}

my $cgi = CGI->new;
print $cgi->header( 'application/json' );
print objToJson( { myData => \@output } );

      [download]

      --
      "Go up to the next female stranger you see and tell her that her "body is a wonderland."
      My hypothesis is that she’ll be too busy laughing at you to even bother slapping you.      " (src)

[reply]
[d/l]
        Of course! Thank you. So the data comes out formatted correctly that way, but then I try and do some manipulation with it and push it back into a hash. But it comes out improperly formatted and out of order. This is what I do 
        %hashData = (
                id   => $total_cdr_count,
                col2 => $total_call_count,
                col3 => $total_error_count,
                col4 => $formatMin,
                col5 => $formatASR,
                col6 => $formatPDD,
                col7 => $formatDUR,
                col8 => $hold_max_date
        );
        push @output, %hashData;

        [download]
        And after encoding, it looks like this 
        {"myData":["col7",null,"col5",null,"col3",0,"col8","2007-08-10 00:59:5
+8","col2",0,"id",3951,"col4",null
,"col6",null,"col7",null,"col5",null,"col3",0,"col8","2007-08-10 00:59
+:58","col2",0,"id",3961,"col4"
,null,"col6",null,"col7",null,"col5",null,"col3",0,"col8","2007-08-10 
+00:59:58","col2",0,"id"

        [download]
        the null is fine, I need to fix a few references, but it's out of order, and it looks like it is putting commas in places where a colon should be for json encoding? Also looks like it isn't putting the {} in between records. Here's an example of what it should look like coming out 
        {"myData":[{"id":"08-10-2007.00","col2":null,"col3":"I","col4":"0","co
+l5":"3951","col6":null,"col7":"8028"

,"col8":"0"},......

        [download]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom