如何从 sqlite 存储和检索一个斑点？

小开

您需要使用 sqlite 准备好的语句接口。基本上，这个想法是您为 blob 准备一个带有占位符的语句，然后使用 bind 调用之一“绑定”您的数据..。

小开

下面是在 C # 中的实现方法:

class Program
{
static void Main(string[] args)
{
if (File.Exists("test.db3"))
{
File.Delete("test.db3");
}
using (var connection = new SQLiteConnection("Data Source=test.db3;Version=3"))
using (var command = new SQLiteCommand("CREATE TABLE PHOTOS(ID INTEGER PRIMARY KEY AUTOINCREMENT, PHOTO BLOB)", connection))
{
connection.Open();
command.ExecuteNonQuery();


byte[] photo = new byte[] { 1, 2, 3, 4, 5 };


command.CommandText = "INSERT INTO PHOTOS (PHOTO) VALUES (@photo)";
command.Parameters.Add("@photo", DbType.Binary, 20).Value = photo;
command.ExecuteNonQuery();


command.CommandText = "SELECT PHOTO FROM PHOTOS WHERE ID = 1";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
byte[] buffer = GetBytes(reader);
}
}


}
}


static byte[] GetBytes(SQLiteDataReader reader)
{
const int CHUNK_SIZE = 2 * 1024;
byte[] buffer = new byte[CHUNK_SIZE];
long bytesRead;
long fieldOffset = 0;
using (MemoryStream stream = new MemoryStream())
{
while ((bytesRead = reader.GetBytes(0, fieldOffset, buffer, 0, buffer.Length)) > 0)
{
stream.Write(buffer, 0, (int)bytesRead);
fieldOffset += bytesRead;
}
return stream.ToArray();
}
}
}

小开

最后，我用这种方法来插入一个 blob:

   protected Boolean updateByteArrayInTable(String table, String value, byte[] byteArray, String expr)
{
try
{
SQLiteCommand mycommand = new SQLiteCommand(connection);
mycommand.CommandText = "update " + table + " set " + value + "=@image" + " where " + expr;
SQLiteParameter parameter = new SQLiteParameter("@image", System.Data.DbType.Binary);
parameter.Value = byteArray;
mycommand.Parameters.Add(parameter);


int rowsUpdated = mycommand.ExecuteNonQuery();
return (rowsUpdated>0);
}
catch (Exception)
{
return false;
}
}

读取代码是:

   protected DataTable executeQuery(String command)
{
DataTable dt = new DataTable();
try
{
SQLiteCommand mycommand = new SQLiteCommand(connection);
mycommand.CommandText = command;
SQLiteDataReader reader = mycommand.ExecuteReader();
dt.Load(reader);
reader.Close();
return dt;
}
catch (Exception)
{
return null;
}
}


protected DataTable getAllWhere(String table, String sort, String expr)
{
String cmd = "select * from " + table;
if (sort != null)
cmd += " order by " + sort;
if (expr != null)
cmd += " where " + expr;
DataTable dt = executeQuery(cmd);
return dt;
}


public DataRow getImage(long rowId) {
String where = KEY_ROWID_IMAGE + " = " + Convert.ToString(rowId);
DataTable dt = getAllWhere(DATABASE_TABLE_IMAGES, null, where);
DataRow dr = null;
if (dt.Rows.Count > 0) // should be just 1 row
dr = dt.Rows[0];
return dr;
}


public byte[] getImage(DataRow dr) {
try
{
object image = dr[KEY_IMAGE];
if (!Convert.IsDBNull(image))
return (byte[])image;
else
return null;
} catch(Exception) {
return null;
}
}


DataRow dri = getImage(rowId);
byte[] image = getImage(dri);

小开

在 C + + 中(没有错误检查) :

std::string blob = ...; // assume blob is in the string




std::string query = "INSERT INTO foo (blob_column) VALUES (?);";


sqlite3_stmt *stmt;
sqlite3_prepare_v2(db, query, query.size(), &stmt, nullptr);
sqlite3_bind_blob(stmt, 1, blob.data(), blob.size(),
SQLITE_TRANSIENT);

那可以是 SQLITE_STATIC 如果查询将在 blob被销毁之前执行。

小开

这对我来说很有效(C #) :

byte[] iconBytes = null;
using (var dbConnection = new SQLiteConnection(DataSource))
{
dbConnection.Open();
using (var transaction = dbConnection.BeginTransaction())
{
using (var command = new SQLiteCommand(dbConnection))
{
command.CommandText = "SELECT icon FROM my_table";


using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader["icon"] != null && !Convert.IsDBNull(reader["icon"]))
{
iconBytes = (byte[]) reader["icon"];
}
}
}
}
transaction.Commit();
}
}

不需要分块，只需要转换为字节数组。

小开

由于还没有完整的 C + + 示例，这就是您如何在不进行错误检查的情况下插入和检索 float 数据的数组/向量的方法:

#include <sqlite3.h>


#include <iostream>
#include <vector>


int main()
{
// open sqlite3 database connection
sqlite3* db;
sqlite3_open("path/to/database.db", &db);


// insert blob
{
sqlite3_stmt* stmtInsert = nullptr;
sqlite3_prepare_v2(db, "INSERT INTO table_name (vector_blob) VALUES (?)", -1, &stmtInsert, nullptr);


std::vector<float> blobData(128); // your data
sqlite3_bind_blob(stmtInsertFace, 1, blobData.data(), static_cast<int>(blobData.size() * sizeof(float)), SQLITE_STATIC);


if (sqlite3_step(stmtInsert) == SQLITE_DONE)
std::cout << "Insert successful" << std::endl;
else
std::cout << "Insert failed" << std::endl;


sqlite3_finalize(stmtInsert);
}


// retrieve blob
{
sqlite3_stmt* stmtRetrieve = nullptr;
sqlite3_prepare_v2(db, "SELECT vector_blob FROM table_name WHERE id = ?", -1, &stmtRetrieve, nullptr);


int id = 1; // your id
sqlite3_bind_int(stmtRetrieve, 1, id);


std::vector<float> blobData;
if (sqlite3_step(stmtRetrieve) == SQLITE_ROW)
{
// retrieve blob data
const float* pdata = reinterpret_cast<const float*>(sqlite3_column_blob(stmtRetrieve, 0));
// query blob data size
blobData.resize(sqlite3_column_bytes(stmtRetrieve, 0) / static_cast<int>(sizeof(float)));
// copy to data vector
std::copy(pdata, pdata + static_cast<int>(blobData.size()), blobData.data());
}


sqlite3_finalize(stmtRetrieve);
}


sqlite3_close(db);


return 0;
}