title: perl dbi mysql - value precision
body: <p><strong>user@host:~#</strong> <code>mysql -V</code> - mysql  Ver 14.14 Distrib 5.7.25-28, for debian-linux-gnu (x86_64) using  7.0 running under debian-9,9</p>
  
  <p><strong>user@host:~#</strong> <code>uname -a</code> - Linux 4.9.0-8-amd64 #1 SMP Debian 4.9.144-3.1 (2019-02-19) x86_64 GNU/Linux</p>
  
  <p><strong>user@host:~#</strong> <code>perl -MDBI -e 'print $DBI::VERSION ."\n";'</code> - 1.636</p>
  
  <p><strong>user@host:~#</strong> <code>perl -v</code> This is perl 5, version 24, subversion 1 (v5.24.1) built for x86_64-linux-gnu-thread-multi</p>
  
  <p><strong>mysql></strong> <code>SHOW CREATE TABLE tbl1;</code></p>
  
  <pre>
  tbl1 | CREATE TABLE `tbl1` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `main_id` bigint(20) NOT NULL DEFAULT '0',
    `debet` varchar(255) NOT NULL DEFAULT '',
    `kurs` double(20,4) NOT NULL DEFAULT '0.0000',
    `summ` double(20,2) NOT NULL DEFAULT '0.00',
    `is_sync` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `main_id` (`main_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=70013000018275 DEFAULT CHARSET=utf8 </pre>
  
  <p><strong>mysql></strong> <code>SELECT * FROM tbl1 WHERE id=70003020040132;</code></p>
  
  <pre>
  -+---------------+----------------+-------+--------+---------+---------+
  | id             | main_id        | debet | kurs   | summ    | is_sync | 
  +----------------+----------------+-------+--------+---------+---------+
  | 70003020040132 | 70003020038511 |       | 0.0000 | 1798.00 | 0       |
  +----------------+----------------+-------+--------+---------+---------+
  </pre>
  
  <p>But when I get this data by perl::DBI module I lose precisions, and values <code>0.0000</code> and <code>1798.00</code> becomes <code>0</code> and <code>1798</code>. </p>
  
  <p>Code is next:</p>
  
  <pre><code>#### 
  #These 3 subs are connecting to DB, executing query and get data by fetchall_arrayref and coverting undef to NULL.
  ####
  sub DB_connect {
      # DataBase Handler
      my $dbh = DBI-&gt;connect("DBI:mysql:$DBNAME", $DBUSER, $DBPWD,{RaiseError =&gt; 0, PrintError =&gt; 0, mysql_enable_utf8 =&gt; 1}) or die "Error connecting to database: $DBI::errstr";
      return $dbh;
  }
  sub DB_executeQuery {
      # Executes SQL query. Return reference to array, or array, according to argv[0]
      # argv[0] - "A" returns array, "R" - reference to array
      # argv[1] - DB handler from DB_connect
      # argv[2] - query to execute
  
      my $choice=shift @_;
      my $dbh=shift @_;
      my $query=shift @_;
      print "$query\n" if $DEBUG&gt;2;
      my $sth=$dbh-&gt;prepare($query) or die "Error preparing $query for execution: $DBI::errstr";
      $sth-&gt;execute;
      my $retval = $sth-&gt;fetchall_arrayref;
  
      if ($choice eq "A" ) {
      my @ret_arr=();
      foreach my $value (@{ $retval }) {
          push @ret_arr,@{ $value };
      }
      return @ret_arr;
      }
      elsif ($choice eq "R") {
      return $retval;
      }
  }
  
  sub undef2null {
      # argv[1] - reference ro array of values where undef
      # values has to be changed to NULL
      # Returns array of prepared values: (...) (...) ...
      my $ref=shift @_;
      my @array=();
      foreach my $row (@{ $ref }) {
      my $str="";
      foreach my $val ( @{ $row} ) {
          if (! defined ( $val )) {
          $str="$str, NULL";
          }
          else {
          # Escape quotes and other symbols listed in square brackets
          $val =~ s/([\"\'])/\\$1/g; 
          $str="$str, \'$val\'";
          }
      }
      # Remove ', ' at the beginning of each VALUES substring
      $str=substr($str,2);
      push @array,"($str)";
      } # End foreach my $row (@{ $ref_values })
      return @array;
  } # End undef2null
  
  #### Main call
  #...
  # Somewhere in code I get data from DB and print it to out file
  my @arr_values=();
  my @arr_col_names=DB_executeQuery("A",$dbh,qq(SELECT column_name FROM `information_schema`.`columns` WHERE `table_schema` = '$DBNAME' AND `table_name` = '@{ $table }'));
  @arr_ids=DB_executeQuery("A",$dbh,qq(SELECT `id` FROM `@{ $table }` WHERE `is_sync`=0));
  my $ref_values=DB_executeQuery("R",$dbh,"SELECT * FROM \`@{ $table }\` WHERE \`id\` IN(".join(",",@arr_ids).")");
          @arr_values=undef2null($ref_values);
  print FOUT  "REPLACE INTO \`@{ $table }\` (`".join("`, `",@arr_col_names)."`) VALUES  ".(join ", ",@arr_values).";\n";
  </code></pre>
  
  <p></p>
  
  <p>and as a result I get next string:</p>
  
  <pre>REPLACE INTO `pko_plat` (`id`, `main_id`, `debet`, `kurs`, `summ`, `is_sync`) VALUES  ('70003020040132', '70003020038511', '', '0', '1798', '0')</pre>
  
  <p>in DB it was <code>0.0000</code> - become <code>0</code>, was <code>1798.00</code>, become <code>1798</code></p>
  
  <p>Perl's DBI documentation says it gets data 'as is' into strings, no translations made. But, then, who rounded values?</p>