SQLite3 C++ example

This is an draft post…

complier string:

mk.sh

apt-get install sqlite3 libsqlite3-dev
g++ main.cpp -o dbtest -lsqlite3

code

main.cpp

#include <iostream>
#include <string>
#include <sqlite3.h>
 
//using namespace std;
 
#define cout std::cout
#define cerr std::cerr
#define endl std::endl
#define string std::string
 
int callback(void*, int argc, char** argv, char** col_names)
{
    for (int i = 0; i < argc; ++i)
        cout << col_names[i] << " = " << (argv[i] ? argv[i] : "NULL") << '\n';
    cout << endl;
    return 0;
}
 
 
 
bool insert_bind(sqlite3* db, string firstname, string lastname, int age, string address, double salary) {
    const char* sql = "INSERT INTO person (firstname,lastname,age,address,salary) VALUES (?, ?, ?, ?, ?);";    
 
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK) {
        cerr << "Prepare failure: " << sqlite3_errmsg(db) << endl;
        return false;
    }
 
    if (sqlite3_bind_text(stmt, 1, firstname.c_str(), -1, SQLITE_TRANSIENT) != SQLITE_OK) {
        sqlite3_finalize(stmt);
        return false;
    }
 
    if (sqlite3_step(stmt) != SQLITE_DONE) {    
        cerr << "Step failure: " << sqlite3_errmsg(db) << endl;
        sqlite3_finalize(stmt);
        return false;
    }
 
    sqlite3_finalize(stmt);
    return true;
}
 
 
 
int main()
{
    sqlite3* db = NULL;
    char* err;
    int ret;
 
    cout << "Hello World" << endl;    
 
    if (sqlite3_open("test.db", &db)) {
        cerr << "Error create/open database: " << sqlite3_errmsg(db) << endl;
        return 0;
    }
 
    string sql = ""
        "CREATE TABLE IF NOT EXISTS person ("
        " id        INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
        " firstname TEXT NOT NULL,"
        " lastname  TEXT NOT NULL,"
        " age       INT,"
        " address   TEXT,"
        " salary    REAL);"
        "INSERT INTO person (firstname,lastname,age,address,salary) VALUES ('Eugene', 'X', 18, 'Homie st. 1', 45000.00);"
        "INSERT INTO person (firstname,lastname,age,address,salary) VALUES ('Jhon', 'Doe', 19, 'Homie st. 18', 42000.00);";
 
    ret = sqlite3_exec(db, sql.c_str(), 0, 0, &err);
    if (ret != SQLITE_OK) {
        cerr << "Error create table " << err << endl;
        sqlite3_free(err);
        sqlite3_close(db);
        return 0;
    }
 
/*    int callback = [](void* data, int argc, char** argv, char** colname) {
        if (data != NULL) {
            cerr << "Error in callback: " << ((const char*) data) << endl;
        }
        for (int i=0; i<argc; i++) {
            cout << colname[i] << "=" << argv[i] << endl;
        }
        cout << endl;
        return 0;
    };*/
 
    string sele = "SELECT * FROM person";
    ret = sqlite3_exec(db, sele.c_str(), callback, 0, 0);
    if (ret != SQLITE_OK) {
        cerr << "Error create table " << err << endl;
        sqlite3_free(err);
    }
 
/*    sqlite3_stmt* stmt;
    sqlite3_prepare(db, sele.c_str(), sele.size(), &companydetails, NULL);
    sqlite3_step(companydetails); */
 
 
    sqlite3_close(db);
 
    cout << "OK" << endl;
 
    return 0;
}

И ещё чуток идей…

test2.cpp

#include <iostream>
#include <math.h>
#include <memory.h>
#include <sqlite3.h>
#include "classes/md5.h"
 
using namespace std;
 
 
char* dbname;
sqlite3* db;
 
 
 
class sl_stmt {
private:
    bool ready = false;
    sqlite3_stmt* stmt;
public:
    sl_stmt(sqlite3* db, const std::string sql) {
        int err = sqlite3_prepare_v2(db, sql.c_str(), (int) sql.length(), &this->stmt, NULL);
        if (err != SQLITE_OK) {
            this->print_error();
        } else {
            cout << "stmt:" << sql << " [ok]" << endl;
            this->ready = true;
        }
    };
    ~sl_stmt() {
        int err = sqlite3_finalize(this->stmt);
        if (err != SQLITE_OK) {
            this->print_error();
        }
    }
    bool bind(int const index, int const value) {
        int err = sqlite3_bind_int(this->stmt, index, value);
        if (err != SQLITE_OK) {
            this->print_error();
        }
        return (err == SQLITE_OK);
    };
    bool bind(int const index, const std::string text) {
        int err = sqlite3_bind_text(this->stmt, index, text.c_str(), (int) text.length(), SQLITE_STATIC);
        if (err != SQLITE_OK) {
            this->print_error();
        }
        return (err == SQLITE_OK);
    };
    bool push() {
        int err = sqlite3_step(this->stmt);
        if (err == SQLITE_ROW) return true;
        if (err == SQLITE_DONE) return false;
        this->print_error();
        return false;
    };
    bool reset() {
        int err = sqlite3_reset(this->stmt);
        if (err != SQLITE_OK) {
            this->print_error();
        }
        return (err == SQLITE_OK);
    };
private:
    void print_error() {
        char* err = (char*) sqlite3_errmsg(db);
        cerr << "Statement error: " << err << endl;
        sqlite3_free(err);
    }
};
 
 
bool sl_exec(const std::string sql) {
    char* errorMessage;
    int err = sqlite3_exec(db, sql.c_str(), nullptr, 0, &errorMessage);
    if (err != SQLITE_OK) {
        cerr << "Query error: " << errorMessage << endl;
        sqlite3_free(errorMessage);
    } else {
        cout << "sql: " << sql << " [ok]" << endl;
    }
    return (err == SQLITE_OK);
}
 
std::string make_password_z(const std::string in) {
    MD5* hasher = new MD5(in);
    std::string hex = hasher->hexdigest();
    delete hasher;
    return hex;
}
 
/*
 * typedef int (*sqlite3_callback)(
 *    void*,    // Data provided in the 4th argument of sqlite3_exec()
 *    int,      // The number of columns in row
 *    char**,   // An array of strings representing fields in the row
 *    char**    // An array of strings representing column names
);
*/
static int callback(void* data, int argc, char** argv, char** azColName)
{
    int i;
    fprintf(stderr, "%s: ", (const char*)data);
 
    for (i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
 
    printf("\n");
    return 0;
}
bool sl_select(const std::string sql) {
    char* errorMessage;
    int err = sqlite3_exec(db, sql.c_str(), callback, nullptr, &errorMessage);
    if (err != SQLITE_OK) {
        cerr << "Query error: " << errorMessage << endl;
        sqlite3_free(errorMessage);
    } else {
        cout << "sql: " << sql << " [ok]" << endl;
    }
    return (err == SQLITE_OK);
}
 
void createTables() {
 
    sl_exec("DROP TABLE IF EXISTS users");
    string sql = "CREATE TABLE users ("
                 "user_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
                 "username TEXT UNIQUE NOT NULL, "
                 "password CHAR(40), "
                 "firstname TEXT, "
                 "lastname TEXT, "
                 "age INT, "
                 "address TEXT,"
                 "salary REAL"
                 ")";
    if (sl_exec(sql)) {
        sl_exec("INSERT INTO users (username, password) VALUES ('eugene', '" + make_password_z("password") + "')");
        sl_exec("INSERT INTO users (username, password) VALUES ('magic', '" + make_password_z("password") + "')");
    }
 
}
 
int main(int, char** argv) {
    dbname = (char*) malloc(strlen(argv[0]) + 10);
    strcpy(dbname, argv[0]);
    strcat(dbname, ".s3db");
 
    int err;
    err = sqlite3_open(dbname, &db);
    free(dbname);
 
    if (err) {
        cerr << "error opening db: " << sqlite3_errmsg(db) << endl;
        return -1;
    }
    else {
        cout << "sqlite3 connected database " << endl;
    }
 
    createTables();
 
    sl_select("SELECT * FROM users");
 
    sl_stmt* stmt = new sl_stmt(db, "INSERT INTO users (username, password) VALUES ( ? , ? )");
 
    stmt->bind(1, "EugeneZ");
    stmt->bind(2, md5("secret"));
    stmt->push();
    stmt->reset();
 
    stmt->bind(1, "EugeneT");
    stmt->bind(2, md5("sex"));
    stmt->push();
 
    delete stmt;
 
    sl_select("SELECT * FROM users");
 
    sqlite3_close(db);
    return 0;
}