Sqlite problem inserting

cocos2d-x C++
#1

I followed:

My implementation

sql = “INSERT INTO BodyINV (name, filename, tier, category, mindmg, maxdmg, aspd, projectileSpd, critChance, critDmgMinMultiplier, critDmgMaxMultiplier, penetration, magPositionX, magPositionY)”;
sql = sql + " VALUES(’"+final_name+"’, ‘"+filename+"’, “+std::to_string(tier)+”, “+std::to_string(category)+”";
sql = sql + “,” + std::to_string(final_MinDmg) + “,” + std::to_string(final_MaxDmg) + “,” + std::to_string(final_BodyAtkSpd);
sql = sql + “,” + std::to_string(final_BodyProjSpd) + “,” + std::to_string(final_subMaterial_TotalCritChance) + “,” + std::to_string(final_subMaterial_TotalMinCritMultiplier) + “”;
sql = sql + “,” + std::to_string(final_subMaterial_TotalMaxCritMultiplier) + “,” + std::to_string(final_subMaterial_TotalPenetration) + “,” + std::to_string(PROJ_body_magPositionX.at(partInView)) + “”;
sql = sql + “,” + std::to_string(PROJ_body_magPositionY.at(partInView)) + “”;
sql = sql + “)”;
log("%s", sql.c_str());
result = sqlite3_exec(pdb, sql.c_str(), NULL, NULL, NULL);
if (result != SQLITE_OK)
log(“insert data failed! %d”, result);
else
log(“Craft Success!”);

log result:
INSERT INTO BodyINV (name, filename, tier, category, mindmg, maxdmg, aspd, projectileSpd, critChance, critDmgMinMultiplier, critDmgMaxMultiplier, penetration, magPositionX, magPositionY) VALUES(‘Boxy’, ‘boxy.png’, 1, 1,5,25,5.000000,100.000000,20.000000,0.020000,0.040000,4.000000,32.000000,46.500000)
insert data failed! 21

My Db:
CREATE TABLE “BodyINV” (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
filename TEXT,
tier INTEGER,
category INTEGER,
mindmg INTEGER,
maxdmg INTEGER,
aspd NUMERIC,
projectileSpd NUMERIC,
critChance NUMERIC,
critDmgMinMultiplier NUMERIC,
critDmgMaxMultiplier NUMERIC,
penetration NUMERIC,
magPositionX NUMERIC,
magPositionY NUMERIC
)

Problem:
insert data failed! 21

#2

So can you execute this SQL in a SQL tool?

#3

You should use prepared statements and bind column values to that statement.

Don’t create a single string with both the insert statement and the values, unless you want to open yourself up to a SQL Injection Attack.

Also binding means you don’t have to escape special characters, which might be your issue in this case.

#4

I looked at sqlite.h and you should be able to call sqlite3_errmsg to get a more useful error message that you can work with. Looks like 21 is that something isn’t formatted correctly, someplace in your sql string.

#5

actually, from sqlite.h

it defines 21 as SQLITE_MISUSE which indicates that the “Library used incorrectly”

More details here:
http://www.sqlite.org/cvstrac/wiki?p=LibraryRoutineCalledOutOfSequence

2 Likes
#6

ouch, Thank you :slight_smile:

#7

yup! Query executed successfully: INSERT INTO BodyINV (name, filename, tier, category, mindmg, maxdmg, aspd, projectileSpd, critChance, critDmgMinMultiplier, critDmgMaxMultiplier, penetration, magPositionX, magPositionY) VALUES(‘Boxy’, ‘boxy.png’, 1, 1,5,25,5.000000,100.000000,20.000000,0.020000,0.040000,4.000000,32.000000,46.500000) (took 19ms)

#8

I was about to try this one, but I’m still figuring out how to use bind. Maybe an example would help :smile:

#9

Thanks!

Here are some possible causes of SQLITE_MISUSE:
1.Calling any API routine with an sqlite3* pointer that was not obtained from sqlite3_open() or sqlite3_open16() or which has already been closed by sqlite3_close().

it’s because of #1

I know it’s kinda dumb, just a simple mistake I have sqlite3_close(pdb) somewhere in my source :frowning: