QueryDef: a dynamic replacement for CRecordset

...
CQueryDef qDef(&theApp.db); // uses application wide database connection
CTabbedString sqlRow;
CListBox listAllBadgeInfo; // a listbox control
qDef.Open(CRecordset::snapshot,"select * from BADGE",CRecordset::none);

while (!qDef.IsEOF())
{
	sqlRow = qDef;
	if (sqlRow[0] != "111")
	listAllBadgeInfo.AddString(sqlRow);
	qDef.MoveNext();
}

...
qDef.Open(CRecordset::snapshot,"select * from BADGE",CRecordset::none);
while (!qDef.IsEOF())
{
	if (qDef["CustID"] != SQL_NULL)
	listBadgeInfo.AddString(qDef["CustName"]);
	qDef.MoveNext();
}

The following examples assume you already have an open database connection defined at the application level. Let it be theApp.m_db.

Ad-hoc query without parameters

int TotalSales = 0;

CQueryDef qDef(&theApp.m_db);
qDef.Open(CRecordset::forwardOnly, "select ID,Name,SaleAmount from CUSTOMERS order by Name");

while (!qDef.IsEOF())
{
	if (qDef["ID"] == SQL_NULL)
		continue;

	m_listbox.AddString(qDef["Name"]+ ": " + qDef["SaleAmount"]);
	TotalSales += qDef["SaleAmount"];
	qDef.MoveNext();
}

Ad-hoc query with parameters

int nClientID;
int TotalSales = 0;
CQueryDef qDef(&theApp.m_db);

do
{
	qDef.Param(0) = nClientID; // set the parameter as introduced by the user
	if (!qDef.IsOpen())
		qDef.Open(CRecordset::forwardOnly, "select ID,Name,SaleAmount from CUSTOMERS where ID = ? order by Name");
	else
		qDef.Requery();

	while (!qDef.IsEOF())
	{
		TotalSales += qDef["SaleAmount"];
		qDef.MoveNext();
	}
} while (...); // code stripped away for brevity

You can access the column values on the current row of a CQueryDef object using several methods:

qDef["SaleAmount"]
qDef[2]
qDef.Field("SaleAmount")
qDef.Field(2)

The first and the third are used when you don't know or don't care about the position of this column in the recordset. The second and the fourth are used when you know the position of the column in the recordset or you don't know the name of the column (SELECT * might be one case). Using numbers instead of column names is faster too since there is no search involved. Using an invalid index or column name will result in an integer exception (CQueryVariant::eBoundary).

On the other hand, the parameters are accessed always using the function CQueryDef::Param():

Param(int nPos) // parameters are numbered starting with 0 from left to right

This functions/operators return CQueryVar& making it possible to use them on both sides of the assignment operator. It is legal to write:

qDef["ID"] = nClientID; or
nClientID = qDef["ID"];

qDef.Param(0) = 10;
qDef.Param(1) = "Books%";
CString strCategory = qDef.Param(0);

Calling a stored procedure with input parameters and returning rows

CQueryDef qDef(&theApp.m_db);
qDef.Param(1) = "AMOCO%";
qDef.Open(CRecordset::forwardOnly,"{?L = CALL rpt_CustomersByName ?}");
// rpt_CustomersByName is "select ID,Name,SaleAmount from CUSTOMERS where Name like ? order by Name"

while (!qDef.IsEOF())
{
	m_listbox1.AddString(qDef[0]); // ID is SQL long
	m_listBox2.AddString(qDef[1]); // Name is varchar
	...
}

Each ? is counted so the input parameter to the stored procedure is number 1 (0 being the stored procedures returned value). Notice the suffix following the first parameter. This is used to specify the output parameters type. The table below shows the meaning of every type suffix:

You dont need to provide values (only types) for the output parameters. They will be dynamically allocated by CQueryDef and populated with the stored procedures output parameters. In a similar way you can use input/output parameters:

Calling a stored procedure with parameters without returning rows

qDef.Param(1) = 0L;
qDef.Param(2) = "CMPT";
qDef.Open(CRecordset::forwardOnly,"{?L = CALL rpt_TotalSalesPerCustomer ?L,?}");
...
if (qDef.Param(0) != 0) // error
	ThrowException();
AfxMessageBox(qDef.Param(1));

In the case you don't know the database schema at design time, CQueryDef can help with its Columns() member function:

CQueryColArray& Columns();

This returns an array of CQueryCol objects describing each column.

Dynamic query

CQueryDef qDef(&theApp.m_db);
qDef.Open(CRecordset::snapshot, "select * from COMPANIES");

for (int i = 0; i < qDef.Columns().GetSize(); ++i)
{
	strColName[i] = qDef.Column(i).Name();
	dwColType[i] = qDef.Column(i).Type();
}

while (!qDef.IsEOF())
{
	for (int j = 0; j < i; ++j)
		strRowValues += qDef[j] + "\t";
	qDef.MoveNext();
}

Positioned updates with CQueryDef

CQueryDef qDef(&theApp.db);

try
{
	qDef.Open(CRecordset::snapshot,"select * from BADGE",CRecordset::none);
	qDef.AddNew();
	qDef["ID"] = "XXX";
	qDef["CustID"] = " 58";
	qDef["LockedStatus"] = 1L;
	qDef["StartDate"] = COleDateTime(1998,02,05,10,30,0);
	qDef["ExportStatus"] = 0l;
	qDef.Update();
	qDef.AddNew();
	qDef["ID"] = "YYY";
	qDef["CustID"] = " 58";
	qDef["LockedStatus"] = SQL_NULL;
	qDef["StartDate"] = COleDateTime(1998,02,05,10,30,0);
	qDef["ExportStatus"] = 1l;
	qDef.Update();
	qDef.MoveFirst();
	qDef.Edit();
	qDef["CustID"] = " 57";
	qDef["StartDate"] = COleDateTime::GetCurrentTime();
	qDef.Update();
}
catch (CDBException* e)
{
	...
}

Simple usage of CQueryDef for a single return value (single row)

AfxMessageBox( CQueryDef(&theApp.m_db,"select max(ID) from CUSTOMERS") );

Use of the formatting functions

CQueryDef qDef(&theApp.m_db);
qDef.SetDateFormat("%Y-%m-%d");
qDef.SetDecimalDigits(3)
qDef.Open(CRecordset::forwardOnly, "select ID,Name,StartDate,BalanceAmount from CUSTOMERS");

while (!qDef.IsEOF())
{
	if (qDef[0] == SQL_NULL)
		continue;

	m_list1.AddString(qDef["Name"]);
	m_list2.AddString(qDef["StartDate"]); // format is 1999-01-18
	m_list3.AddString(qDef["BalanceAmount"]); // format is 4552.923
	qDef.MoveNext();
}

SetDateFormat() and SetDecimalDigits() can be applied to the whole CQueryDef object or to individual columns. They have effect only on appropriate data types. When you apply one of these functions to the CQueryDef object level, it will overwrite individual column settings. An EndingDate column may be formatted different in the example above using:

qDef["EndingDate"].SetDateFormat("%m/%d/%y");

The string parameter for the SetDateFormat() function is documented in strftime() C runtime function.

Use of the ReOpen() function

CQueryDef qDef(&theApp.m_db);
q.Open(CRecordset::snapshot, "select a,b from t",CRecordset::none);

try
{
	while (!qDef.IsEOF())
	{
		...
		qDef.MoveNext();
	}
}
catch (CDBException* e)
{ 
	// test the exception type
	... // reestablish the link with the data source
	qDef.ReOpen(newpDB);
}

The moment the link with the data source is lost (e.g. due to a communication problem), the framework will close all the recordsets opened on that connection. Using ReOpen() you may again open the CQueryDef object with the same attributes (without preserving the cursor position).

Notifications

The notification to a user implemented sink, is started calling the function Advise():

EVNHANDLE CQueryDef::Advise(IQueryDefEventSink* pSink);

More than a sink may be connected to a CQueryDef object. The notification of all the sinks will be done in the order of the advise. One event is sent to a particular sink only and only if the sink just before it in the advise loop doesnt respond with a non-zero value. This means that you may choose to stop the event to "bubble" by returning a non-zero value from your implemented notification function. The handle returned from the Advise() function must be saved to be passed as the parameter to the Unadvise() function.

void CQueryDef::Unadvise(EVNHANDLE evnHandle);

A disconnected sink will no longer receive any notification from the CQueryDef object. To temporarily stop the receiving of notifications you can call the FreezeEvents() function:

void CQueryDef::FreezeEvents(EVNHANDLE evnHandle,BOOL bFreeze=TRUE);

To resume receiving notifications youll have to call the same function with bFreeze FALSE. Just before a CQueryDef object is closed a Close notification will be sent and all the sinks are disconnected. After the CQueryDef object is closed the Advise handle becomes invalid and its use must be discontinued.

Points of Interest

Known problems and workarounds

The CRecordset::SetFieldNull() and CRecordset::SetParamNull() functions don't fit in the QueryDef architecture. Their usage is forbidden and a call to any of them will result in a linker error. This behavior is by design. Instead use the assingment operator and the SQL_NULL constant.

CRecordset and CQueryDef henceforth, doesn't handle multiple TIMESTAMP columns. Furthermore such a column must be used at the end of the SQL query otherwise the Invalid descriptor index will be fired.

CQueryDef is not directly derived from CRecordset. Instead CRecordset2 is used to correct a problem related to binding string values without specifying the length of the string variable. Due to this problem QueryDef.dll is related to RecordSetPatch.dll. This secondary DLL must be shipped with your executable alongside QueryDef.dll.

Usage

To use this class library you have to take the following steps:

1. Include QueryDef.H file in your project.
2. Specify the QueryDef namespace by using USE_QUERYDEF macro in the files where you refer the library.
3. Use the CQueryDef class directly (you dont need to derive from it).
4. Have the QueryDef(D).lib and RecordsetPatch(D).lib files in your library path (D is for the debug version your executable will link with the correct version).
5. Put QueryDef(D).dll and RecordsetPatch(D).dll in your path.

The example accompanying this document assumes the presence of an ODBC data source defined against a SQL Server 6.5 (7.0). The initial query works on the pubs database (make an ODBC DS with pubs the default database).

1. Experiment with different queries and see the results.
2. Call the formatting functions. Use stored procedures.
3. Modify the source code to accommodate parameters. The TEST project was compiled with VC++ 6.0. The QueryDef library in the zip file is bult also with this compiler. You'll have to rebuild the library with VC++ 5.0 if you intend to use it with this compiler (see QueryDef.h file for special steps to take in library rebuild).

You may use this class library, even modify it without restriction if you specify the source. However you understand you do that it at your own risk.