OLEDB Consumer templates coding

Executing SQL commands

When you need to execute some plain SQL command, the easiest way is through CCommand:

TCHAR *sql_create_table = L"CREATE TABLE `t`(`id` INT)";
TCHAR *sql_insert_in_table = L"INSERT INTO `t` VALUES(1)";

CCommand<CNoAccessor, CNoRowset> cmd;

HRESULT hr = cmd.Open(session, sql_create_table);
if(FAILED(hr))
	return false;
cmd.Close();

hr = cmd.Open(session, sql_insert_in_table);
if(FAILED(hr))
	return false;
cmd.Close();

Please note that, as long as the SQL command does not return a rowset, the right thing to do is to specify the CNoRowset when you define the command.

Insert data

We will be using the nation table and the following data structure:

//Accessor class for Insert tests using Accessor and CCommand
class CNationInsert
{
public:
	//Parameter variable
	TCHAR m_Code4;
	TCHAR m_Name41;
	TCHAR m_Continent11;
	TCHAR m_Capital31;

	//Parameter binding map
	BEGIN_PARAM_MAP(CNationInsert)
		SET_PARAM_TYPE(DBPARAMIO_INPUT)
		COLUMN_ENTRY(1, m_Code)
		COLUMN_ENTRY(2, m_Name)
		COLUMN_ENTRY(3, m_Continent)
		COLUMN_ENTRY(4, m_Capital)
	END_PARAM_MAP()
};

And this is how we are inserting a new row in the table (using CTable):

CTable <CAccessor<CNationInsert>> nation_cmd;

CDBPropSet propset(DBPROPSET_ROWSET);
propset.AddProperty(DBPROP_IRowsetChange, true);
propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);

hr = nation_cmd.Open(session, L"nation", &propset);

nation_cmd.MoveNext();

//Insert new row
wcscpy(nation_cmd.m_Code, L"ZZZ");
wcscpy(nation_cmd.m_Name, L"ABCDEF");
wcscpy(nation_cmd.m_Continent, L"MyXYZ");
wcscpy(nation_cmd.m_Capital, L"QWERTY");

hr = nation_cmd.Insert();

Update data

By default, when connecting to CUBRID, the auto-commit mode is set to ON and this will prevent updates to work from OLE DB Consumer templates. Therefore, you will need to explicitly start a session transaction, which will turn auto-commit to OFF and enable updates.

We will be using the nation table and the following data structure:

//Accessor class for explicit Update tests using Accessor and CCommand
class CNationUpdate
{
public:
	//Parameter variable
	TCHAR m_Code4;
	TCHAR m_Name41;
	TCHAR m_Continent11;
	TCHAR m_Capital31;

	//Parameter binding map
	BEGIN_PARAM_MAP(CNationUpdate)
		SET_PARAM_TYPE(DBPARAMIO_INPUT)
		COLUMN_ENTRY(1, m_Code)
		COLUMN_ENTRY(2, m_Name)
		COLUMN_ENTRY(3, m_Continent)
		COLUMN_ENTRY(4, m_Capital)
	END_PARAM_MAP()
};

To update the data, we will use this code:

session.StartTransaction();

CCommand <CAccessor<CNation>> nation_cmd;

CDBPropSet propset(DBPROPSET_ROWSET);
propset.AddProperty(DBPROP_CANFETCHBACKWARDS, true);
propset.AddProperty(DBPROP_IRowsetScroll, true);
propset.AddProperty(DBPROP_IRowsetChange, true);
propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);

HRESULT hr = nation_cmd.Open(session, "select * from nation", &propset);

//Position on the row
hr = nation_cmd.MoveLast();

//Update the current row
wcscpy(nation_cmd.m_Name, L"AAA");
wcscpy(nation_cmd.m_Continent, L"BBB");
wcscpy(nation_cmd.m_Capital, L"CCC");

hr = nation_cmd.SetData();

Please note that we have not used deferred updates.

Unless you specify otherwise, calls to the SetData, Insert, and Delete methods update the data store immediately. You can, however, defer updates so that the consumer stores all changes in a local cache and then transfers them to the data store when you call one of the following update methods:

• CRowset::Update transfers any pending changes made to the current row since the last fetch or Update call on it.
• CRowset::UpdateAll transfers any pending changes made to all rows since the last fetch or Update call on it.

Read more about here: http://msdn.microsoft.com/en-us/library/xwdy8hwx%28v=vs.80%29.aspx.

Delete data

We will be using the nation table and the following data structure:

class CNation
{
public:
	TCHAR m_Code4;
	TCHAR m_Name41;
	TCHAR m_Continent11;
	TCHAR m_Capital31;

	//Output binding map.
	BEGIN_COLUMN_MAP(CNation)
		COLUMN_ENTRY(1, m_Code)
		COLUMN_ENTRY(2, m_Name)
		COLUMN_ENTRY(3, m_Continent)
		COLUMN_ENTRY(4, m_Capital)
	END_COLUMN_MAP()
};

Deleting rows is quite simple:

CCommand <CAccessor<CNation>> nation_cmd;

CDBPropSet propset(DBPROPSET_ROWSET);
propset.AddProperty(DBPROP_CANFETCHBACKWARDS, true);
propset.AddProperty(DBPROP_IRowsetScroll, true);
propset.AddProperty(DBPROP_IRowsetChange, true);
propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);

hr = nation_cmd.Open(session, L"SELECT * FROM nation WHERE code = 'ZZZ', &propset);

//Position on the row
hr = nation_cmd.MoveNext();

//Delete the current row
hr = nation_cmd.Delete();

Using a Dynamic Accessor

One of the most powerful ways to access data using OLE DB Consumer templates is using CDynamicAccessor, which mean that there is no need to predefine a data structure – the data can be accessed dynamically. It’s about allowing users to access a data source when you have no knowledge of the database schema (the database's underlying structure).

Let’s see a simple example of how we can take leverage of this powerful interface in CUBRID:

CTable<CDynamicAccessor> rs;

HRESULT hr = rs.Open(session, "nation");

hr = rs.MoveNext();

Or, if we want to use CCommand instead of CTable:

CCommand<CDynamicAccessor, CRowset> cmd;

HRESULT hr = cmd.Open(session, L"select capital from nation where ROWNUM = 3");

hr = cmd.MoveNext();

void *val = cmd.GetValue(1);
ATLASSERT(strcmp((char*)val,  "Beograd") == 0);

Accessing CLOB data

Let’s use the following table and let’s suppose we already have some data in the table: "CREATE TABLE `TestLOB`(`id` INT PRIMARY KEY, `str` CLOB)"

First, we need to define the appropriate data structure:

class CLOB
{
public:
	long m_Id;
	ISequentialStream*   pPicture;
	
	BEGIN_COLUMN_MAP(CLOB)
		COLUMN_ENTRY(1, m_Id)
		BLOB_ENTRY(2, IID_ISequentialStream, STGM_READWRITE, pPicture)
	END_COLUMN_MAP()

	DEFINE_COMMAND_EX(CLOB, _T(" SELECT `id`, `str` FROM `TestLOB`"))
};

Next, we will use the following code to get the (CLOB) data from the database:

CTable<CAccessor<CLOB>> clob;
ULONG cb;
BYTE myBuffer65536;

clob.Open(session, "TestLOB");
if(clob.MoveFirst() == S_OK)
{
do
{
hr = clob.pPicture->Read(myBuffer, 65536, &cb);

...
}
while (hr != S_FALSE);
}

You can find more details about accessing LOB data online: http://msdn.microsoft.com/en-us/library/ms131399.aspx.