2016-04-15 19:47 GMT+02:00 Dominique Devienne <ddevienne at gmail.com>:
> On Fri, Apr 15, 2016 at 3:56 PM, Cecil Westerhof <cldwesterhof at gmail.com>
> wrote:
>
> > 2016-04-15 8:45 GMT+02:00 Dominique Devienne <ddevienne at gmail.com>:
> > > On Thu, Apr 14, 2016 at 9:30 PM, Cecil Westerhof <
> cldwesterhof at gmail.com>>
> > wrote:
> >
> > ?Is there a way to convert a hex string to a blob? I did not find it
> yet.?
> >
>
> I didn't find one either (see below), beside formatting a SQL statement
> with a blob literal,
> kinda like sprintf(..., "x'%s'", hex_string), which is hardly an option.
> I'm surprised there's no built-in function or SQL way to do it.
> I suspect there is, and I'm missing something. Hopefully someone will chime
> in.
>
> Perhaps a CTE could do it.
?I have to delve in that also. You are taking my sleep away. ;-)
?
> I'd reach for C/C++ and custom functions for
> such things.
>
?I am probably going to work with Java, but when exploring I am using Bash
and SQLite Browser.
In a program I have been testing with UUID, I had:
ps.setBytes(1, getRandomUUIDBlob());
and the function itself:
private static byte[] getRandomUUIDBlob() {
ByteBuffer bb;
byte[] bytes = new byte[16];
UUID uuid = UUID.randomUUID();
bb = ByteBuffer.wrap(bytes);
bb.putLong(uuid.getMostSignificantBits());
bb.putLong(uuid.getLeastSignificantBits());
return bytes;
}
This worked. I have to clean it up to show some strange things I noticed.
(In my eyes.)
?
> (I'd use a UDF for the printing part as well, I suspect it's faster that
> way, but didn't measure it).
>
?And something else to keep me awake. ;-)?
> C:\Users\DDevienne>sqlite3
> SQLite version 3.10.2 2016-01-20 15:27:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite>
> sqlite> select typeof(x'ab');
> blob
> sqlite> select typeof('ab');
> text
> sqlite> select typeof(cast('ab' as blob));
> blob
> sqlite> select hex(x'ab');
> AB
> sqlite> select hex(cast('ab' as blob));
> 6162
> sqlite>
>
?Here I get a blob:
sqlite> WITH UUIDTable AS (
...> SELECT '3DBA81DE-7AA7-412E-954F-5B2DA8D4AB6C' AS UUIDStr
...> )
...> SELECT TYPEOF(CAST(SUBSTR(UUIDStr, 1, 8) ||
...> SUBSTR(UUIDStr, 10, 4) ||
...> SUBSTR(UUIDStr, 15, 4) ||
...> SUBSTR(UUIDStr, 20, 4) ||
...> SUBSTR(UUIDStr, 25, 12) AS blob))
...> FROM UUIDTable
...> ;
blob
But when I do not use TYPEOF I get a string instead of a blob:
sqlite> WITH UUIDTable AS (
...> SELECT '3DBA81DE-7AA7-412E-954F-5B2DA8D4AB6C' AS UUIDStr
...> )
...> SELECT CAST(SUBSTR(UUIDStr, 1, 8) ||
...> SUBSTR(UUIDStr, 10, 4) ||
...> SUBSTR(UUIDStr, 15, 4) ||
...> SUBSTR(UUIDStr, 20, 4) ||
...> SUBSTR(UUIDStr, 25, 12) AS blob)
...> FROM UUIDTable
...> ;
3DBA81DE7AA7412E954F5B2DA8D4AB6C
What am I doing wrong?
--
Cecil Westerhof
