iwongu/sqlite3pp

SQLite3++ - C++ wrapper of SQLite3 API

ANNOUNCEMENTS

Use files in headeronly_src directory. The files in src are exactly same but in the form of h/cpp files, which you need to compile and link with.

boost_src is no longer maintained. Do not use unless you need to use pre-c++1x. It requires boost library.

sqlite3pp

This library makes SQLite3 API more friendly to C++ users. It supports almost all of SQLite3 features using C++ classes such as database, command, query, and transaction. The query class supports iterator concept for fetching records.

With ext::function class, it's also easy to use the sqlite3's functions and aggregations in C++.

Usage

database

sqlite3pp::database db("test.db");
db.execute("INSERT INTO contacts (name, phone) VALUES ('Mike', '555-1234')");

command

sqlite3pp::command cmd(
  db, "INSERT INTO contacts (name, phone) VALUES (?, ?)");
cmd.binder() << "Mike" << "555-1234";
cmd.execute();

sqlite3pp::command cmd(db, "INSERT INTO contacts (name, phone) VALUES (?, ?)");
cmd.bind(1, "Mike", sqlite3pp::nocopy);
cmd.bind(2, "555-1234", sqlite3pp::nocopy);
cmd.execute();

sqlite3pp::command cmd(
  db, "INSERT INTO contacts (name, phone) VALUES (?100, ?101)");
cmd.bind(100, "Mike", sqlite3pp::nocopy);
cmd.bind(101, "555-1234", sqlite3pp::nocopy);
cmd.execute();

sqlite3pp::command cmd(
  db, "INSERT INTO contacts (name, phone) VALUES (:user, :phone)");
cmd.bind(":user", "Mike", sqlite3pp::nocopy);
cmd.bind(":phone", "555-1234", sqlite3pp::nocopy);
cmd.execute();

sqlite3pp::command cmd(
  db,
  "INSERT INTO contacts (name, phone) VALUES (:user, '555-0000');"
  "INSERT INTO contacts (name, phone) VALUES (:user, '555-1111');"
  "INSERT INTO contacts (name, phone) VALUES (:user, '555-2222')");
cmd.bind(":user", "Mike", sqlite3pp::nocopy);
cmd.execute_all();

transaction

sqlite3pp::transaction xct(db);
{
  sqlite3pp::command cmd(
    db, "INSERT INTO contacts (name, phone) VALUES (:user, :phone)");
  cmd.bind(":user", "Mike", sqlite3pp::nocopy);
  cmd.bind(":phone", "555-1234", sqlite3pp::nocopy);
  cmd.execute();
}
xct.rollback();

query

sqlite3pp::query qry(db, "SELECT id, name, phone FROM contacts");

for (int i = 0; i < qry.column_count(); ++i) {
  cout << qry.column_name(i) << "\t";
}

for (sqlite3pp::query::iterator i = qry.begin(); i != qry.end(); ++i) {
  for (int j = 0; j < qry.column_count(); ++j) {
    cout << (*i).get<char const*>(j) << "\t";
  }
  cout << endl;
}

for (sqlite3pp::query::iterator i = qry.begin(); i != qry.end(); ++i) {
  int id;
  char const* name, *phone;
  std::tie(id, name, phone) =
    (*i).get_columns<int, char const*, char const*>(0, 1, 2);
  cout << id << "\t" << name << "\t" << phone << endl;
}

for (sqlite3pp::query::iterator i = qry.begin(); i != qry.end(); ++i) {
  string name, phone;
  (*i).getter() >> sqlite3pp::ignore >> name >> phone;
  cout << "\t" << name << "\t" << phone << endl;
}

for (auto v : qry) {
  string name, phone;
  v.getter() >> sqlite3pp::ignore >> name >> phone;
  cout << "\t" << name << "\t" << phone << endl;
}

attach

sqlite3pp::database db("foods.db");
db.attach("test.db", "test");

sqlite3pp::query qry(
  db,
  "SELECT epi.* FROM episodes epi, test.contacts con WHERE epi.id = con.id");

backup

sqlite3pp::database db("test.db");
sqlite3pp::database backupdb("backup.db");

db.backup(backupdb);

db.backup(
  backupdb,
  [](int pagecount, int remaining, int rc) {
    cout << pagecount << "/" << remaining << endl;
    if (rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED) {
      // sleep or do nothing.
    }
  });

callback

struct rollback_handler
{
  void operator()() {
    cout << "handle_rollback" << endl;
  }
};

sqlite3pp::database db("test.db");

db.set_commit_handler([]{ cout << "handle_commit\n"; return 0; });
db.set_rollback_handler(rollback_handler());

int handle_authorize(int evcode, char const* p1, char const* p2,
                     char const* dbname, char const* tvname) {
  cout << "handle_authorize(" << evcode << ")" << endl;
  return 0;
}

db.set_authorize_handler(&handle_authorize);

struct handler
{
  handler() : cnt_(0) {}

  void handle_update(int opcode, char const* dbname,
                     char const* tablename, int64_t rowid) {
    cout << "handle_update(" << opcode << ", " << dbname << ", "
         << tablename << ", " << rowid << ") - " << cnt_++ << endl;
  }
  int cnt_;
};

using namespace std::placeholders;

db.set_update_handler(std::bind(&handler::handle_update, &h, _1, _2, _3, _4));

function

int test0()
{
  return 100;
}

sqlite3pp::database db("test.db");
sqlite3pp::ext::function func(db);

func.create<int ()>("test0", &test0);

void test1(sqlite3pp::ext::context& ctx)
{
  ctx.result(200);
}

void test2(sqlite3pp::ext::context& ctx)
{
  string args = ctx.get<string>(0);
  ctx.result(args);
}

void test3(sqlite3pp::ext::context& ctx)
{
  ctx.result_copy(0);
}

func.create("test1", &test1);
func.create("test2", &test2, 1);
func.create("test3", &test3, 1);

func.create<int ()>("test4", []{ return 500; });

string test5(string const& value)
{
  return value;
}

string test6(string const& s1, string const& s2, string const& s3)
{
  return s1 + s2 + s3;
}

func.create<int (int)>("test5", [](int i){ return i + 10000; });
func.create<string (string, string, string)>("test6", &test6);

sqlite3pp::query qry(
  db,
  "SELECT test0(), test1(), test2('x'), test3('y'), test4(), test5(10), "
  "test6('a', 'b', 'c')");

aggregate

void step(sqlite3pp::ext::context& c)
{
  int* sum = (int*) c.aggregate_data(sizeof(int));

  *sum += c.get<int>(0);
}
void finalize(sqlite3pp::ext::context& c)
{
  int* sum = (int*) c.aggregate_data(sizeof(int));
  c.result(*sum);
}

sqlite3pp::database db("foods.db");
sqlite3pp::ext::aggregate aggr(db);

aggr.create("aggr0", &step, &finalize);

struct mycnt
{
  void step() {
    ++n_;
  }
  int finish() {
    return n_;
  }
  int n_;
};

aggr.create<mycnt>("aggr1");

struct strcnt
{
  void step(string const& s) {
    s_ += s;
  }
  int finish() {
    return s_.size();
  }
  string s_;
};

struct plussum
{
  void step(int n1, int n2) {
    n_ += n1 + n2;
  }
  int finish() {
    return n_;
  }
  int n_;
};

aggr.create<strcnt, string>("aggr2");
aggr.create<plussum, int, int>("aggr3");

sqlite3pp::query qry(
  db,
  "SELECT aggr0(id), aggr1(type_id), aggr2(name), aggr3(id, type_id) "
  "FROM foods");

loadable extension

#define SQLITE3PP_LOADABLE_EXTENSION
#include <sqlite3ppext.h>

int sqlite3_extension_init(
  sqlite3 *pdb,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  sqlite3pp:database db(sqlite3pp::ext::borrow(pdb));
  // pdb is not closed since db just borrows it.
}

See also

• http://www.sqlite.org/
• https://code.google.com/p/sqlite3pp/
• https://github.com/iwongu/sqlite3pp/wiki/Using-variadic-templates-with-different-parameter-types
• https://github.com/iwongu/sqlite3pp/wiki/Using-variadic-templates-with-function-calls-using-tuple
• c-of-day-43-sqlite3-c-wrapper-1 (Korean)
• c-of-day-44-sqlite3-c-wrapper-2 (Korean)
• c-of-day-45-sqlite3-c-wrapper-3 (Korean)

sqlite3pp's Issues

Will add unicode support?

This is a great and easy use library,and will you add unicode to it?

Incorporating std::error_code

This is just for the sake of discussion. Feel free to close.

Have you considered replacing your errno-style return types with std::error_code? Doing this would give us inter-operable, type-stripped ways to propagate errors without losing any information.

int database::execute(char const* sql);

would become

std::error_code database::execute(char const* sql);

Instead of using upstream SQLITE_OK and sqlite3_errstr() directly like this:

sqlite3pp::database db;
int ec = db.connect(...);

if (ec != SQLITE_OK)
    throw std::runtime_error( sqlite3_errstr(ec) );

we could use an sqlite3pp-supplied C++ wrapper:

sqlite3pp::database db;
std::error_code ec = db.connect(...);

if (ec != sqlite3pp::ok)
    throw std::runtime_error( ec.message() );

It could also help us to combine sqlite3pp errors with our own errors:

std::error_code MyDb::AddUser(User u) 
{
    if (u.size() < 3)
        return UserError::too_short;

    return _db->execute( SqlInsert(u) );
}

It could also be used to permit the use of optional exceptions:

void database::detach(char const* name) throws;
void database::detach(char const* name, std::error_code& ec) nothrow;

Or you could also splice in your own error codes.

inline std::error_code statement::bind(char const* name, int value)
{
  auto idx = sqlite3_bind_parameter_index(stmt_, name);
  if (idx == 0)
    return sqlite3pp::bad_bind_name;
  return bind(idx, value);
}

If you really want to take this a step further, you could implement chaining patterns for your binds:

inline statement& bind(const char* name, int value, std::error_code& ec){
  auto idx = sqlite3_bind_parameter_index(stmt_, name);
  if (idx == 0)
  {
    ec = sqlite3pp::bad_bind_name;
    return *this;
  }
  ec = bind(idx, value);
  return *this;
}

allowing us to:

  std::error_code ec1, ec2, ec3, ec4;
  sqlite3pp::command cmd(
    db, "INSERT INTO contacts (name, phone) VALUES (:user, :phone)", ec1
  ).bind(":user", "Mike", ec2)
   .bind(":phone", "555-1234", ec3)
   .execute(ec4);

An implementation could look like this:

namespace sqlite3pp {
  enum class error {
    ok         = SQLITE_OK        ,
    row        = SQLITE_ROW       ,
    done       = SQLITE_DONE      ,
    error      = SQLITE_ERROR     ,
    internal   = SQLITE_INTERNAL  ,
    perm       = SQLITE_PERM      ,
    abort      = SQLITE_ABORT     ,
    busy       = SQLITE_BUSY      ,
    locked     = SQLITE_LOCKED    ,
    ...
  };

  class error_category : public std::error_category {
  public:
    virtual const char* name() const noexcept override { return "sqlite3pp"; }
    virtual std::string message(int ev) const override { return sqlite3_errstr(ev); }
  };

  const std::error_category& ErrorCategory() noexcept {
    static class error_category c;
    return c;
  }
}

std::error_code make_error_code(sqlite3pp::error code) noexcept {
  return std::error_code(static_cast<int>(code), sqlite3pp::ErrorCategory() );
}

template<>
struct std::is_error_code_enum<sqlite3::error> : public std::true_type {};

Of course, the major disadvantage is the break in the API this would cause. But I'm curious, if it wasn't for API compatibility, would this be interesting?

Inconsistency in error codes vs exceptions

It is not clear why query throws an exception in case of error, but command not, so the user has to manually check the return code.

SQLite Online Backup API

I had to use SQLiteCpp as it provides a wrapper for sqlite3_backup_init, but I'd prefer to use sqlite3pp!

https://github.com/SRombauts/SQLiteCpp/blob/master/include/SQLiteCpp/Backup.h

not able to execute multiple queries at a time

hi,
i was trying to execute INSERT and DELETE in a single command, but i couldn't do it using Sqlite3pp.
sample code:

query = "INSERT INTO Logs (logData) VALUES(?);"
"DELETE FROM Logs WHERE sequenceId=(SELECT MIN(sequenceId) FROM Logs)";
sqlite3pp::command cmd(clinicalDb_, query.c_str());
cmd.bind(1, (void *)&info, sizeof(info), sqlite3pp::copy);

     if ((rc = cmd.execute_all()) != NO_SQL_ERROR)
     {
        PX_ERRORF("Command Execution failed. rc = {}", rc);
     }

in this case i am getting rc value 1(SQLITE_ERROR).

if i pass some string to VALUES("some string), query executes successfully.

Please help me how to solve this.

Query to a remote server (local network)

How can I connect and query remote SQLite server, that are in the same network as a client? Is it possible?

Exception when string column is null

I'm getting exception when I run
std::string test = qry.getstd::string(12);

and the column value is null......

What I'm doing wrong?

Throw from destructors

Hi,

Looks like the sqlite3pp::statement destructor is just processing the result of sqlite3_finalize call. Would it no be safer just to ignore the non SQLITE_OK return, rather then throw an exception from destructor? Looking at documentation for sqlite3_finalize:

“If the most recent evaluation of statement S failed, then sqlite3_finalize(S) returns the appropriate error code or extended error code.”

I guess the same does not apply to the sqlite3pp::transaction destructor. Modifying your example a bit:

sqlite3pp::transaction xct(db, true);
{
sqlite3pp::command cmd1(
db, "INSERT INTO contacts (name, phone) VALUES (:user, :phone)");
cmd1.bind(":user", "Mike", sqlite3pp::nocopy);
cmd1.bind(":phone", "555-1234", sqlite3pp::nocopy);
cmd1.execute();

... more code ...

sqlite3pp::command cmd2(
db, "INSERT INTO contacts (name, phone) VALUES (:user, :phone)");
cmd2.bind(":user", "Dave", sqlite3pp::nocopy);
cmd2.bind(":phone", "555-5678", sqlite3pp::nocopy);
cmd2.execute();
}
xct.rollback();

If “... more code ...” throws would the xct try to commit the cmd1 part only?

Best Regards,
Gorby

sqlitepp::database construct error with memory leak?

I had a question with the constructor of sqlite3pp::database obeject.
The sqlite_open_v2() function is called in by function sqlite3pp::connect() in file sqlite3pp.ipp. It means also called by sqlite3pp::database constructor function.

  database::database(char const* dbname, int flags, char const* vfs) : db_(nullptr), borrowing_(false)
  {
    if (dbname) {
      auto rc = connect(dbname, flags, vfs);                      ///< sqlite3_open_v2() is called here
      if (rc != SQLITE_OK)                                                   
        throw database_error("can't connect database");   ///< if exception throws, sqlite3_close() will not called
    }
  }

But if there is error occured, like a illegal path, the constructor function will throw exception. And sqlite3_close() function will not called, which was contrary to sqlite3 docs(https://www.sqlite.org/capi3ref.html#sqlite3_open). Sqlite3 docs declares： Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required.

In fact , I can catch exception and csalled sqlite3_close() by myself. But I need to get raw sqlite3 obeject managerd by sqlite3pp. I don't know if it was designed, or there is better way to solve this question?
Thanks for help!

By the way, I tried use illegal path to make exceptions while constructing. It did have some memory leak, perhaps 70-100KB each 100 times if I did not called sqlite3_close(). The enviroment is win10, vs2015.

statement doesn't have a bind(int, std::string)

Hi, what is the reasoning behind that missing bind function?
I would do:

int statement::bind(int idx, const std::string& value)
{
  return sqlite3_bind_blob(stmt_, idx, value.c_str(), value.size(),  SQLITE_TRANSIENT);
}

Create Table

how to create a table?

library without boost

Hi
I love your library - just what I needed,... but I can't use boost. I have a large project that does not use boost and I can't add it now just for this. I think a lot of what you use boost for is now part of the standard c++ library, like std::tuple, std::function, and most compilers are now C++11. I really think you should try to get rid of boost. I tried to give it a shot, but got stuck on the iterator part. I will try to see if the boost headers are 'header only' - maybe I can include them in my library that way.
Any thoughts on this?

Best Regards, Peter

Wrong version in 1.0.5 package

You have wrong version in sqlite3pp.h in sqlite3pp-1.0.5.tar.gz
#define SQLITE3PP_VERSION "1.0.0"
#define SQLITE3PP_VERSION_MAJOR 1
#define SQLITE3PP_VERSION_MINOR 0
#define SQLITE3PP_VERSION_PATCH 0

As i understand it should be 1.0.5? (SQLITE3PP_VERSION_PATCH == 5?)

headeronly_src is older than src

Compiling 64 bit (Windows)

Hi!
is it possible to compile with 64bit on windows?
when I try all sources are unavailable

Changing the meaning of bool values is a bad idea

In b780095 the fstatic parameter has been renamed to fcopy, which in itself is probably better API but is really dangerous for people who already use a previous version and want to upgrade.

All the people who have previously passed true as fstatic will still pass true after updating to your newest version, and then their code will be broken. Fixing it would require inspecting every single bind call, and is error prone as heck.

Instead of reversing the boolean, a better approach would be to make it an enum. For instance I would call:

const char* str1 = "foo";
std::string str2 = "foo";
stmt.bind(0, str1, strlen(str1), sqlite3pp::reference);
stmt.bind(1, str2, str2.size(), sqlite3pp::copy);

This has two benefits:

• It provides a compiler error to upgrading developers, instead of silently breaking their programs.
• It's also much more readable than any of the previous two versions, so I know what it does without even looking up the function in the header file.

Please fix this and save your existing users from segfaults.

Row count of table?

Hello,
I've taken a look at this & can't seem to find a decent answer.

Is there any suggested method to get the row count (i.e. length) of a table? I've tried using COUNT(*) without success & also something trivial like:

int row_count = 0;
sqlite3pp::query qry(this->db, "SELECT * FROM some_table");
auto i = qry.begin();
if (i != qry.end())
    row_count++;

Thanks

my sqlite c++ wrapper

Query iterator has unexpected side effects

Currently, if you call begin two or more times you will skip records as the cursor will be moved every time. This is not how begin should behave and definitely not what most people expect from. Typical use case - check if the result has any rows

qry.begin() != qry.end()

and then iterate

What is the current stable version?

I am having troubles to identify a version of this library to pair it with the last changes. Is this a missing feature?

Add a method wrapping `sqlite3_clear_bindings`

The statement::reset method calls sqlite3_reset, which makes the sqlite3_stmt ready to run again, but does not clear bindings. That means if you run the statement again but don't re-bind every parameter, some of the old bindings stay in effect.

There should be a method that calls sqlite3_clear_bindings, to ensure that all bindings are restored to null.

how to insert a NULL value ?

Hi! Thank you for this library!
I wanted to know the syntax i could use to insert a NULL value in a database ? For now I insert an empty string using:

sqlite3pp::command cmd(
  this->m_database,
  "INSERT INTO table (a, b, c) VALUES (?,?,?)"
);
cmd.binder() << a << b << "";
cmd.execute();

Thank you!

compile failed with vs2012

lots of features are not supported

sqlite3pp as header-only library

The code is mostly forwarding to sqlite3 functions directly, and no build system is provided as sqlite3pp is meant to be included directly into the project tree (as far as I understand it). I think this makes it a perfect candidate for being a header-only library.

I would imagine it needs only a few changes:

• Declare all exportable symbols (probably just class methods) as inline.
• Rename .cpp files to .ipp and include them from the .h
• Put all of that into a folder include/sqlite3pp, so parent projects can add [sqlite3pp-repo]/include to their include directories.

VS 2013 does not support `default` for move constructors

While using sqlite3pp in VS 2013 (yeah, old, but still in widely use) the compiler generates next errors:

2>C:\work\tmclient\common\libs\sqlite3pp\sqlite3pp.h(57): error C2610: 'sqlite3pp::noncopyable::noncopyable(sqlite3pp::noncopyable &&)' : is not a special member function which can be defaulted
2>C:\work\tmclient\common\libs\sqlite3pp\sqlite3pp.h(58): error C2610: 'sqlite3pp::noncopyable &sqlite3pp::noncopyable::operator =(sqlite3pp::noncopyable &&)' : is not a special member function which can be defaulted

Error points to sqlite3pp.h, lines 57 & 58:

    noncopyable(noncopyable&&) = default;
    noncopyable& operator=(noncopyable&&) = default;

See also similar question on stackoverflow

Can you please fix it?

Are your code MIT licensed?

Will be very good, if you will add a license file, like MIT, BSD, Apache etc.

src is older than headeronly_src

Please keep both in sync or remove it (together with boost_src)

Move semantics for query?

Hi,

Looks like (noncopyable) sqlite3pp::database class have move constructor and move assignment operators, so we can do:

auto db = sqlite3pp::database{ ..... };

Should the sqlite3pp::query have the same? (and maybe other noncopyables types in sqlite3pp?) So we could do:

auto q = sqlite3pp::query{ ..... };

I am trying to see how well "Almost Always Auto" would work:
https://herbsutter.com/2013/08/12/gotw-94-solution-aaa-style-almost-always-auto/

Thanks,
Gorby

Unable to catch "sqlite" database exceptions

Hi there, I have started playing around with this but I have noticed that when there is a exception at the sqlite level, all exceptions are impossible to catch, regardless of whether or not they are in a try/catch. This is causing the app to crash.

An example is when trying to insert a record with duplicate key. It just dies. Is there a way to handle these? Rather than doing a lookup before doing the insert?

database file is locked even after database::disconnect

The bug in your code:

int database::disconnect()
{
int rc = SQLITE_OK;
if (db_) {
rc = sqlite3_close(db_);
db_ = 0;
}
return rc;
}

if sqlite3_close returned an error, db_ becomes NULL and database never closed.

Probably, you should add rc check
if (rc == SQLITE_OK)
db_ = 0;

query::rows::get(...) implementation for std::string type crashes on NULL

In case when returned "const char*" value points to nullptr, the following implementation crashes. Is this expected implementation and the client code is requested to not use this wrapper for possible NULL values?

  std::string query::rows::get(int idx, std::string) const
  {
    return get(idx, (char const*)0);
  }

Could you please add unicode sopport ?

This is an awesome library, however, I can not use it in my win32 project. Do you have a plan on it ?

Thank you!

sqlite3_prepare or sqlite3_prepare_v2

Hello!

Do you have a specific reason for using sqlite3_prepare instead of sqlite3_prepare_v2?

The v2 form has an advantage, that it can recompile the statement on schema change, instead of returning SQLITE_SCHEMA error code. I had a problem related to this, and changing sqlite3_prepare to sqlite3_prepare_v2 resolved that.

Also, the documentation states:

The preferred routine to use is sqlite3_prepare_v2(). The sqlite3_prepare() interface is legacy and should be avoided.

The sqlite3_prepare_v2(), ... interfaces are recommended for all new programs. The older interfaces (...) are retained for backwards compatibility, but their use is discouraged.

SIGSEGV in command::execute_all() with trailing whitespace

When running sqlite3ppp::command::execute_all() with a command that has trailing whitespace, we get a SIGSEGV from sqlite3_transfer_bindings().

Consider the following:

sqlite3pp::database db(":memory:");
                                                                                
sqlite3pp::command cmd0(db, " ");                                       
assert( cmd0.execute() == SQLITE_MISUSE );                              
                                                                                
sqlite3pp::command cmd1(db, "CREATE TABLE Bar ( v ); ");                
cmd1.execute_all();  

cmd0.execute() runs a command that is empty (one space). This returns SQLITE_MISUSE as expected.

I expect cmd1.execute_all() should run "CREATE TABLE BAR(v);" and then should either break from the while loop, or run the command on " " which would return SQLITE_MISUSE. Instead it segfaults. I think the problem stems from the while loop that detects if we're done:

while (std::strlen(sql) > 0) { // sqlite3_complete() is broken.
    ...
    sqlite3_transfer_bindings(old_stmt, stmt_); // <-- SIGSEGV here
    ...
    sql = tail_;
}

If sqlite3_complete() really is broken and we need to write our own implementation, then perhaps we should do something more elaborate than std::strlen(sql) > 0. Maybe something like this could work:

while (std::strlen(sql) > 0) { // sqlite3_complete() is broken.
    ...
    sqlite3_transfer_bindings(old_stmt, stmt_);
    ...
    for(sql = tail_; *sql != '\0' && std::isspace(*sql); ++sql){}
}

throwing destructors

Destructors in C++11 are implicitly have nothrow specification, and cannot throw.

1>..\..\common\libs\sqlite3pp\sqlite3pp.cpp(195): warning C4297: 'sqlite3pp::statement::~statement': function assumed not to throw an exception but does
1>  ..\..\common\libs\sqlite3pp\sqlite3pp.cpp(195): note: destructor or deallocator has a (possibly implicit) non-throwing exception specification
1>..\..\common\libs\sqlite3pp\sqlite3pp.cpp(494): warning C4297: 'sqlite3pp::transaction::~transaction': function assumed not to throw an exception but does
1>  ..\..\common\libs\sqlite3pp\sqlite3pp.cpp(494): note: destructor or deallocator has a (possibly implicit) non-throwing exception specification

Compiling on Ubuntu error

Hi! Nice library. I have used it on windows (VS 2015) and now I'm using on Ubuntu (Netbeans 8.1).
When I try to compile it i get this error:

sqlite3pp.h:191:40: error: call of overloaded ‘bind(int&, long int&)’ is ambiguous

Any idea about it?

Thank you very much.

iterator fix

Hi
In order to compile the code with C++17 a fix needs to be done to the iterator, here is the fix:
fixiterator-STL4015.zip

HTH
Anders.

`cmd.binder() << "Mike";` doesn't work

Since int Statement::bind(int idx, char const* value, bool fstatic = true); has been changed to int Statement::bind(int idx, char const* value, copy_semantic fcopy);. I would prefer fcopy=copy.

Can not build with Visual Studio 2013

Using a fresh clone of the source code, I see two errors when attempting to build with Visual Studio 2013.

sqlite3pp.cpp Line 49
Error C2146: syntax error : missing ';' before identifier 'noncopyable'

sqlite3pp.cpp Line 49
Error C4430: missing type specifier - int assumed. Note: C++ does not support default-int

Test code looks like this:

#include "sqlite3pp.h"

class MyApp {
public:
    sqlite3pp::database db;
    void setup();    
}

void MyApp::setup() {
    db.connect("test.sqlite");
}

C++17 deprecated usage of std::iterator as a base class

Hi all,

While compiling with VisualStudio 2022 (and allowing C++20 features) I am getting the following warning:

headeronly_src\sqlite3pp.h(279,21): warning C4996: '<std::input_iterator_tag,sqlite3pp::query::rows,ptrdiff_t,sqlite3pp::query::rows *,sqlite3pp::query::rows &>': warning STL4015: The std::iterator class template (used as a base class to provide typedefs) is deprecated in C++17. (The header is NOT deprecated.) The C++ Standard has never required user-defined iterators to derive from std::iterator. To fix this warning, stop deriving from std::iterator and start providing publicly accessible typedefs named iterator_category, value_type, difference_type, pointer, and reference. Note that value_type is required to be non-const, even for constant iterators. You can define _SILENCE_CXX17_ITERATOR_BASE_CLASS_DEPRECATION_WARNING or _SILENCE_ALL_CXX17_DEPRECATION_WARNINGS to suppress this warning.

Would it be possible to follow the suggestion from the warning and not derive from std::iterator please (especially as it suggests that this never was an intention of the C++ Standard, even before C++17)?

Thank you,
Gorby

License

Hi, can you please add a license.md ?
I saw in the code that it's should be licensed under MIT, adding a license.md should be straightforward and optimal for everyone.
Thanks!

access to the sqlite3* pointer inside class database

There are many sqlite3 features that are not supported by sqlite3pp::database. As a workaround, it would be nice if user can use the internal sqlite3* pointer.

Some useful features that are not supported;

1. sqlite3_changes(): tell how many rows are changed.
2. sqlite3_extended_errcode(): can be used to route errors based on more detailed classification.

Support for sqlite3_column_text16

Does sqlite3pp API supports sqlite3_column_text16 ? Does it plan to support sqlite3_column_text16 ? Please clarify. Thank you!

Calling begin() repeatedly on a query results in invalid rows

As begin() calls sqlite3_step internally, the row pointer gets incremented each time begin() is called on the same query object.

query::begin() is a misnomer

My coworker just spent a few hours trying to find out why he was getting a crash from this innocuous block of code:

sqlite3pp::query qry(...);
// not listed: initializing the statement, binding variables

if (qry.begin() != qry.end()) {
    sqlite3pp::query::iterator it = qry.begin();
    do_stuff((*it).get<std::string>(0)); // this crashes despite being declared as TEXT NOT NULL
}

As the author who has dealt with SQLite extensively, you might quickly be able to tell what's going on there:

• query::begin() is implemented as "create query_iterator, which calls sqlite3_step() in its constructor".
• If query::begin() is called twice, this will just call sqlite3_step() a second time, which means it is at an invalid row now because the result only had a single row.

If I write the same code using sqlite_*() functions, I won't make this error. However, by using begin() as an alias for step(), you implement a well-defined C++ concept with unexpected (one might say wrong) behavior. Popular libraries teach us that we can have as many iterators as we want, they're all independent from each other as long as the underlying structure isn't changed, and begin() always gives you the first element.

In the current code, this behavior is more similar to Java's next()/hasNext() than C++'s begin()/end(). It appears SQLite doesn't really allow you to have multiple iterators/cursors for the same prepared statement, so the best course of action would be to rename begin() so people don't expect the wrong things from it.

My suggestion would be to rename it to execute() (the same name that command is using as well) but unlike command, let it continue returning the iterator. The query_iterator constructor would call sqlite3_reset() before calling sqlite3_step(). Everything else from then on would stay the same, end() does match the established C++ semantics so I think it's okay to leave it with its current name. The resulting code would then look like this:

sqlite3pp::query::iterator it = qry.execute();
if (it != qry.end()) {
    do_stuff((*it).get<std::string>(0)); // this crashes despite being declared as TEXT NOT NULL
}

and the code from the current README.md example also still makes sense:

for (sqlite3pp::query::iterator i = qry.execute(); i != qry.end(); ++i) {
  for (int j = 0; j < qry.column_count(); ++j) {
    cout << (*i).get<char const*>(j) << "\t";
  }
  cout << endl;
}

In addition, my coworker's original code would clearly indicate what it's doing:

if (qry.execute() != qry.end()) { // first execution of the query
    sqlite3pp::query::iterator it = qry.execute(); // second execution of the query
    // reset() doesn't clear bindings, so the second execution still behaves as expected.
    do_stuff((*it).get<std::string>(0)); // doesn't crash, only needlessly inefficient
}

Because it's clear that it's doing the wrong thing, it would probably never get written this way - but even in this form, it behaves as the developer would expect it to behave.

Either way, begin() deserves to be deprecated or removed. Thank you for your time and patience :)