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?
reader.Readinstead of.NextResultwhyever. Should I close this Q?