Click here to Skip to main content
Click here to Skip to main content

Two classes for parameterized SQL statement and stored procedure

, 13 Oct 2002
Rate this:
Please Sign up or sign in to vote.
An article on using parameterized SQL statement and stored procedure with ODBC
<!-- Download Links --> <!-- Add the rest of your HTML here -->

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

Share

About the Author

dapeng lin
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

 
QuestionUnicode version Pinmemberthelvaci10-Sep-13 9:23 
GeneralNot working in VC6 Pinmembersomdipa22-Nov-09 21:53 
GeneralUpdating a record in the recordset Pinmembergaryt30-Jul-09 11:48 
Questionhow to write "delete" sql statement? Pinmemberroro_an3-Mar-08 7:07 
QuestionHow to make this work in VC++ [modified] Pinmembernishanthrao14-Jun-06 22:59 
QuestionTimeout expired PinmemberTomSyk10-Apr-06 23:57 
Generaldatabase insert query Pinmembery_naga_malleswararao4-Mar-06 5:03 
QuestionNULL Parameter PinmemberPetitPapaNoël30-Nov-05 6:02 
QuestionHow to insert/read image in MS SQL Server Pinmemberthanhthuyvn_vtajkhskjakjd12-Nov-05 21:30 
AnswerRe: How to insert/read image in MS SQL Server Pinmemberkazoo of the north29-Nov-05 12:38 
GeneralFunction Sequence Error PinmemberSBurns130-Sep-04 6:14 
GeneralRe: Function Sequence Error PinmemberTWilliams30-Sep-04 6:31 
GeneralODBC to DAO Pinmemberm0xx17-May-04 0:14 
GeneralReading Strings OutPuts PinmemberMIKE20015-Sep-03 3:36 
Laugh | :laugh: Hi Guys,
 
I found this classes very helpful, but without returning strings on on stored proc they can't be helpful.
 
Due to an hardcoded limitation in MFC RFX*** FieldExchange no strings are returned from stored procedures.
 
To Have this Functionality You only have to change the field binding in
 
Function:
CMyDatabase::BindParameters(HSTMT hstmt) (CMydatabase.cpp)
 
for all character types to:
 
case SQL_CHAR:
m_nBufferLength=SQL_NTS;
retcode = SQLBindParameter(hstmt, i+1, nColType, SQL_C_CHAR,SQL_CHAR,
m_mapParam[i].m_nLength, 0, (SQLPOINTER)
((LPCTSTR)m_mapParam[i].m_strVal),
m_mapParam[i].m_nLength, &m_nBufferLength);
break;
 
This means change the last paramater containing a zero value ("0") to
 
m_mapParam[i].m_nLength
 
and it works fine.
 
Have fun!!!
 
M.Strohmayer@2smesswert.de

GeneralRe: Reading Strings OutPuts PinmemberTarmik20-Nov-03 20:31 
GeneralRe: Reading Strings OutPuts PinmemberStephen Woods16-May-06 18:52 
Questionhow can I Use bigint and image field Pinmemberjsoft9-May-03 0:30 
GeneralThanks... PinmemberMartin Bach29-Apr-03 2:07 
GeneralSolved it myself PinmemberMartin Bach29-Apr-03 4:12 
GeneralError using Code PinmemberRough16-Jan-03 1:27 
GeneralRe: Error using Code PinsussAnonymous3-Apr-03 14:56 
GeneralCRecordSet::GetDefaultSQL PinmemberEdwin Chen11-Dec-02 20:14 
GeneralImproving the syntax PinmemberVagif Abilov19-Oct-02 14:10 
GeneralRe: Improving the syntax Pinmemberdapeng lin21-Oct-02 4:03 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.1411023.1 | Last Updated 14 Oct 2002
Article Copyright 2002 by dapeng lin
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid