dbQuery q; dbCursor<Contract> contracts; dbCursor<Supplier> suppliers; int price, quantity; SQL_DATE_STRUCT delivery; q = "(price >=",price,"or quantity >=",quantity, ") and delivery <=", delivery; // input price, quantity and delivery values if (contracts.select(q) != 0) { while (contracts.fetch()); do { contract.print(); } }So binding of variables is performed exactly in place where they are used in the query. Programmer should not worry about specifying types and sizes of query parameters, binding buffers to retrieve results, allocation and deallocation of resources. As a result programming of interaction with database becomes significantly less error prone. What is more important, this interface allows programmer to abstract from relational database table and deal only with application objects. OODBC also provides flexible mapping of application classes on relational database tables, making application code less dependent of the database structure.
Macro | Parameter 1 | Parameter 2 | Description |
---|---|---|---|
KEY_COL | Field name | Database table attribute name | Describes primary key field (names of class field and table column are different) |
KEY_COL | Field name | - | Describes primary key field (names of class field and table column are different) |
FIELD_COL | Field name | Database table attribute name | Describes non-primary key field (names of class field and table column are different) |
KEY_COL | Field name | - | Describes non-primary key field (names of class field and table column are different) |
SUPERCLASS | Base class name | - | Specify descriptor of base class |
TYPE_DESCRIPTOR | List of fields (in extra parentheses) | - | Specify class descriptor |
REGISTER_AS | Database table name | Application class name | Associate class descriptor with database table |
REGISTER | Database table name | - | Associate class descriptor with database table (with the same name as the name of the application class) |
Primary keys specified in class descriptor (one or more) are the set of fields (columns) which can be used to uniquely identify any record in the table. They should not match with primary keys declared at table creation time. OODBC needs to have information about primary keys to implement update/delete operations.
It is not necessary to declare all variables in TYPE_DESCRIPTOR - you should
only describe those variables which are mapped on the database table columns.
You should specify type descriptors for all application classes and structures
which either are mapped to the database tables or used for declaring components
of such classes. All mapped on the tables classes should be registered using
REGISTER
macro. For example:
class Employee { public: int code; char name[100]; int age; SQL_DATE_STRUCT hired; long salary; Activity project; TYPE_DESCRIPTOR((KEY(code), FIELD(name), FIELD(age), FIELD(hired), FIELD(salary), FIELD(project))); }; REGISTER(Employee);The following field types are supported by OODBC:
C type | ODBC SQL type | OODBC parameter type |
---|---|---|
char[] | SQL_VARCHAR | SQL_C_CHAR |
bool | SQL_BIT | SQL_C_BIT |
char | SQL_TINYINT | SQL_C_STINYINT |
signed char | SQL_TINYINT | SQL_C_STINYINT |
signed short | SQL_SMALLINT | SQL_C_SSHORT |
signed int | SQL_INTEGER | SQL_C_SLONG |
signed long | SQL_INTEGER | SQL_C_SLONG |
unsigned char | SQL_TINYINT | SQL_C_UTINUINT |
unsigned short | SQL_SMALLINT | SQL_C_USHORT |
unsigned int | SQL_INTEGER | SQL_C_ULONG |
unsigned long | SQL_INTEGER | SQL_C_ULONG |
float | SQL_FLOAT | SQL_C_FLOAT |
double | SQL_DOUBLE | SQL_C_DOUBLE |
SQL_DATE_STRUCT | SQL_TIMESTAMP | SQL_C_DATA |
SQL_TIME_STRUCT | SQL_TIME | SQL_C_TIME |
SQL_TIMESTAMP_STRUCT | SQL_TIMESTAMP | SQL_C_TIMESTAMP |
SQL_NUMERIC_STRUCT | SQL_NUMERIC | SQL_C_NUMERIC |
OODBC provides special template class dbFieldWithIndicator
for declaring variables with indicators.
The indicators are needed to detect NULL values. You can substitute
template parameter of this class with any type supported by OODBC.
The method isNull
of this class can be used after fetching object
from the database to check attribute for NULL. Another way of associating
indicator with variable is to use FIELD_IND_COL
macro
in type descriptor, but I do not recommend it because it.
OODBC provides overloaded '=
' and ',
' C++ operators
to construct query statements with parameters. Parameters can be specified
directly in places where they are used, eliminating any mapping between
parameter placeholders and C variables. In the following sample query,
pointers to the parameters price
and quantity
are stored in the query, so that the query can be executed several times
with different parameter values. C++ overloaded functions make it possible
to automatically determine the type of the parameter,
requiring no extra information
to be supplied by the programmer (such reducing the possibility of a bug).
dbQuery q; int price, quantity; q = "price >=",price,"or quantity >=",quantity;Since the
char*
type can be used both for specifying a fraction
of a query (such as "price >=") and for a parameter of string type,
OODBC uses a special rule to resolve this ambiguity. This rule is based on the
assumption that there is no reason for splitting a query text into two strings
like ("price ",">=") or specifying more than one parameter sequentially
("color=",color,color). So OODBC assumes the first string to be a fraction
of the query text and switches to operand mode
after it. In operand mode, OODBC treats the char*
argument
as a query parameter and switches back to query text mode, and so on...
It is also possible not to use this "syntax sugar" and construct
query elements explicitly by the
dbQuery::append(dbQueryElement::ElementType type, void const* ptr)
method. Before appending elements to the query,
it is necessary to reset the query by the dbQuery::reset()
method
('operator=
' does it automatically).It is not possible to use C++ numeric constants as query parameters, because parameters are accessed by reference. String parameters should be always linked to the array of char not to pointer to chars. Values should be placed to the buffers before query execution:
dbQuery q; char name[256]; q = "name=",name; scanf("%s", name); cursor.select(q); ... scanf("%s", name); cursor.select(q); ...
In addition to builtin types, it is possible to use user-defined types
in queries. Programmers should
define methods, which will not do actual calculations, but instead
return an expression (in terms of predefined database types), which
performs the necessary calculation. It is better to describe it by example.
For example we need to have Point type having x
and y
attributes. Declaration of this class can look something like this:
class Point { int x, y; public: dbQueryExpression operator == (char const* field) { dbQueryExpression expr; expr = dbComponent(field,"x"),"=",x,"and", dbComponent(field,"y"),"=",y; return expr; } dbQueryExpression operator @= (char const* field) { dbQueryExpression expr; expr = dbComponent(field,"x"),"<>",x,"or", dbComponent(field,"y"),"<>",y; return expr; } ... };All these methods receive as their parameter a name of a field in the record. This name is used to contract the full name of the record's component. This can be done by class
dbComponent
, which constructor takes
the name of the structure field and the name of the component of the structure
and returns a compound name separated by a '.' symbol.
The class dbQueryExpression
is used to collect expression items.
The expression is automatically enclosed in parentheses, eliminating conflicts
with operator precedence.
So, assuming a record containing a field location
of Point
type, it is possible
to construct queries like these:
Point p; Query q; q = (p == "location");
dbCursor<T>
,
where T
is the name of a C++ class associated with
the database table. When cursor is created, database connection is associated
with the cursor by the constructor. Instance of dbConnection
object should be passed to the cursor. To avoid access conflicts, the cursor
should be used only by one thread each moment of time.
A cursor can be also used for updating or removing selected records.
In this case "for update"
clause should be added to SQL
selection statement.
A query is executed either by the cursor
select(dbQuery& q)
method or by the select()
method,
which can be used to iterate through
all records in the table. To fetch the records, method fetch()
should be called. It returns false
if there are not more records
in selection or in case of error. Fetched record is placed to the instance of
the object located in cursor itself. Components of this object can be accessed
by means of redefined ->
operator.
To update the selected object instance, you should store new values in
cursor object component and execute update()
method of the
cursor. Selected row can be removed from the table by means of
remove()
method.
A cursor for class T contains an instance of class T, used for fetching the current record. That is why table classes should have a default constructor (constructor without parameters), which has no side effects.
All cursor methods return boolean result code: true if operation succeeded
and false if failed. By means of getLastResult()
function it is possible to knew more about the reasons of failure. This
function return code, which was in its turn returned by failed ODBC
function (or SQL_SUCCESS if no error s took place). For example, when
last record is fetched by the cursor, the following execution of
fetch()
method will return false
and successive
invocation of getlastError
- SQL_NO_DATA.
Except construction queries using dbQuery
class, it is
possible to specify query by example. In this case fields
of the object which should be matched with the correspondent columns of
the table should be set in the cursor and queryByExample
method will construct SQL statement which will match only these columns
of the tables, which were specified in sample object.
Field of the object considered to be specified if:
Query by example semantic is especially convenient for interactive applications in which user fill the request form to specify selection criteria.
dbConnection
encapsulates establishing connection with
database server and handling session errors. Method open()
of dbConnection
class takes three parameters - data sources name,
user name and password. It is possible to insert, update and remove record
from the table using methods of dbConnection
class.
update
and remove
methods use set of primary
keys specified in class descriptor to locate record within the table.
All these methods accepts class descriptor and object reference parameters.
Values of the primary key field of specified object are using in
SQL query to locate target table record. It is also possible to use
template functions insert
, remove
and
update
which takes only one parameter - pointer to the object.
By default SQL server implicitly commit transaction after execution of each
ODBC request. It is possible to switch off autocommit mode and control
fixation of transactions explicitly using commit()
and
rollback()
methods of dbConnection
class.
Setting transaction mode can be done using standard ODBC mechanism.
It is possible to execute arbitrary SQL data manipulation or data definition
statement using execute
method of connection class.
This method also accepts dbQuery
parameter, making it possible to
bind parameters with the query.
All methods of dbConnection
class returns boolean value -
true if operation is completed successfully and false otherwise.
Also when ODBC function call is failed, virtual method
dbConnection::handleError
is invoked.
This method is given three parameters - error code returned by ODBC function,
string with description of place in sources where fault took place
(in format "Error in function %s at %s:%d"
)
and string with description of
the error returned by SQLError
function.
nmake
if you are using
Microsoft make).There is also test example of application using OODBC for interaction with Microsoft SQL Server. It can be easily changed to work with other databases with ODBC driver, but login information and may be some type in table definition SQL statements should be changed.
I will provide e-mail support and help you with development of OODBC applications.
Look for new version at my homepage | E-Mail me about bugs and problems