\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.