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"}]}

[download]
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)
        {

[download]

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

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

[reply]
[d/l]
[select]
      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]
Re: JSON module
by duff (Parson) 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);

    [download]

    Caveat emptor, etc.

    duff

[reply]
[d/l]

Back to Seekers of Perl Wisdom