Sqlite3 stores string as hexadecimal

Question

In a Ruby script I need to read some values from an existing g SQLite3 database.

DB = SQLite3::Database.open "#{App.root}/db/dm4sea_#{App.env}.db"

The database has 1 table (batches) with the following structure

DB.execute "PRAGMA table_info(batches);"
=> [[0, "batch", "VARCHAR(30)", 0, nil, 1], 
   [1, "fdl", "INT", 0, nil, 0], 
   [2, "created_at", "DATETIME", 0, nil, 0], 
   [3, "updated_at", "DATETIME", 0, nil, 0]]

The current content is

DB.execute "SELECT * FROM batches"
=> [["TTX1", 0, "2018-02-20 10:26:17 +0100", "2018-02-20 10:26:17 +0100"], 
    ["TTX2", 0, "2018-02-20 10:36:33 +0100", "2018-02-20 10:36:33 +0100"], 
    ["TTX3", 0, "2018-02-20 10:39:52 +0100", "2018-02-20 10:39:52 +0100"]]

However, with my big surprise, the following happens

DB.execute "SELECT * FROM batches WHERE batch = 'TTX3'"
=> [] 

Here the database dump

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE batches (
          batch VARCHAR(30) PRIMARY KEY,
          fdl INT,
          created_at DATETIME,
          updated_at DATETIME
        );
INSERT INTO batches VALUES(X'54545831',0,'2018-02-20 11:40:46 +0100','2018-02-20 11:40:46 +0100');
INSERT INTO batches VALUES(X'54545832',0,'2018-02-20 11:40:54 +0100','2018-02-20 11:40:54 +0100');
INSERT INTO batches VALUES(X'54545833',0,'2018-02-20 11:41:02 +0100','2018-02-20 11:41:02 +0100');
CREATE INDEX batches_batch
        ON batches (batch);
CREATE INDEX batches_fdl
        ON batches (fdl);
COMMIT;

Why are the batches stored as hexadecimal values?

DB.execute "SELECT * FROM batches WHERE batch = X'54545833'"
=> [["TTX3", 0, "2018-02-20 11:41:02 +0100", "2018-02-20 11:41:02 +0100"]]

Answer 1

These values are not stored as hexadecimal, they are stored as blobs. In SQL statements, the only way to write a blob is with a blob literal, in which the blob's bytes are represented with hexadecimal digits.

Whatever program wrote the database wrote these values as blobs.

To search for a blob, convert your search value into a blob:

SELECT * FROM batches WHERE batch = CAST('TTX3' AS BLOB);

Alternatively, modify the database so that it contains text values (which might break that other program):

UPDATE batches SET batch = CAST(batch AS TEXT);