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

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks!

I am working on this code where I need to send json back to some jQuery code. I would like to know if there is better way of doing this, I mean formatting JSON data. Is there anything that could be done better?
Here is the code sample for your evaluation:
!/usr/bin/perl use strict; use warnings; use CGI::Carp qw(fatalsToBrowser); use CGI qw(-oldstyle_urls :standard); use DBI; use DBD::ODBC; use Data::Dumper; use JSON; my $q = CGI->new; my $search = $q->param('search') || ""; my $user = "user"; my $pwd = "pwd"; my $dbh = DBI->connect("DBI:ODBC" ,$user ,$pass ,{ PrintError => 0 } ) + or die "Can not connect to ODBC database: $DBI::errstr\n +" ; my $sql = qq{SELECT name,city,state from mytable where name like ? lim +it 5}; my $data_handle = $dbh->prepare($sql); $data_handle->execute($search.'%') or die "SQL Error: $DBI::errstr\ +n"; my %returned_data; while (my $row = $data_handle->fetchrow_hashref()) { $returned_data{'name'} = $row->{NAME}; $returned_data{'city'} = $row->{CITY}; $returned_data{'state'} = $row->{STATE}; } $data_handle->finish; print $q->header(-type => "application/json", -charset => "utf-8"); my $json = encode_json \%returned_data; print $json;

Thanks for looking!

Replies are listed 'Best First'.
Re: Formatting JSON the right way
by hippo (Bishop) on Aug 20, 2014 at 12:08 UTC

    It looks to me like your while loop is overwriting the values in your hash at every iteration. It would probably be better to construct an array of hashrefs to store the data instead and then convert that array to your JSON at the end.

      Could you provide any code to illustrate what your are suggesting? Thanks!

        Sure, try this:

        my $returned_data = $data_handle->fetchall_arrayref ({}); # Any other ops here, and then: print $q->header(-type => "application/json", -charset => "utf-8"); my $json = encode_json ($returned_data); print $json;

        Untested (especially with ODBC which I almost never use) but hopefully you get the gist. See DBI for the details of fetchall_arrayref.

Re: Formatting JSON the right way
by derby (Abbot) on Aug 20, 2014 at 12:26 UTC

    Looks like you're just lower casing your result set keys ... you should be able to simplify via DBI's FetchHashKeyName param. Added with a combination of selectall_arrayref. (code not tested):

    !#/usr/bin/perl use strict; use warnings; use CGI::Carp qw(fatalsToBrowser); use CGI qw(-oldstyle_urls :standard); use DBI; use DBD::ODBC; use JSON; my $q = CGI->new; my $search = $q->param('search') || ""; my $user = "user"; my $pwd = "pwd"; my $dbh = DBI->connect("DBI:ODBC" ,$user ,$pass , { PrintError => 0, FetchHashKeyName => 'NAME_lc' } ) or die "Can not connect to ODBC database: $DBI::errstr\n" ; my $sql = qq{SELECT name,city,state from mytable where name like ? limit 5}; my $data = $dbh->selectall_arrayref( $sql, { Slice => {} }, $search.'%' ); print $q->header(-type => "application/json", -charset => "utf-8"); print encode_json( $data )
    -derby
Re: Formatting JSON the right way
by Anonymous Monk on Aug 21, 2014 at 02:28 UTC
    Construct a complete hashref structure containing the entire JSON packet that you want to transmit, then encode_json to prepare the string to send. When receiving data from the host, do the opposite with decode_json. In this way, the JSON encoder/decoder becomes a black-box and the text-string is a mystery that only the black-box needs to know how to handle.