Home / Downloads / Database access / Help

Database access

Database access is a portable C++ library, which enables high performance identical client access to the different database servers (ODBC and Oracle).
All software code is written in ANSI C++ programming language using platform-independent approach.

1. Create Database access object

Database access internally incapsulates (ODBC, ORACLE) APIs and provides an identical server independent interface.
Database access provides synchronous (blocking) and asynchronous(non-blocking) SQL statement executing and data fetching. All SQL statements as well as stored procedures can be accomplished with parameters. All asynchronous requests can be interrupted by user at any time. The result of asynchronous operations is availbale through either checking a state of object or by using callback objects.
Database access provides two factories for creating database access objects (dbserver* type) - odbcaccess and orclaccess objects. Once a user created dbserver object he becomes responsible to destroy it.

a. Create ODBC access object:
odbcaccess acc;
dbserver* srv = acc.get_dbserver(0);
....
delete srv;

b. Create ORACLE access object:
orclaccess acc;
dbserver* srv = acc.get_dbserver(0);
....
delete srv;

// the syntax of connect string depends on server type
// for ODBC
bool res = srv->connect(false, "DSN=alias;UID=user;PWD=password");
// also trusted connection syntax is supported
bool res = srv->connect(true, "DSN=alias");

...

bool res = srv->disconnect();

// for ORACLE
bool res = srv->connect(false, "user/password@sid");
// trusted connection isn't supported
...

bool res = srv->disconnect();

// to start transaction
bool res = srv->start_transaction();
// to commit previously started transaction
bool res = srv->commit();
// to rollback previously started transaction
bool res = srv->rollback();

// First of all you have to allocate parameters
bool res = srv->resize_params(5); // 5, for example
// It is possible to resize parameters at any time (the previous assigned data will be valid in case of increasing number of parameters)
// To clear parameters call function with 0 argument
srv->resize_params(0);
// The second step is to assign the value to a parameter.
// However if parameter has be null then you should set it explicitly
bool res = srv->set_param_as_null(0, db_param_in, db_ub4);
// otherwise call the correspondent to the parameter type function
bool res = srv->set_param_as_dword(0, db_param_in, 173); 
...
bool res = srv->set_param_as_bool(1, db_param_in, false); 
bool res = srv->set_param_as_date(2, db_param_in, 2005, 1, 1, 0, 0, 0, 0);
bool res = srv->set_param_as_string(3, db_param_in, "Hi, guys!", -1);
bool res = srv->set_param_as_wstring(4, db_param_in, L"Hi, guys!", 2); // only "Hi" will be assigned
// what if assigned string very long, let say 10 Mb, and you would like to avoid memory reallocation
// allowed only for input parameters
bool res = srv->set_param_as_string_ptr(4, "Hi, guys!");
// and so on...

// It is possible to obtain the value of the correspondent parameter anytime
sb4_t value = srv->get_param_as_dword(0);
// If caller violates index bounderies the finction will return 0 (or null pointer).
// If parameter type is mismatched then DB access will try to convert the original type into requested one.

// DDL or insert SQL statement must be executed by function exec_sql
// Suppose we need to specify 2 binding parameters
const char* sql = "insert into table(col1, col2) values(:col1, :col2)";
// two parameters were specified (:col1 and :col2)
// parameter syntax starts with special quote symbol 
// ':' by default, but user can change special quote to the another one, 
// let say '?' calling function srv->set_quote('?');
// then sql staement must looks like 
// "insert into table(col1, col2) values(?col1, ?col2)"
// moreover a parameter name is optional, it means the following queries are also valid
// "insert into table(col1, col2) values(?, ?)" - quote symbol is '?'
// "insert into table(col1, col2) values(:, :)" - quote symbol is ':'
bool res = srv->resize_params(2);
res = srv->set_param_as_date(0, db_param_in, 2005, 1, 1, 0, 0, 0, 0);
res = srv->set_param_as_string(1, db_param_in, "Hi, guys!", -1);
res = src->exec_sql(sql);

// if transaction management is required then:...
if (!src->start_transaction())
{
	// can't start transaction
	// get why here
	const char* err = src->get_error();
	return -1; // error
}

if (!src->exec_sql(false, sql)) // consider synchronous executing
{
	src->rollback();
	// the latest error is availbale here
	const char* err = src->get_error();
	return -1; // error
}
else
{
	srv->commit();
	return 0; // success
}
...


// To execute stored procedure without returning recordset use function exec_proc
const char* sp_name = "sp_do_something";
// assign parameters
bool res = srv->resize_params(2);
res = srv->set_param_as_date(0, db_param_in, 2005, 1, 1, 0, 0, 0, 0);
// let the second parameter be "out" type
res = srv->set_param_as_string(1, db_param_out, 0, 0);
if (!src->exec_sql(false, sql)) // consider synchronous executing
{
	// handle error here
	const char* err = src->get_error();
	return -1; // error
}

// get output value of second parameter
const char* out_val = srv->get_param_as_string(1);

// If SQL statement is suppose to return recordset use another function open_sql like
const char* sql = "select name, type from customers where open_date < :date and name like :name";
bool res = srv->resize_params(2);
res = srv->set_param_as_date(0, db_param_in, 2005, 1, 1, 0, 0, 0, 0);
res = srv->set_param_as_string(1, db_param_in, "George%", -1);
res = src->open_sql(false, sql); // consider synchronous executing

// Well if there are no error occured we can get recordset
// but before that the columns description is available

size_t cols = srv->get_column_count(); // obviously it will be two of them

dbtypes type1 = srv->get_column_type(0); // probably string, depends on type of column name in table customers
dbtypes type2 = srv->get_column_type(1); // probably int, depends on type of column type in table customers

const char* name1 = srv->get_column_name(0); // should be "name"
const char* name2 = srv->get_column_name(1); // should be "type"

bool nul1 = srv->get_column_nullable(0); // is null value allowed?
bool nul2 = srv->get_column_nullable(1); // is null value allowed?

size_t scale1 = srv->get_column_scale(0); // scale of "name" column in database (0 typically), useful for numeric, decimal types
size_t scale2 = srv->get_column_scale(1); // scale of "type" column in database (0 typically), useful for numeric, decimal types

size_t precision1 = srv->get_column_precision(0); // precision of "name" column in database (typically - the max length of column "name"), useful for numeric, decimal types
size_t precision2 = srv->get_column_precision(1); // precision of "type" column in database (typically 4 for integer), useful for numeric, decimal types

size_t precision1 = srv-> get_column_max_length(0); // max length of "name" column in database (typically - the max length of column "name")
size_t precision2 = srv-> get_column_max_length(1); // max length of "type" column in database (typically 4 for integer)


// well if we get all information about columns we can go ahead
// consider synchronous fetching
// get all rows starting from the beginning
bool res= srv->fetch_data(false, 0, -1, true);

// if we are interested only in particular rows then
// fetch only 1000 rows starting with 100th row as the first one
bool res= srv->fetch_data(false, 100, 1000, true);

// May be we requested more then selected?
// let check it
size_t rows = srv->get_row_count();

// how to navigate through fetched rows?
// initially internal "iterator" points to the nowhere
// therefor the first step is to set "iterator" to the first row
res = srv->next(); // theoretically it can be zero fetched rows
// or if we need to llok through all of them
while (srv->next())
{
....
}

// to make a step back user can use prev() function
bool res = srv->prev();

// remeber that after calling next, prev function and getting return value as false or calling reset function 
// will set internal iterator beyond the sequence of row the same as after fetch_data() function calling

// how to get the values of selected columns?
while (srv->next())
{
	// if the example described above
	const char* value_of_name = srv->get_value_as_string(0); // on the current row 
	int value_of_type = srv->get_value_as_dword(1); // on the current row 
	// if we don't remember the type of column
	// let use type detector
	dbtype type_of_column1 = srv->get_column_type(0);
	dbtype type_of_column2 = srv->get_column_type(1);
	// Don't try to reach 3rd column, it's not in the recordset
	// db access object (srv) will return 0 or (null pointer)
}



// We can use the similar approach to get recordset from stored procedure (ORACLE doesn't support it)
// If SQL statement is supposed to return recordset, use open_sql:
const char* sp_name = "sp_get_customer_name_and_type";
// we assume here that stored procedure has two input parameters the same as above SQL request
bool res = srv->resize_params(2);
res = srv->set_param_as_date(0, db_param_in, 2005, 1, 1, 0, 0, 0, 0);
res = srv->set_param_as_string(1, db_param_in, "George%", -1);
res = src->open_proc(false, sp_get_customer_name_and_type); // consider synchronous executing


// each time when we are going to excute SQL statement or stored procedure 
// the internal resources allocated by previous function calls
// have to be released - user must call function close_sql()