CODEWITHSUNDEEP
c++sqlite
user1595932
user1595932

Reputation: 135

SQLite Insert Statement with C++

Below is the code I have tried on a test2.cpp

I checked my database and found out no new record had been added. What actually went wrong in my statement?

#include <iostream>
#include <sqlite3.h>

//g++ -o test2 test2.cpp -lsqlite3
using namespace std;

int main()
{
int counter = 0;

    sqlite3 *db;
    sqlite3_stmt * stmt;

string username = "panda";
string name = "Kungfu Panda";
string department = "normal";
string password = "hellopassword";

string sqlstatement = "INSERT INTO abe_account (" + username + "," + name + "," + department + "," + password + ");";

    if (sqlite3_open("abeserver.db", &db) == SQLITE_OK)
    {
    sqlite3_prepare( db, sqlstatement.c_str(), -1, &stmt, NULL );//preparing the statement
    sqlite3_step( stmt );//executing the statement
        }
    else
    {
        cout << "Failed to open db\n";
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);


    return 0;

}

I would like to ask if it's possible to know if the statement is executed with success too. Like one row added, some form of confirmation from sqlite3. And if there's an error, will it be able to cout out too?

Upvotes: 4

Views: 25410

Answers (3)

Sks Sapare
Sks Sapare

Reputation: 37

Yes you can know for why its failing.

include "sqlite_exception.h"

follow this way:

int m = sqlite3_step(stmt);
if(m == SQLITE_BUSY)
{

/*try doing sqlite3_step again, with a delay using sleep() **/ }

if(m == SQLITE_ERROR)
    std::cout(sqlite3_sql(stmt), sqlite3_errmsg(db));
if(m == SQLITE_MISUSE)
    std::cout(sqlite3_sql(stmt), sqlite3_errmsg(db));

have a look at these: http://www.sqlite.org/c3ref/c_abort.html

Upvotes: 0

paddy
paddy

Reputation: 63481

When you do an insert, you generally specify the fields in the order that you are going to supply the data. Otherwise you have to specify all data in the correct order (passing a value for all fields in the table in the order it was defined).

Your syntax is therefore incomplete... Either do this:

INSERT INTO tablename (field1, field2, field3) VALUES (value1, value2, value3);

Or this:

INSERT INTO tablename VALUES (value1, value2, value3)

Also, because you are not binding data to a prepared query, you need to quote your strings. It's not okay to just substitute in panda for the username. You need to supply 'panda'. Therefore, the strings go in like this:

"('" + username + "','" + name + "','" + department + "','" + password + "');"

Because it's easy to mess this up (and there are escape codes for special characters, eg the single-quote), you might prefer to make a function to quote the string which at the very least would do:

string quotesql( const string& s ) {
    return string("'") + s + string("'");
}

Then:

"(" + quotesql(username) + "," + quotesql(name) + ...

So, all up you might end up with this (assuming field names):

string sqlstatement =
    "INSERT INTO abe_account (username, name, department, password) VALUES ("
    + quotesql(username) + ","
    + quotesql(name) + ","
    + quotesql(department) + ","
    + quotesql(password) + ");";

Upvotes: 4

criticalfix
criticalfix

Reputation: 2870

Try adding some single quotes around your strings:

string sqlstatement = "INSERT INTO abe_account ('" + username + "','" + name + "','" + department + "','" + password + ");";

Not familiar with sqllite, but usually an INSERT looks like:

string sqlstatement = "INSERT INTO abe_account (ColumnName1, ColumnName2, ColumnName3, ColumnName4) VALUES ('" + username + "','" + name + "','" + department + "','" + password + "');";

Upvotes: 1

Related Questions