{

/************************

try

{

cout.precision(12);

cout.setf(ios_base::fixed, ios::floatfield);

cout << endl << "============= Simple example =============" << endl;

cout << "===== connecting to database server\n";

connection conn = driver<sqlite::driver>::load().get_connection(DBNAME);

if (conn.connect())

{

cout << "===== done...\n\n";

statement stmt = conn.get_statement();

cout << "===== drop table if it exists\n";

stmt.execute("drop table if exists test; ");

cout << "===== done...\n\n";

cout << "===== creating table\n";

stmt.execute("create table test ( "

"id integer not null, "

"txt1 text not null, "

"txt2 text null, "

"bool integer not null, "

"flag text not null, "

"short integer not null, "

"long integer not null, "

"float real not null, "

"double real not null, "

"date1 int not null, "

"date2 text not null, "

"date3 real not null, "

"datetime1 int not null, "

"datetime2 text not null, "

"datetime3 real not null, "

"time1 int not null, "

"time2 text not null, "

"time3 real not null, "

"u16str text16 not null, "

"u16char text16 not null, "

"bin blob, "

"primary key(id) ); ");

cout << "===== done...\n\n";

cout << "===== inserting row into the table\n";

stmt.execute("insert into test values ( "

"1, "

"'text1', "

"null, "

"0, "

"'Y', "

"2, "

"167890000, "

"12345.123, "

"122337203685477.58, "

"CURRENT_DATE, "

"CURRENT_DATE, "

"CURRENT_DATE, "

"CURRENT_TIMESTAMP, "

"CURRENT_TIMESTAMP, "

"CURRENT_TIMESTAMP, "

"CURRENT_TIME, "

"CURRENT_TIME, "

"CURRENT_TIME, "

"'\u041F\u0441\u0438\u0445', "

"'\u0414', "

"X'0000008300000000000100000000013c'); ");

cout << "===== done...\n\n";

cout << "===== selecting data from the table\n";

result_set rs = stmt.execute("select * from test;");

int date;

time_t datetime;

double time;

std::u16string u16str;

char16_t char16;

char* c;

std::vector<uint8_t> binvec;

time_t tm;

while (rs.next())

{

size_t i = 0;

cout << "-------------- data by index\n";

cout << rs.column_name(i) << ": >" << rs.get_int(i) << "<\n"; ++i;

cout << rs.column_name(i) << ": >" << rs.get_string(i) << "<\n"; ++i;

cout << rs.column_name(i) << ": >" << (rs.is_null(i) ? "NULL" : rs.get_string(i)) << "<\n"; ++i;

cout << rs.column_name(i) << ": >" << rs.get_bool(i) << "<\n"; ++i;

cout << rs.column_name(i) << ": >" << rs.get_char(i) << "<\n"; ++i;

cout << rs.column_name(i) << ": >" << rs.get_short(i) << "<\n"; ++i;

cout << rs.column_name(i) << ": >" << rs.get_long(i) << "<\n"; ++i;

cout << rs.column_name(i) << ": >" << rs.get_float(i) << "<\n"; ++i;

cout << rs.column_name(i) << ": >" << rs.get_double(i) << "<\n"; ++i;

cout << rs.column_name(i) << ": >" << rs.get_date(i) << "<\n"; date = rs.get_date(i); ++i;

cout << rs.column_name(i) << ": >" << rs.get_date(i) << "<\n"; ++i;

cout << rs.column_name(i) << ": >" << rs.get_date(i) << "<\n"; ++i;

cout << rs.column_name(i) << ": >" << ctime(&(tm = rs.get_datetime(i))); ++i;

cout << rs.column_name(i) << ": >" << ctime(&(tm = rs.get_datetime(i))); datetime = rs.get_datetime(i); ++i;

cout << rs.column_name(i) << ": >" << ctime(&(tm = rs.get_datetime(i))); ++i;

cout << rs.column_name(i) << ": >" << rs.get_time(i) << "<\n"; time = rs.get_time(i); ++i;

cout << rs.column_name(i) << ": >" << rs.get_time(i) << "<\n"; ++i;

cout << rs.column_name(i) << ": >" << rs.get_time(i) << "<\n"; ++i;

// Standard code conversion facets

//wstring_convert<codecvt_utf8<char16_t>, char16_t> cv;

//cout << rs.column_name(i) << ": >" << cv.to_bytes(rs.get_u16string(i)) << "<\n"; ++i;

//cout << rs.column_name(i) << ": >" << cv.to_bytes(rs.get_u16char(i)) << "<\n"; ++i;

// Or just print out each char after manual conversion

cout.setf(ios_base::hex, ios::basefield);

cout << rs.column_name(i) << ": >";

u16str = rs.get_u16string(i); ++i;

for (auto chr16 : u16str)

{

// little endian

c = reinterpret_cast<char*>(&chr16);

cout << "\\u" << setfill('0') << setw(2) << hex << uppercase << (int)(*(++c));

cout << setfill('0') << setw(2) << hex << uppercase << (int)(*(--c));

}

cout.setf(ios_base::dec, ios::basefield);

cout << "<\n";

cout << rs.column_name(i) << ": >";

char16 = rs.get_u16char(i); ++i;

c = reinterpret_cast<char*>(&char16);

// little endian

cout << "\\u" << setfill('0') << setw(2) << hex << uppercase << (int)(*(++c));

cout << setfill('0') << setw(2) << hex << uppercase << (int)(*(--c));

cout << "<\n";

// End of code conversion facets

auto bdata1 = rs.get_binary(i);

binvec.insert(binvec.begin(), bdata1.begin(), bdata1.end());

cout << rs.column_name(i) << ": >"; ++i;

for (uint16_t k : binvec)

cout << setfill('0') << setw(2) << k;

cout.setf(ios_base::dec, ios::basefield);

cout << "<\n";

cout << "-------------- data by name\n";

cout << "column id: >" << rs.get_int("id") << "<\n";

cout << "column txt1: >" << rs.get_string("txt1") << "<\n";

cout << "column txt2: >" << (rs.is_null("txt2") ? "NULL" : rs.get_string("txt2")) << "<\n";

cout << "column bool: >" << rs.get_bool("bool") << "<\n";

cout << "column flag: >" << rs.get_char("flag") << "<\n";

cout << "column short: >" << rs.get_long("short") << "<\n";

cout << "column long: >" << rs.get_long("long") << "<\n";

cout << "column float: >" << rs.get_float("float") << "<\n";

cout << "column double: >" << rs.get_double("double") << "<\n";

cout << "column date1: >" << rs.get_date("date1") << "<\n";

cout << "column date2: >" << rs.get_date("date2") << "<\n";

cout << "column date3: >" << rs.get_date("date3") << "<\n";

cout << "column datetime1: >" << ctime(&(tm = rs.get_datetime("datetime1")));

cout << "column datetime2: >" << ctime(&(tm = rs.get_datetime("datetime2")));

cout << "column datetime3: >" << ctime(&(tm = rs.get_datetime("datetime3")));

cout << "column time1: >" << rs.get_time("time1") << "<\n";

cout << "column time2: >" << rs.get_time("time2") << "<\n";

cout << "column time3: >" << rs.get_time("time3") << "<\n";

cout.setf(ios_base::hex, ios::basefield);

cout << "column u16str1: >";

u16str = rs.get_u16string("u16str");

for (auto chr16 : u16str)

{

// little endian

c = reinterpret_cast<char*>(&chr16);

cout << "\\u" << setfill('0') << setw(2) << hex << uppercase << (int)(*(++c));

cout << setfill('0') << setw(2) << hex << uppercase << (int)(*(--c));

}

cout.setf(ios_base::dec, ios::basefield);

cout << "<\n";

cout << "column u16char: >";

char16 = rs.get_u16char("u16char");

c = reinterpret_cast<char*>(&char16);

// little endian

cout << "\\u" << setfill('0') << setw(2) << hex << uppercase << (int)(*(++c));

cout << setfill('0') << setw(2) << hex << uppercase << (int)(*(--c));

cout << "<\n";

auto bdata2 = rs.get_binary("bin");

cout << "column bin: >";

for (uint16_t i : bdata2)

cout << setfill('0') << setw(2) << i;

cout.setf(ios_base::dec, ios::basefield);

cout << "<\n";

}

cout << "===== done...\n\n";

cout << "===== testing prepared statement (stored proc would be the same) data types binding\n";

u16str = u"\u041F\u0441\u0438\u0445";

size_t i = 0;

stmt.prepare("insert into test values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");

stmt.set_int(i++, 2);

stmt.set_string(i++, "text1");

stmt.set_null(i++);

stmt.set_bool(i++, false);

stmt.set_char(i++, 'Y');

stmt.set_short(i++, 2);

stmt.set_long(i++, 167890000);

stmt.set_float(i++, 12345.123);

stmt.set_double(i++, 122337203685477.58);

stmt.set_date(i++, date);

stmt.set_date(i++, date);

stmt.set_date(i++, date);

stmt.set_datetime(i++, datetime);

stmt.set_datetime(i++, datetime);

stmt.set_datetime(i++, datetime);

stmt.set_time(i++, time);

stmt.set_time(i++, time);

stmt.set_time(i++, time);

stmt.set_u16string(i++, u16str);

stmt.set_u16char(i++, u16str[0]);

stmt.set_binary(i++, binvec);

stmt.execute();

cout << "===== done...\n\n";

cout << "===== updating row in the table\n";

stmt.execute("update test set txt1 = 'text_1.1' where id = 1; ");

cout << "===== done...\n\n";

cout << "===== deleting from the table\n";

stmt.execute("delete from test where id = 1; ");

cout << "===== done...\n\n";

cout << "===== dropping the table\n";

stmt.execute("drop table test; ");

cout << "===== done...\n\n";

}

else

cout << "===== failed to connect!\n";

}

catch (const exception& e)

{

cout << "===== simple example exception: " << e.what() << endl;

}

/************************

try

{

cout << endl << "============= Advanced example =============" << endl;

long version = 0;

string verstr;

bool verbose = true;

cout << "===== connecting to database server using custom settings\n";

/*

sqlite::driver& sqltdriver = driver<sqlite::driver>::load().

// get version

version(version).

version_string(verstr).

// set maximum connections

max_connections(1).

// set custome config settings

config(sqlite::config_flag::MEMSTATUS, 1).

config(sqlite::config_flag::SOFT_HEAP_LIMIT, 8 * 1024 * 1024).

#ifdef SQLITE_CONFIG_LOG

config(sqlite::config_flag::LOG, [](void* data, int errcode, const char* msg)

{

bool isverbose = !!data;

if ((errcode == 0 || errcode == SQLITE_CONSTRAINT || errcode == SQLITE_SCHEMA) && isverbose)

cout << __FUNCTION__ << ": Error Code: " << errcode << ": Message: " << msg << endl;

else

cout << __FUNCTION__ << ": Error Code: " << errcode << ": Message: " << msg << endl;

}, (verbose ? reinterpret_cast<void*>(1) : nullptr))

#endif

config(sqlite::config_flag::MULTITHREAD);

/*

cout << "SQLite Library version number: " << version << endl;

cout << "SQLite Library version string: " << verstr << endl;

/*

cout << "===== connecting to database server\n";

connection conn = sqltdriver.get_connection(DBNAME);

if (conn.connect())

{

cout << "===== done...\n\n";

/*

static_cast<sqlite::connection&>(conn).

config(sqlite::db_config_flag::LOOKASIDE, nullptr, 0, 0);

statement stmt = conn.get_statement();

cout << "===== drop table if it exists\n";

result_set rs = stmt.execute("drop table if exists test; ");

cout << "rows affected = " << rs.rows_affected() << "\n";

cout << "===== done...\n\n";

cout << "===== creating table\n";

rs = stmt.execute("create table test (id integer not null, txt text null, date int not null, primary key(id) );");

cout << "rows affected = " << rs.rows_affected() << "\n";

cout << "===== done...\n\n";

/********************************************************************

cout << "===== using multiple statements to insert rows into the table - all will fail on invalid table name in one of the statements\n";

// If failed to prepare all statement - then whole chain fails

try

{

rs = stmt.execute("insert into test values (1, 'hello1', CURRENT_DATE); \

insert into test values (4, 'hello3', CURRENT_DATE); ");

}

catch (const exception& e)

{

cout << e.what() << "\n";

}

cout << "rows affected = " << rs.rows_affected() << "\n";

cout << "===== done...\n\n";

cout << "===== using multiple statements to insert rows into the table - two will fail on broken unique index\n";

// If certain statements fail during runtime - then all the other statements continue to be executed

try

{

rs = stmt.execute("insert into test values (1, 'hello1', CURRENT_DATE); \

insert into test values (5, 'hello5', CURRENT_DATE); ");

}

catch (const exception& e)

{

cout << e.what() << "\n";

}

cout << "rows affected = " << rs.rows_affected() << "\n";

cout << "===== done...\n\n";

/********************************************************************

cout << "===== using multiple statements to update rows in the table - all will fail on invalid table name in one of the statements\n";

// If failed to prepare all statement (invalid table name) - then whole chain fails

try

{

rs = stmt.execute("update test set txt = 'boom' where id = 1; \

update test set id = 3 where id = 4; ");

}

catch (const exception& e)

{

cout << e.what() << "\n";

}

cout << "rows affected = " << rs.rows_affected() << "\n";

cout << "===== done...\n\n";

cout << "===== using multiple statements to update rows in the table - one will fail on invalid id\n";

// If certain statements fail during runtime - then all the other statements continue to be executed

try

{

rs = stmt.execute("update test set txt = 'boom' where id = 5; \

update test set txt = 'test2' where id = 6; ");

}

catch (const exception& e)

{

cout << e.what() << "\n";

}

cout << "rows affected = " << rs.rows_affected() << "\n";

cout << "===== done...\n\n";

/********************************************************************

cout << "===== using multiple statements to select data from the table - all will fail on invalid table name in one of the statements\n";

// If failed to prepare all statement (invalid table name) - then whole chain fails

try

{

rs = stmt.execute( "select id from test where txt = 'hello1'; \

select id, txt from test where txt = 'hello3'; ");

}

catch (const exception& e)

{

cout << e.what() << "\n";

}

cout << "has data = " << rs.has_data() << "\n";

cout << "more results = " << rs.more_results() << "\n";

cout << "===== done...\n\n";

cout << "===== using multiple statements to select data from the table - one will return no data\n";

// If certain statements fail during runtime - then all the other statements continue to be executed

try

{

rs = stmt.execute( "select id from test where txt = 'hello1'; \

select id, txt from test where txt = 'hello3'; ");

}

catch (const exception& e)

{

cout << e.what() << "\n";

}

for (int i = 1; rs.more_results(); ++i)

{

try

{

cout << "select " << i << ": data set (" << rs.column_count() << " columns):\n";

if (rs.has_data())

{

while (rs.next())

{

cout << "\trow " << rs.row_count() << ":\n";

cout << "\tcolumn 1: >" << (rs.is_null(0) ? -1 : rs.get_int(0)) << "<\n";

if (rs.column_count() > 1)

cout << "\tcolumn 2: >" << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << "<\n";

}

}

cout << "rows affected = " << rs.rows_affected() << "\n";

cout << "---------\n";

}

catch (const exception& e)

{

cout << "select " << i << ": exception: " << e.what() << "\n";

cout << "---------\n";

}

}

cout << "===== done...\n\n";

/********************************************************************

cout << "===== using prepared select statements - repeated execution setting new values\n";

/*

stmt.prepare("select id, txt from test where id = ? or txt = ?; ");

for (int i = 0; i < 6; ++i)

{

cout << "--------------\n";

stmt.set_int(0, i);

stmt.set_string(1, "hello3");

rs = stmt.execute();

if (rs.has_data())

{

while (rs.next())

{

cout << "\trow " << rs.row_count() << ":\n";

cout << "\t" << rs.column_name(0) << ": >" << (rs.is_null(0) ? -1 : rs.get_int(0)) << "<\n";

cout << "\t" << rs.column_name(1) << ": >" << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << "<\n";

}

cout << "done\n";

}

else

cout << "no data\n";

cout << "rows affected = " << rs.rows_affected() << "\n";

}

cout << "===== done...\n\n";

cout << "===== using prepared update statements - repeated execution setting new values\n";

/*

stmt.prepare("update test set txt = ?, date = ? where id = ?; ");

for (int i = 4; i < 6; ++i)

{

cout << "--------------\n";

stmt.set_null(0);

stmt.set_long(1, 20170101);

stmt.set_int(2, i);

rs = stmt.execute();

cout << "rows affected = " << rs.rows_affected() << "\n";

}

cout << "===== done...\n\n";

cout << "===== using prepared delete statements - repeated execution setting new values\n";

/*

stmt.prepare("delete from test where id = ?;");

cout << "--------------\n";

stmt.set_int(0, 4);

rs = stmt.execute();

cout << "rows affected = " << rs.rows_affected() << "\n";

cout << "===== done...\n\n";

cout << "===== using delete with number of affected rows\n";

/********************************************************************

rs = stmt.execute("delete from test where id = 1 or id = 2;");

cout << "delete : rows affected = " << rs.rows_affected() << "\n";

cout << "===== done...\n\n";

cout << "===== using drop with number of affected rows\n";

/********************************************************************

rs = stmt.execute("drop table test;");

cout << "drop : rows affected = " << rs.rows_affected() << "\n";

cout << "===== done...\n\n";

cout << "===== using explicit commit\n";

/********************************************************************

stmt.execute("create table test (id int, txt text NULL, primary key(id) );");

conn.autocommit(false);

cout << "autocommit is OFF\n";

cout << "insert 1 row\n";

stmt.execute("insert into test values (1, 'test1');");

cout << "run commit\n";

conn.commit();

cout << "insert 2 rows\n";

stmt.execute("insert into test values (2, 'test2');");

stmt.execute("insert into test values (3, 'test3');");

cout << "run rollback\n";

conn.rollback();

conn.autocommit(true);

cout << "autocommit is ON\n";

cout << "check row count to make sure rollback worked\n";

rs = stmt.execute( "select count(*) from test;");

rs.next();

cout << "\tTable has >" << rs.get_int(0) << "< rows\n";

cout << "===== done...\n\n";

}

}

catch (const exception& e)

{

cout << "advanced example exception: " << e.what() << endl;

}

return 0;