1

I really need your help for understanding with the following perl example code:

#!/usr/bin/perl

# Hashtest

use strict;
use DBI;
use DBIx::Log4perl;
use Data::Dumper;
use utf8;

if (my $dbh = DBIx::Log4perl->connect("DBI:mysql:myDB","myUser","myPassword",{
            RaiseError => 1,
            PrintError => 1,
            AutoCommit => 0,
            mysql_enable_utf8 => 1
        }))
{

    my $data = undef;
    my $sql_query = <<EndOfSQL;
SELECT  1
EndOfSQL
    my $out = $dbh->prepare($sql_query);
    $out->execute() or exit(0);
    my $row = $out->fetchrow_hashref();
    $out->finish();

    # Debugging
    print Dumper($row);

    $dbh->disconnect;
    exit(0);
}

1;

If i run this code on two machines i get different results.

Result on machine 1: (Result i needed with integer value)

arties@p51s:~$ perl hashTest.pl 
Log4perl: Seems like no initialization happened. Forgot to call init()?
$VAR1 = {
          '1' => 1
        };

Resulst on machine 2: (Result that makes trouble because of string value)

arties@core3:~$ perl hashTest.pl
Log4perl: Seems like no initialization happened. Forgot to call init()?
$VAR1 = {
          '1' => '1'
        };

As you can see on machine 1 the value from MySQL will be interpreted as integer value and on machine 2 as string value. I need on both machines the integer value. And it is not possible to modify the hash later, because the original code has too much values, that must be changed...

Both machines uses DBI 1.642 and DBIx::Log4perl 0.26

The only difference is the perl version machine 1 (v5.26.1) vs. machine 2 (v5.14.2)

So the big question is, how can I make sure I always get the integer in the hash as the result?

Update 10.10.2019:

To show perhaps better the problem, i improve the above example:

...
use Data::Dumper;
use JSON;  # <-- Inserted
use utf8;
...

...
print Dumper($row);

# JSON Output
print JSON::to_json($row)."\n"; # <-- Inserted

$dbh->disconnect;
...

Now the output on machine 1 with last line the JSON Output:

arties@p51s:~$ perl hashTest.pl 
Log4perl: Seems like no initialization happened. Forgot to call init()?
$VAR1 = {
          '1' => 1
        };
{"1":1}

Now the output on machine 2 with last line the JSON Output:

arties@core3:~$ perl hashTest.pl
$VAR1 = {
          '1' => '1'
        };
{"1":"1"}

You see, that both Data::Dumper AND JSON has the same behavor. And as i wrote bevor, +0 is not an option because the original hash is much more complex.

Both machines use JSON 4.02

Arties
  • 190
  • 4
  • 11
  • Whether Data::Dumper thinks it's a string or number is irrelevant (and this is the most likely thing that has changed). In Perl, it is both. What output are you using where it actually matters? – Grinnz Oct 09 '19 at 21:09
  • It's not an Data::Dumper thing because JSON::to_json has the "same" difference! I need the hashresult for json output. And than in jquery / bootstrap the following modal.find('.modal-body input#checkbox').prop('checked', objData.checked); If objData.checked 0 or 1 (integer) all fine, but "0" or "1" are always "true" -> so bad... – Arties Oct 09 '19 at 21:17
  • Can you show the actual code that is affected by this number-vs-string (?), or whatever the problem is? Thanks for an example but `Dumper` does non-trivial things with data, and you don't care for it -- how's that JSON you mention (and how are its values used?), if that's what has a problem? – zdim Oct 09 '19 at 22:21
  • JSON, or more specifically the backend it's choosing to use, has completely different heuristics for deciding whether to output a scalar as a string or number from Data::Dumper, and even from each other and between different versions. – Grinnz Oct 09 '19 at 22:33
  • DBD::mysql creates all result values as strings, and so all of these heuristics will guess that it is a string, unless you use it somewhere in a numerical operation, in which case the latest versions of JSON::PP and Cpanel::JSON::XS will consider it a number. This behavior of DBD::mysql was probably buggy in an older version. – Grinnz Oct 09 '19 at 22:34
  • 2
    To be explicit, try [Cpanel::JSON::XS::Type](https://metacpan.org/pod/Cpanel::JSON::XS::Type) to specify what types your values should be, or add 0 to them in-place to force them to be interpreted as a number by any JSON library. – Grinnz Oct 09 '19 at 22:35
  • 2
    As suggested, adding +0 to the result will turn it into a number. If you want to do this programmatically without hard-coding tables, you can possibly use 'mysql_is_num' but I have never tried. More information here: https://stackoverflow.com/questions/40800205/why-does-dbi-implicitly-change-integers-to-strings – Nick P Oct 10 '19 at 03:21
  • If you using the binding functions of DBI, you can specify the expected type. – ikegami Oct 10 '19 at 04:53

1 Answers1

2

@Nick P : That's the solution you linked Why does DBI implicitly change integers to strings? , the DBD::mysql was different on both systems! So i upgraded on machine 2 from Version 4.020 to Version 4.050 and now both systems has the same result! And Integers are Integers ;-)

So the result on both machines is now:

$VAR1 = {
          '1' => 1
        };
{"1":1}

Thank you!

Arties
  • 190
  • 4
  • 11