Compressed Data Values with PurePerl MySQL API
Using the PurePerl MySQL API– Part 11
Foreword: In this part of the series, I explain how you can use MySQL to compress a long value (kilo and mega-bytes) and insert into a table; I also explain the data types to use for the table.
By: Chrysanthus Date Published: 9 Nov 2016
Introduction
Data Types
In this scheme, compression takes place at the server (not at the client API). The table data type to use for a compressed column is BINARY or VARBINARY or BLOB. A Western European Character (e.g. A) occupies a space in memory of one byte. Each of these data types stores bytes (not really characters) in a table cell.
BINARY
With the binary data type, the number of bytes in each cell for a column are the same. Spaces are padded to make the length of the byte strings, the same, in all the cells of the column.
VARBINARY
VARBINARY is similar to BINARY; however, with VARBINARY a string of bytes in a cell can be of any length up to the maximum, indicated; there is no padding with spaces. So, in a column of VARBINARY, the string of bytes in the different cells are of different lengths.
The BLOB type is similar to VARBINARY, but it is for large value, such as a large image or a video file. The BLOB type is further divided into, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.
COMPRESS() and UNCOMPRESS()
The function for MySQL server to compress value, before saving, is COMPRESS(); the reverse function is, UNCOMPRESS(). The following SQL statement will compress 'any string' before inserting into the table.
INSERT INTO Pet (Comp) VALUES (COMPRESS('any string'));
The following SQL statement will select all the cell values from the column, Comp and uncompress them (before outputting).
SELECT UNCOMPRESS(Comp) from Pet;
Alter the pet table in the PetStore database with the following code:
#!C:/Perl/bin/perl5.18.2.exe
use Mysql;
use strict;
if (!Mysql::connect("root", "azemawo", "localhost", 3306))
{
print "$Mysql::Error_msg";
}
else
{
if (Mysql::select_db("PetStore") != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
if (!Mysql::query("ALTER TABLE Pet ADD Comp VARBINARY(100)"))
{
print $Mysql::Error_msg, "\n";
}
}
}
Mysql::close();
The following code will compress 'any string' before inserting into the table, in a cell in the column, Comp:
#!C:/Perl/bin/perl5.18.2.exe
use Mysql;
use strict;
if (!Mysql::connect("root", "azemawo", "localhost", 3306))
{
print "$Mysql::Error_msg";
}
else
{
if (Mysql::select_db("PetStore") != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
if (!Mysql::query("INSERT INTO Pet (Comp) VALUES (COMPRESS('any string'))"))
{
print $Mysql::Error_msg, "\n";
}
}
}
Mysql::close();
#!C:/Perl/bin/perl5.18.2.exe
use Mysql;
use strict;
if (!Mysql::connect("root", "azemawo", "localhost", 3306))
{
print "$Mysql::Error_msg";
}
else
{
if (Mysql::select_db("PetStore") != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
if (!Mysql::query("SELECT UNCOMPRESS(Comp) from Pet"))
{
print $Mysql::Error_msg, "\n";
}
else
{
my $numFields = @Mysql::Fields;
for (my $i=0; $i<$Mysql::No_of_Rows; ++$i)
{
for (my $j=0; $j<$numFields; ++$j)
{
my $filedName = $Mysql::Fields[$j][0];
print $Mysql::Result[$i]{$filedName};
print ', ' if ($j != ($numFields-1));
}
print "\n";
}
}
}
}
Mysql::close();
That is it for this part of the series. We stop here and continue in the next part.
Chrys
Perl Mailsend
PurePerl MySQL API
Perl Course - Professional and Advanced
Major in Website Design
Web Development Course
Producing a Pure Perl Library
MySQL Course
BACK NEXT