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"]]

i've created a similar schema and tried - no surprise happened. please add your [sql dump](http://www.sqlitetutorial.net/sqlite-dump/), maybe there is something I can not not populate. — marmeladze, Feb 20 '18 at 10:30

score 2 · Answer 1 · answered Feb 20 '18 at 17:14

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);

Thanks for the explanation. I'm not sure why they were written as blobs, anyway I have re-written the INSERT query and now everything seems fine. — Sig, Feb 21 '18 at 10:59

score 0 · Answer 2 · answered Feb 20 '18 at 12:19

X prefix converts HEX values to strings.

sqlite> SELECT X'41';
A
sqlite> SELECT X'42';
B
sqlite> SELECT X'4242';
BB
sqlite> SELECT X'54545831';
TTX1 # Do not forget this one
sqlite> SELECT X'54';
T
sqlite> SELECT X'58';
X

Not let's see your code,

sqlite> select * from batches;
TTX1|0|2018-02-20 11:40:46 +0100|2018-02-20 11:40:46 +0100
TTX2|0|2018-02-20 11:40:54 +0100|2018-02-20 11:40:54 +0100
TTX3|0|2018-02-20 11:41:02 +0100|2018-02-20 11:41:02 +0100

Below queries returns empty sets.

sqlite> SELECT * FROM batches WHERE batch = 'TTX1';
sqlite> SELECT * FROM batches WHERE batch = 'TTX2';
sqlite> SELECT * FROM batches WHERE batch = 'TTX3';

Not insert ordinary string data.

sqlite> INSERT INTO batches VALUES('TTX2',0,'2018-02-20 11:40:46 +0100','2018-02-20 11:40:46 +0100');
sqlite> select * from batches;
TTX1|0|2018-02-20 11:40:46 +0100|2018-02-20 11:40:46 +0100
TTX2|0|2018-02-20 11:40:54 +0100|2018-02-20 11:40:54 +0100
TTX3|0|2018-02-20 11:41:02 +0100|2018-02-20 11:41:02 +0100
TTX2|0|2018-02-20 11:40:46 +0100|2018-02-20 11:40:46 +0100
sqlite> select * from batches where batch = 'TTX2';
TTX2|0|2018-02-20 11:40:46 +0100|2018-02-20 11:40:46 +0100

let's check some equalities.

sqlite> Select 123 = 123;
1
sqlite> Select X'54545831' = 'TTX1';
0

The data which responds to batch = TTX{number} are the ones which you inserted as strings. But the others do not met that condition.

Thanks for the reply. Unfortunately, I don't understand what you are trying to tell me. Why my strings are stored as hex? — Sig, Feb 20 '18 at 13:00
Your strings are not stored as hex, you are instructing those numeric strings to be handled as hex. `X'{hex_literal}'` takes given as hex and converts them to string. — marmeladze, Feb 20 '18 at 13:32

Sqlite3 stores string as hexadecimal

2 Answers2