This will be a very long response. I apologize in advance. I added a rudimentary Ada binding to libmysqlclient.so to the Linux Simple I/O Library a while ago for my own purposes. I make no claim for completeness, but it is enough for me to push sensor data to a MySQL database and perform basic queries. My binding only handles string data items.
After reading your post, I decided to see what would happen with blob data items. First I created some blobs and stuffed them into a MySQL database table:
# Create some blobs, and *make them readable by the MySQL server process*:
dd if=/dev/random of=blob1.bin bs=1M count=1
dd if=/dev/random of=blob2.bin bs=1M count=1
dd if=/dev/random of=blob3.bin bs=1M count=1
md5sum -b blob1.bin >blob1.md5
md5sum -b blob2.bin >blob2.md5
md5sum -b blob3.bin >blob3.md5
chmod 444 blob*.bin
mv blob*.bin /tmp
sudo chown mysql:mysql /tmp/blob*
ls -l /tmp/blob*
-r--r--r-- 1 mysql mysql 1048576 Jan 15 13:44 /tmp/blob1.bin
-r--r--r-- 1 mysql mysql 1048576 Jan 15 13:44 /tmp/blob2.bin
-r--r--r-- 1 mysql mysql 1048576 Jan 15 13:44 /tmp/blob3.bin
# Create a simple table of blobs, as *database root user*:
mysql -p -u root
MariaDB [(none)]> use test1
MariaDB [test1]> create table Blobs(ID int, data longblob not null, primary key(ID));
Query OK, 0 rows affected (0.013 sec)
MariaDB [test1]> show tables ;
+-----------------+
| Tables_in_test1 |
+-----------------+
| Blobs |
| LoRaMessages |
| Temperature |
+-----------------+
3 rows in set (0.001 sec)
MariaDB [test1]> show fields from Blobs ;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ID | int(11) | NO | PRI | NULL | |
| data | longblob | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.001 sec)
MariaDB [test1]> insert into Blobs values(1, load_file('/tmp/blob1.bin'));
Query OK, 1 row affected (0.086 sec)
MariaDB [test1]> insert into Blobs values(2, load_file('/tmp/blob2.bin'));
Query OK, 1 row affected (0.017 sec)
MariaDB [test1]> insert into Blobs values(3, load_file('/tmp/blob3.bin'));
Query OK, 1 row affected (0.018 sec)
MariaDB [test1]> select ID from Blobs ;
+----+
| ID |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.001 sec)
MariaDB [test1]>
Bye
# Verify that a normal user can read the Blobs table:
mysql -p
MariaDB [(none)]> use test1
MariaDB [test1]> select ID from Blobs ;
+----+
| ID |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.001 sec)
MariaDB [test1]> quit
Bye
Then I wrote the following Ada program to reconstruct a blob file from the database. It lacks error checking and isn’t particularly efficient:
WITH Ada.Command_Line;
WITH Ada.Sequential_IO;
WITH Ada.Strings.Fixed;
WITH MySQL.libmysqlclient;
PROCEDURE test_mysql_fetch_blob IS
FUNCTION Trim
(Source : IN String;
Side : IN Ada.Strings.Trim_End :=
Ada.Strings.Both) RETURN String RENAMES Ada.Strings.Fixed.Trim;
TYPE Byte IS MOD 256;
-- Convert a hex string to a byte
FUNCTION ToByte(s : string) RETURN Byte IS
BEGIN
RETURN Byte'Value("16#" & s & "#");
END ToByte;
PACKAGE ByteIO IS NEW Ada.Sequential_IO(Byte);
server : MySQL.libmysqlclient.Server;
blobID : Natural;
dstfile : ByteIO.File_Type;
BEGIN
-- Connect to the database server
server := MySQL.libmysqlclient.Create
(Ada.Command_Line.Argument(1),
Ada.Command_Line.Argument(2),
Ada.Command_Line.Argument(3),
Ada.Command_line.Argument(4));
blobID := Natural'Value(Ada.Command_line.Argument(5));
-- Issue query
server.Dispatch("select hex(data) from Blobs where ID=" & Trim(blobID'Image));
-- Process results
server.FetchResults;
server.FetchRow;
ByteIO.Create(dstfile, name => "blob" & Trim(blobID'Image) & ".bin");
DECLARE
blobhex : String := server.FetchColumn(1);
blobsize : Natural := blobhex'Length/2;
BEGIN
FOR i IN 1 .. blobsize LOOP
ByteIO.Write(dstfile, ToByte(blobhex((2*i-1) .. 2*i)));
END LOOP;
END;
ByteIO.Close(dstfile);
server.Disconnect;
END test_mysql_fetch_blob;
Finally, I ran the program to reconstruct the three blob files:
# Reconstruct blob files from database:
./test_mysql_fetch_blob localhost pmunts redacted test1 1
./test_mysql_fetch_blob localhost pmunts redacted test1 2
./test_mysql_fetch_blob localhost pmunts redacted test1 3
ls -l *.bin
-rw-r--r-- 1 pmunts pmunts 1048576 Jan 15 15:17 blob1.bin
-rw-r--r-- 1 pmunts pmunts 1048576 Jan 15 15:17 blob2.bin
-rw-r--r-- 1 pmunts pmunts 1048576 Jan 15 15:17 blob3.bin
md5sum -c *.md5
blob1.bin: OK
blob2.bin: OK
blob3.bin: OK
So, my rudimentary client binding can retrieve blobs from a database simply by using the MySQL hex function to fetch a blob as a (very long) hex string. This scheme should work for any MySQL client binding.
Converting a blob to an in-memory binary object and dealing with compression are left as an exercise for the reader.
Addendum #1: I repeated my tests with 500 MB blobs. They failed until I added
[mysqld]
max_allowed_packet=1G
to /etc/mysql/conf.d/mysql.cnf on my Debian 13 (Trixie) Linux database server. This is a brute force solution that works because my server has 16 GB of ram. I’m actually a little surprised that Ada handles 1 GB strings.
Addendum #2: Next I tried running test_mysql_fetch_blob with 500 MB blobs on a Raspberry Pi 5 with 16 GB RAM running 64-bit Raspberry Pi OS and connected to the same gigabit LAN as the MySQL server:
time ./test_mysql_fetch_blob shiloh.munts.net pmunts redacted test1 1
real 0m57.958s
user 0m37.778s
sys 0m1.352s
md5sum -c blob1.md5test_mysql_fetch_blob
blob1.bin: OK
Finally, I tried running test_mysql_fetch_blob on a Rasberry Pi 5 with 4 GB RAM running MuntsOS Embedded Linux and connected to the MySQL server via WiFi. It fails with an out of memory kernel fault. Trying same with 8 GB RAM succeeded:
tarsus# time ./test_mysql_fetch_blob shiloh.munts.net pmunts redacted test1 1
real 4m 10.68s
user 0m 52.95s
sys 0m 3.56s
tarsus# md5sum -c blob1.md5
blob1.bin: OK
Test runs on the 4 GB Raspberry Pi 5 lasted 1 to 3 minutes, suggesting that 4 GB RAM is almost enough.
