ODBC Database Access – A Templatised Approach

Dave Handley

Rate me:

4.80/5 (22 votes)

22 Nov 200417 min read

90.2K

2.9K

A library of template classes that enables the rapid production of client-side database code.

#pragma once

#include <Typelist.h>
#include <DataGenerators.h>
#include <boost/shared_ptr.hpp>
#include "ForwardDeclares.h"
#include "SQLValue.h"
#include "DBException.h"
#include "QueryNode.h"
#include "QuerySQLGenVisitor.h"
#include "PersistentObject.h"
#include <vector>
#include <iterator>
#include <string>
#include <sstream>
#include "afxdb.h"

namespace TemplateDB
{

	class TableDefInterface
	{
	protected:
		TableDefInterface() {}
	public:
		virtual ~TableDefInterface() {}
		virtual bool IsBOF() const = 0;
		virtual bool IsEOF() const = 0;
		virtual void First() = 0;
		virtual void Last() = 0;
		virtual void Next() = 0;
		virtual void Prev() = 0;
		virtual void Requery() = 0;
		virtual const SQLValue &GetFieldValue( unsigned int index ) const = 0;
		virtual const ColumnDefInterface &GetColumnDef( unsigned int index ) const = 0;
	};
	// DBDEF is the name of the database class
	// TABLENAME is the name of the table
	// COLDEFS is a typelist of column definitions
	template < class DBDEF, char *TABLENAME, class COLDEFS >
	class TableDef : public TableDefInterface
	{
	public:
		typedef typename boost::shared_ptr< TableDef< DBDEF, TABLENAME, COLDEFS > > _ptr;
		static _ptr GetTableDef();
		static _ptr GetTableDef( const WhereClause &where );
	protected:
		TableDef();
		TableDef( const WhereClause &where );
	public:
		virtual ~TableDef();

		// Navigation functions
		virtual bool IsBOF() const;
		virtual bool IsEOF() const;

		virtual void First();
		virtual void Last();
		virtual void Next();
		virtual void Prev();

		// Force a requery of the dataset
		virtual void Requery();

		// Access the data
		virtual const SQLValue &GetFieldValue( unsigned int index ) const;
		virtual const ColumnDefInterface &GetColumnDef( unsigned int index ) const;

		// Return the number of columns in this class
		static unsigned int GetNumberOfColumns();
		// Return the first column def with the primary key set
		static ColumnDefInterface *GetPrimaryKey();
		static unsigned int GetPrimaryKeyColumnIndex();

	public:
		// API for create persistent objects on this table
		typedef typename PersistentObject<DBDEF, TABLENAME, COLDEFS>::_ptr persistentObjectPtr;
		persistentObjectPtr NewRow();
		persistentObjectPtr NewRow( const PersistentObjectPtr &dependentEntry );
		persistentObjectPtr AccessRow();

	protected:
		// Get the SQL string to open the record set
		std::string GetOpenSQL();
		// Assert that the record set is open
		void OpenRecordSet();
		// Create the columns ready to receive data
		void CreateColumns();
		// Ensure that data from the record set is copied down into this object
		void SyncData();

	private:
		CRecordset m_recordSet;
		std::vector<ColumnDefInterface*> m_columnDefs;
		WhereClause m_whereClause;

		template <typename T>
		struct Create
		{
			T *operator()()
			{
				return T::Create();
			}
		};

 		template <class TList>
		struct FindPrimaryKey
		{
			typedef typename TList::Head Head;
			typedef typename TList::Tail Tail;
		private:
			// Main template IfThenElse handles the default - ie true
			template<bool C, class T, class F>
			struct IfThenElse
			{
				// True variant
				typedef typename T Result;
			};
			// Partial specialisation for the alternate case - false
			template<class T, class F>
			struct IfThenElse<false, T, F>
			{
				// False variant
				typedef typename F Result;
			};
			// Main template returns the Head if it is the PrimaryKey, or recurses onto the Tail
			template<class TList1>
			struct In
			{
				typedef typename TList1::Head Head;
				typedef typename TList1::Tail Tail;
				typedef typename IfThenElse<Head::IsPrimaryKey, Head, typename In<Tail>::Result>::Result Result;
			};
			// Partial specialisation to end recursion - returns the Head of the list by default
			template<>
			struct In< ::Loki::NullType >
			{
				typedef typename Head Result;
			};
		public:
			typedef typename In<TList>::Result Result;
		};

	};

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	typename TableDef<DBDEF, TABLENAME, COLDEFS>::_ptr
	TableDef<DBDEF, TABLENAME, COLDEFS>::GetTableDef()
	{
		return _ptr( new TableDef<DBDEF, TABLENAME, COLDEFS>() );
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	typename TableDef<DBDEF, TABLENAME, COLDEFS>::_ptr
	TableDef<DBDEF, TABLENAME, COLDEFS>::GetTableDef( const WhereClause &where )
	{
		return _ptr( new TableDef<DBDEF, TABLENAME, COLDEFS>( where ) );
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	TableDef<DBDEF, TABLENAME, COLDEFS>::TableDef() :
		m_recordSet( DBDEF::MySingleton::Instance().GetDatabase() ),
		m_columnDefs( Loki::TL::Length< COLDEFS >::value ),
		m_whereClause()
	{
		CreateColumns();
		First();
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	TableDef<DBDEF, TABLENAME, COLDEFS>::TableDef( const WhereClause &where ) :
		m_recordSet( DBDEF::MySingleton::Instance().GetDatabase() ),
		m_columnDefs( Loki::TL::Length< COLDEFS >::value ),
		m_whereClause( where )
	{
		CreateColumns();
		First();
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	TableDef<DBDEF, TABLENAME, COLDEFS>::~TableDef()
	{
		if ( m_recordSet.IsOpen() )
			m_recordSet.Close();
		// Empty the column list
		std::vector<ColumnDefInterface*>::iterator iter = m_columnDefs.begin();
		for ( ; iter != m_columnDefs.end(); ++iter )
			if ( (*iter) )
				delete (*iter);
		m_columnDefs.clear();
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	bool
	TableDef<DBDEF, TABLENAME, COLDEFS>::IsBOF() const
	{
		if ( !m_recordSet.IsOpen() )
			throw DBException( "RecordSet is not yet open" );
		return m_recordSet.IsBOF() ? true : false;
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	bool
	TableDef<DBDEF, TABLENAME, COLDEFS>::IsEOF() const
	{
		if ( !m_recordSet.IsOpen() )
			throw DBException( "RecordSet is not yet open" );
		return m_recordSet.IsEOF() ? true : false;
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	void
	TableDef<DBDEF, TABLENAME, COLDEFS>::First()
	{
		OpenRecordSet();
		if ( IsBOF() && IsEOF() )
			return;
		m_recordSet.MoveFirst();
		SyncData();
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	void
	TableDef<DBDEF, TABLENAME, COLDEFS>::Last()
	{
		OpenRecordSet();
		if ( IsBOF() && IsEOF() )
			return;
		m_recordSet.MoveLast();
		SyncData();
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	void
	TableDef<DBDEF, TABLENAME, COLDEFS>::Next()
	{
		if ( !m_recordSet.IsOpen() )
			throw DBException( "RecordSet is not yet open" );
		m_recordSet.MoveNext();
		SyncData();
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	void
	TableDef<DBDEF, TABLENAME, COLDEFS>::Prev()
	{
		if ( !m_recordSet.IsOpen() )
			throw DBException( "RecordSet is not yet open" );
		m_recordSet.MovePrev();
		SyncData();
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	void
	TableDef<DBDEF, TABLENAME, COLDEFS>::Requery()
	{
		if ( !m_recordSet.IsOpen() )
			throw DBException( "RecordSet is not yet open" );
		m_recordSet.Requery();
		First();
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	const SQLValue &
	TableDef<DBDEF, TABLENAME, COLDEFS>::GetFieldValue( unsigned int index ) const
	{
		if ( !m_recordSet.IsOpen() )
			throw DBException( "RecordSet is not yet open" );
		if ( index >= m_columnDefs.size() )
			throw DBException( "Attempt to access field out of range" );
		return m_columnDefs[ index ]->GetValue();
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	const ColumnDefInterface &
	TableDef<DBDEF, TABLENAME, COLDEFS>::GetColumnDef( unsigned int index ) const
	{
		if ( !m_recordSet.IsOpen() )
			throw DBException( "RecordSet is not yet open" );
		if ( index >= m_columnDefs.size() )
			throw DBException( "Attempt to access field out of range" );
		return *m_columnDefs[ index ];
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	unsigned int
	TableDef<DBDEF, TABLENAME, COLDEFS>::GetNumberOfColumns()
	{
		return Loki::TL::Length< COLDEFS >::value;
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	ColumnDefInterface *
	TableDef<DBDEF, TABLENAME, COLDEFS>::GetPrimaryKey()
	{
		return FindPrimaryKey<COLDEFS>::Result::Create();
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	unsigned int
	TableDef<DBDEF, TABLENAME, COLDEFS>::GetPrimaryKeyColumnIndex()
	{
		return Loki::TL::IndexOf<COLDEFS, FindPrimaryKey<COLDEFS>::Result>::value;
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	typename TableDef<DBDEF, TABLENAME, COLDEFS>::persistentObjectPtr
	TableDef<DBDEF, TABLENAME, COLDEFS>::NewRow()
	{
		PersistentObject<DBDEF, TABLENAME, COLDEFS>::_ptr row = PersistentObject<DBDEF, TABLENAME, COLDEFS>::GetPersistentObject();
		row->PersistentObjectInterface::Create();
		return row;
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	typename TableDef<DBDEF, TABLENAME, COLDEFS>::persistentObjectPtr
	TableDef<DBDEF, TABLENAME, COLDEFS>::NewRow( const PersistentObjectPtr &dependentEntry )
	{
		PersistentObject<DBDEF, TABLENAME, COLDEFS>::_ptr row = PersistentObject<DBDEF, TABLENAME, COLDEFS>::GetPersistentObject();
		row->PersistentObjectInterface::Create( dependentEntry );
		return row;
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	typename TableDef<DBDEF, TABLENAME, COLDEFS>::persistentObjectPtr
	TableDef<DBDEF, TABLENAME, COLDEFS>::AccessRow()
	{
		PersistentObject<DBDEF, TABLENAME, COLDEFS>::_ptr row = PersistentObject<DBDEF, TABLENAME, COLDEFS>::GetPersistentObject();
		row->CopyInColumns( m_columnDefs );
		return row;
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	std::string
	TableDef<DBDEF, TABLENAME, COLDEFS>::GetOpenSQL()
	{
		std::ostringstream str;
		str << "SELECT ";
		std::vector<ColumnDefInterface*>::const_iterator iter = m_columnDefs.begin();
		if ( iter == m_columnDefs.end() )
			str << "* ";
		else
		{
			// Special case for first to ensure commas work
			str << (*iter)->GetColumnName();
			for ( ++iter; iter != m_columnDefs.end(); ++iter )
				str << ", " << (*iter)->GetColumnName();
		}
		str << " FROM ";
		str << std::string( TABLENAME );
		str << " WHERE ";
		QuerySQLGenVisitor visitor;
		m_whereClause.Accept( visitor );
		str << visitor.GetSQL();
		return str.str();
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	void
	TableDef<DBDEF, TABLENAME, COLDEFS>::OpenRecordSet()
	{
		if ( m_recordSet.IsOpen() )
			return;
		// Open the recordset
		m_recordSet.m_nFields = 0;
		m_recordSet.Open( CRecordset::snapshot, GetOpenSQL().c_str(), CRecordset::readOnly );
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	void
	TableDef<DBDEF, TABLENAME, COLDEFS>::CreateColumns()
	{
		// Empty the old list
		std::vector<ColumnDefInterface*>::iterator iter = m_columnDefs.begin();
		for ( ; iter != m_columnDefs.end(); ++iter )
			if ( (*iter) )
				delete (*iter );
		m_columnDefs.clear();

		// Iterate over the typelist, creating ColumnDef records from the data in the recordset
		Loki::TL::iterate_types< COLDEFS, Create, std::back_insert_iterator< std::vector<ColumnDefInterface*> > >( std::back_inserter( m_columnDefs ) );
	}

	template < class DBDEF, char *TABLENAME, class COLDEFS >
	void
	TableDef<DBDEF, TABLENAME, COLDEFS>::SyncData()
	{
		if ( !m_recordSet.IsOpen() )
			throw DBException( "RecordSet is not yet open" );
		// Cannot sync data if we are at BOF or EOF
		if ( IsEOF() || IsBOF() )
			return;

		// Now populate the data with actual values
		std::vector<ColumnDefInterface*>::iterator iter = m_columnDefs.begin();
		short ii = 0;
		for ( ; iter != m_columnDefs.end(); ++iter, ++ii )
		{
			CDBVariant value;
			m_recordSet.GetFieldValue( ii, value );
			(*iter)->AcquireValue( value );
		}
	}
}	// namespace TemplateDB

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Written By

Dave Handley

Web Developer

United Kingdom

I started programming on 8 bit machines as a teenager, writing my first compiled programming language before I was 16. I went on to study Engineering and Computer Science at Oxford University, getting a first and the University Prize for the best results in Computer Science. Since then I have worked in a variety of roles, involving systems management and development management on a wide variety of platforms. Now I manage a software development company producing CAD software for Windows using C++.

My 3 favourite reference books are: Design Patterns, Gamma et al; The C++ Standard Library, Josuttis; and Computer Graphics, Foley et al.

Outside computers, I am also the drummer in a band, The Unbelievers and we have just released our first album. I am a pretty good juggler and close up magician, and in my more insane past, I have cycled from Spain to Eastern Turkey, and cycled across the Namib desert.

ODBC Database Access – A Templatised Approach

License

Comments and Discussions