问题:将16进制数据转换成字符串,然后插入到SQLite3数据库,通过可视化工具或sqlite3_get_table()可以正常获取数据,但16进制数据转换成字符串带来的问题是会导致NandFlash空间增大一倍,如16进制0x34转换成字符串后是0x33、0x34,占用两字节空间,在紧缺的NandFlash资源下是不允许的!所以入库的16进制数据需直接存储到数据库,而不进行字符串的转换之后再入库,但这带来的问题是在通过sqlite3_get_table() api函数获取记录时记录不完整,例如存储的一笔记录为{0x01, 0x03, 0x12, 0x01, 0x0a, 0x12, 0x00, 0x14, 0x12, 0x00, 0x1e },通过可视化工具可以正常显示,但通过sqlite3_get_table() 时只能获取到记录数据为{0x01, 0x03, 0x12, 0x01, 0x0a, 0x12, 0x00 },这条记录中存在字节0x00,ASCII码表中显示0x00是结束符,而sqlite3_get_table()的处理机制是将数据库里的记录导入到内存,而内存判断0x00是结束字符,所以sqlite3_get_table()获取的记录不完整!!!正常读取记录的机制应该是sqlite3_prepare()->sqlite3_step()->sqlite3_column_bytes()\sqlite3_column_blob(stmt, 0),具体详见下案例源码:
void Example()
{
sqlite3 *db;
sqlite3_stmt *stmt;
int iRet, nrows, ncols;
char *pszSqliteName="gwooDb";
char *errmsg, **results;
//1. 创建数据库gwooDb
if (sqlite3_open(pszSqliteName, &db) == SQLITE_OK)
{
//2. 创建数据库表TbReal
char *szSqlCreateTab = "CREATE TABLE TbReal (StartTime DATETIME,SuccTime DATETIME,SaveTime DATETIME,MtrAddr BLOB, x20000200 BLOB, x20010200 BLOB)";
sqlite3_exec(db, szSqlCreateTab, 0, 0, &errmsg);
//3. 添加记录到数据库表TbReal
char *szInsertSql = "INSERT INTO TbReal (StartTime, SuccTime, SaveTime, MtrAddr, x20000200) VALUES(DATETIME(1551262800, 'unixepoch'), DATETIME(1551263014, 'unixepoch'), DATETIME(1551262800, 'unixepoch'), \"000337448041\",?)";
if ((iRet=sqlite3_prepare(db, szInsertSql, -1, &stmt, NULL)) == SQLITE_OK)
{
char szBlob[] = {0x01, 0x03, 0x12, 0x01, 0x0a, 0x12, 0x00, 0x14, 0x12, 0x00, 0x1e };
sqlite3_bind_blob(stmt, 1, szBlob, sizeof(szBlob), SQLITE_TRANSIENT);
if ((iRet=sqlite3_step(stmt)) != SQLITE_DONE)
{
sqlite3_finalize(stmt);
return;
}
}
sqlite3_finalize(stmt);
//4. sqlite3_prepare()查询记录(可以读取到完整记录)
char *szQuerySql1 = "SELECT x20000200 FROM TbReal WHERE MtrAddr==\"000337448041\"";
if (sqlite3_prepare(db, szQuerySql1, -1, &stmt, 0) == SQLITE_OK)
{
while (sqlite3_step(stmt) == SQLITE_ROW)
{
int iLen;
BYTE bRdBuf[128]={0};
if ((iLen=sqlite3_column_bytes(stmt, 0)) > 0)
memcpy(bRdBuf, sqlite3_column_blob(stmt, 0), iLen);
for (int i=0; i<iLen; i++)
printf("%02x ", bRdBuf[i]);
printf("\n");
}
}
sqlite3_finalize(stmt);
//5. sqlite3_get_table()查询记录(不能读取到完整记录,遇到字节0x00时就没有记录了)
char *szQuerySql2 = "SELECT x20000200 FROM TbReal WHERE MtrAddr==\"000337448041\"";
sqlite3_get_table(db, szQuerySql2, &results, &nrows, &ncols, &errmsg);
for (int iRowIdx=1; iRowIdx<nrows+1; iRowIdx++)
{
for (int iColIdx=0; iColIdx<ncols; iColIdx++)
{
TRACE("azResult[%d][%d] = %s\n", iRowIdx, iColIdx, results[iRowIdx*ncols + iColIdx]);
int iLen = strlen(results[iRowIdx*ncols + iColIdx]);
char *p= results[iRowIdx*ncols+iColIdx];
for (int i=0; i<iLen; i++)
printf("%02x ", p[i]);
printf("\n");
}
}
sqlite3_free_table(results);
}
}