query - sqlite print blob as string




How to display blob value using x'abc' binary string literal syntax? (5)

Benjamin Berry's answer isn't right — it produces malformed UUIDs — but it shows an interesting technique using a subselect to generate randomness then selecting substrings from that. Here's something similar that I've confirmed does work:

select substr(u,1,8)||'-'||substr(u,9,4)||'-4'||substr(u,13,3)||
  '-'||v||substr(u,17,3)||'-'||substr(u,21,12) from (
    select lower(hex(randomblob(16))) as u, substr('89ab',abs(random()) % 4 + 1, 1) as v);

Some sample output:

c71122df-18e4-4a78-a446-fbf7b8f2969b
61e75f87-978b-4d9e-b587-bedcc2d23898
30eee0fa-2ff2-4ff5-b8ef-f99378272999

You can easily enter a blob value using the x'abc' syntax, but is there a way to display it that way too? As shown below, selecting directly, or implicitly converting to a string using concatenation doesn't work, and shows garbage (Windows DOS prompt here).

sqlite> create table blobs (uid blob);
sqlite> insert into blobs values (x'0123456789abcdef0123456789abcdef');
sqlite> select * from blobs;
☺#Egë½═∩☺#Egë½═∩
sqlite> select ''||uid from blobs;
☺#Egë½═∩☺#Egë½═∩

I'd like the select to display:

x'0123456789abcdef0123456789abcdef'

Thanks, --DD


Here is something similar which can be used directly as a expression:

lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))

for example to be passed as default value for column:

sqlite> create table "table" (
  "id" char(36) default (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))), 
  "data" varchar(255), primary key ("id")
);

sqlite> insert into "table" ("data") values ('foo');
sqlite> insert into "table" ("data") values ('bar');
sqlite> select * from "table";
947efcc9-4212-442a-b68c-eb6fbd8a7128|foo
a2c3857b-1eb4-40bd-aed2-6e8d68cc2ab8|bar

select quote(uid) from blobs

returns:

X'0123456789ABCDEF0123456789ABCDEF'

and if you really need it in lowercase, you can use:

select lower(quote(uid)) from blobs

Is there UID datatype in SQLITE if Yes then how to generate value for that

You can argue that SQLite doesn't support data types at all. In SQLite3, you can do this, for example.

sqlite> create table test (id wibblewibble primary key);

SQLite will happily create a column with the "data type" wibblewibble. SQLite will also happily create columns with the "data types" uuid, guid, and SuperChicken.

The crucial point for you is probably how to automatically generate a uid. SQLite can't help you much there.

You can leave it entirely up to the client program. If you're programming in python, use the uuid module. In ruby, you have the SecureRandom.uuid function. Other languages have similar features or workarounds.

You can write your own uid-generating function in C. (See Create or Redefine SQL Functions.) I'd call this a relatively extreme approach.

You can store it in either binary or text format.


Other conversations online suggest that there's a widespread misunderstanding about what a UUID is. A UUID is not simply a 128-bit random number. A UUID has structure and rules. See RFC 4122.


Using guid in sqlite select where guid is stored in the sqlite db as binaries

The GUID is probably being stored as a binary blob; try:

SELECT * FROM Employee
WHERE Employee.Id = X'a8828ddfef224d36935a1c66ae86ebb3';