Harlinn.ODBC

Harlinn.ODBC was developed to work with Microsoft SQL Server, and originally designed for use with a Vessel Traffic Management System (VTMS). The library provides a fast, comprehensive and convenient set of C++ classes that wraps the ODBC C API.

This library uses the Harlinn.Common.Core library.

Detailed Documentation is under development.

You need to include the HODBC.h header file to use the library, and the types exposed by the library resides within the Harlinn::ODBC namespace.

I think it’s fair to say that the C based ODBC API is already sort of object oriented, exposed through the four handle types identified by the HandleType enumeration:

  1. HandleType::Environment
  2. HandleType::Connection
  3. HandleType::Statement
  4. HandleType::Descriptor

The library wraps each handle type as a C++ class. The classes are move constructible and move assignable, but not copy constructible nor copy assignable.

If an error occurs while calling the ODBC C API, the library will throw an exception, making it straightforward to use:

ODBC::Environment environment = ODBC::Environment::Create( );
auto connection = environment.Connect( DataSourceName );
connection.SetCurrentCatalog( DatabaseName );
auto statement = connection.CreateStatement( );
statement.Prepare( L"SELECT Id, LastName, FirstName, MiddleName FROM Persons FOR BROWSE" );
auto baseName = statement.ColumnBaseTableName( 1 );
BOOST_CHECK( baseName == L"Persons" );

This is not an ODBC tutorial, and if you really want to dive into the ODBC C API then Microsoft Open Database Connectivity is a very good place to start.

The Handle Classes

The handle classes are all derived from the SqlHandle template class, which implements the functionality that ensures that handles are freed when expected, and if it’s a connection handle: Closed before freed.

The Environment class

Every ODBC application needs an initialized ODBC environment handle, and you call the static Environment::Create() function to create an Environment object wrapping an initialized ODBC environment handle.

The environment holds global data on behalf of the application, such as:

  • The state of the environment, including the current state of environment level attributes.
  • The handles to the connections currently allocated by the environment.
  • The current environment level diagnostics.
  • Available ODBC drivers
  • Configured data sources

An ODBC driver is a dynamic link library that implements ODBC function calls, and a data source is a named set of configuration data required by an ODBC driver to establish a connection with the source of the data, which can be a datafile or a full-blown relational database management server such as IBM DB2, Microsoft SQL Server and Oracle RDBMS.

The Connection class

Connection handles represent a connection between an application and a data source. The connection contains data about:

  • The state of the connection, including the values of connection level attributes.
  • The handles of the descriptors and statements allocated for the connection.
  • The connection level diagnostic information.

Once you have an Environment object, you can use it to connect to an ODBC data source by calling the Connect function on the environment object, passing the name of a data source, configured using the ODBC Data Source Administrator, as its argument:

auto connection = environment.Connect( DataSourceName );

The Statement class

A Statement represents all the information associated with an SQL statement, including:

  • The state of the statement, including the values of statement level attributes.
  • The result sets created by the statement.
  • The parameters used in the execution of the SQL statement.
  • The addresses of the application variables bound to the statement’s result set columns and parameters.

Statement objects are created using the Connection::CreateStatement( ) function:

auto statement = connection.CreateStatement( );

The Descriptor class

A Descriptor represents metadata that describes the columns of a statement or the parameters for a SQL statement. When an application allocates a statement ODBC automatically creates four descriptors:

  • Application Parameter Descriptor (APD). Contains information about the application buffers bound to the parameters in a SQL statement, such as their addresses, lengths, and C data types.
  • Implementation Parameter Descriptor (IPD). Contains information about the parameters in a SQL statement, such as their SQL data types, lengths, and nullability.
  • Application Row Descriptor (ARD). Contains information about the application buffers bound to the columns in a result set, such as their addresses, lengths, and C data types.
  • Implementation Row Descriptor (IRD). Contains information about the columns in a result set, such as their SQL data types, lengths, and nullability.

The support classes

The library contains several support classes that facilitates efficient data exchange between the ODBC C API and your application.

DataReader

The DataReader class mimics, to some extent, the .Net IDataReader interface. It provides methods that allows you to iterate over a result set, while retrieving column values for each row in the result set:

ODBC::Environment environment = ODBC::Environment::Create( );
auto connection = environment.Connect( DataSourceName );
connection.SetCurrentCatalog( DatabaseName );
auto statement = connection.CreateStatement( );
auto reader = statement.ExecuteReader( L"SELECT Id, FloatValue FROM TestTable1" );
if( reader->Read( ) )
{
    auto dbDouble = reader->GetDBDouble( 2 );
    BOOST_CHECK( dbDouble.has_value( ) );
    BOOST_CHECK( dbDouble.value( ) == 1.0 );
}

The above example can easily be understood by anyone that has worked with ADO.Net, but it also highlights two key differences:

  • There is no IsDBNull(...) function, as information about whether the column is NULL, or not, is part of the DBDouble object returned by the GetDBDouble(...) function.
  • Column ordinals start with 1 not 0.

Nullable types

The library implements a wide range of types capable of holding column values and their associated NULL indicator. All of them can be bound to statement input parameters, and most of them can be bound to result set columns and statement output parameters.

FixedDBWideString

FixedDBWideString is a template providing a simple C++ string implementation backed by a zero terminated fixed size wchar_t array. It’s intended for use with smaller nvarchar and nchar columns.

FixedDBAnsiString

FixedDBAnsiString is a template providing a simple C++ string implementation backed by a zero terminated fixed size char array. It’s intended for use with smaller varchar and char columns.

FixedDBBinary

FixedDBBinary is a template providing a simple C++ binary buffer implementation backed by a fixed size Byte array. It’s intended for use with smaller binary and varbinary columns.

DBBoolean

DBBoolean is intended for use with bit columns.

DBSByte

DBSByte is intended for use with signed tinyint columns.

DBByte

DBByte is intended for use with unsigned tinyint columns.

DBInt16

DBInt16 is intended for use with signed smallint columns.

DBUInt16

DBUInt16 is intended for use with unsigned smallint columns.

DBInt32

DBInt32 is intended for use with signed int columns.

DBUInt32

DBUInt32 is intended for use with unsigned int columns.

DBInt64

DBInt64 is intended for use with signed bigint columns.

DBUInt64

DBUInt64 is intended for use with unsigned bigint columns.

DBEnum

DBEnum is intended for use with enums, mapping the value to/from the underlying integer type.

DBSingle

DBSingle is intended for use with 32-bit floating point columns.

DBDouble

DBDouble is intended for use with 64-bit floating point columns.

DBDateTime

DBDateTime is intended for use with signed bigint columns, storing the value in ‘ticks’.

DBTimeSpan

DBTimeSpan is intended for use with signed bigint columns, storing the value in ‘ticks’.

DBGuid

DBGuid is intended for use with uniqueidentifier columns.

DBCurrency

DBCurrency is intended for use with signed bigint columns, storing the scaled value of Currency objects.

DBWideString

DBWideString is intended for use with larger nvarchar and nchar columns. Do not bind result set columns and output parameters to this type.

DBAnsiString

DBAnsiString is intended for use with larger varchar and char columns. Do not bind result set columns and output parameters to this type.

DBBinary

DBBinary is intended for use with larger binary and varbinary columns. Do not bind result set columns and output parameters to this type.

DBTimeStamp

DBTimeStamp is intended for use with datetime and datetime2 columns.

DBTime

DBTime is intended for use with time columns with a resolution of one second.

DBTime2

DBTime2 is intended for use with time columns with a resolution of one 100 nanoseconds, and it’s a Microsoft SQL Server specific extension.

DBInterval

DBInterval is intended for use with interval columns, like Oracles INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND

DBTimestampOffset

DBTimestampOffset is intended for use with datetimeoffset columns.

DBNumeric

DBNumeric is intended for use with decimal and numeric columns.

DBMoney

DBMoney is intended for use with money and smallmoney columns.

DBRowVersion

DBRowVersion is intended for use with rowversion columns.

Non-nullable types

bool, SByte, Byte, Int16, UInt16, Int32, UInt32, Int64, UInt64, enums, float, double, DataTime, TimeSpan, Currency, WideString, AnsiString, Binary, TimeStamp, Time, Time2, Interval, TimestampOffset, Numeric, Money and RowVersion are intended for use with the same column types, with the same restrictions, as their DB prefixed counterparts - but without the ability to assign or retrieve NULL values.

How to use the library

Common steps

Before you can execute an SQL statement against an existing SQL Server database you must:

  1. Create a data source using the ODBC Data Source Administrator.
  2. Create an Environment object.
  3. Create a Connection object.

Once you have created a data source, you can establish a connection using two lines of code:

ODBC::Environment environment = ODBC::Environment::Create( );
auto connection = environment.Connect( DataSourceName );

ODBC::Environment::Create( ) allocates an ODBC environment handle and calls SQLSetEnvAttr to set the SQL_ATTR_APP_ODBC_VER environment attribute to SQL_OV_ODBC3_80, indicating to ODBC that the calling application conforms to the ODBC 3.8 specification.

It then uses the newly created environment to connect to the data source configuration specified by DataSourceName.