Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

JSON module

by hallikpapa (Scribe)
on Aug 14, 2007 at 00:32 UTC ( #632350=perlquestion: print w/ replies, xml ) Need Help??
hallikpapa has asked for the wisdom of the Perl Monks concerning the following question:

I am running SQL queries in php, and can return the data in JSON format by a pre-built function in the newer releases of php. What I need to do is do the actual JSON encoding in perl. This is what the php code echos, and was hoping to get the result with the JSON module.
{"myData":[{"id":"08-10-2007.00","col2":"2007-08-10","col3":"I","col4" +:"0","col5":"3951","col6":"8028","col7":"0","col8":"2007-08-10 00:59: +58"},{"id":"08-10-2007.00","col2":"2007-08-10","col3":"S","col4":"1", +"col5":"10","col6":"19","col7":"1430","col8":"2007-08-10 00:48:41"}]}
How would I do this in perl using that JSON module? After getting the 8 column results, from the DBI query. How would I loop thru the output so it returns the correctly formatted response like above (can be anywhere from 500 to 100,000 rows)? I just can't seem to figure out how to do it even though it's probably super easy. Here's the simple while loop that grabs each row. How would I push all these variables into an array/hash and encode it to JSON to give the correct response to the browser?
$sth->execute(); while (($col_val1,$col_val2,$col_val3,$cdr_day,$call_status,$i +sdn_code,$cdr_count,$hold_time,$duration,$lastcall) = $sth->fetchrow_array) {

Comment on JSON module
Select or Download Code
Re: JSON module
by duff (Vicar) on Aug 14, 2007 at 02:26 UTC

    At a guess, I think you'd do something like this:

    #!/usr/bin/perl use DBI; use JSON; ... $sth->execute(); while (my $hr = $sth->fetchrow_hashref) { push @array, $hr; } my $json = JSON->new; print $json->objToJson(@array);

    Caveat emptor, etc.

Re: JSON module
by LTjake (Prior) on Aug 14, 2007 at 02:27 UTC

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

    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)

      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.

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

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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (4)
As of 2014-08-30 23:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (294 votes), past polls