Can not prepare INSERT statement in SQLITE, C API
/* DATABASE INIT */
ret = sqlite3_open_v2(dbfile, &DB, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
if (SQLITE_OK != ret) {
printf("Could not open database.\n");
exit(1);
}
const char* zSql = "INSERT INTO abc VALUES (?)";
ret = sqlite3_prepare_v2(sites[DB, zSql, strlen(zSql), &s, NULL);
if (SQLITE_OK != ret) {
printf("Could not compile statement '%s', got %d.\n", zSql, ret);
exit(1);
}
Well, there you go. What's wrong? sqlite3_prepare_v2 always fails.
Update: I tried the suggestions so far, but no cigar. I created the simpl开发者_StackOverflow社区est case I could think of, with a schema:
CREATE TABLE abc(a INTEGER);
And the C code as above, still does not work, gets 'Could not compile statement'
got return code 26 which apparently means SQLITE_NOTADB
. So it seems my database file is not... a database file. Strange, I will have to look into this.
The sqlite CLI accepts the file and can show the schema if I use the ".dump" command.
I changed the database file name from "data.db" to "data.sqlite". Now I get a return code of 1, SQLITE_ERROR, instead.
Update: I was accessing the wrong file, AKA user error. Accepting one of the answers that pointed out a syntax error in my unedited question.
I think you should try
const char* zSql =
"INSERT INTO abc (moderation_status, phonenumber, email) VALUES(?,?,?)";
assuming moderation_status
phonenumber
and email
are the names of the fields in your table.
or:
const char* zSql = "INSERT INTO abc VALUES(?,?,?)";
The ?
is the placeholder for where the arguments will be inserted.
You haven't supplied a list of values; you've supplied a list of names (presumably column names) where a list of values is needed.
You might need:
char* zSql = "INSERT INTO abc(moderation_status, phonenumber, email) "
"VALUES('U', '212-234-6678', 'example@example.com')";
Or you might not bother with the column names:
char* zSql = "INSERT INTO abc VALUES('U', '212-234-6678',"
"'example@example.com')";
Or you might use placeholders:
char* zSql = "INSERT INTO abc VALUES(?, ?, ?)";
If you use placeholders, you'll have to provide the corresponding values when you execute the statement. (But placeholders are generally the best solution - they avoid SQL Injection problems.)
精彩评论