Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
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
Replies are listed 'Best First'.
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)

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.

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 imbibing at the Monastery: (12)
As of 2015-07-31 10:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (276 votes), past polls