Click here to Skip to main content
15,867,308 members
Articles / Desktop Programming / MFC
Article

Two classes for parameterized SQL statement and stored procedure

Rate me:
Please Sign up or sign in to vote.
4.73/5 (8 votes)
13 Oct 20023 min read 130.1K   1.9K   43   25
An article on using parameterized SQL statement and stored procedure with ODBC

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);

//test get records from  stored procedure
    
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();
    
//test CMyDatabase execute a SQL

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 (?,?,?)");

//test output parameters from stored procedure
    
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.

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
Web Developer ccbwh
China China
After graduation, I worked in a Bank for 7 years. I love programming very much, it gives me a lot of pleasure. During my working time, I mainly program with C on Unix system. Now I focuse on Win32 programming. I haved programmed with MFC for 2 years.

Comments and Discussions

 
Questionparameterized Query Using CRecordset Pin
Zouaoui Billel26-May-21 8:34
Zouaoui Billel26-May-21 8:34 
QuestionUnicode version Pin
thelvaci10-Sep-13 8:23
thelvaci10-Sep-13 8:23 
GeneralNot working in VC6 Pin
somdipa22-Nov-09 20:53
somdipa22-Nov-09 20:53 
GeneralUpdating a record in the recordset Pin
garyt30-Jul-09 10:48
garyt30-Jul-09 10:48 
Questionhow to write "delete" sql statement? Pin
roro_an3-Mar-08 6:07
roro_an3-Mar-08 6:07 
QuestionHow to make this work in VC++ [modified] Pin
nishanthrao14-Jun-06 21:59
nishanthrao14-Jun-06 21:59 
QuestionTimeout expired Pin
TomSyk10-Apr-06 22:57
TomSyk10-Apr-06 22:57 
Generaldatabase insert query Pin
y_naga_malleswararao4-Mar-06 4:03
y_naga_malleswararao4-Mar-06 4:03 
QuestionNULL Parameter Pin
PetitPapaNoël30-Nov-05 5:02
PetitPapaNoël30-Nov-05 5:02 
QuestionHow to insert/read image in MS SQL Server Pin
An Phung Nguyen12-Nov-05 20:30
An Phung Nguyen12-Nov-05 20:30 
AnswerRe: How to insert/read image in MS SQL Server Pin
kazoo of the north29-Nov-05 11:38
kazoo of the north29-Nov-05 11:38 
GeneralFunction Sequence Error Pin
Stephanie Mahaffey30-Sep-04 5:14
Stephanie Mahaffey30-Sep-04 5:14 
GeneralRe: Function Sequence Error Pin
TWilliams30-Sep-04 5:31
TWilliams30-Sep-04 5:31 
GeneralODBC to DAO Pin
m0xx16-May-04 23:14
m0xx16-May-04 23:14 
Hello
I have the following problem: I opened a database and a recordset using ODBC and I printed it on the screen (using a list). I have now to save it as a *.mdb Access file. Is there a simpler solution or i just have to create a CDaoDatabase and use CDaoTableDef and "insert..." statements? Isn't there an "attach" or something solution?

Thanks,
GeneralReading Strings OutPuts Pin
MIKE20015-Sep-03 2:36
MIKE20015-Sep-03 2:36 
GeneralRe: Reading Strings OutPuts Pin
Tarmo Pikaro20-Nov-03 19:31
Tarmo Pikaro20-Nov-03 19:31 
GeneralRe: Reading Strings OutPuts Pin
Stephen Woods16-May-06 17:52
Stephen Woods16-May-06 17:52 
Questionhow can I Use bigint and image field Pin
jsoft8-May-03 23:30
jsoft8-May-03 23:30 
GeneralThanks... Pin
martinbf29-Apr-03 1:07
martinbf29-Apr-03 1:07 
GeneralSolved it myself Pin
martinbf29-Apr-03 3:12
martinbf29-Apr-03 3:12 
GeneralError using Code Pin
Rough16-Jan-03 0:27
Rough16-Jan-03 0:27 
GeneralRe: Error using Code Pin
Anonymous3-Apr-03 13:56
Anonymous3-Apr-03 13:56 
GeneralCRecordSet::GetDefaultSQL Pin
Edwin Chen11-Dec-02 19:14
Edwin Chen11-Dec-02 19:14 
GeneralImproving the syntax Pin
Vagif Abilov19-Oct-02 13:10
professionalVagif Abilov19-Oct-02 13:10 
GeneralRe: Improving the syntax Pin
dapeng lin21-Oct-02 3:03
professionaldapeng lin21-Oct-02 3:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.