Environment: VC++ 6.0, NT 4.0,Win2000, Win95/98
Introduction
Imagine the advantages of being able to dynamically access a database from the client-side. As a few possibilities, you could dynamically query a database from client-side, dynamically populate a drop-down select list, or dynamically update certain fields of a table. In fact, you can do this, with the help of the CSQLQuery
object.
This article describes the dynamic class CSQLQuery
which based on ATL/OLE DB
library and offer several samples.
Submitting an SQL statement and retrieving data
To submit an SQL statement, you simply instantiate a CSQLQuery
object and then call the ExecuteSQL
member function passing the SQL string as argument.
CSQLQuery
query(GetSession());
query.ExecuteSQL(“SELECT MAX(UnitPrice) FROM Products”);
Retrieving data is easy like 1,2,3. You just need to define variable and get the data by using overloaded operator like >>
.
double
dblUnitPrice = 0; query
>> dblUnitPrice;
Note: in this case you have to take care about data mapping. For example, SQL Server data like int
should to map in C++ long
type.
To retrieve any data to string and avoid mapping you can use operator >
like here:
CString str; query >
str.
In this case you don’t have to take care about data mapping. All types of data will be map to the CString
object automatically.
You can also use overloaded operators Like <<
to pass a parameters
double
dblMaxPrice = 2.8;
CSQLQuery
query(GetSession());
query<<
“SELECT ProductName,UnitPrice FROM Products \
WHERE UnitPrice > ”<<
dblMaxPrice;
query.ExecuteSQL();
The execution of stored procedure with parameters may looks like here:
double
dblPar1 = 2.8, dblPar2 = 1.8;
CSQLQuery
query(GetSession());
query<<
“EXECUTE sp_MyProcedure ”<<
dblPar1<<
” , ”<<
dblPar2;
query.ExecuteSQL();
The SQL update statement will not complicated as well:
CSQLQuery
query(GetSession());
query<<
“UPDATE Orders SET ShipName = ‘MyName’ WHERE EmployeeID = 5”;
query.ExecuteSQL();
An Example Using the Object
An example of list box population
In this example the function PoulateListBox
is making population depends from the value of argument dblMaxPrice. The list box will populated with data having UnitPrice more than dblMaxPrice. You can extend that idea for any condition to retrieve data.
void
PoulateListBox(CListBox
& box,double
dblMaxPrice)
{
box.ResetContent();
CSQLQuery
query(GetSession());
query<<
“SELECT ProductID,ProductName FROM \
Products WHERE UnitPrice > ”<<
dblMaxPrice;
if
(!query.ExecuteSQL())
return
;
while
(!query.eof())
{
LONG
lProductID = 0;CString
strProductName;
query >>
lProductID >> strProductName;
int
idx = box.AddString(strProductName);
box.SetItemData(idx,lProductID);
}
}
En example of CListCtrl population
This sample shows how you can populate CListCtrl with columns and data.
void
CQueryView::ShowQuery(const CString
strTableName)
{
// Clear List control
m_listCtrl.DeleteAllItems();
while
(m_listCtrl.DeleteColumn(0)) {}
CSQLQuery
query(GetSession());
query<<
” SELECT TOP 100 * FROM ”<<
strTableName;
if
(!query.ExecuteSQL())
return
;// Show columns
int
cols = query.GetColumnsCount();
for
(int
nCol = 0; nCol < cols; nCol++)
{
CString
strColName = query.GetColumnName(nCol);
m_listCtrl.InsertColumn(nCol,strColName,LVCFMT_LEFT,80);
}// Show data
int
nItem = 0;
while
(!query.eof())
{
CString
str; query>
str;
m_listCtrl.InsertItem(nItem,str);
for(int
nSubItem = 1; nSubItem < cols; nSubItem++)
{
CString
str; query>
str;
m_listCtrl.SetItemText(nItem,nSubItem,str);
}
nItem++;
}
}
Is this not easy?
About GetSession()
The CSQLQuery
object is using pointer to the CSession
class
. A CSession
object represents a single database access session defined in ATL
library. To create a new CSession
for a CDataSource
simply instantiate an objects:
CDataSource m_datasource;
CSession m_session;
And create one using the following function:
bool Connect(ATL::CDataSource* pDataSource,
ATL::CSession* pSession) {
ASSERT(pDataSource);
CComBSTR bstrServer(m_strServerName);
CComBSTR bstrUser(m_strLoginName);
CComBSTR bstrPassword(m_strPassword);
CComBSTR bstrDatabase(m_strDatabaseName);
if(pSession && pSession->m_spOpenRowset != NULL)
pSession->m_spOpenRowset.Release();CDBPropSet dbinit(DBPROPSET_DBINIT);
dbinit.AddProperty(DBPROP_AUTH_PASSWORD, bstrPassword);
dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
dbinit.AddProperty(DBPROP_AUTH_USERID, bstrUser);
dbinit.AddProperty(DBPROP_INIT_CATALOG, bstrDatabase);
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, bstrServer);
dbinit.AddProperty(DBPROP_INIT_LCID, (long)1049);
dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
if(FAILED(pDataSource->Open(_T(“SQLOLEDB.1”), &dbinit))){
pDataSource->Close(); return
false;
}
else
{
if (pSession &&pSession->Open(*pDataSource) != S_OK)
{
return false;
}
}
return true;
}
So, GetSession()
will be like here:
CSession* GetSession()
{
return &m_session;
}
Notes about demo
The demo project of this article was tested on SQL Server 7.0/8.0 and database Northwind.To connect to another type of database such as Oracle, I believe you should only need to change Function Connect
.