cPanel Question posted in
The official documentation can be seen here.

Perl, JSON, float, quotes – CPanel

We have a Perl application which creates JSONs from DB queries. Unfortunately, it handles floats wrong, as it puts double quotes around floats.

E.g.:

use DBI;
use JSON::MaybeXS;

my $dbs="dbi:ODBC:myconnection,myuser,mypwd,";
my @ARR=split/,/ ,$dbs;
$dbh = DBI->connect(@ARR, { PrintError=>0, RaiseError=>1,  LongReadLen=>60000}) ;
my $sql = "SELECT 'Hello there' str, '0.0123' str_flt, 0.0123 flt_sm, 10.1234 flt_lg, 1234 nt, getdate() dt";
my $sth = $dbh->prepare($sql);
$sth->execute();

my $rows = $sth->fetchall_arrayref({});

print "Structure of result is: nn";
my $num_fields = $sth->{NUM_OF_FIELDS};
for ( my $i=0; $i< $num_fields; $i++ ) {
    my $field = $sth->{NAME}->[$i];
    my $type = $sth->{TYPE}->[$i];
    my $precision = $sth->{PRECISION}->[$i]; # e.g. VARCHAR(50) has a precision of 50
    print "Field $field is of type $type, with precision $ precisionn";
}

$sth->finish();
$dbh->disconnect;

my $json_opts = JSON::MaybeXS->new(utf8 => 1, pretty => 1);
my $json_maybe = $json_opts->encode($rows);

print("nJSON::MaybeXS:n");
print($json_maybe);

The output looks like:

Structure of result is:

Field str is of type -8, with precision 11
Field str_flt is of type -8, with precision 6
Field flt_sm is of type 2, with precision 4
Field flt_lg is of type 2, with precision 6
Field nt is of type 4, with precision 10
Field dt is of type 93, with precision 23

JSON::MaybeXS:
[
   {
      "dt" : "2018-10-05 09:42:43.483",
      "nt" : 1234,
      "flt_sm" : ".0123",
      "str" : "Hello there",
      "str_flt" : "0.0123",
      "flt_lg" : "10.1234"
   }
]

Perl version: 5.18.2

Intalled JSON libraries: JSON, JSON-Any, JSON-PP, JSON-XS, Cpanel-JSON-XS, JSON-MaybeXS, Parse-CPAN-Meta

One thing that the output shuffles the fields and each run results in a different order in the JSON. The main problem is the double quotes around the floats, which causes problems when consuming it in another application, as it recognises these fields as strings and has to parse them one by one. DBI recognises the field types correctly, it somehow lost during the json encoding… Any idea how to fix it? (Yes, I could easily fix it using regex, but that wouldn’t be so nice…)

Tags:

2

Answers


  1. 0

    That’s why Cpanel::JSON::XS has Cpanel::JSON::XS::Type. Note that the floats might lose precision.

    #!/usr/bin/perl
use warnings;
use strict;

use Cpanel::JSON::XS;
use Cpanel::JSON::XS::Type;

my $type = json_type_arrayof({
    dt      => JSON_TYPE_STRING,
    nt      => JSON_TYPE_INT,
    flt_sm  => JSON_TYPE_FLOAT,
    str     => JSON_TYPE_STRING,
    str_flt => JSON_TYPE_FLOAT,
    flt_lg  => JSON_TYPE_FLOAT,
});


my $data = [{
    dt      => "2018-10-05 09:42:43.483",
    nt      => 1234,
    flt_sm  => ".0123",
    str     => "Hello there",
    str_flt => "0.0123",
    flt_lg  => "10.1234",
}];

my $jsonizer = Cpanel::JSON::XS->new->canonical->pretty;
print $jsonizer->encode($data, $type);
    Login or Signup to reply.
  2. 0

    Try something like this to turn your numeric fields into numbers by adding 0. Something similar worked for me once. Using JSON::XS version 3.02.

    use JSON::XS 3.02;
...
my @numfield=qw( nt flt_sm str_flt flt_lg );
for my $f (@numfield) {
  defined $$_{$f} and $$_{$f}+=0 for @$rows;
}
...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search