Why perl put empty on my database while it has value?

Question

I'm working on a project and I want to insert some data into a database. I created the database and tables, but I am having some problems.

I decided to write a test program, here is my program code:

#!/us/bin/perl                                                                                                                                
use strict;
use warnings;
use Data::Dumper;
use DBI;

my $localtime = localtime(time);
print $localtime,"\n",;
my $char_data;
open(my $fh, "<", "/root/testfile/1453800452_5.117.219.107.bin");

while (<$fh>){
    $char_data .= $_;
}
print $char_data,"\n",;
my @Record = unpack('C*',$char_data);
my @IMEI = splice @Record,0,17;

my $IMEI = pack('C*',@IMEI);
  #print$IMEI,"\n";                                                                                                                             
my $dbh = DBI->connect("DBI:Pg:dbname=test;host=localhost","postgres", "", {PrintError=>0,RaiseError=>1});
my $sth=$dbh->prepare(qq/insert into testtime(time,data,imei) values(?,?,?)/);
$sth->execute($localtime,$char_data,$IMEI);

and here is my db:

time -----> timestamp without time zone

data------->text

imei-------->varchar           

and here is content of my opened file:

^@^O356307043839678^@^@^@^@^@^@^AM^H^K^@^@^AR}DB^H^@^^\2458\340^UJ(\300^D\356^@^@^H^@^@^@^@^@^@^@^@^@^@^AR}CW0^@^^\2458\340^UJ(\300^D\357^@^@^\ H^@^@^@^@^@^@^@^@^@^@^AR}Bl\224^@^^\2458\340^UJ(\300^D\356^@^@ ^@^@^@^@^@^@^@^@^@^@^AR}A\201\344^@^^\2458\340^UJ(\300^D\357^@^@ ^@^@\ ^@^@^@^@^@^@^@^@^AR}@\227>^@^^\2458\340^UJ(\300^D\351^@^@^H^@^@^@^@^@^@^@^@^@^@^AR}?\254\254^@^^\2458\340^UJ(\300^D\347^@^@^H^@^@^@^@^@^@^@^@^\ @^@^AR}>\302^P^@^^\2458\340^UJ(\300^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^AR}=\327j^@^^\2458\340^UJ(\300^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^AR}<\354\330^@^\ ^\2458\340^UJ(\300^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^AR}<^B2^@^^\2458\340^UJ(\300^D\213^@(^K^@^@^@^@^@^@^@^@^@^@^AR};^W\226^@^^\2458\340^UJ(\300^D\ \215^@(^L^@^@^@^@^@^@^@^@^K^@^@~A

My problem is each time that I run the program, only localtime is inserted into database, both imei and data will remain empty.

Can anyone help me with this?

Answer 1

PostgreSQL doesn't allow columns with a data type of text to contain null characters, and the strings you are trying to store in both your data and imei columns start with a null character

You should use a data type of bytea instead

Also, you should use an IO mode of :raw when you are reading binary files, and rather than concatenating multiple lines you should set the record separator $/ to undef like this

my $char_data = do {
    open my $fh, '<:raw', '/root/testfile/1453800452_5.117.219.107.bin' or die $!;
    local $/;
    <$fh>;
};

And rather than use unpack to aplit your string into an array and then pack to reform it into a string, it is much more convenient to use substr to select part of the string like this

my $IMEI = substr $char_data, 0, 17

---

You need to do a little more than just pass the strings to the execute method when you are using non-standard data types. Here's an example program that shows you how to associate data types with the placeholders in a newly-prepared statement. It needs to be done only once for each statement handle. Thereafter, DBI will know how to translate each parameter passed to execute

I have called bind_param only for the second and third placeholders. PostgreSQL is happy with a simple string value for time datatypes

I have used Data::Dumper to display the contents of the $imei variable before it is written to the database, as well as all the values in the record after it is retrieved from the database. As you can see, the two match as they should

use strict;
use warnings 'all';
use feature 'say';

use DBI;
use DBD::Pg qw/ PG_BYTEA /;
use Data::Dumper;
$Data::Dumper::Useqq = 1;
$Data::Dumper::Terse = 1;

my $char_data = do {
    open my $fh, '<:raw', '1453800452_5.117.219.107.bin';
    local $/;
    <$fh>;
};

my $imei = substr $char_data, 0, 17;
say Dumper $imei;

my $dbh = DBI->connect(
    "DBI:Pg:dbname=test;host=localhost",
    'postgres', '',
    {
        PrintError => 0,
        RaiseError => 1,
    }
);

$dbh->do('DROP TABLE test');

$dbh->do(<<'END_SQL');
CREATE TABLE test (
    "time"  timestamp without time zone,
    "data"  bytea,
    "imei"  bytea
)
END_SQL

my $insert = $dbh->prepare('INSERT INTO test (time, data, imei) VALUES (?, ?, ?)');
$insert->bind_param(2, undef, { pg_type => PG_BYTEA });
$insert->bind_param(3, undef, { pg_type => PG_BYTEA });

$insert->execute(scalar localtime, $char_data, $imei);

my @row = $dbh->selectrow_array('SELECT * FROM test');

say Dumper \@row;

output

"\0\017356307043839678"

[
  "2016-01-31 17:20:08",
  "\0\017356307043839678\0\0\0\0\0\0\1M\b\13\0\0\1R}DB\b\0\36\\2458\\340\25J(\\300\4\\356\0\0\b\0\0\0\0\0\0\0\0\0\0\1R}CW0\0\36\\2458\\340\25J(\\300\4\\357\0\0\34 H\0\0\0\0\0\0\0\0\0\0\1R}Bl\\224\0\36\\2458\\340\25J(\\300\4\\356\0\0 \0\0\0\0\0\0\0\0\0\0\1R}A\\201\\344\0\36\\2458\\340\25J(\\300\4\\357\0\0 \0\0\\ \0\0\0\0\0\0\0\0\1R}\@\\227>\0\36\\2458\\340\25J(\\300\4\\351\0\0\b\0\0\0\0\0\0\0\0\0\0\1R}?\\254\\254\0\36\\2458\\340\25J(\\300\4\\347\0\0\b\0\0\0\0\0\0\0\0\34 \@\0\1R}>\\302\20\0\36\\2458\\340\25J(\\300\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1R}=\\327j\0\36\\2458\\340\25J(\\300\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1R}<\\354\\330\0\34 \0342458\\340\25J(\\300\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1R}<\0022\0\36\\2458\\340\25J(\\300\4\\213\0(\13\0\0\0\0\0\0\0\0\0\0\1R};\27\\226\0\36\\2458\\340\25J(\\300\4\\ \\215\0(\f\0\0\0\0\0\0\0\0\13\0\0~A\n",
  "\0\017356307043839678"
]