Data not inserted into the sqlite table c++

Question

I am trying to save the contents of the key and value of a map into a database table. The .dbo file is created, but nothing goes into the table. It doesn't create table but it doesn't exit. I wonder what is wrong with my code.

void names_table( std::map<std::string, unsigned int> &names ){
std::string sql; 
std::string str1;
std::string str2;
std::string str3;

sqlite3_stmt *stmt;
const char *file_names = create_db_file( ); /* default to temp db */
sqlite3 *db;
sqlite3_initialize( );

int rc = sqlite3_open_v2( file_names, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
if ( rc != SQLITE_OK) {
    sqlite3_close( db );
    cout << "Error: Database cannot open!" << endl;
    exit( EXIT_FAILURE);
}
sql = "CREATE TABLE IF NOT EXISTS names_table (offset INTEGER PRIMARY KEY, stname TEXT);";
sqlite3_prepare_v2(db, sql.c_str(), sql.size(), &stmt, NULL);
if (sqlite3_step(stmt) != SQLITE_DONE) cout << "Didn't Create Table!" << endl;

for (auto pm = names.begin(); pm != names.end(); pm++) {
    str2 = "'" + pm->first + "'";
    char tmp[15];
    sprintf(tmp,"%u",pm->second);
    str3 = tmp;
    str1 = (((("INSERT INTO  names_table VALUES(" + str3) + ", ") + str2) + ");");
    std::cout << str1 << std::endl;
    sql = (char *)str1.c_str();
    // stmt = NULL;
    rc = sqlite3_prepare_v2(db, sql.c_str(), sql.size(), &stmt, NULL);
    if ( rc != SQLITE_OK) {
        sqlite3_close(db);
        cout << "Error: Data cannot be inserted!" << endl;
        exit ( EXIT_FAILURE);
    }
}
sqlite3_close( db );

}

Most likely, you have some errors in your SQL syntax. Try to `std::cout << str1 << std::endl` and paste it here. Also, what is the return value for the second call to `sqlite3_prepare_v2`? — Mihai Todor, Apr 04 '13 at 16:45
I cout one of the statements for str1: INSERT INTO names_table VALUES(ramsar, 8329); the return value for rc is 0 — Persianux, Apr 04 '13 at 16:49
@Persianux I wasn't asking for the value of `rc`. Try changing your code inside the `for` loop to catch the return value of `sqlite3_prepare_v2` and see if it's different than `SQLITE_OK` (which translates to 0). — Mihai Todor, Apr 04 '13 at 16:58
As a side note, your script will probably die if the `names_table` table already exists in the database file. — Mihai Todor, Apr 04 '13 at 17:41
I use Openoffice to see the contents of database file. The file is created, but I can't find any table in it! I guess in INSERT INTO it can't find the table — Persianux, Apr 04 '13 at 17:45
@Persianux You're code is just too broken, so I won't bother patching it up line by line. I have added two links in my answer which explain how to create a table and insert data into it properly. — Mihai Todor, Apr 04 '13 at 18:06
Duplicate: http://stackoverflow.com/questions/10850375/c-create-database-using-sqlite-for-insert-update — Mihai Todor, Apr 04 '13 at 18:06

score 3 · Accepted Answer · edited May 23 '17 at 10:30

3

INSERT INTO names_table VALUES(ramsar, 8329) - I hope you're aware that string literals in SQL need to be enclosed in quotes. Try this: INSERT INTO names_table VALUES('ramsar', 8329).

EDIT: Actually, your code will never do what you want, because you're not even calling sqlite3_step after sqlite3_prepare_v2, which means that you're only compiling your SQL statement, but never evaluating it. Where did you find this bad example? See here and here decent examples on how to use the SQLite C++ interface properly.

PS: Stop messing around with sprintf in C++. You have std::stringstream for it.

edited May 23 '17 at 10:30

Community

1
1

answered Apr 04 '13 at 16:53

Mihai Todor

8,014
9
49
86

1

Thank you @Mihai, I was careless. I tried this too, but nothing changed with this correction. – Persianux Apr 04 '13 at 16:58
1

@Persianux Then see my comment above. – Mihai Todor Apr 04 '13 at 17:00
1

Thank you for your comments. I read the tutorials and write what I understand from them. Your comments helped me so much to know how to work with sqlite. I added sqlite3_step, it can't create table. I try figure out how to fix it. The file is created, though. – Persianux Apr 04 '13 at 19:48
I updated the code. it tuns without error. Created 2kb .odb file. At least the file is not empty, but in Openoffice I can't see any table. The file show this line: SQLite format 3######@ #########################################################################-� – Persianux Apr 04 '13 at 20:11
Although I can't find any table using OpenOffice, it created 13kb .odb file :) It stored data I wanted. Thank you, I greatly appreciate your help. – Persianux Apr 04 '13 at 20:31
Why don't you use something smarter to query that database? You can either do it with the native sqlite application via command line or with the Firefox plugin [SQLite Manager](https://addons.mozilla.org/en-us/firefox/addon/sqlite-manager/). Perhaps Open Office is messing with you. – Mihai Todor Apr 04 '13 at 21:24
Also, in your updated code, you're still not calling `sqlite3_step` after the last `sqlite3_prepare_v2`. That code will just create one empty database table, without inserting any data into it... – Mihai Todor Apr 04 '13 at 21:35

Data not inserted into the sqlite table c++

1 Answers1