c++ : Create database using SQLite for Insert & update

Question

I am trying to create a database in c++ using sqlite3 lib.. I am getting error sqlite3_prepare_v2'
was not declared in this scope as shown in logcat.

log file

..\src\Test.cpp: In function 'int main(int, const char**)':
..\src\Test.cpp:21:85: error: 'sqlite3_prepare_v2' was not declared in this scope
..\src\Test.cpp:30:13: error: variable 'sqlite3 in' has initializer but incomplete type
..\src\Test.cpp:30:30: error: invalid use of incomplete type 'sqlite3 {aka struct sqlite3}'
..\src\/sqlite3.h:73:16: error: forward declaration of 'sqlite3 {aka struct sqlite3}'

Here is my code

#include <iostream>
using namespace std;
 #include "sqlite3.h"

 int main (int argc, const char * argv[]) {

sqlite3 *db;
sqlite3_open("test.db", & db);

   string createQuery = "CREATE TABLE IF NOT EXISTS items (busid INTEGER PRIMARY KEY, ipaddr TEXT,    time TEXT NOT NULL DEFAULT (NOW()));";
   sqlite3_stmt *createStmt;
  cout << "Creating Table Statement" << endl;
  sqlite3_prepare_v2(db, createQuery.c_str(), createQuery.size(), &createStmt, NULL);
   cout << "Stepping Table Statement" << endl;
   if (sqlite3_step(createStmt) != SQLITE_DONE) cout << "Didn't Create Table!" << endl;

   string insertQuery = "INSERT INTO items (time, ipaddr) VALUES ('test', '192.168.1.1');"; // WORKS!


   sqlite3_stmt *insertStmt;
   cout << "Creating Insert Statement" << endl;
   sqlite3_prepare(db, insertQuery.c_str(), insertQuery.size(), &insertStmt, NULL);
   cout << "Stepping Insert Statement" << endl;
   if (sqlite3_step(insertStmt) != SQLITE_DONE) cout << "Didn't Insert Item!" << endl;

cout << "Success!" << endl;

 return 0;
}

please help me out. thanks.....

score 19 · Accepted Answer · answered Jun 01 '12 at 19:22

 #include <sqlite3.h>

should contain sqlite3_prepare_v2 and struct sqlite3. Make sure you're including the right sqlite3.h file.

Also in sqlite3_prepare_v2 the 3rd arg can be (and should be in your case) -1 so the sql is read to the first null terminator.

Working bare-metal sample using sqlite 3.7.11:

#include <sqlite3.h>
int test()
{
    sqlite3* pDb = NULL;
    sqlite3_stmt* query = NULL;
    int ret = 0;
    do // avoid nested if's
    {
        // initialize engine
        if (SQLITE_OK != (ret = sqlite3_initialize()))
        {
            printf("Failed to initialize library: %d\n", ret);
            break;
        }
        // open connection to a DB
        if (SQLITE_OK != (ret = sqlite3_open_v2("test.db", &pDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)))
        {
            printf("Failed to open conn: %d\n", ret);
            break;
        }
        // prepare the statement
        if (SQLITE_OK != (ret = sqlite3_prepare_v2(pDb, "SELECT 2012", -1, &query, NULL)))
        {
            printf("Failed to prepare insert: %d, %s\n", ret, sqlite3_errmsg(pDb));
            break;
        }
        // step to 1st row of data
        if (SQLITE_ROW != (ret = sqlite3_step(query))) // see documentation, this can return more values as success
        {
            printf("Failed to step: %d, %s\n", ret, sqlite3_errmsg(pDb));
            break;
        }
        // ... and print the value of column 0 (expect 2012 here)
        printf("Value from sqlite: %s", sqlite3_column_text(query, 0));     

    } while (false);
    // cleanup
    if (NULL != query) sqlite3_finalize(query);
    if (NULL != pDb) sqlite3_close(pDb);
    sqlite3_shutdown();
    return ret;
}

Hope this helps

Vijay, sqlite is unlike a DB server. It actually is an embedded DB engine which uses a file to store your DB. One DB per file so essentially opening a DB means "use existing or create new DB". — Cristian Niculescu, Aug 01 '14 at 16:36

score 6 · Answer 2 · answered Jul 12 '12 at 12:33

Guys , creating database using sqlite3 in c/c++, here I'm using follwing steps...

1) Firstly you include MinGw file .
2) Add header file sqlite3.h, sqlite3.c in your src folder.
3) Add libr folder , in libr here include these file

    mysqlite.h, shell.c, sqlite3.c, sqlite3.h, sqlite3ext.h

After then start your coding...

     #include <iostream>
 using namespace std;
 #include "sqlite3.h"

 int main (int argc, const char * argv[]) {

    sqlite3 *db;
    sqlite3_open("test1.db", & db);

  string createQuery = "CREATE TABLE IF NOT EXISTS items (userid INTEGER PRIMARY KEY, ipaddr
      TEXT,username TEXT,useradd TEXT,userphone INTEGER,age INTEGER, "
                                                    "time TEXT NOT NULL DEFAULT 
                                                                             (NOW()));";
    sqlite3_stmt *createStmt;
    cout << "Creating Table Statement" << endl;
    sqlite3_prepare(db, createQuery.c_str(), createQuery.size(), &createStmt, NULL);
    cout << "Stepping Table Statement" << endl;
    if (sqlite3_step(createStmt) != SQLITE_DONE) cout << "Didn't Create Table!" << endl;

    string insertQuery = "INSERT INTO items (time, ipaddr,username,useradd,userphone,age) 
        VALUES ('7:30', '192.187.27.55','vivekanand','kolkatta','04456823948',74);"; // WORKS!
    sqlite3_stmt *insertStmt;
    cout << "Creating Insert Statement" << endl;
    sqlite3_prepare(db, insertQuery.c_str(), insertQuery.size(), &insertStmt, NULL);
    cout << "Stepping Insert Statement" << endl;
    if (sqlite3_step(insertStmt) != SQLITE_DONE) cout << "Didn't Insert Item!" << endl;  



         return 0;
     }

Instead of keeping the insert query's values part as static text, it might be nice if you update the example as, after the statement is prepared, how to change the values part dynamically.. — phoad, Jul 14 '14 at 07:35
phoad, you do this by using "?" or ?1 ?2 etc inside the query and then binding parameters to a statement.See http://www.sqlite.org/c3ref/bind_blob.html — Cristian Niculescu, Aug 01 '14 at 16:38

score 1 · Answer 3 · answered Jun 01 '12 at 12:39

go through this link. I am not sure. It might help you out.

I think their is no sqlite3_prepare_v2 in sqlite3.h lib, so try this.. sqlite3_prepare_v2 can be replaced by sqlite3_prepare, but more care is needed, because it changes the semantics of subsequent calls slightly.

c++ : Create database using SQLite for Insert & update

3 Answers3

Linked