A class to call stored procedures that do not return records

The class CRecordset provides a well documented way to call stored procedures that return records. If a stored procedure does not return records and has no output parameters, the CDatabase member ExecuteSQL can be used to call it. On the other hand, the documentation explicitly states that the MFC classes do not support for calling stored procedures that do not return records and have output parameters.

The problem I was trying to solve when started this work, was to obtain the automatically generated value for an SQL Server identity column, after a CRecordset update. What I did was to create a stored procedure that does the INSERT and returns the desired value:

CREATE PROCEDURE sp_insertrecord @record_id int OUTPUT, @field1 int, @field2 datetime, @field3 varchar
AS
INSERT INTO mytable(first_field, second_field, third_field) VALUES(@field1, @field2, @field3) 
IF @@ERROR = 0
BEGIN
  SELECT @record_id = @@IDENTITY
  RETURN 0
END
ELSE
BEGIN
  SELECT @record_id = 0
  RETURN -100
END

Then I "only" had to call the stored procedure and get the Id for the new record. Here was where I found that MFC classes do not support these kind of stored procedures, and started looking somewhere else, including www.codeguru.com.

There is already an article in the database section (contributed by Craig Lucas) that shows how to call stored procedures from a CRecordset derived class. The class presented here is an alternative to that of Craig, which IMHO is less prone to errors due to the use of private inheritance. The class is easy to use for those that have used the MFC database classes before. You just forget everything (members and functions) related with fields and use the SetFieldType function of the CFieldExchange class with care inside your DoFieldExchange override.

One final note before providing the source files and an example. The RFX_Text function can not be used for output parameters (see the article by Craig Lucas in the database section) so I modified it and renamed as RFX_String. You can use RFX_String everywhere in place of RFX_Text, or keep using RFX_Text for input parameters. For text return values or output parameters, you must use RFX_String. The function RFX_Date was overloaded to allow COleDateTime parameters. The overload provided is the same of the article by Dan Querciagrossa also in the database section, with some bugs corrected.

Using the CStoredProcResult class to call sp_insertrecord stored procedure:

#if !defined(_INSERTPROC_H_)
#define _INSERTPROC_H_

// RecordInsertProc.h : header file
//

///////////////////////////////////////////////////////////////////////
// CRecordInsertProc stored procedure

class CRecordInsertProc : public CStoredProcResult
{
public:
	CRecordInsertProc(CDatabase* pDatabase = NULL);
	DECLARE_DYNAMIC(CRecordInsertProc)

// Param Data
	long			m_record_id;
	long			m_field1;
	COleDateTime	m_field2;
	CString		m_field3;

	long m_result;

// Overrides
	// ClassWizard generated virtual function overrides
	//{{AFX_VIRTUAL(CRecordInsertProc)
	public:
	virtual CString GetDefaultConnect();    // Default connection string
	virtual CString GetDefaultSQL();    // Default SQL for Recordset
	virtual void DoFieldExchange(CFieldExchange* pFX);  // RFX support
	//}}AFX_VIRTUAL

// Implementation
#ifdef _DEBUG
	virtual void AssertValid() const;
	virtual void Dump(CDumpContext& dc) const;
#endif
};

//{{AFX_INSERT_LOCATION}}
// Microsoft Developer Studio will insert additional declarations immediately before the previous line.

#endif // !defined(_INSERTPROC_H_)


// RecordInsertProc.cpp : implementation file
//

#include "stdafx.h"
#include "StoredProc.h"
#include "RecordInsertProc.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CRecordInsertProc

IMPLEMENT_DYNAMIC(CRecordInsertProc, CStoredProcResult)

CRecordInsertProc::CRecordInsertProc(CDatabase* pdb)
	: CStoredProcResult(pdb)
{
	m_record_id = 0;
	m_field1 = 0;
	m_field3 = _T("");
	
	m_result = 0;

	m_nParams = 5;
}


CString CRecordInsertProc::GetDefaultConnect()
{
	return _T("ODBC;DSN=");
}

CString CRecordInsertProc::GetDefaultSQL()
{
	return _T("{? = call sp_insertRecord(?, ?, ?, ?)}");
}

void CRecordInsertProc::DoFieldExchange(CFieldExchange* pFX)
{
	pFX->SetFieldType(CFieldExchange::outputParam);
	RFX_Long(pFX, _T("RETURN_VALUE"), m_result);

	pFX->SetFieldType(CFieldExchange::inoutParam);
	RFX_Long(pFX, _T("@record_id"), m_record_id);

	pFX->SetFieldType(CFieldExchange::inputParam);
	RFX_Long(pFX, _T("@field1"), m_field1);
	RFX_Date(pFX, _T("@field2"), m_field2);
	RFX_String(pFX, _T("@field3"), m_field3);
}

/////////////////////////////////////////////////////////////////////////////
// CRecordInsertProc diagnostics

#ifdef _DEBUG
void CRecordInsertProc::AssertValid() const
{
	CStoredProcResult::AssertValid();
}

void CRecordInsertProc::Dump(CDumpContext& dc) const
{
	CStoredProcResult::Dump(dc);
}
#endif //_DEBUG

Now you do the following to execute the stored procedure (no error checking):

CRecordInsertProc InsertProc(&db); 

InsertProc.m_field1 = intValue;
InsertProc.m_field2 = COleDateTimeValue; 
InsertProc.m_field3 = TextValue;
InsertProc.Open()
if InsertProc.m_result==0
// record succesfully inserted, the id is in InsertProc.m_record_id
else
// error inserting record, InsertProc.m_result is -100

Download Source Code


Last updated: 18 May 1998



Comments

  • getting error on specifying parameters

    Posted by Legacy on 11/06/2003 12:00am

    Originally posted by: Rekha

    Hello, 1st of all thanku for the help provided in calling an Oracle stored proc from vc code.

    But im getting an error when i make a call stmt from the GetdefaultSQL function.My call GetDefaultSQL func goes like this:

    CString CRecordInsertProc::GetDefaultSQL()
    {
    return _T("{call procedurename(parameter1, parameter2, parameter3, parameter4, parameter5, parameter6, parameter7)}");
    }

    error im getting is :
    PLS -00201 identifier 'parameter1' must be declared.
    PL-SQL - statement ignored.

    i have very well declared all the parameters in the storedproc.

    same error occurs if i try giving any other parameter as my 1st argument. so im doing some mistake while calling the proc..but where..im unable to trace.

    So someone please help.
    thanks once again.

    Reply
  • A class to call stored procedures that do not return records

    Posted by Legacy on 06/05/2002 12:00am

    Originally posted by: Jack Moore

    Please Help!  
    
    I also have a requirement to do inserts and just need the record_id back. I am having trouble though with getting the return values back from the Stored procedure.

    The inserts happen and thus the SP is being called and not throwing exceptions.

    What part of the CRecordInsertProc class does the binding of return data to the calling application? The DoFieldExchange that gets called looks llike your example.
    void CRecordInsertProc::DoFieldExchange(CFieldExchange* pFX)
    {
    pFX->SetFieldType(CFieldExchange::outputParam);
    RFX_Long(pFX, _T("RETURN_VALUE"), m_result);

    pFX->SetFieldType(CFieldExchange::inoutParam);
    RFX_Long(pFX, _T("@record_id"), m_RecordKey);

    pFX->SetFieldType(CFieldExchange::inputParam);
    RFX_Long(pFX, _T("@aliaskey"), m_Alias_Key);
    RFX_Long(pFX, _T("@wordcnt"), m_WordCount);
    RFX_Long(pFX, _T("@bidkey"), m_BIDKey);
    RFX_Long(pFX, _T("@tod"), m_TOD);
    RFX_Long(pFX, _T("@tod_ext"), m_TOD_Ext);
    RFX_Long(pFX, _T("@fcmodekey"), m_FC_Mode_Key);
    RFX_Long(pFX, _T("@unitid"), m_Unit_ID);
    RFX_Long(pFX, _T("@nodeid"), m_Node_ID);
    RFX_Long(pFX, _T("@crewkey"), m_CrewKey);
    RFX_Long(pFX, _T("@datafunctkey"), m_DataFunctionKey);
    RFX_Long(pFX, _T("@calctod"), m_CALC_TOD);
    RFX_Long(pFX, _T("@calctod_ext"), m_CALC_TOD_Ext);
    RFX_String(pFX, _T("@deftod"), m_DefTOD);
    }

    As I said, the inserts are successful and recordIds are generated but never returned to the member variables in mt appl.

    Reply
  • Great class, pity the modern ODBC drivers aren't backwardly compatible.

    Posted by Legacy on 01/13/2002 12:00am

    Originally posted by: Nicholas Hynes

    I've been attempting to use this class in a project I'm on.

    It appears that the output parameters are never being set (coming up as 0xCCCCCCCC in the debugger).

    The ODBC code is quite esoteric to me. I've noticed a few things which might give someone out there a clue as to what is happening:


    The modern implementation of PreBindFields( ), which can be found in the latest ODBC drivers, is empty:


    void CRecordset::PreBindFields()
    {
    // Do nothing
    }


    The original concurrency requested wasn't available:
    " Warning: Driver does not support requested concurrency."


    My code is binding to all parameters okay (no error is returned), and the input parameter is getting passed in okay, but none of the output parameters (including the return status) is getting set when the stored proc returns without error.

    I think this is a problem with the new drivers, since I've seen mention of problems with binding output parameters to stored procs using ODBC.

    Any help muchly appreciated.


    - Nick Hynes

    Reply
  • How to use them?

    Posted by Legacy on 03/14/1999 12:00am

    Originally posted by: nadir

    Hi,
    I have been trying to call stored procedure in Oracle.
    And meet the same problem.
    It is quite of pleasure that I found the very class.
    While after puting it into my project,
    something strange.
    First, no corresponding class shown when I add those source code in to the project: nothing happens in the classview;

    Second, After managing to clean off those grammatical errors.
    some fatal errors appear as follows:
    RecordInsertProc.obj : error LNK2001: unresolved external symbol "public: virtual int __thiscall CStoredProcResult::Open(char const *)" (?Open@CStoredProcResult@@UAEHPBD@Z)
    hellView.obj : error LNK2001: unresolved external symbol "public: virtual __thiscall CStoredProcResult::~CStoredProcResult(void)" (??1CStoredProcResult@@UAE@XZ)
    RecordInsertProc.obj : error LNK2001: unresolved external symbol "public: virtual __thiscall CStoredProcResult::~CStoredProcResult(void)" (??1CStoredProcResult@@UAE@XZ)
    RecordInsertProc.obj : error LNK2001: unresolved external symbol "public: static struct CRuntimeClass const CStoredProcResult::classCStoredProcResult" (?classCStoredProcResult@CStoredProcResult@@2UCRuntimeClass@@B)
    RecordInsertProc.obj : error LNK2001: unresolved external symbol "public: virtual int __thiscall CStoredProcResult::Requery(void)" (?Requery@CStoredProcResult@@UAEHXZ)
    RecordInsertProc.obj : error LNK2001: unresolved external symbol "public: __thiscall CStoredProcResult::CStoredProcResult(class CDatabase *)" (??0CStoredProcResult@@QAE@PAVCDatabase@@@Z)
    RecordInsertProc.obj : error LNK2001: unresolved external symbol "public: virtual void __thiscall CStoredProcResult::AssertValid(void)const " (?AssertValid@CStoredProcResult@@UBEXXZ)
    RecordInsertProc.obj : error LNK2001: unresolved external symbol "public: virtual void __thiscall CStoredProcResult::Dump(class CDumpContext &)const " (?Dump@CStoredProcResult@@UBEXAAVCDumpContext@@@Z)
    Debug/hell.exe : fatal error LNK1120: 7 unresolved externals
    Error executing link.exe.

    I am still confused.
    Could you give us an sample project?
    Thanks a lot.

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds