Server side processing using perl (for Datatables.net ver-1.10.12)

RoshanJohn

Below sample code can be used as reference code for server side processing using perl scripting. In the Datatables forums, most of sample codes are PHP flavour, hope this sample will be useful for Perl DEV community. This sample code can be considered for DataTables.net option like individual column filtering, column sorting, paging.

General Instruction:

# ----------------------------------------------------------------------------------------
# This perl file can be called from AnyHTML.html, which is using DataTables.net version - 1.10.12 ( src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.js" )
# This perl code will fetch records from database table according to filter criteria selected in datatables used inside HTML GUI 
# This program can be considered as a sample code for Server side processing using perl. 
# You need to change the column name and table name in SQL query.
# ----------------------------------------------------------------------------------------

#!/usr/bin/perl -w

use strict;
use warnings;
use Data::Dumper;
use CGI;
use DBI;
use JSON;
use JSON::XS;
use Log::Log4perl;

# Initialize Logger
my $log_conf ='/some/folder/log4perl.conf';  #config file with logger parameters
Log::Log4perl::init($log_conf);
my $logger = Log::Log4perl->get_logger("yourloggerName");

# CGI OBJECT
my $q = new CGI;
my $params = $q->Vars;
#important, if you want to see the received parameters from GUI.
$logger->info ("Parameter's received: " . Dumper($params)); 

# connection details for Test and Prod database 
my $dsn = 'DBI:mysql:schemaName:databaseName:port';


# user, password for database.
my $user = 'test';
my $pass = 'nashor';
 
# get database handle
my $dbh = DBI->connect($dsn, $user, $pass);
  
#define the columnNames of table name from which,  you are display details.
my @columns = qw/columnName1 columnName2 columnName3 columnName4 columnName5 columnName6 columnName7 columnName8/;

my $sql_rowcount = "SELECT FOUND_ROWS() ";

my $sql_count = "SELECT COUNT(*) FROM tableName";
# get total number of rows in table, replace the above tableName with a actual table in datbase.
my $count = $dbh->selectrow_arrayref($sql_count)->[0];

my @values;
# base query for data. 
my $sql = "SELECT SQL_CALC_FOUND_ROWS columnName1, columnName2, columnName3, columnName4, columnName5, 
        columnName6, columnName7, columnName8 FROM tableName";


# filtering for search criteria 
my $searchValue = $params->{'search[value]'};
$logger-> info("header search value : $searchValue");

if( $searchValue ne '' ) {
    $sql .= ' WHERE (';
    $sql .= 'columnName1 LIKE ? OR columnName2 LIKE ? or columnName3 LIKE ? or columnName4 LIKE ? 
            or columnName5 LIKE ? or columnName6 LIKE ? or columnName7 LIKE ? or columnName8 LIKE ? )';
    push @values, ('%'. $searchValue .'%','%'. $searchValue .'%','%'. $searchValue .'%','%'. $searchValue .'%',
            '%'. $searchValue.'%', '%'. $searchValue .'%', '%'. $searchValue .'%', '%'. $searchValue .'%');
}

#individual column filtering
my $colsValue = "";
my $columnsStatus = "";
for (my $i = 0; $i < @columns; $i++) {
        $colsValue = $params->{'columns['. $i . '][search][value]'};
        $columnsStatus = $params->{'columns['. $i .'][searchable]'};
        
        if ($colsValue ne '' and $columnsStatus eq "true" ) {
            if (index ($sql,'WHERE') > 0) {
                 
                 $sql .= ' AND ';
            }
            else {
                $sql .= ' WHERE ';
                
            }
            $logger->info("Value for col Search Value : $colsValue");
            $logger->info("Value for col Search Status : $columnsStatus");
            $logger->info("Search Column Name : $columns[$i]");
            $sql .= "`" . $columns[$i] . "` LIKE '%$colsValue%' ";
            
        }
}


# sorting 
my $sortColumnId = $params->{'order[0][column]'};
$logger -> info('sort column Id :' . $sortColumnId);
my $sortColumnName = "";
my $sortDir = "";
    
if ( $sortColumnId ne '' ) {
    $sql .= ' ORDER BY ';
    $sortColumnName = $columns[$sortColumnId];
    $logger -> info('Sort Col name ' . $sortColumnName);  
    $sortDir = $params->{'order[0][dir]'};
    
    $sql .= $sortColumnName . ' ' . $sortDir;
    
    $logger -> info('sort details  :' . $sortColumnName . ' ' . $sortDir);
}

# paging 
if ($params->{'start'} ne '' and $params->{'length'} ne '-1' ) {
    $sql .= " LIMIT ? OFFSET ? ";
    push @values, $params->{'length'};
    push @values, $params->{'start'};
    $logger->info("Values for Limit and offset : @values");
}
else
{
    $sql .= " LIMIT ? OFFSET ? ";
    push @values, $params->{'length'};
    push @values, $params->{'start'};
    $logger->info("else part Values for Limit and offset : @values");
}
 
$logger-> info("SQL Value :" . $sql);

my $sth = $dbh->prepare($sql);
$sth->execute(@values);

# for filtered row count.
my $s1th = $dbh->prepare($sql_rowcount);
$s1th->execute();

my @result = $s1th->fetchrow_array();
my $filterCount = $result[0];

$logger-> info("Filter count :" . $filterCount);
$logger-> info("Total  count :" . $count);

# output hash
my %output = (
       "draw" => $q->param('draw'),
        "iTotalRecords" => $count,
        "iTotalDisplayRecords" => $filterCount
    );
    

my $rowcount = 0;
my $dataElement = "";
# fetching the different rows data.
while(my @aRow = $sth->fetchrow_array) {
        my @row = ();
        $logger->info ("Row fetch  : @aRow");
        
        for (my $i = 0; $i < @columns; $i++) {
            # looping thru different columns, pushing data to an array.
            $dataElement = "";
            #$logger->info ("column value :" . $columns[$i] . ":" . $aRow[$i]);
            $dataElement = $aRow[$i];
            push @row, $dataElement;           
            
        }
        push @row, $rowcount;
        # add each row data to hash collection.
        @{$output{'data'}}[$rowcount] = [@row];
        $logger->info ("Row value - $rowcount :" . Dumper([@row]));
        $rowcount++;
}
 
unless($rowcount) {
    $output{'data'} = '';  #we don't want to have 'null'. will break js
}

#covert the hash to string format. 
my $json_response =  \%output;

# add response header, important 
print $q->header('content-type: application/json \n\n');
# convert the response data to JSON format 
my $jsonOutput = encode_json $json_response;
$logger->info ("Received data from database  :  $jsonOutput ");

print $jsonOutput;

Juery code for HTML is as provided below for reference.

<script type="text/javascript">
  $(document).ready(function() {
        var dTable =  $('#example').DataTable( {
          "processing": true,
          "serverSide": true,
          "ajax": {
              "url": "cgi-bin/sampleServerScripting.pl",
              "type": "POST",
              "dataType":"json"
              },
           "columnDefs": [
                             {
                                 "targets": [8],
                                 "width": "20%", 
                                 "visible": false
                             }
                         ]   
         } );