SQLite3：在 C++ 中插入带有 NULL 字符的 BLOB；

发布于 2024-07-11 06:05:35 字数 642 浏览 5 评论 0原文

我正在开发一个使用定制设计插件的 C++ API 使用 API 和特定 SQL 与不同数据库引擎交互句法。

目前，我正在尝试找到一种插入 BLOB 的方法，但由于 NULL 是 C/C++ 中的终止字符，BLOB 在构造时会被截断 INSERT INTO 查询字符串。到目前为止，我预计

//...
char* sql;
void* blob;
int len;
//...
blob = some_blob_already_in_memory;
len = length_of_blob_already_known;
sql = sqlite3_malloc(2*len+1);
sql = sqlite3_mprintf("INSERT INTO table VALUES (%Q)", (char*)blob);
//...

，如果可以在 SQLite3 交互式控制台中完成此操作，则应该可以使用正确转义的 NULL 字符构造查询字符串。也许有一种方法可以使用 SQLite SQL 语法也支持的标准 SQL 来做到这一点？

肯定有人以前遇到过同样的情况。我用谷歌搜索并找到了一些答案，但是是其他编程语言（Python）。

预先感谢您的反馈。

原文

I'm working on the development of a C++ API which uses custom-designed plugins
to interface with different database engines using their APIs and specific SQL
syntax.

Currently, I'm attempting to find a way of inserting BLOBs, but since NULL is
the terminating character in C/C++, the BLOB becomes truncated when constructing
the INSERT INTO query string. So far, I've worked with

//...
char* sql;
void* blob;
int len;
//...
blob = some_blob_already_in_memory;
len = length_of_blob_already_known;
sql = sqlite3_malloc(2*len+1);
sql = sqlite3_mprintf("INSERT INTO table VALUES (%Q)", (char*)blob);
//...

I expect that, if it is at all possible to do it in the SQLite3 interactive console, it should be possible to construct the query string with properly escaped NULL characters. Maybe there's a way to do this with standard SQL which is also supported by SQLite SQL syntax?

Surely someone must have faced the same situation before. I've googled and found some answers but were in other programming languages (Python).

Thank you in advance for your feedback.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

宁愿没拥抱 2024-07-18 06:05:35

再次感谢大家的反馈。这次我将报告我如何借助此处提供的指示解决问题。希望这将来能帮助其他人。

正如前三张海报所建议的，我确实使用了准备好的语句 - 另外，因为我也对获取列的数据类型感兴趣，而简单的 sqlite3_get_table() 是做不到的。

以以下常量字符串的形式准备 SQL 语句后：

INSERT INTO table VALUES(?,?,?,?);

它仍然是相应值的绑定。这是通过发出与列一样多的 sqlite3_bind_blob() 调用来完成的。（我还使用 sqlite3_bind_text() 来处理其他“简单”数据类型，因为我正在开发的 API 可以将整数/双精度数/等转换为字符串）。所以：

#include <stdio.h>
#include <string.h>
#include <sqlite3.h>
/* ... */
void* blobvalue[4] = { NULL, NULL, NULL, NULL };
int blobsize[4] = { 0, 0, 0, 0 };
const char* tail = NULL;
const char* sql = "INSERT INTO tabl VALUES(?,?,?,?)";
sqlite3_stmt* stmt = NULL;
sqlite3* db = NULL;
/* ... */
sqlite3_open("sqlite.db", &db);
sqlite3_prepare_v2(db,
                   sql, strlen(sql) + 1,
                   &stmt, &tail);
for(unsigned int i = 0; i < 4; i++) {
    sqlite3_bind_blob(stmt, 
                      i + 1, blobvalue[i], blobsize[i], 
                      SQLITE_TRANSIENT);
}
if(sqlite3_step(stmt) != SQLITE_DONE) {
    printf("Error message: %s\n", sqlite3_errmsg(db));
}
sqlite3_finalize(stmt);
sqlite3_close(db);

还要注意一些函数（sqlite3_open_v2()、sqlite3_prepare_v2()）出现在更高版本的 SQLite 上（我想是 3.5.x 及更高版本）。

文件 sqlite.db 中的 SQLite 表 tabl 可以使用（例如）创建

CREATE TABLE tabl(a TEXT PRIMARY KEY, b TEXT, c TEXT, d TEXT);

Thank you all again for your feedback. This time I'm reporting how I solved the problem with the help of the indications provided here. Hopefully this will help others in the future.

As suggested by the first three posters, I did use prepared statements — additionally because I was also interested in getting the columns' data types, and a simple sqlite3_get_table() wouldn't do.

After preparing the SQL statement in the form of the following constant string:

INSERT INTO table VALUES(?,?,?,?);

it remains the binding of the corresponding values. This is done by issuing as many sqlite3_bind_blob() calls as the columns. (I also resorted to sqlite3_bind_text() for other "simple" data types because the API I'm working on can translate integers/doubles/etc into a string). So:

#include <stdio.h>
#include <string.h>
#include <sqlite3.h>
/* ... */
void* blobvalue[4] = { NULL, NULL, NULL, NULL };
int blobsize[4] = { 0, 0, 0, 0 };
const char* tail = NULL;
const char* sql = "INSERT INTO tabl VALUES(?,?,?,?)";
sqlite3_stmt* stmt = NULL;
sqlite3* db = NULL;
/* ... */
sqlite3_open("sqlite.db", &db);
sqlite3_prepare_v2(db,
                   sql, strlen(sql) + 1,
                   &stmt, &tail);
for(unsigned int i = 0; i < 4; i++) {
    sqlite3_bind_blob(stmt, 
                      i + 1, blobvalue[i], blobsize[i], 
                      SQLITE_TRANSIENT);
}
if(sqlite3_step(stmt) != SQLITE_DONE) {
    printf("Error message: %s\n", sqlite3_errmsg(db));
}
sqlite3_finalize(stmt);
sqlite3_close(db);

Note also that some functions (sqlite3_open_v2(), sqlite3_prepare_v2()) appear on the later SQLite versions (I suppose 3.5.x and later).

The SQLite table tabl in file sqlite.db can be created with (for example)

CREATE TABLE tabl(a TEXT PRIMARY KEY, b TEXT, c TEXT, d TEXT);

回复收藏 0 原文

我只土不豪 2024-07-18 06:05:35

您需要将此函数与准备好的语句一起使用。

int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));

在 C/C++ 中，处理字符串中 NULL 的标准方法是存储字符串的开头和长度，或者存储指向字符串开头的指针和指向字符串结尾的指针。

You'll want to use this function with a prepared statement.

int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));

In C/C++, the standard way of dealing with NULLs in strings is to either store the beginning of the string and a length, or store a pointer to the beginning of a string and one to the end of the string.

回复收藏 0 原文