每當我想在資料庫中插入一個型別物件（定義如下）時，我都會收到“錯誤 25：列索引超出范圍”。這意味著我嘗試系結的值不適合查詢嗎？
CREATE TABLE color (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
label TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS object (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
label TEXT NOT NULL,
color_id INTEGER,
position_x REAL,
position_y REAL,
position_z REAL,
distance REAL,
FOREIGN KEY(color_id) REFERENCES color(id)
);
-- few insert to populate color
INSERT INTO color(id,label) VALUES (1,'Black');
INSERT INTO color(id,label) VALUES (2,'White');
INSERT INTO color(id,label) VALUES (3,'Red');
INSERT INTO color(id,label) VALUES (4,'Lime');
INSERT INTO color(id,label) VALUES (5,'Blue');
INSERT INTO color(id,label) VALUES (6,'Yellow');
我嘗試使用 CLI 界面進行查詢，這很有效。
insert into object (label,color_id,position_x,position_y,position_z,distance) values ("can",2,0.2,0.2,0.2,1.2);
我檢查了欄位的數量，變數的名稱，我的資料庫在創建類時打開并在銷毀時關閉。最后一個 colorIndex 是我從一個函式中檢索到的一個 int，我已經用它來在不同的表中插入其他元素。我還列印了這些值，它們是正確的。
我想我在這里遺漏了一些東西。我看不出還有什么地方是我的錯誤。
typedef struct Object{
std::string label;
std::string color;
float pos_x;
float pos_y;
float pos_z;
float distance;
} Object;
int VisionModel::getColorByLabel(std::string sColor){
int colorId;
query = "SELECT id FROM color WHERE label = (?)";
pStmt = nullptr;
int rc;
rc = sqlite3_prepare_v2(db,query.c_str(), -1, &pStmt, NULL);
if (rc != SQLITE_OK){
std::cout << "prepare getColorByLabel didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return colorId;
}
if (sqlite3_bind_text(pStmt, 1, sColor.c_str(), -1, NULL) != SQLITE_OK){
std::cout << "bind color label didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return colorId;
}
while ( (rc = sqlite3_step(pStmt)) == SQLITE_ROW) {
colorId = sqlite3_column_int(pStmt, 0);
}
sqlite3_finalize(pStmt);
return colorId;
}
void VisionModel::createObject(Object object){
query = "INSERT INTO object (label,color_id,position_x,position_y,position_z,distance) VALUES (?,?,?,?,?,?)";
pStmt = nullptr;
int rc;
// get the index for given color
int colorIndex = -1;
if (!object.color.empty() && object.color != "0"){
colorIndex = getColorByLabel(object.color);
}
rc = sqlite3_prepare_v2(db,query.c_str(), -1, &pStmt, NULL);
if (rc != SQLITE_OK){
std::cout << "prepare createObject didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return ;
}
if (sqlite3_bind_text(pStmt, 1, object.label.c_str(), -1, NULL) != SQLITE_OK){
std::cout << "bind object label didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return ;
}
if (colorIndex != -1){
rc = sqlite3_bind_int(pStmt, 2, colorIndex);
if ( rc != SQLITE_OK){
std::cout << "bind object color index didn t went through" << std::endl;
std::cout << std::to_string(colorIndex) << std::endl;
manageSQLiteErrors(pStmt);
return ;
}
}
if (sqlite3_bind_double(pStmt, 3, object.pos_x) != SQLITE_OK){
std::cout << "bind object pos x didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return ;
}
if (sqlite3_bind_double(pStmt, 4, object.pos_y) != SQLITE_OK){
std::cout << "bind person object y didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return ;
}
if (sqlite3_bind_double(pStmt, 5, object.pos_z) != SQLITE_OK){
std::cout << "bind person object z didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return ;
}
if (sqlite3_bind_double(pStmt, 6, object.distance) != SQLITE_OK){
std::cout << "bind person object distance didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return ;
}
if ((rc = sqlite3_step(pStmt)) != SQLITE_DONE) { /* 2 */
std::cout << "step didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return ;
}
sqlite3_finalize(pStmt);
}
這是您可以復制粘貼和編譯的代碼的最小可復制版本。不要忘記首先使用上面的查詢創建資料庫。
編譯：g -o main main.cpp -lsqlite3
代碼 ：
#include <string>
#include <iostream>
#include <sqlite3.h>
typedef struct Object
{
std::string label;
std::string color;
float pos_x;
float pos_y;
float pos_z;
float distance;
} Object;
class VisionModel
{
private:
sqlite3 *db;
const char *zErrMsg = 0;
std::string query;
sqlite3_stmt *pStmt;
public:
VisionModel()
{
connect();
}
~VisionModel()
{
close();
}
void connect()
{
int rc;
std::string db_file_path("./database.db");
rc = sqlite3_open(db_file_path.c_str(), &db);
if (rc)
{
std::cerr << "Can't open database" << sqlite3_errmsg(db) << std::endl;
std::cerr << "SQL code error : " << sqlite3_extended_errcode(db) << std::endl;
return;
}
};
void manageSQLiteErrors(sqlite3_stmt *pStmt)
{
std::cerr << "SQL error : " << sqlite3_errmsg(db) << std::endl;
std::cerr << "SQL code error : " << sqlite3_extended_errcode(db) << std::endl;
sqlite3_finalize(pStmt);
}
void close()
{
sqlite3_close(db);
};
int getColorByLabel(std::string sColor)
{
int colorId;
query = "SELECT id FROM color WHERE label = (?)";
pStmt = nullptr;
int rc;
rc = sqlite3_prepare_v2(db, query.c_str(), -1, &pStmt, NULL);
if (rc != SQLITE_OK)
{
std::cout << "prepare getColorByLabel didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return colorId;
}
if (sqlite3_bind_text(pStmt, 1, sColor.c_str(), -1, NULL) != SQLITE_OK)
{
std::cout << "bind color label didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return colorId;
}
while ((rc = sqlite3_step(pStmt)) == SQLITE_ROW)
{
colorId = sqlite3_column_int(pStmt, 0);
}
sqlite3_finalize(pStmt);
return colorId;
}
void createObject(Object object)
{
query = "INSERT INTO object (label,color_id,position_x,position_y,position_z,distance) VALUES (?,?,?,?,?,?)";
pStmt = nullptr;
int rc;
// get the index for given color
int colorIndex = -1;
if (!object.color.empty() && object.color != "0")
{
colorIndex = getColorByLabel(object.color);
}
rc = sqlite3_prepare_v2(db, query.c_str(), -1, &pStmt, NULL);
if (rc != SQLITE_OK)
{
std::cout << "prepare createObject didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return;
}
if (sqlite3_bind_text(pStmt, 1, object.label.c_str(), -1, NULL) != SQLITE_OK)
{
std::cout << "bind object label didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return;
}
if (colorIndex != -1)
{
rc = sqlite3_bind_int(pStmt, 2, colorIndex);
if (rc != SQLITE_OK)
{
std::cout << "bind object color index didn t went through" << std::endl;
std::cout << std::to_string(colorIndex) << std::endl;
manageSQLiteErrors(pStmt);
return;
}
}
if (sqlite3_bind_double(pStmt, 3, object.pos_x) != SQLITE_OK)
{
std::cout << "bind object pos x didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return;
}
if (sqlite3_bind_double(pStmt, 4, object.pos_y) != SQLITE_OK)
{
std::cout << "bind person object y didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return;
}
if (sqlite3_bind_double(pStmt, 5, object.pos_z) != SQLITE_OK)
{
std::cout << "bind person object z didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return;
}
if (sqlite3_bind_double(pStmt, 6, object.distance) != SQLITE_OK)
{
std::cout << "bind person object distance didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return;
}
if ((rc = sqlite3_step(pStmt)) != SQLITE_DONE)
{ /* 2 */
std::cout << "step didn t went through" << std::endl;
manageSQLiteErrors(pStmt);
return;
}
sqlite3_finalize(pStmt);
}
};
int main()
{
Object obj = {"apple", "Blue", 0.1, 0.2, 0.1, 2.0};
VisionModel vm;
vm.createObject(obj);
}
uj5u.com熱心網友回復：
如果你用你的除錯器來顯示query這里的值，問題就變得很容易看出：
rc = sqlite3_prepare_v2(db, query.c_str(), -1, &pStmt, NULL);
你會發現這query并不是INSERT你認為的那樣。
這是因為前面幾行：
colorIndex = getColorByLabel(object.color);
這最終破壞了query變數，將其設定為只有一個?占位符的“SELECT”。
您可以將此作為學習如何使用除錯器的絕佳機會，這使得解決這些 Scooby-Doo 謎團變得簡單。嘗試在除錯器中使用顯示的程式，完全按照原樣sqlite3_prepare_v2在 中的行設定斷點createObject，然后檢查 中的內容query，并擁有自己的“Eureka！” 片刻。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/498264.html
下一篇：如何避免重復插入sqlite