Why is perl empty in my database while it matters?

Question

I am working on a project and I want to insert some data into a database. I created a database and tables, but I'm 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 the contents of my open file:

^ @ ^ O356307043839678 ^ @ ^ @ ^ @ ^ @ ^ @ ^ @ ^ AM ^ ^ ^ @ ^ @ ^ AR} DB ^ ^ @ ^^ \ 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 ^ @ ^ @ ^ N ^ @ ^ @ ^ @ ^ @ ^ @ ^ @ ^ @ ^ @ ^ @ ^ @ ^ 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}; ^ \ 226 ^ @ ^ ^ \ 2458 \ 340 ^ UJ (\ 300 ^ D \ \ 215 ^ @ (^ L ^ @ ^ @ ^ @ ^ @ ^ @ ^ @ ^ @ ^ @ ^ K ^ @ ^ @ ~ A

My problem is that every time I run the program, only local time is inserted into the database, and imei and data remain empty.

Can anyone help me with this?

Answer 1

PostgreSQL does not allow columns with text data type to contain null characters, and the rows you are trying to store in both data and imei columns begin with a null character

Instead you should use the bytea data bytea

In addition, you should use the I / O :raw mode when you read binary files, and instead of 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 instead of using unpack to add a string to an array and then pack to convert it to a string, it is much more convenient to use substr to select a part of a string like this

 my $IMEI = substr $char_data, 0, 17 

---

You need to do a little more than just pass strings to the execute method when using non-standard data types. Here is an example program that shows how to associate data types with placeholders in a recently prepared statement. This needs to be done only once for each instruction descriptor. After that, the DBI will know how to translate each parameter passed to execute

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

I 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 received from the database. As you can see, they 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" ]