1 #include "sqlite_driver.hpp" 8 constexpr
auto DBNAME =
":memory:";
11 int main(
int argc,
char** argv)
19 cout.setf(ios_base::fixed, ios::floatfield);
21 cout << endl <<
"============= Simple example =============" << endl;
22 cout <<
"===== connecting to database server\n";
26 cout <<
"===== done...\n\n";
29 cout <<
"===== drop table if it exists\n";
30 stmt.
execute(
"drop table if exists test; ");
31 cout <<
"===== done...\n\n";
33 cout <<
"===== creating table\n";
34 stmt.
execute(
"create table test ( " 35 "id integer not null, " 36 "txt1 text not null, " 38 "bool integer not null, " 39 "flag text not null, " 40 "short integer not null, " 41 "long integer not null, " 42 "float real not null, " 43 "double real not null, " 44 "date1 int not null, " 45 "date2 text not null, " 46 "date3 real not null, " 47 "datetime1 int not null, " 48 "datetime2 text not null, " 49 "datetime3 real not null, " 50 "time1 int not null, " 51 "time2 text not null, " 52 "time3 real not null, " 53 "u16str text16 not null, " 54 "u16char text16 not null, " 56 "primary key(id) ); ");
57 cout <<
"===== done...\n\n";
59 cout <<
"===== inserting row into the table\n";
60 stmt.
execute(
"insert into test values ( " 69 "122337203685477.58, " 79 "'\u041F\u0441\u0438\u0445', " 81 "X'0000008300000000000100000000013c'); ");
82 cout <<
"===== done...\n\n";
84 cout <<
"===== selecting data from the table\n";
90 std::u16string u16str;
93 std::vector<uint8_t> binvec;
99 cout <<
"-------------- data by index\n";
100 cout << rs.
column_name(i) <<
": >" << rs.get_int(i) <<
"<\n"; ++i;
101 cout << rs.
column_name(i) <<
": >" << rs.get_string(i) <<
"<\n"; ++i;
102 cout << rs.
column_name(i) <<
": >" << (rs.
is_null(i) ?
"NULL" : rs.get_string(i)) <<
"<\n"; ++i;
103 cout << rs.
column_name(i) <<
": >" << rs.get_bool(i) <<
"<\n"; ++i;
104 cout << rs.
column_name(i) <<
": >" << rs.get_char(i) <<
"<\n"; ++i;
105 cout << rs.
column_name(i) <<
": >" << rs.get_short(i) <<
"<\n"; ++i;
106 cout << rs.
column_name(i) <<
": >" << rs.get_long(i) <<
"<\n"; ++i;
107 cout << rs.
column_name(i) <<
": >" << rs.get_float(i) <<
"<\n"; ++i;
108 cout << rs.
column_name(i) <<
": >" << rs.get_double(i) <<
"<\n"; ++i;
109 cout << rs.
column_name(i) <<
": >" << rs.get_date(i) <<
"<\n"; date = rs.get_date(i); ++i;
110 cout << rs.
column_name(i) <<
": >" << rs.get_date(i) <<
"<\n"; ++i;
111 cout << rs.
column_name(i) <<
": >" << rs.get_date(i) <<
"<\n"; ++i;
112 cout << rs.
column_name(i) <<
": >" << ctime(&(tm = rs.get_datetime(i))); ++i;
113 cout << rs.
column_name(i) <<
": >" << ctime(&(tm = rs.get_datetime(i))); datetime = rs.get_datetime(i); ++i;
114 cout << rs.
column_name(i) <<
": >" << ctime(&(tm = rs.get_datetime(i))); ++i;
115 cout << rs.
column_name(i) <<
": >" << rs.get_time(i) <<
"<\n"; time = rs.get_time(i); ++i;
116 cout << rs.
column_name(i) <<
": >" << rs.get_time(i) <<
"<\n"; ++i;
117 cout << rs.
column_name(i) <<
": >" << rs.get_time(i) <<
"<\n"; ++i;
123 cout.setf(ios_base::hex, ios::basefield);
125 u16str = rs.get_u16string(i); ++i;
126 for (
auto chr16 : u16str)
129 c =
reinterpret_cast<char*
>(&chr16);
130 cout <<
"\\u" << setfill(
'0') << setw(2) << hex << uppercase << (int)(*(++c));
131 cout << setfill(
'0') << setw(2) << hex << uppercase << (int)(*(--c));
133 cout.setf(ios_base::dec, ios::basefield);
136 char16 = rs.get_u16char(i); ++i;
137 c =
reinterpret_cast<char*
>(&char16);
139 cout <<
"\\u" << setfill(
'0') << setw(2) << hex << uppercase << (int)(*(++c));
140 cout << setfill(
'0') << setw(2) << hex << uppercase << (int)(*(--c));
144 auto bdata1 = rs.get_binary(i);
145 binvec.insert(binvec.begin(), bdata1.begin(), bdata1.end());
147 for (uint16_t k : binvec)
148 cout << setfill(
'0') << setw(2) << k;
149 cout.setf(ios_base::dec, ios::basefield);
152 cout <<
"-------------- data by name\n";
153 cout <<
"column id: >" << rs.get_int(
"id") <<
"<\n";
154 cout <<
"column txt1: >" << rs.get_string(
"txt1") <<
"<\n";
155 cout <<
"column txt2: >" << (rs.
is_null(
"txt2") ?
"NULL" : rs.get_string(
"txt2")) <<
"<\n";
156 cout <<
"column bool: >" << rs.get_bool(
"bool") <<
"<\n";
157 cout <<
"column flag: >" << rs.get_char(
"flag") <<
"<\n";
158 cout <<
"column short: >" << rs.get_long(
"short") <<
"<\n";
159 cout <<
"column long: >" << rs.get_long(
"long") <<
"<\n";
160 cout <<
"column float: >" << rs.get_float(
"float") <<
"<\n";
161 cout <<
"column double: >" << rs.get_double(
"double") <<
"<\n";
162 cout <<
"column date1: >" << rs.get_date(
"date1") <<
"<\n";
163 cout <<
"column date2: >" << rs.get_date(
"date2") <<
"<\n";
164 cout <<
"column date3: >" << rs.get_date(
"date3") <<
"<\n";
165 cout <<
"column datetime1: >" << ctime(&(tm = rs.get_datetime(
"datetime1")));
166 cout <<
"column datetime2: >" << ctime(&(tm = rs.get_datetime(
"datetime2")));
167 cout <<
"column datetime3: >" << ctime(&(tm = rs.get_datetime(
"datetime3")));
168 cout <<
"column time1: >" << rs.get_time(
"time1") <<
"<\n";
169 cout <<
"column time2: >" << rs.get_time(
"time2") <<
"<\n";
170 cout <<
"column time3: >" << rs.get_time(
"time3") <<
"<\n";
171 cout.setf(ios_base::hex, ios::basefield);
172 cout <<
"column u16str1: >";
173 u16str = rs.get_u16string(
"u16str");
174 for (
auto chr16 : u16str)
177 c =
reinterpret_cast<char*
>(&chr16);
178 cout <<
"\\u" << setfill(
'0') << setw(2) << hex << uppercase << (int)(*(++c));
179 cout << setfill(
'0') << setw(2) << hex << uppercase << (int)(*(--c));
181 cout.setf(ios_base::dec, ios::basefield);
183 cout <<
"column u16char: >";
184 char16 = rs.get_u16char(
"u16char");
185 c =
reinterpret_cast<char*
>(&char16);
187 cout <<
"\\u" << setfill(
'0') << setw(2) << hex << uppercase << (int)(*(++c));
188 cout << setfill(
'0') << setw(2) << hex << uppercase << (int)(*(--c));
190 auto bdata2 = rs.get_binary(
"bin");
191 cout <<
"column bin: >";
192 for (uint16_t i : bdata2)
193 cout << setfill(
'0') << setw(2) << i;
194 cout.setf(ios_base::dec, ios::basefield);
197 cout <<
"===== done...\n\n";
199 cout <<
"===== testing prepared statement (stored proc would be the same) data types binding\n";
200 u16str = u
"\u041F\u0441\u0438\u0445";
202 stmt.
prepare(
"insert into test values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
203 stmt.set_int(i++, 2);
204 stmt.set_string(i++,
"text1");
206 stmt.set_bool(i++,
false);
207 stmt.set_char(i++,
'Y');
208 stmt.set_short(i++, 2);
209 stmt.set_long(i++, 167890000);
210 stmt.set_float(i++, 12345.123);
211 stmt.set_double(i++, 122337203685477.58);
212 stmt.set_date(i++, date);
213 stmt.set_date(i++, date);
214 stmt.set_date(i++, date);
215 stmt.set_datetime(i++, datetime);
216 stmt.set_datetime(i++, datetime);
217 stmt.set_datetime(i++, datetime);
218 stmt.set_time(i++, time);
219 stmt.set_time(i++, time);
220 stmt.set_time(i++, time);
221 stmt.set_u16string(i++, u16str);
222 stmt.set_u16char(i++, u16str[0]);
223 stmt.set_binary(i++, binvec);
225 cout <<
"===== done...\n\n";
227 cout <<
"===== updating row in the table\n";
228 stmt.
execute(
"update test set txt1 = 'text_1.1' where id = 1; ");
229 cout <<
"===== done...\n\n";
231 cout <<
"===== deleting from the table\n";
232 stmt.
execute(
"delete from test where id = 1; ");
233 cout <<
"===== done...\n\n";
235 cout <<
"===== dropping the table\n";
236 stmt.
execute(
"drop table test; ");
237 cout <<
"===== done...\n\n";
240 cout <<
"===== failed to connect!\n";
242 catch (
const exception& e)
244 cout <<
"===== simple example exception: " << e.what() << endl;
252 cout << endl <<
"============= Advanced example =============" << endl;
258 cout <<
"===== connecting to database server using custom settings\n";
265 version_string(verstr).
269 config(sqlite::config_flag::MEMSTATUS, 1).
270 config(sqlite::config_flag::SOFT_HEAP_LIMIT, 8 * 1024 * 1024).
271 #ifdef SQLITE_CONFIG_LOG
272 config(sqlite::config_flag::LOG, [](
void* data,
int errcode,
const char* msg)
274 bool isverbose = !!data;
275 if ((errcode == 0 || errcode == SQLITE_CONSTRAINT || errcode == SQLITE_SCHEMA) && isverbose)
276 cout << __FUNCTION__ <<
": Error Code: " << errcode <<
": Message: " << msg << endl;
278 cout << __FUNCTION__ <<
": Error Code: " << errcode <<
": Message: " << msg << endl;
279 }, (verbose ?
reinterpret_cast<void*
>(1) :
nullptr))
281 config(sqlite::config_flag::MULTITHREAD);
286 cout <<
"SQLite Library version number: " << version << endl;
287 cout <<
"SQLite Library version string: " << verstr << endl;
292 cout <<
"===== connecting to database server\n";
293 connection conn = sqltdriver.get_connection(DBNAME);
296 cout <<
"===== done...\n\n";
302 config(sqlite::db_config_flag::LOOKASIDE,
nullptr, 0, 0);
306 cout <<
"===== drop table if it exists\n";
309 cout <<
"===== done...\n\n";
311 cout <<
"===== creating table\n";
312 rs = stmt.
execute(
"create table test (id integer not null, txt text null, date int not null, primary key(id) );");
314 cout <<
"===== done...\n\n";
321 cout <<
"===== using multiple statements to insert rows into the table - all will fail on invalid table name in one of the statements\n";
325 rs = stmt.
execute(
"insert into test values (1, 'hello1', CURRENT_DATE); \ 326 insert into test values (1, 'hello1', CURRENT_DATE); \ 327 insert into test values (2, 'hello2', CURRENT_DATE); \ 328 insert into test values (7, 'hello2', CURRENT_DATE); \ 329 insert into bogus values (3, 'hello2', CURRENT_DATE); \ 330 insert into test values (4, 'hello3', CURRENT_DATE); ");
332 catch (
const exception& e)
334 cout << e.what() <<
"\n";
337 cout <<
"===== done...\n\n";
340 cout <<
"===== using multiple statements to insert rows into the table - two will fail on broken unique index\n";
344 rs = stmt.
execute(
"insert into test values (1, 'hello1', CURRENT_DATE); \ 345 insert into test values (1, 'hello1', CURRENT_DATE); \ 346 insert into test values (2, 'hello2', CURRENT_DATE); \ 347 insert into test values (3, 'hello2', CURRENT_DATE); \ 348 insert into test values (4, 'hello3', CURRENT_DATE); \ 349 insert into test values (4, 'hello3', CURRENT_DATE); \ 350 insert into test values (5, 'hello5', CURRENT_DATE); ");
352 catch (
const exception& e)
354 cout << e.what() <<
"\n";
357 cout <<
"===== done...\n\n";
366 cout <<
"===== using multiple statements to update rows in the table - all will fail on invalid table name in one of the statements\n";
370 rs = stmt.
execute(
"update test set txt = 'boom' where id = 1; \ 371 update bogus set txt = 'test2' where id = 2; \ 372 update test set id = 3 where id = 4; ");
374 catch (
const exception& e)
376 cout << e.what() <<
"\n";
379 cout <<
"===== done...\n\n";
382 cout <<
"===== using multiple statements to update rows in the table - one will fail on invalid id\n";
386 rs = stmt.
execute(
"update test set txt = 'boom' where id = 5; \ 387 update test set txt = 'test2' where id = 6; ");
389 catch (
const exception& e)
391 cout << e.what() <<
"\n";
394 cout <<
"===== done...\n\n";
402 cout <<
"===== using multiple statements to select data from the table - all will fail on invalid table name in one of the statements\n";
406 rs = stmt.
execute(
"select id from test where txt = 'hello1'; \ 407 select id, txt from test where txt = 'hello2'; \ 408 select id from test where txt = 'hello4'; \ 409 select id, txt from bogus where txt = 'aa'; \ 410 select id, txt from test where txt = 'hello3'; ");
412 catch (
const exception& e)
414 cout << e.what() <<
"\n";
416 cout <<
"has data = " << rs.
has_data() <<
"\n";
418 cout <<
"===== done...\n\n";
423 cout <<
"===== using multiple statements to select data from the table - one will return no data\n";
427 rs = stmt.
execute(
"select id from test where txt = 'hello1'; \ 428 select id, txt from test where txt = 'hello2'; \ 429 select id from test where txt = 'hello4'; \ 430 select id, txt from test where txt = 'hello3'; ");
432 catch (
const exception& e)
434 cout << e.what() <<
"\n";
440 cout <<
"select " << i <<
": data set (" << rs.
column_count() <<
" columns):\n";
445 cout <<
"\trow " << rs.
row_count() <<
":\n";
446 cout <<
"\tcolumn 1: >" << (rs.
is_null(0) ? -1 : rs.get_int(0)) <<
"<\n";
448 cout <<
"\tcolumn 2: >" << (rs.
is_null(1) ?
"NULL" : rs.get_string(1)) <<
"<\n";
452 cout <<
"---------\n";
454 catch (
const exception& e)
456 cout <<
"select " << i <<
": exception: " << e.what() <<
"\n";
457 cout <<
"---------\n";
460 cout <<
"===== done...\n\n";
469 cout <<
"===== using prepared select statements - repeated execution setting new values\n";
473 stmt.
prepare(
"select id, txt from test where id = ? or txt = ?; ");
474 for (
int i = 0; i < 6; ++i)
476 cout <<
"--------------\n";
478 stmt.set_string(1,
"hello3");
484 cout <<
"\trow " << rs.
row_count() <<
":\n";
485 cout <<
"\t" << rs.
column_name(0) <<
": >" << (rs.
is_null(0) ? -1 : rs.get_int(0)) <<
"<\n";
486 cout <<
"\t" << rs.
column_name(1) <<
": >" << (rs.
is_null(1) ?
"NULL" : rs.get_string(1)) <<
"<\n";
494 cout <<
"===== done...\n\n";
497 cout <<
"===== using prepared update statements - repeated execution setting new values\n";
501 stmt.
prepare(
"update test set txt = ?, date = ? where id = ?; ");
502 for (
int i = 4; i < 6; ++i)
504 cout <<
"--------------\n";
506 stmt.set_long(1, 20170101);
511 cout <<
"===== done...\n\n";
513 cout <<
"===== using prepared delete statements - repeated execution setting new values\n";
517 stmt.
prepare(
"delete from test where id = ?;");
518 cout <<
"--------------\n";
522 cout <<
"===== done...\n\n";
526 cout <<
"===== using delete with number of affected rows\n";
530 rs = stmt.
execute(
"delete from test where id = 1 or id = 2;");
531 cout <<
"delete : rows affected = " << rs.
rows_affected() <<
"\n";
532 cout <<
"===== done...\n\n";
535 cout <<
"===== using drop with number of affected rows\n";
539 rs = stmt.
execute(
"drop table test;");
540 cout <<
"drop : rows affected = " << rs.
rows_affected() <<
"\n";
541 cout <<
"===== done...\n\n";
544 cout <<
"===== using explicit commit\n";
548 stmt.
execute(
"create table test (id int, txt text NULL, primary key(id) );");
550 cout <<
"autocommit is OFF\n";
551 cout <<
"insert 1 row\n";
552 stmt.
execute(
"insert into test values (1, 'test1');");
553 cout <<
"run commit\n";
555 cout <<
"insert 2 rows\n";
556 stmt.
execute(
"insert into test values (2, 'test2');");
557 stmt.
execute(
"insert into test values (3, 'test3');");
558 cout <<
"run rollback\n";
561 cout <<
"autocommit is ON\n";
562 cout <<
"check row count to make sure rollback worked\n";
563 rs = stmt.
execute(
"select count(*) from test;");
565 cout <<
"\tTable has >" << rs.get_int(0) <<
"< rows\n";
566 cout <<
"===== done...\n\n";
570 catch (
const exception& e)
572 cout <<
"advanced example exception: " << e.what() << endl;
void autocommit(bool ac)
Function sets auto-commit option, default is 'true'.
const std::string column_name(size_t col_idx)
Function returns column name by column index or throws an exception if index is invalid.
statement - is a class that manages native driver statement handle.
bool has_data()
Function checks if current result set contains data.
sqlite driver class based on sqlite3 C++ library.
driver is a singleton template class which creates a concrete driver on load() and returns its instan...
bool more_results()
Function checks if there is another data set or status.
statement get_statement()
Function returns statement object for the connection.
size_t rows_affected() const
Function returns number of affected rows after execution of command.
size_t column_count() const
Function returns number of columns of current result set or zero.
bool is_null(size_t col_idx)
Function checks if cell data is NULL by column index or throws an exception if index is invalid...
size_t row_count() const
Function returns current row count while iterating through the result set and total number of rows af...
result_set execute()
Function runs last executed SQL statement.
result_set - is a class that manages native driver result_set handle.
connection - is a class that implements dbi::iconnection interface and represents native database con...
bool connect()
Function opens connection to the database.
void prepare(const std::string &sql)
Function prepares dynamic SQL statement.
bool next()
Function moves iterator to the next row of the current result data set.
connection - is a class that manages native driver connection handle.
void commit()
Function commits changes to the database.
void rollback()
Function rolls back uncommitted changes.