pvarney pvarney - 1 year ago 96
jQuery Question

Perl: How to include scalar reference for DBIx::Class query information passed with ajax/json

I have a query I'm trying to pass from jquery/ajax/json to a perl script that will execute using DBIx::Class and return the results. I can get a basic query to work just fine, but am running into trouble when I have to include a scalar reference. My grasp on all the concepts around what I'm doing here is a little loose, so please forgive any inaccuracies.

Here is where I define the query parameters to pass to the perl script:

var jsonData = $.ajax({
url: 'cgi-bin/getdata.cgi',
data: JSON.stringify({
table: 'Processes',
search: {
query_time: {
'>=':'now() - INTERVAL 1 DAY'
attrib: {
select: [
order_by: 'query_time',
group_by: 'query_time'
contentType: "application/json",
dataType: 'json',
method: 'POST',
async: false

Here is the part of the perl script that reads standard in, converts from json and runs the query:

while ( <STDIN> )
$json .= $_;

my $query = from_json($json);

my $table = $query->{'table'};
my $search = $query->{'search'};
my $attrib= $query->{'attrib'};

print "Table:\n" . Dumper($table) . "\n";
print "Search:\n" . Dumper($search) . "\n";
print "Attrib:\n" . Dumper($attrib) . "\n";

my $retSet = $schema->resultset($table)->search( $search, $attrib );
my $retRow = $retSet->next;

And here is the output I get (with DBIC_TRACE enabled):

$VAR1 = 'Processes';

$VAR1 = {
'query_time' => {
'>=' => 'now() - INTERVAL 1 DAY'
'query_server' => 'SERVER1'

$VAR1 = {
'order_by' => 'query_time',
'group_by' => 'query_time',
'select' => [
'avg' => 'bytes_processed'

SELECT me.query_time, AVG( bytes_processed ) FROM processes me WHERE ( ( query_server = ? AND query_time >= ? ) ) GROUP BY query_time ORDER BY query_time: 'TSMCORP6', 'now() - INTERVAL 1 DAY'

The problem is that the
'now() - INTERVAL 1 DAY'
value needs to be a scalar reference in order to execute the query properly. If I build the query within the perl script (instead of passing from from the html file) like so:

my $search = {
query_server => 'SERVER1',
query_time => {
'>=' => \'now() - INTERVAL 1 DAY'

then search data looks like this and executes the way I want it to:

$VAR1 = {
'query_time' => {
'>=' => \'now() - INTERVAL 1 DAY'
'query_server' => 'SERVER1'
SELECT me.query_time, AVG( bytes_processed ) FROM processes me WHERE ( ( query_server = ? AND query_time >= now() - INTERVAL 1 DAY ) ) GROUP BY query_time ORDER BY query_time: 'TSMCORP6'

Based on my understanding, I don't think passing the value as a reference makes any sense, so I'm trying to understand how to "convert" it once it's in the perl script. I've tried throwing in the backslash before it gets processed by
, but that did not work. Much thanks for any help!

Update: I was able to turn the value into a reference with the following code. However, this worked because I knew what value needed to be converted to a reference. Under normal circumstances, the value could be anywhere in the data structure. Could I flag the value with something like
then search the entire data structure and convert each one I find? How would that look?

my $cond = $query->{'search'}->{'query_time'}->{'>='};

Answer Source

The problem is that JSON doesn't have anything that maps cleanly to a reference type because it's just data. However, I think you're on the right track with pre-processing the incoming data and converting literal SQL to the references that DBIC expects. On the sending side, you could add a literal_sql flag to the objects that need special handling, and then you could use filter_json_object to find those keys, delete them, and create scalar string references.

use strict;
use warnings;

use Data::Dump;
use JSON;

my $json = <<'EOF';
    "table": "Processes",
    "search": {
        "query_server": "SERVER1",
        "query_time": {
            ">=": "now() - INTERVAL 1 DAY",
            "literal_sql": true
    "attrib": {
        "select": [
            "query_time", {
                "avg": "bytes_processed"
        "order_by": "query_time",
        "group_by": "query_time"

my $query = JSON
    ->filter_json_object (sub {
        my $obj = shift;
        return unless $obj->{literal_sql};

        my ($key, $val) = each(%$obj);
        $obj->{$key} = \"$val";
        return $obj;



  attrib => {
              group_by => "query_time",
              order_by => "query_time",
              select   => ["query_time", { avg => "bytes_processed" }],
  search => {
              query_server => "SERVER1",
              query_time   => { ">=" => \"now() - INTERVAL 1 DAY" },
  table  => "Processes",
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download