Harlinn.ODBC
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:
HandleType::Environment
HandleType::Connection
HandleType::Statement
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 isNULL
, or not, is part of theDBDouble
object returned by theGetDBDouble(...)
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:
- Create a data source using the ODBC Data Source Administrator.
- Create an Environment object.
- 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
.