Introduction
Many of us often are working with SQL Server database, but there is
not a convenient way to execute parameterized SQL statements or stored
procedures. Stefan Tchekanov's CODBCRecordset class is a
very good class to fetch rows from database, but it does not support
parameterized SQL and stored procedures, and we cannot get an output
parameter's value from a stored procedure. I made two classes CMyRecordset
and CMyDatabase to do such work. CMyRecordset
deals with SQL and stored procedures which return row sets and CMyDatabase
deals with SQL and stored procedures which not return row sets.
CMyRecordset
CMyRecordset is a derived class based on CRecordset. when the user enters
a parameterized statement the application calls SQLPrepare to prepare the
statement, SQLNumParams to determine the number of parameters, and
SQLDescribeParam to describe each parameter. When Open the
statement, MFC calls DoFieldExchange, so we put parameter data exchanging
code here.
The following member functions are methods you may use in order.
CMyRecordset(CDatabase* pDatabase);
bool PrepareStoreProcedure(CString strStoreProcedureName);
bool PrepareSQL(CString strSQL);
void SetParam_Long(UINT nParamId, long nParamValue);
void SetParam_Float(UINT nParamId, float fltParamValue);
void SetParam_Double(UINT nParamId, double dblParamValue);
void SetParam_String(UINT nParamId, CString strParamValue);
void SetParam_DateTime(UINT nParamId, TIMESTAMP_STRUCT dtParamValue);
void SetParam_Bool(UINT nParamId, BOOL bParamValue);
bool SafeOpen(CString strSql);
BOOL Open( CString strSQL, UINT nOpenType = AFX_DB_USE_DEFAULT_TYPE,
DWORD dwOptions = 0 );
bool GetBool( int nCol );
unsigned char GetChar( int nCol );
short GetShort( int nCol );
int GetInt( int nCol );
long GetLong( int nCol );
float GetFloat( int nCol );
double GetDouble( int nCol );
COleDateTime GetDate( int nCol );
CString GetString( int nCol );
CLongBinary* GetBinary( int nCol );
BOOL GetParam_Bool(UINT nParamId);
int GetParam_Long(UINT nParamId);
float GetParam_Float(UINT nParamId);
double GetParam_Double(UINT nParamId);
TIMESTAMP_STRUCT GetParam_DateTime(UINT nParamId);
void Close();
There are at most seven steps when using the class. If you just execute a
nonparameterized statement, you only need to do step 1,4,5,7.
(1) Construct the CMyRecordset object
You should give a pointer to a CDatabase class to instantiate a
CMyRecordset object.
CMyRecordset m_Recordset(&m_Database);
(2) Prepare the statement to get parameter information and allocate memory
for parameters
If the statement is not a stored procedure, you call
PrepareSQL, or you will call PrepareStoreProcedure. If you call
PrepareSQL, the parameter position should be filled with a question mark(?),
if you call PrepareStoreProcedure, you just supply the name of the
stored procedure.
m_Recordset.PrepareSQL("select name from employee where id = ?");
m_Recordset.PrepareStoreProcedure("test_sp1");
(3) Set parameter values for each parameter
There is no type convertion between different types, so you must execute the right function.
The parameter number starts with 0, if you execute a stored procedure, you should set value
for parameter from 1, because parameter 0 is used by the return value from stored procedure.
m_Recordset.SetParam_Long(0,1002);
(4) Execute the statement
You can use SafeOpen or Open, the only difference
is SafeOpen catches exception and displays an error message.
m_Recordset.SafeOpen("select name from employee where id = ?");
m_Recordset.SafeOpen("{? = call test_sp1(?,?)}");
(5) Fetch rows from the record set
There is a type convertion.
while(!m_Recordset.IsEOF())
{
strName = m_Recordset.GetString(0);
m_Recordset.MoveNext();
}
(6) Get output data
If a stored procedure supply output parameters, we can get them after
we finished fetching rows.
sum = m_Recordset.GetParam_Double(2);
(7) Close the recordset
m_Recordset.Close();
CMyDatabase:
CMyDatabase is a derived class based on CDatabase.
When we execute a parameterized statement, CDatabase will call
member function BindParameters, so we must derive this member
function to bind data for parameters.
This class supplies the following methods we will use in order.
bool PrepareSQL(CString strSql);
bool PrepareStoreProcedure(CString strStoreProcedureName);
void SetParam_Bool(UINT nParamId, bool bParamValue);
void SetParam_Char(UINT nParamId, char chParamValue);
void SetParam_Short(UINT nParamId, short iParamValue);
void SetParam_Long(UINT nParamId, long nParamValue);
void SetParam_Float(UINT nParamId, float fltParamValue);
void SetParam_Double(UINT nParamId, double dblParamValue);
void SetParam_String(UINT nParamId, CString strParamValue);
void SetParam_DateTime(UINT nParamId, TIMESTAMP_STRUCT dtParamValue);
void ExecuteSQL(CString strSql);
bool SafeExecuteSQL(CString strSql);
bool GetParam_Bool(UINT nParamId);
char GetParam_Char(UINT nParamId);
short GetParam_Short(UINT nParamId);
long GetParam_Long(UINT nParamId);
float GetParam_Float(UINT nParamId);
double GetParam_Double(UINT nParamId);
TIMESTAMP_STRUCT GetParam_DateTime(UINT nParamId);
There are at most five steps when using the class.
(1) Open a database with Open or OpenEx method
m_db.OpenEx("DRIVER=SQL Server;SERVER=(local);DATABASE=testdb;Trusted_Connection=Yes");
(2) Prepare the statement to get parameter information and allocate
memory for parameters
The same as CMyRecordset::PrepareSQL and
CMyRecordset::PrepareStoreProcedure.
m_db.PrepareSQL("insert into table1(id,name) values (?,?)");
m_db.PrepareStoreProcedure("test_sp1");
(3) Set parameter values for each parameter
The same as CMyRecordset.
m_db.SetParam_Long(0,1002);
m_db.SetParam_String(1,"name");
(4) Execute the statement
You can use SafeExecuteSQL or ExecuteSQL,
the only difference is SafeExecuteSQL catches exception and displays
an error message.
m_db.SafeExecuteSQL("insert into table1(id,name) values (?,?)");
m_db.SafeExecuteSQL("{? = call test_sp1(?,?)}");
(5) Get data from output parameters
If a stored procedure supply output parameters, we can get them after we finished executing the statement.
return = m_Recordset.GetParam_Long(0);
Example
#include "MyDatabase.h"
#include "MyRecordset.h"
CMyDatabase m_db;
m_db.OpenEx("DRIVER=SQL Server;SERVER=(local);DATABASE=testdb;Trusted_Connection=Yes");
CMyRecordset m_Recordset(&m_db);
m_Recordset.PrepareStoreProcedure("test_prc1");
m_Recordset.SetParam_Long(1,1);
m_Recordset.SetParam_Double(2,123.45);
m_Recordset.SetParam_String(3,"string");
m_Recordset.SafeOpen(_T("{?=call test_prc1(?,?,?)}"));
while(!m_Recordset.IsEOF())
{
AfxMessageBox(m_Recordset.GetString(0));
m_Recordset.MoveNext();
}
m_Recordset.Close();
m_db.PrepareSQL("insert into test2(id,name,salary) values (?,?,?)");
m_db.SetParam_Long(0,100);
m_db.SetParam_String(1,"the name");
m_db.SetParam_Double(2,1234.56);
m_db.SafeExecuteSQL("insert into test2(id,name,salary) values (?,?,?)");
m_Dedb.PrepareStoreProcedure("test_prc2");
TIMESTAMP_STRUCT dt1;
ZeroMemory(&dt1,sizeof(dt1));
dt1.year=2002;
dt1.month=9;
dt1.day=1;
m_db.SetParam_Long(1,100);
m_db.SetParam_String(2,"abcdefg");
m_db.SetParam_Double(3,1234.56);
m_db.SetParam_Bool(4,false);
m_db.SetParam_DateTime(5,dt1);
m_db.SafeExecuteSQL("{?=call test_prc2(?,?,?,?,?)}");
long retcode = m_db.GetParam_Long(0);
m_db.Close();
Limitation
The two classes were designed for SQL server, perhaps they can not work
on other database, becuase most data sources do not provide a way for
the driver to discover parameter metadata, so SQLDescribeParam is not
widely supported (for example, they can not work on Access database).
Also we can not get data for string type output parameters from stored procedure, I tried a
lot but I failed, perhaps someone will help me.
Thanks
Great thanks to Stefan Tchekanov (stefant@iname.com) who has written
CODBCRecordset and CDBField class for fetching rows from non parameterized
SQL statment. Some of my codes were taken from the two classes.
