无法使用 QT 在 sqlite 中存储二进制数据 [英] Not able to store binary data in sqlite using QT
问题描述
无法使用 QT 将所有二进制数据值存储到 sqlite3 表中.
为此,我创建了一个 BLOB 数据列来存储名为 logTable 的二进制数据表.我正在尝试将二进制数据插入 binary_data[] 缓冲区,其值为 0x1 到 0xFF 和 0x00 到 0xFF 等等,直到 1024 字节.但是当我执行查询来存储数据时,该表只显示 0x1 到 0xFF,但没有存储剩余的字符(因为下一个立即数是 0x00).我想存储所有的二进制数据值?
For this I have created a BLOB data column to store binary data table named as logTable. I am trying to insert binary data to binary_data[] buffer, with values as 0x1 to 0xFF and 0x00 to 0xFF and so on till 1024 bytes. But when I execute the query to store the data, the table shows only 0x1 to 0xFF, but remaining character are not getting stored (since next immediate value is 0x00). I want to store all the binary data values?
int main(int argc, char *argv[])
{
QApplication a(argc, argv);
conect_to_log_db("./log.db");
unsigned char binary_data[1024];
for(unsigned int i = 0, value = 1; i < 1024; i++, value++)
{
binary_data[i] = value;
}
store_to_log_db("01/02/2012,13:03:58", binary_data, 1024);
......
}
bool store_to_log_db(QString dateTime, unsigned char *data, unsigned int dataLength)
{
QSqlQuery objQuery(objLogsDB);
QString query = "CREATE TABLE IF NOT EXISTS logTable(logDateTime VARCHAR(19), packet BLOB, direction INTEGER)";
objQuery.exec(query);
QByteArray dataArr;
dataArr.resize(dataLength);
for(unsigned int i = 0; i < dataLength; i++)
{
dataArr[i] = data[i];
}
QVariant blobData = dataArr.data();
objQuery.prepare("INSERT INTO logTable VALUES(:logDateTime,:packet,:direction)");
objQuery.bindValue(":logDateTime",dateTime);
objQuery.bindValue(":packet",blobData,QSql::In | QSql::Binary);
objQuery.bindValue(":direction",1);
qDebug() << objQuery.exec();
return true;
}
执行这段代码后,当我使用sqlite输出时,表的结果是254个字符
after executing this code, the result of the table is till 254 characters when I output from sqlite using
$sqlite3 日志.db
$sqlite3 log.db
sqlite>.output try.txt
sqlite>.output try.txt
sqlite>select * from logTable;
sqlite>select * from logTable;
$ls -l try.txt
$ls -l try.txt
大小为 406 字节
推荐答案
您必须使用 .dump
.sqlite3 交互式客户端不输出 BLOB 列.
You must use .dump
. The sqlite3 interactive client doesn't output BLOB columns.
$sqlite3 log.db
sqlite> .output try.txt
sqlite> .dump
sqlite> .quit
下面的代码是创建一个简单的包含 blob 的数据库的自包含示例.
The code below is a self contained example of creating a simple blob-containing database.
// https://github.com/KubaO/stackoverflown/tree/master/questions/sqlite-blob-11062145
#include <QtSql>
int main()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("./log.db");
if (!db.open()) { qDebug() << "can't open the database"; return 1; }
QSqlQuery query{db};
query.exec("DROP TABLE log");
if (!query.exec("CREATE TABLE log(packet BLOB)"))
qDebug() << "create table failed";
QVariant data[2] = {QByteArray{1024, 1}, QByteArray{2048, 2}};
query.prepare("INSERT INTO log VALUES(:packet)");
query.bindValue(":packet", data[0], QSql::In | QSql::Binary);
if (!query.exec()) qDebug() << "insert failed";
query.bindValue(":packet", data[1], QSql::In | QSql::Binary);
if (!query.exec()) qDebug() << "insert failed";
db.close();
if (!db.open()) { qDebug() << "can't reopen the database"; return 2; }
query.prepare("SELECT (packet) FROM log");
if (!query.exec()) qDebug() << "select failed";
for (auto const & d : data) if (query.next()) {
qDebug() << query.value(0).toByteArray().size() << d.toByteArray().size();
if (d != query.value(0)) qDebug() << "mismatched readback value";
}
db.close();
}
这篇关于无法使用 QT 在 sqlite 中存储二进制数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!