jQuery Grid Plugin - jqGrid - Topic: Perl -> JSON -> JQGrid

Mose on Perl -> JSON -> JQGrid

Thu, 26 May 2011 02:21:36 +0300

This is the script I am currently working on. I am being inundated with newline characters and escaped qoutes which are not returning a valid JSON response:

#!/usr/bin/perl
use CGI;
use DBI;
use JSON;
use Test::JSON;
use POSIX qw(ceil);
use strict;
use warnings;

my $cgi = CGI->new;
my $page = $cgi->param('page');
my $limit = $cgi->param('limit');
my $sidx = $cgi->param('sidx');
my $sordx = $cgi->param('sordx');

if(!$sidx) {$sidx = 1};
my $start = $limit*$page - $limit;

my $dbh = DBI->connect('dbi:mysql:hostname=test;database=test',"test","test") or die $DBI::errstr;
my $count = $dbh->selectrow_array("SELECT COUNT(id) AS count FROM test;");
my $sql = "SELECT ID, title FROM test ORDER BY ? ? LIMIT ?, ?;";
my $sth = $dbh->prepare($sql) or die $dbh->errstr;

$sth->execute($sidx,$sordx,$start,$limit) or die $sth->errstr;
my $total_pages;
if( $count >0 ) {
        $total_pages = ceil($count/$limit);
} else {
        $total_pages = 0;
}
if ($page > $total_pages){ $page=$total_pages};
$start = $limit*$page - $limit;
my $i=0;
my $test;
my $response = {};
$response->{page} = $page;
$response->{total} = $total_pages;
$response->{records} = $count;
        while (my $row = $sth->fetchrow_arrayref) {
                my @arr = @{$row};
                s/\n/\\n/g for $response->{rows}[$i]{id} = $row->[0];
                s/\n/\\n/g for $response->{rows}[$i]{cell} = \@arr;
                $i++;
        }
my $json = JSON->new;
print $cgi->header(-type => "application/json", -charset => "utf-8");
my $json_text = $json->encode($response);
Test::JSON::is_valid_json $json_text;

I'll try to see if there is anything in your code example I can implement to rectify the issue.

laidback01 on Perl -> JSON -> JQGrid

Thu, 26 May 2011 00:38:38 +0300

So, basically you'll want to query your data however you do – DBI, DBIx::Class, Rose::DB, whatever. Load your resultset to a hash, then use JSON, and send it. I can't look at the example provided because the link doens't do what you think it will. Here's some early, extremely verbose (ugly) code to do what I said… However, it's the most straighforward stuff I have. YMMV. I'm using this in a catalyst project, but you can get the gist.

            my $dtf = $c->model('CommonDB')->storage->datetime_parser;
           my $rs =
              $c->{result_rs}
              ->procure_avoximeter_results( $dtf, $form_query->valid, );

           my %results_hash;
           my @results_array;

           $rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
           while ( my $hashref = $rs->next ) {
               my $operator = $c->{'avoximeter_operators'}->{ $hashref->{'Avoximeter1000ePatientData'}->{'user_id'} };
               $results_hash{ $hashref->{'result_id'} } =
               {
                  result_id   => $hashref->{'result_id'},
                   warnings    => $hashref->{'warnings'},
                   facility    => $c->{'facility_name_hr'}->{ $hashref->{'facility_id'} },
                   department => $c->{'department_name_hr'}->{ $hashref->{'department_id'} },
                   comment     => ($hashref->{'comment'}) ? "Yes" : "No",
                   _th_b_g_dl => $hashref->{'Avoximeter1000ePatientData'}->{'th_b_g_dl'},
                   _hb02       => $hashref->{'Avoximeter1000ePatientData'}->{'hb02'},
                   _patient_id => $hashref->{'Avoximeter1000ePatientData'}->{'patient_id'},
                   _sample_datetime => $hashref->{'Avoximeter1000ePatientData'}->{'sample_datetime'},
                    upload_status   => ( $hashref->{'lis_upload_status'} ) ? $hashref->{'lis_upload_status'} : "Not uploaded",
                    operator    => ( $operator->{'f_name'} && $operator->{'l_name'} ) ? "$operator->{'f_name'} $operator->{'l_name'}" : $operator->{'f_name'},

               };
               push (@results_array, $results_hash{ $hashref->{'result_id'} } );

           };

           my %list_hash;
           $list_hash{'total_count'} = @results_array;
           $list_hash{'list'} = \\@results_array;
           my $json = JSON->new;
           my $results_json = $json->encode( \\%list_hash );

Anyway, the jqGrid config that works with the above:

var data = [% results_json %];

jQuery(document).ready(function() {
jQuery("#results").jqGrid({
    datatype: 'jsonstring',
    datastr: data,
    cellEdit: true,
    cellurl:"[% c.uri_for('/rpc/AvoxPatDat/update') %]",
    colNames:['result_id','Date Performed','Patient ID','Thb (g/dL)', '%HbO2', 'Warnings', 'Operator', 'Comment' ],
    colModel :[
      {name:'result_id', index:'result_id', width:1, hidden:true, key:true},
      {name:'_sample_datetime', index:'_sample_datetime', width:130 },
      {name:'_patient_id', index:'_patient_id', width:90},
      {name:'_th_b_g_dl', index:'_th_b_g_dl', width:90 },
      {name:'_hb02', index:'_hb02', width:80 },
      {name:'warnings', index:'warnings', width:90 },
      {name:'operator', index:'operator', width:100, editable:true },
      {name:'comment',index:'comment',width:80, editable:true },
    ],
    rowNum: [% total_count %],
    sortname: 'result_id',
    sortorder: 'asc',
    viewrecords: true,
    forceFit: true,
    jsonReader : {
      root:"list",
      records:"total_count",
      repeatitems: false,
      id: "result_id",
    },
    height: $("#table_container").height(),
    caption: '[% total_count %][% total_count == 1 ? " result " : " results " %] returned',

});
});

</script>

hopefully it's enough to make sense of it.

Mose on Perl -> JSON -> JQGrid

Mon, 16 May 2011 05:12:42 +0300

I'm trying to set up a basic grid using Perl CGI. I'm having a hard time converting the PHP code from http://www.trirand.com/blog/jq.....qgrid.html to Perl.

I was wondering if anyone was building this with Perl that might share a sniplet as I am having trouble getting this to work.