Bind blob parameter in node-sqlite3

Question

I have an SQLite3 table with BLOB primary key (id):

CREATE TABLE item (
    id BLOB PRIMARY KEY,
    title VARCHAR(100)
);

In javascript models, the primary key (id) is represented as a Javascript string (one HEX byte per character):

var item = { 
    id: "2202D1B511604790922E5A090C81E169",
    title: "foo"
}

When I run the query below, the id parameter gets bound as a string. But I need it to be bound as a BLOB.

db.run('INSERT INTO item (id, title) VALUES ($id, $title)', {
    $id: item.id,
    $title: item.title
});

To illustrate, the above code generates the following SQL:

INSERT INTO item (id, title) VALUES ("2202D1B511604790922E5A090C81E169", "foo");

What I need is this:

INSERT INTO item (id, title) VALUES (X'2202D1B511604790922E5A090C81E169', "foo");

score 2 · Accepted Answer · edited Sep 24 '18 at 15:26

2

Apparently, the string needs to be converted to a buffer:

db.run('INSERT INTO item (id, title) VALUES ($id, $title)', {
    $id: Buffer.from(item.id, 'hex'),
    $title: item.title
});

edited Sep 24 '18 at 15:26

0x8BADF00D

answered Mar 20 '18 at 12:53

astreltsov

score 0 · Answer 2 · answered Mar 05 '18 at 21:03

0

Try casting the string as a blob:

INSERT INTO item(id, title) VALUES(CAST(id_string AS BLOB), 'foo');

Note also that the right way to quote strings in SQL is to use single quotes.

answered Mar 05 '18 at 21:03

varro

1

Strangely, this does absolutely nothing. Still inserted as a string. – astreltsov Mar 06 '18 at 04:04
db.run('INSERT INTO item (id, title) VALUES (CAST($id as BLOB), $title)', { $id: item.id, $title: item.title }); – astreltsov Mar 07 '18 at 04:04

2 Answers2