Boost::sql::odbc

Documentation

Conceptual overlook

The ODBC-API is widely known and well documented. So it seams obvious to base a C++ SQL acces atop of it.

The Task of this Boost::Sql module is:

• provide a typeafe and comfortable layer to the ODBC api, but let the semantics of the api unchanged!
• provide RAII livetime mangement to the handles
• provide exeption based error handling
• provide RAII based and exeption aware transaction handling

The following sections describe importent aspects of the strategie.

mapping C++ classes to ODBC handles

Every ODBC API Function has as the first parameter a handle. These handles hold the resources, which are needed to access the the database.

There are 3 ODBC handle types

• SQLHENV the environment handle
• SQLHDBC the database connection handle
• SQLHSTMT the statement handle

This handle kinds map to the boost::sql::odbc 'handle classes'

• 'env'
• 'connect'
• 'stmt'

and the ODBC API function is mapped to the class representing its handle parameter.

There is a hierarchy in the handles:

• an environment handle can be constructed wuíthout a parent handle
• a database connection handle needs an environment handle as parent
• a statement handle needs an database handle as parent

The lifetime of the handles are managed with pairing calls to SQLAllocHandle and SQLFreeHandle

mapping C++ to ODBC functions

To ilustrate the schema, let us go concrete an look how SQLBindCol looks like in the ODBC reference implementation:

class stmt : public handle< SQL_HANDLE_STMT >
{
    ...
    void bind_col
	( SQLSMALLINT	column_number
	, SQLSMALLINT   target_type, SQLPOINTER target_value_ptr, SQLINTEGER buffer_length
	, SQLLEN* strlen_or_ind 
	)
   {
	check( ::SQLBindCol
		( *this, column_number
		, target_type, target_value_ptr, buffer_length
		, strlen_or_ind 
		) 
	);
   }
   void bind_col( SQLSMALLINT column_number, long int* target_value, SQLLEN* ind )
   {
       bind_col( column_number, SQL_C_SLONG, target_value, 0, ind );
   }
   void bind_col( SQLSMALLINT column_number, long int* target_value )
   {
       bind_col( column_number, target_value, 0 );
   }
   template< std::size_t size > 
   void bind_col( SQLSMALLINT column_number, boost::array< char, size >* target_value, SQLLEN* ind )
   {
       bind_col( column_number, SQL_C_CHAR, target_value, size, &ind );
   }
   template< std::size_t size > 
   void bind_col( SQLSMALLINT column_number, boost::array< char, size >* target_value )
   {
       bind_col( column_number, SQL_C_CHAR, target_value, size, 0 );
   }
    ...

What is shown here?

• The first overload's task is
  • to get the HSTMT from this
  • handle the returned code for errors
• The second overload handles the case, where the column should be bound to an 'long int' and an indicator variable.
• the third overload handles is the same as the second, but ignores the indicator variable.
  • Be aware, that this results in an error( that means an exception) when the fetched value is 'null'
• The ODBC datatypes are reused in the pulic interface of the stmnt class.
  • This is part of the design strategie, to reuse all ODBC documentation and stay semanticaly as close as possible to the origin.
• The fourth and fivth overload play are template parameterized by the size of the target, and play the same game again for the boost::array< char, ... > which is used as the prefered host variable for char(*) columns.

Naming of the member functions

at the example of "SQLBindCol"

• SQLBindCol = same name as ODBC
  • +++ very easy connection to ODBC documentation
  • -- not boost conform

• BindCol = same name as ODBC, without SQL prefix
  • ++ easy connection to documentation
  • + lesser redundancy
  • -- not boost conform

• bind_col = straight concersion
  • + connection to documentation possible
  • + boost conform

problem:

• sometimes col
• sometimes column

Maybe better: never use abreviations

• bind_column
  • +...(?) clearer in application
  • -...(?) ODBC equivalent is mental farer away

Error handling

Every ODBC API function returns an integer to indicate succes, failure or special conditons like 'no data'. Checking this return value at every call seams to be tedious, error prone and abstruses the application of the native ODBC api.

To simplify the task, we use the following strategie: every ODBC API return value is imideatly passed to the check or check_data_found function. if the the return value:

• indicates an error (SQL_ERROR, SQL_INVALID_HANDLE) convert the value to an exeption and throw.
• if the return value is SQL_SUCCESS return (check_data_found true)
• if the return value is SQL_SUCCESS_WITH_INFO write to cerr and return (check_data_found true)
• if the return value is SQL_NO_DATA
  • check_data_found return false
  • check assert's and throws afterward