datatables

dear all,

I have a question about datatables (jquery). I have been trying although 
with a simple file, it is not displaying in a browser (got 500 internal 
server error), but in CLI is working.

here is the HTML side:

#!/usr/bin/perl -w

use URI::Escape;
use strict;




print "Content-type: text/html; charset=utf-8\n\n";
print "<html>\n";
print "<head>\n";

print "<link rel=\"stylesheet\" 
href=\"http://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css\">\n";

print "<script 
src=\"http://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js\"></script>\n";
print "<script src=\"https://code.jquery.com/jquery-3.3.1.js\"></script>\n";
print "<script>
\$(document).ready( function () {
     \$('#pt_table').DataTable( {
         serverSide: true,
         processing: true,
         lengthMenu: [ 10, 25, 50, 100 ],
         columns: [ [ {'sName': 'Child_ID'}, {'sName': 'Full_Name_EN'}, 
{'sName': 'Full_Name_KH'}, {'sName': 'Gender'}, {'sName': 
'Date_of_Birth'}  ]
         pageLength: 10,
         ajax: {
             url: 'children_dynamic_finder_ajax.pl',
             type: 'POST',
             dataType: 'json'
         }
     } );
} );
</script>\n";
print "</head>\n";
print "<body>\n";
print "<table id=\"pt_table\" class=\"display\">\n";
     print "<thead>\n";
     print "<tr>\n";
         print "<th style=\"font-face=arial; font-size=10px; padding: 
10px 18px; border-top: 1px solid #111111; border-bottom: 1px solid 
#111111;\" width=12%>Child ID</th>\n";
         print "<th style=\"font-face=arial; font-size=10px; padding: 
10px 18px; border-top: 1px solid #111111; border-bottom: 1px solid 
#111111;\">Full Name EN</th>\n";
         print "<th style=\"font-face=arial; font-size=10px; padding: 
10px 18px; border-top: 1px solid #111111; border-bottom: 1px solid 
#111111;\">Full Name KH</th>\n";
         print "<th style=\"font-face=arial; font-size=10px; padding: 
10px 18px; border-top: 1px solid #111111; border-bottom: 1px solid 
#111111;\" width=12%>Gender</th>\n";
         print "<th style=\"font-face=arial; font-size=10px; padding: 
10px 18px; border-top: 1px solid #111111; border-bottom: 1px solid 
#111111;\" width=12% >Date of Birth</th>\n";
     print "</tr>\n";
     print "</thead>\n";
     print "<tbody>\n";
         print "<tr>\n";
             print "<td colspan=\"5\" 
class=\"dataTables_empty\">...</td>\n";
         print "</tr>\n";
     print "</tbody>\n";
print "</table>\n";


print "</body>\n";
print "</html>\n";

----

here is the ajax file used in the query.

#!/usr/bin/perl -w

use URI::Escape;
use strict;
use DBI;
use CGI qw(:standard);
use Time::Format;
use JSON;
use JSON::XS;
use Log::Log4perl;
use Data::Dumper;

my ($logger, $data) = "";


# Initialize Logger
my $log_conf ='/var/www/html/logs/log4perl.conf';  #config file with 
logger parameters
Log::Log4perl::init($log_conf);
$logger = Log::Log4perl->get_logger("children_dynamic_finder_ajax");

#$logger->info("Info message");

#my @columns = qw/child_id Full_Name_EN Full_Name_KH gender Date_of_Birth/;
my @columns = 
qw/Id,ContactCode,FirstNameEN,NameEN,FirstNameKH,NameKH,Gender,DateOfBirth/;
$logger->debug(@columns);
print @columns;




my $q = CGI->new;
my $params = $q->Vars;
#$logger->info("Parameter's received: " . Dumper($params));
print $params;
$logger->debug($params);

#my $table_length = $q->param('pt_table_length');
my $server2 = 'localhost';
my $db2 = 'css';
my $username2 = 'root';
my $password2 = 'PSE#2019';

my $dbh2 = DBI->connect("dbi:mysql:$db2:$server2", $username2, $password2);
my $sql_count = "select count(id) from Contacts";
my $count = $dbh2->selectrow_arrayref($sql_count)->[0];
$dbh2->disconnect();

$dbh2 = DBI->connect("dbi:mysql:$db2:$server2;mysql_enable_utf8mb4=1", 
$username2, $password2);
# Start building up the database query
my @values;
my $sql = "select 
Id,ContactCode,FirstNameEN,NameEN,FirstNameKH,NameKH,Gender,DateOfBirth 
from Contacts";

# if a search parameter was supplied in the AJAX call, build the WHERE 
part in the SQL statement
if( $params->{sSearch} ){
     $sql .= ' WHERE ';
     $sql .= 'ContactCode LIKE ? OR FirstNameEN LIKE ? or NameEN LIKE ? 
or Gender LIKE ? or DateOfBirth LIKE ?';
     push @values, 
('%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%');
}

# if a sorting parameter was supplied in the AJAX call, build up the 
ORDER BY part in the SQL statement
if( $params->{iSortingCols} ){
     $sql .= ' ORDER BY';
     foreach my $c (0 .. ( $params->{iSortingCols} -1 )){
         $sql .= ' ' . $columns[ $params->{"iSortCol_$c"} ] . ' ' . 
$params->{"sSortDir_$c"};
         $sql .= ','
     }
     $sql =~ s/,$//;
}

# Limit the output and also allow to paginate or scroll infinitely
#print $table_length;
$sql .= " LIMIT ? OFFSET ?";
push @values, (($params->{iDisplayLength} > 0 ? 
$params->{iDisplayLength} : 25), ( $params->{iDisplayStart} // 0));
print ($sql);
# Fetch the data from the database
$data = $dbh2->selectall_arrayref($sql, { Slice => [] }, @values);
print ($data);


# Return the JSON object
print $q->header('application/json');
my $json = encode_json({ aaData => $data, iTotalRecords => $count, 
iTotalDisplayRecords => $count, sEcho => int($params->{sEcho}) });
#my $json = encode_json({ tableLength => $table_length, iTotalRecords => 
$count, params=>$params, iTotalDisplayRecords => $count, sEcho => 
int($params->{sEcho}) });

print $json;

$dbh2->disconnect();

I tried to understand, by doing the inspect element, nothing appears in 
debugger.


Please help....
0
eko
7/15/2019 8:28:32 AM
perl.beginners 29352 articles. 3 followers. Follow

4 Replies
18 Views

Similar Articles

[PageSpeed] 5

> I have been trying although with a simple file, it is not displaying in a browser (got 500 internal server error)

500 errors mean the actual error msg is in the httpd error log (e.g.
/var/log/httpd/error_log) and often are about permissions or paths or
simple syntax errors.  But it does mean that your probably not
actually running anything, the script is dying at interpolation time.

On Mon, Jul 15, 2019 at 3:29 AM Eko Budiharto <eko.budiharto@gmail.com> wrote:
>
> dear all,
>
> I have a question about datatables (jquery). I have been trying although
> with a simple file, it is not displaying in a browser (got 500 internal
> server error), but in CLI is working.
>
> here is the HTML side:
>
> #!/usr/bin/perl -w
>
> use URI::Escape;
> use strict;
>
>
>
>
> print "Content-type: text/html; charset=utf-8\n\n";
> print "<html>\n";
> print "<head>\n";
>
> print "<link rel=\"stylesheet\"
> href=\"http://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css\">\n";
>
> print "<script
> src=\"http://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js\"></script>\n";
> print "<script src=\"https://code.jquery.com/jquery-3.3.1.js\"></script>\n";
> print "<script>
> \$(document).ready( function () {
>      \$('#pt_table').DataTable( {
>          serverSide: true,
>          processing: true,
>          lengthMenu: [ 10, 25, 50, 100 ],
>          columns: [ [ {'sName': 'Child_ID'}, {'sName': 'Full_Name_EN'},
> {'sName': 'Full_Name_KH'}, {'sName': 'Gender'}, {'sName':
> 'Date_of_Birth'}  ]
>          pageLength: 10,
>          ajax: {
>              url: 'children_dynamic_finder_ajax.pl',
>              type: 'POST',
>              dataType: 'json'
>          }
>      } );
> } );
> </script>\n";
> print "</head>\n";
> print "<body>\n";
> print "<table id=\"pt_table\" class=\"display\">\n";
>      print "<thead>\n";
>      print "<tr>\n";
>          print "<th style=\"font-face=arial; font-size=10px; padding:
> 10px 18px; border-top: 1px solid #111111; border-bottom: 1px solid
> #111111;\" width=12%>Child ID</th>\n";
>          print "<th style=\"font-face=arial; font-size=10px; padding:
> 10px 18px; border-top: 1px solid #111111; border-bottom: 1px solid
> #111111;\">Full Name EN</th>\n";
>          print "<th style=\"font-face=arial; font-size=10px; padding:
> 10px 18px; border-top: 1px solid #111111; border-bottom: 1px solid
> #111111;\">Full Name KH</th>\n";
>          print "<th style=\"font-face=arial; font-size=10px; padding:
> 10px 18px; border-top: 1px solid #111111; border-bottom: 1px solid
> #111111;\" width=12%>Gender</th>\n";
>          print "<th style=\"font-face=arial; font-size=10px; padding:
> 10px 18px; border-top: 1px solid #111111; border-bottom: 1px solid
> #111111;\" width=12% >Date of Birth</th>\n";
>      print "</tr>\n";
>      print "</thead>\n";
>      print "<tbody>\n";
>          print "<tr>\n";
>              print "<td colspan=\"5\"
> class=\"dataTables_empty\">...</td>\n";
>          print "</tr>\n";
>      print "</tbody>\n";
> print "</table>\n";
>
>
> print "</body>\n";
> print "</html>\n";
>
> ----
>
> here is the ajax file used in the query.
>
> #!/usr/bin/perl -w
>
> use URI::Escape;
> use strict;
> use DBI;
> use CGI qw(:standard);
> use Time::Format;
> use JSON;
> use JSON::XS;
> use Log::Log4perl;
> use Data::Dumper;
>
> my ($logger, $data) = "";
>
>
> # Initialize Logger
> my $log_conf ='/var/www/html/logs/log4perl.conf';  #config file with
> logger parameters
> Log::Log4perl::init($log_conf);
> $logger = Log::Log4perl->get_logger("children_dynamic_finder_ajax");
>
> #$logger->info("Info message");
>
> #my @columns = qw/child_id Full_Name_EN Full_Name_KH gender Date_of_Birth/;
> my @columns =
> qw/Id,ContactCode,FirstNameEN,NameEN,FirstNameKH,NameKH,Gender,DateOfBirth/;
> $logger->debug(@columns);
> print @columns;
>
>
>
>
> my $q = CGI->new;
> my $params = $q->Vars;
> #$logger->info("Parameter's received: " . Dumper($params));
> print $params;
> $logger->debug($params);
>
> #my $table_length = $q->param('pt_table_length');
> my $server2 = 'localhost';
> my $db2 = 'css';
> my $username2 = 'root';
> my $password2 = 'PSE#2019';
>
> my $dbh2 = DBI->connect("dbi:mysql:$db2:$server2", $username2, $password2);
> my $sql_count = "select count(id) from Contacts";
> my $count = $dbh2->selectrow_arrayref($sql_count)->[0];
> $dbh2->disconnect();
>
> $dbh2 = DBI->connect("dbi:mysql:$db2:$server2;mysql_enable_utf8mb4=1",
> $username2, $password2);
> # Start building up the database query
> my @values;
> my $sql = "select
> Id,ContactCode,FirstNameEN,NameEN,FirstNameKH,NameKH,Gender,DateOfBirth
> from Contacts";
>
> # if a search parameter was supplied in the AJAX call, build the WHERE
> part in the SQL statement
> if( $params->{sSearch} ){
>      $sql .= ' WHERE ';
>      $sql .= 'ContactCode LIKE ? OR FirstNameEN LIKE ? or NameEN LIKE ?
> or Gender LIKE ? or DateOfBirth LIKE ?';
>      push @values,
> ('%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%');
> }
>
> # if a sorting parameter was supplied in the AJAX call, build up the
> ORDER BY part in the SQL statement
> if( $params->{iSortingCols} ){
>      $sql .= ' ORDER BY';
>      foreach my $c (0 .. ( $params->{iSortingCols} -1 )){
>          $sql .= ' ' . $columns[ $params->{"iSortCol_$c"} ] . ' ' .
> $params->{"sSortDir_$c"};
>          $sql .= ','
>      }
>      $sql =~ s/,$//;
> }
>
> # Limit the output and also allow to paginate or scroll infinitely
> #print $table_length;
> $sql .= " LIMIT ? OFFSET ?";
> push @values, (($params->{iDisplayLength} > 0 ?
> $params->{iDisplayLength} : 25), ( $params->{iDisplayStart} // 0));
> print ($sql);
> # Fetch the data from the database
> $data = $dbh2->selectall_arrayref($sql, { Slice => [] }, @values);
> print ($data);
>
>
> # Return the JSON object
> print $q->header('application/json');
> my $json = encode_json({ aaData => $data, iTotalRecords => $count,
> iTotalDisplayRecords => $count, sEcho => int($params->{sEcho}) });
> #my $json = encode_json({ tableLength => $table_length, iTotalRecords =>
> $count, params=>$params, iTotalDisplayRecords => $count, sEcho =>
> int($params->{sEcho}) });
>
> print $json;
>
> $dbh2->disconnect();
>
> I tried to understand, by doing the inspect element, nothing appears in
> debugger.
>
>
> Please help....
>
> --
> To unsubscribe, e-mail: beginners-unsubscribe@perl.org
> For additional commands, e-mail: beginners-help@perl.org
> http://learn.perl.org/
>
>


-- 

a

Andy Bach,
afbach@gmail.com
608 658-1890 cell
608 261-5738 wk
0
afbach
7/15/2019 6:01:35 PM
dear Andy and all,

I opened and read the apache error log. it said there is an invalid 
character. And I think there is a unicode character. My question is also 
how to pass a unicode data from mysql to a browser through JSON.

in the mysql connection, I already enable utf8mb4, but I do not know how 
to enable specific charset in JSON.

On 16/07/19 01.01, Andy Bach wrote:
>> I have been trying although with a simple file, it is not displaying in a browser (got 500 internal server error)
> 500 errors mean the actual error msg is in the httpd error log (e.g.
> /var/log/httpd/error_log) and often are about permissions or paths or
> simple syntax errors.  But it does mean that your probably not
> actually running anything, the script is dying at interpolation time.
>
>
0
eko
7/16/2019 4:26:40 AM
You may want to give us the entire error msg.  If it's saying an
invalid char, it may be an actual non-ascii char in the text of the
program, not necessarily from the data, though that might depend upon
how you're displaying the data.

On Mon, Jul 15, 2019 at 11:26 PM Eko Budiharto <eko.budiharto@gmail.com> wrote:
>
> dear Andy and all,
>
> I opened and read the apache error log. it said there is an invalid
> character. And I think there is a unicode character. My question is also
> how to pass a unicode data from mysql to a browser through JSON.
>
> in the mysql connection, I already enable utf8mb4, but I do not know how
> to enable specific charset in JSON.
>
> On 16/07/19 01.01, Andy Bach wrote:
> >> I have been trying although with a simple file, it is not displaying in a browser (got 500 internal server error)
> > 500 errors mean the actual error msg is in the httpd error log (e.g.
> > /var/log/httpd/error_log) and often are about permissions or paths or
> > simple syntax errors.  But it does mean that your probably not
> > actually running anything, the script is dying at interpolation time.
> >
> >



-- 

a

Andy Bach,
afbach@gmail.com
608 658-1890 cell
608 261-5738 wk
0
afbach
7/17/2019 7:45:48 PM
dear Andy and all,

while waiting for any respond from the group, I try to debug and get 
better understanding about it. I stripe everything and start with one 
column of data. I got a new understanding. The error was caused in the 
sql query statement. In the SQL query statement, there is "? mark, wrong 
concat (it is my mistake to put in the wrong way).

Now, it is showing the JSON data in the browser console, not in the 
browser page. I am still looking for how to do it.

On 18/07/19 02.45, Andy Bach wrote:
> You may want to give us the entire error msg.  If it's saying an
> invalid char, it may be an actual non-ascii char in the text of the
> program, not necessarily from the data, though that might depend upon
> how you're displaying the data.
0
eko
7/18/2019 1:18:37 AM
Reply: