CODEWITHSUNDEEP
c#system.data.sqlite
metadings
metadings

Reputation: 3848

Querying SQLite by BLOB column not possible?

I'm trying to query a SQLite table by a blob column which is a SHA256 hash.

CREATE TABLE Articles (HASH BLOB(32) PRIMARY KEY, Article TEXT)

A query like this doesn't return anything:

SELECT * FROM Articles WHERE HASH = ?
-- the parameter is bound using sqlite3_bind_blob, not to use literals

I made this little Program in C# which creates the table and also inserts a row to try.

using System;
using System.Linq;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Security.Cryptography;

// using lib;

public class Program {

    static int Main(string[] args) 
    {
        // Console.WriteLine(sqlite3.LibVersion);

        byte[] qHash;
        string qArticle = "Lorem ipsum dolor sit amet";

        using (var hashAlgorithm = new SHA256CryptoServiceProvider ()) {

            byte[] bytes = System.Text.Encoding.UTF8.GetBytes(qArticle);

            qHash = hashAlgorithm.ComputeHash(bytes);
        }

        var con = new SQLiteConnection ("Data Source=test.sqlite");
        con.Open();

        try {
            var create = new SQLiteCommand ("CREATE TABLE Articles (HASH BLOB(32) PRIMARY KEY, Article TEXT)", con);
            create.ExecuteNonQuery();

            var insert = new SQLiteCommand ("INSERT INTO Articles (HASH, Article) VALUES (?, ?); SELECT last_insert_rowid();", con);

            var insertParam0 = new SQLiteParameter(DbType.Binary);
            insertParam0.Value = qHash;
            insert.Parameters.Add(insertParam0);

            var insertParam1 = new SQLiteParameter(DbType.String);
            insertParam1.Value = qArticle;
            insert.Parameters.Add(insertParam1);

            object insert_result = insert.ExecuteScalar();
            var insert_rowid = (long)insert_result; 

        } catch { } // silently fail if exists usually

        var query = new SQLiteCommand ("SELECT * FROM Articles WHERE HASH = @hash", con);

        var param = new SQLiteParameter ("@hash", DbType.Binary);
        param.Value = qHash;
        query.Parameters.Add(param);

        using (SQLiteDataReader reader = query.ExecuteReader()) {

            while (reader.NextResult()) {
                var hash = new byte[32];
                long hashLen = reader.GetBytes(0, 0, hash, 0, 32);

                string article = reader.GetString(1);

                Console.WriteLine("HASH: {0}; Article: {1}", 
                    hash == null ? null : BitConverter.ToString(hash).Replace("-", ""), 
                    article
                );
            }

        }
        con.Close();

        return 0;
    }

}

However the reader doesn't have a result. Is querying a BLOB column not supported at all?
Is something else bugging me?

Upvotes: 0

Views: 1251

Answers (1)

metadings
metadings

Reputation: 3848

The problem here was solved by discovering that Read is used in a

while (reader.Read()) { }

loop, whereas NextResult is used in a 

do { } while (reader.NextResult());

loop.

However the inital problem was, that in my real project I created the database using SQLiteStudio (SQLite 3.7.16.1) in the BLOB Hex Editor; that confused the System.Data.SQLite library (using SQLite 3.8.2).

Now by creating the database using the program itself solved also the initial issue

So said, don't mix SQLite versions, not even to quickly try something. I tried so hard, I've even started a UnmanagedLibrary with DllImport bindings for sqlite3_*, just to discover that this was not the problem.

Upvotes: 1

Related Questions