How can I read a `uuid` from a column in a sqlite3 db using sqlite-net-pcl in Xamarin Forms

Question

I have a database schema that I do not control (it's a sqlite3 file exported from a desktop application that I need to interoperate with), that contains UUIDs for some of the columns. I'm using sqlite-net-pcl in a Xamarin.Forms app, and I cannot work out how to successfully read these columns. Here's what I've tried:

using the sqlite3 command line, I've confirmed that the schema has type uuid for the relevant column, and using select count(distinct uuidcolumn) from mytable; I've confirmed that there are values for each row. (The column is nullable which is relevant for the code snippet below but in practice all the rows have non-null values)
I have this model object:

namespace brahms.Model
{
    [Table("mytable")]
    public class MyTable
    {
        [Column("uuidcolumn")]
        public Guid UUIDColumn { get; }

        [PrimaryKey, AutoIncrement, NotNull]
        [Column("recordid")]
        public int RecordID { get; set; }
    }
}

if I fetch an object using database.Query<MyTable>() queries, UUIDColumn is always equal to Guid.Empty.
I tried switching the type in the class definition to Byte[]; it's always null.
I tried switching the type in the class definition to string; it's always null.
Same applies to the UInt16[] type (the GUID might be stored as a blob of 16-bit words, so I tried that type too)

How can I read the values in uuid-typed columns using sqlite-net-pcl?

@Jason the type is only given as `uuid` in the schema, I've read that [there isn't really a uuid type](https://stackoverflow.com/a/24043264/6105673) but I don't know what that means. I'd guess `blob`, as I can't read the content as strings, but I can't read it as bytes either. — Graham Lee, Feb 23 '21 at 12:26
Thinking `blob` made me think to try `UInt16[]` as the mapped type, which also doesn't work. — Graham Lee, Feb 23 '21 at 12:45
it sounds like byte[] is the correct approach - https://github.com/praeclarum/sqlite-net/issues/412 — Jason, Feb 23 '21 at 12:53
@Jason I thought so, not sure why it doesn't work in my case. I'd normally break out a minimally-reproducing test project, but because I don't control the schema there's not much minimisation I can do :-( — Graham Lee, Feb 23 '21 at 13:02

Graham Lee · Accepted Answer · 2021-03-01T10:25:05.160

I gave up on using the ORM features in sqlite-net-pcl and used this query:

db.executeScalar<byte[]>('select hex(uuidcolumn) from mytable where recordid=1');

What I get back is 72 bytes, which appear to represent the 36 ASCII characters in a string representation of a Guid (every so often one of the characters is 2D, which is - in the ASCII set). So I think that the backing store is a blob but one that's storing the text representation of the Guid, which is weird, but I'll be able to reconstruct the Guid from here.

Using this answer and getting that blob as a string, I ended up with this implementation:

        public Guid GetUUIDColumn()
        {
            string dbRep = _database.ExecuteScalar<string>("select hex(uuidcolumn) from mytable where recordid = ?", RecordID);
            if (dbRep == null || dbRep == string.Empty) return Guid.Empty;
            var bytes = new byte[dbRep.Length / 2];
            // each pair of bytes represents the ASCII code (in hexadecimal) for a character in the string representation of a Guid.
            for (var i = 0; i < bytes.Length; i++)
            {
                bytes[i] = Convert.ToByte(dbRep.Substring(i * 2, 2), 16);
            }

            string asString = Encoding.ASCII.GetString(bytes);
            return new Guid(asString);  
        }

How can I read a `uuid` from a column in a sqlite3 db using sqlite-net-pcl in Xamarin Forms

1 Answers1