\mainpage db_conn
Header files based C++ database connection API based on KISS principle
I had a hard time finding a free open source C++ Sybase driver that would be lightweight with no dependencies, easy to add and use, having a generic extensible interface in case database would be switched later on. Since then I also added SQLite driver.
The driver functions are thread-safe, all other objects (connection, statement, result_set) are not.
It's up to the users to decide whether to use (and what kind) any synchronization means or use a different design, eg:
- use a single connection per thread
- create a connection pool
- create a separate database connection thread with its own connection
- Sybase ASE (ASA was not tested) - see sybase_example.cpp
- SQLite - see sqlite_example.cpp
The code has not been extensively tested, thus there could be some bugs.
Especial concerns would be the large data type (text, blob, unitext) and utf support.
NOTE 1: You need to download and install specific database client (and if separate an SDK) for your platform.
NOTE 2: Please keep in mind that you should not include concrete driver (such as eg sybase_driver.hpp) .hpp file in your header file. You should just include driver.hpp in your header file and then in you .cpp file you can include concrete driver file.
@code
#include "sybase_driver.hpp" using namespace std; using namespace vgi::dbconn::dbi; using namespace vgi::dbconn::dbd; int main(int argc, char** argv) { try { connection conn = driver<sybase::driver>::load().get_connection("DBSYB1", "sa", ""); if (conn.connect()) { statement stmt = conn.get_statement(); // change database stmt.execute("use tempdb"); stmt.execute("if object_id('tempdb..test') is not null drop table test"); // create stmt.execute("create table test (id int, txt varchar(10) null, num numeric(18, 8) null, primary key(id))"); // insert stmt.execute("insert into test (id, txt) values (1, 'txt1') \ insert into test (id, txt) values (2, 'txt2')"); // update stmt.execute("update test set txt = 'test1' where id = 1 \ update test set txt = 'test2' where id = 2"); // select result_set rs = stmt.execute("select * from test"); while (rs.next()) { cout << rs.column_name(0) << ": " << rs.get_int(0) << endl; cout << rs.column_name(1) << ": " << rs.get_string(1) << endl; cout << "column id: " << rs.get_int("id") << endl; cout << "column txt1: " << rs.get_string("txt") << endl; } // delete stmt.prepare("delete from test where id = 2"); rs = stmt.execute(); cout << "rows affected = " << rs.rows_affected() << endl; // prepared statement stmt.prepare("insert into test values (?, ?)"); stmt.set_int(0, 2); stmt.set_string(1, "test2"); stmt.execute(); // cursor rs = stmt.execute("select id, txt from test", true); while (rs.next()) { cout << rs.column_name(0) << ": " << (rs.is_null(0) ? -1 : rs.get_int(0)) << endl; cout << rs.column_name(1) << ": " << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << endl; } // scrollable cursor rs = stmt.execute("select id, txt from test", true, true); while (rs.next()) { cout << rs.column_name(0) << ": " << (rs.is_null(0) ? -1 : rs.get_int(0)) << endl; cout << rs.column_name(1) << ": " << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << endl; } rs.first(); do { cout << rs.column_name(0) << ": " << (rs.is_null(0) ? -1 : rs.get_int(0)) << endl; cout << rs.column_name(1) << ": " << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << endl; } while (rs.next()); while (rs.prev()) { cout << rs.column_name(0) << ": " << (rs.is_null(0) ? -1 : rs.get_int(0)) << endl; cout << rs.column_name(1) << ": " << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << endl; } // stored procedure stmt.execute("create procedure test_proc @id int, @error varchar(128) output AS \ BEGIN \ DECLARE @ret int \ SET @error = NULL \ SET @ret = 0 \ IF @id = 0 \ BEGIN \ SET @error = 'id must be > 0' \ SET @ret = 1 \ END \ SELECT txt FROM test WHERE id = @id \ RETURN @ret \ END"); stmt.call("test_proc"); stmt.set_int(0, 3); rs = stmt.execute(); while (rs.next()) cout << rs.column_name(0) << ": " << (rs.is_null(0) ? "NULL" : rs.get_string(0)) << endl; cout << "rows affected = " << rs.rows_affected() << endl; cout << "stored proc return = " << stmt.proc_retval() << endl; cout << "@error: >" << (rs.is_null(0) ? "NULL" : rs.get_string("@error")) << endl; // truncate stmt.execute("truncate table test"); // transaction conn.autocommit(false); stmt.execute("insert into test values (1, 'test1')"); conn.commit(); stmt.execute("insert into test values (2, 'test2')"); stmt.execute("insert into test values (3, 'test3')"); conn.rollback(); conn.autocommit(true); rs = stmt.execute("select count(*) from test"); rs.next(); cout << "Table has " << rs.get_int(0) << " rows\n"; // drop stmt.execute("drop table test"); } else cout << "failed to connect!\n"; } catch (const exception& e) { cout << "exception: " << e.what() << endl; } return 0; } @endcode
Limited amount of testing was done.
If someone would like to contribute please ping me.