Calling Stored Procedures

This article goes out to all MFC programmers who wish to improve their applications performance and those who have tried calling stored procedures and failed.

Your first question may be, Why use stored procedures when I can just say,

rs.Open( CRecordset::snapshot, "Select * from Clients where
account_number = '1234567'");.

Well theres 2 answers that I can think of. First off, stored procedures are tremendously fast. Secondly, stored procedures are more reliable.

From what I've tested on a 200 record table, after the initial connection stored procedures return INSTANTLY!! Where an SQL statement returns in 500ms on the initial connection and 100ms thereafter. This can be very helpful in the Client/Server environment and in any application that is database intensive.

This example calls a stored procedure with the following definition:

CREATE PROCEDURE OutputParams @InputParam char(20) , @OutputParam
char(20) = '' OUTPUT As

SELECT @OutputParam = @InputParam
RETURN 1

Basically all it does is return the same string you send it. You can define all the paramaters you like. The reason for this example was to show how to return a character output parameter. MFC does not support it. So you have to create your own RFX_Text function to support it.

If you would like to call a stored procedure that returns a recordset, its not much different. The VC help files tell you exactly how to do it. You can do a searh on "stored procedures and recordsets".

// StoredProcedure.h : header file
//

/*
Craig Lucas, CoderMan@netrox.net
*/
#include "afxdb.h"
/////////////////////////////////////////////////////////////////////////////
// StoredProcedure recordset

class StoredProcedure : public CRecordset
{
public:
	StoredProcedure(CDatabase* pDatabase = NULL);
	DECLARE_DYNAMIC(StoredProcedure)
		
	// Field/Param Data
	//{{AFX_FIELD(StoredProcedure, CRecordset)
	long m_retRETURN_VALUE;
	CString m_paramInputParam;  //The input param 
	CString m_paramOutputParam; //The output param 
	//}}AFX_FIELD
	
	// Overrides
	// ClassWizard generated virtual function overrides
	//{{AFX_VIRTUAL(StoredProcedure)
public:
	virtual CString GetDefaultConnect();    // Default connection string
	virtual CString GetDefaultSQL();    // Default SQL for Recordset
	virtual void DoFieldExchange(CFieldExchange* pFX);  // RFX support
	virtual void Move( long nRows, WORD wFetchType = SQL_FETCH_RELATIVE );
	//}}AFX_VIRTUAL
	
	// Implementation
#ifdef _DEBUG
	virtual void AssertValid() const;
	virtual void Dump(CDumpContext& dc) const;
#endif
};



// StoredProcedure.cpp : implementation file
//

#include "stdafx.h"
#include "StoredProcedure.h"

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


void AFXAPI RFX_TextOut(CFieldExchange* pFX, LPCTSTR szName,
CString& value, int nMaxLength, int nColumnType, short nScale);
/////////////////////////////////////////////////////////////////////////////
// StoredProcedure

IMPLEMENT_DYNAMIC(StoredProcedure, CRecordset)

StoredProcedure::StoredProcedure(CDatabase* pdb)
: CRecordset(pdb)
{
	//{{AFX_FIELD_INIT(StoredProcedure)
	m_retRETURN_VALUE = 0;
	m_paramInputParam = _T("");
	m_paramOutputParam = _T("");
	m_nFields = 0;
	//}}AFX_FIELD_INIT
	m_nParams = 3;
	m_nDefaultType = snapshot;
}


CString StoredProcedure::GetDefaultConnect()
{
	return _T("ODBC;DSN=CodeGuru;UID=sa;PWD=;");
}

CString StoredProcedure::GetDefaultSQL()
{
	return _T("{? = call OutputParams;1 (?,?)}"); 
	// this is the SQL String to call a stored procedure
	// 1 question mark for every parameter in the 
	// stored procedure
}

void StoredProcedure::DoFieldExchange(CFieldExchange* pFX)
{
	//{{AFX_FIELD_MAP(StoredProcedure)
	
	//}}AFX_FIELD_MAP
	
	//Make sure these are put outside the AFX_FIELD_MAP comments
	pFX->SetFieldType(CFieldExchange::outputParam); //set the field type to
	outputParam for the return value
		RFX_Long(pFX, "RETURN_VALUE", m_retRETURN_VALUE); //Bind the return value
	to the variable 
		pFX->SetFieldType(CFieldExchange::inputParam); //reset the field type to
	inputParam
		RFX_TextOut(pFX, "@InputParam", m_paramInputParam,255,SQL_CHAR,0); //call
	the new RFX_Text to get the character output params
		pFX->SetFieldType(CFieldExchange::inoutParam); // reset the field
	type to receive the output param
		RFX_TextOut(pFX, "@OutputParam", m_paramOutputParam,255,SQL_CHAR,0);
	//bind the output parameter to the variable
}

/////////////////////////////////////////////////////////////////////////////
// StoredProcedure diagnostics

#ifdef _DEBUG
void StoredProcedure::AssertValid() const
{
	CRecordset::AssertValid();
}

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

void StoredProcedure::Move( long nRows, WORD wFetchType )
{
	// Protection so that if the procedure returns no result sets, no 
	// fetch operations are attempted.
	if (m_nFields)
		CRecordset::Move(nRows, wFetchType);
	else
		m_bBOF = m_bEOF = TRUE;
}

void AFXAPI RFX_TextOut(CFieldExchange* pFX, LPCTSTR szName,
						CString& value, int nMaxLength, int nColumnType, short nScale)
{
	//This is MFC's RFX_Text function with 2 modifications:
	//   1. All unicode definitions are removed for brevity.
	//   2. A fixed version of SQLBindParamaters() was inserted
	
	ASSERT(AfxIsValidAddress(pFX, sizeof(CFieldExchange)));
	ASSERT(AfxIsValidString(szName));
	ASSERT(AfxIsValidAddress(&value, sizeof(CString)));
	
	RETCODE nRetCode;
	UINT nField;
	if (!pFX->IsFieldType(&nField))
		return;
	
	LONG* plLength = pFX->m_prs->GetFieldLengthBuffer(
		nField - 1, pFX->m_nFieldType);
	switch (pFX->m_nOperation)
	{
	default:
		pFX->Default(szName, value.GetBuffer(0), plLength,
			SQL_C_CHAR, value.GetLength(), nMaxLength);
		value.ReleaseBuffer();
		return;
		
	case CFieldExchange::BindParam:
		{
			// Preallocate to nMaxLength and setup binding address
			value.GetBufferSetLength(nMaxLength);
			void* pvParam = value.LockBuffer(); // will be overwritten if UNICODE
			*plLength = pFX->m_prs->IsParamStatusNull(nField - 1) ?
								SQL_NULL_DATA : SQL_NTS;
			
			// This is the new version of SQLBindParamaters with the 2nd to last
			param fixed.
				AFX_SQL_SYNC(::SQLBindParameter(pFX->m_hstmt, (UWORD)nField,
				(SWORD)pFX->m_nFieldType, SQL_C_CHAR, (SWORD)nColumnType,
				nMaxLength, nScale, pvParam, nMaxLength, plLength));
			
			//The reason character output params can not be returned was because
			//MS had hardcoded the max_buffer length to 0.
			
			/*   This was the old version of SQLBindParamaters.
			AFX_SQL_SYNC(::SQLBindParameter(pFX->m_hstmt, (UWORD)nField,
			(SWORD)pFX->m_nFieldType, SQL_C_CHAR, (SWORD)nColumnType,
			nMaxLength, nScale, pvParam, 0, plLength));
			*/
			
			value.ReleaseBuffer();
			
			if (nRetCode != SQL_SUCCESS)
				pFX->m_prs->ThrowDBException(nRetCode, pFX->m_hstmt);
			
			// Add the member address to the param map
			pFX->m_prs->m_mapParamIndex.SetAt(&value, (void*)nField);
		}
		return;
	case CFieldExchange::BindFieldToColumn:
		{
			// Assumes all bound fields BEFORE unbound fields
			CODBCFieldInfo* pODBCInfo =
				&pFX->m_prs->m_rgODBCFieldInfos[nField - 1];
			UINT cbColumn = pODBCInfo->m_nPrecision;
			
			switch (pODBCInfo->m_nSQLType)
			{
			default:
#ifdef _DEBUG
				// Warn of possible field schema mismatch
				if (afxTraceFlags & traceDatabase)
					TRACE1("Warning: CString converted from SQL type %ld.\n",
					pODBCInfo->m_nSQLType);
#endif // _DEBUG
				
				// Add room for extra information like sign, decimal point, etc.
				cbColumn += 10;
				break;
				
			case SQL_LONGVARCHAR:case SQL_CHAR: case SQL_VARCHAR:
				break;
				
			case SQL_FLOAT: case SQL_REAL:case SQL_DOUBLE:
				// Add room for sign, decimal point and " E +XXX"
				cbColumn += 10;
				break;
				
			case SQL_DECIMAL:case SQL_NUMERIC:
				// Add room for sign and decimal point
				cbColumn += 2;
				break;
				
			case SQL_TIMESTAMP: case SQL_DATE:case SQL_TIME:
				// May need extra space, i.e. "{TS mm/dd/yyyy hh:mm:ss}"
				cbColumn += 10;
				break;
				
			case SQL_TINYINT:case SQL_SMALLINT: case SQL_INTEGER:case SQL_BIGINT:
				// Add room for sign
				cbColumn += 1;
				break;
			}
			
			// Constrain to user specified max length, subject to 256 byte min
			if (cbColumn > (UINT)nMaxLength || cbColumn < 256)
				cbColumn = nMaxLength;
			
			// Set up binding addres
			void* pvData;
			value.GetBufferSetLength(cbColumn+1);
			pvData = value.LockBuffer();    // will be overwritten if UNICODE
			
			AFX_SQL_SYNC(::SQLBindCol(pFX->m_prs->m_hstmt, (UWORD)nField,
				SQL_C_CHAR, pvData, cbColumn+1, plLength));
			value.ReleaseBuffer();
			if (!pFX->m_prs->Check(nRetCode))
				pFX->m_prs->ThrowDBException(nRetCode);
			
			// Add the member address to the field map
			pFX->m_prs->m_mapFieldIndex.SetAt(&value, (void*)nField);
		}
		return;
		
	case CFieldExchange::Fixup:
		if (*plLength == SQL_NULL_DATA)
		{
			pFX->m_prs->SetNullFieldStatus(nField - 1);
			value.GetBufferSetLength(0);
			value.ReleaseBuffer();
		}
		else
		{
			LPTSTR lpsz = value.GetBuffer(0);
			if (pFX->m_prs->m_pDatabase->m_bStripTrailingSpaces)
			{
				// find first trailing space
				LPTSTR lpszFirstTrailing = NULL;
				while (*lpsz != '\0')
				{
					if (*lpsz != ' ')
						lpszFirstTrailing = NULL;
					else
					{
						if (lpszFirstTrailing == NULL)
							lpszFirstTrailing = lpsz;
					}
					lpsz = _tcsinc(lpsz);
				}
				// truncate
				if (lpszFirstTrailing != NULL)
					*lpszFirstTrailing = '\0';
				
			}
			value.ReleaseBuffer();
			*plLength = value.GetLength();
		}
		return;
		
	case CFieldExchange::SetFieldNull:
		if ((pFX->m_pvField == NULL &&
			pFX->m_nFieldType == CFieldExchange::outputColumn) ||
			pFX->m_pvField == &value)
		{
			if (pFX->m_bField)
			{
				// Mark fields null
				pFX->m_prs->SetNullFieldStatus(nField - 1);
				// Set string 0 length
				value.GetBufferSetLength(0);
				value.ReleaseBuffer();
				*plLength = SQL_NULL_DATA;
			}
			else
			{
				pFX->m_prs->ClearNullFieldStatus(nField - 1);
				*plLength = SQL_NTS;
			}
#ifdef _DEBUG
			pFX->m_nFieldFound = nField;
#endif
		}
		return;
		
	case CFieldExchange::MarkForAddNew:
		// can force writing of psuedo-null value (as a non-null) by setting
		field dirty
			if (!value.IsEmpty())
			{
				pFX->m_prs->SetDirtyFieldStatus(nField - 1);
				pFX->m_prs->ClearNullFieldStatus(nField - 1);
			}
			return;
			
	case CFieldExchange::MarkForUpdate:
		if (value.IsEmpty())
			pFX->m_prs->SetNullFieldStatus(nField - 1);
		else
			pFX->m_prs->ClearNullFieldStatus(nField - 1);
		pFX->Default(szName, &value, plLength,
			SQL_C_CHAR, value.GetLength(), nMaxLength);
		return;
		
	case CFieldExchange::LoadField:
		{
			// Get the field data
			CFieldInfo* pInfo = &pFX->m_prs->m_rgFieldInfos[nField - 1];
			CString* pStrCachedValue = (CString*)pInfo->m_pvDataCache;
			
			// Restore the status
			pFX->m_prs->SetFieldStatus(nField - 1, pInfo->m_bStatus);
			
			// If not NULL, restore the value and length
			if (!pFX->m_prs->IsFieldStatusNull(nField - 1))
			{
				value = *pStrCachedValue;
				*plLength = value.GetLength();
			}
			else
			{
				*plLength = SQL_NULL_DATA;
			}
			
#ifdef _DEBUG
			// Buffer address must not change - ODBC's SQLBindCol depends upon this
			void* pvBind;
			
#ifdef _UNICODE
			pvBind = pFX->m_prs->m_pvFieldProxy[nField-1];
#else // !_UNICODE
			pvBind = value.GetBuffer(0);
			value.ReleaseBuffer();
#endif
			
			if (pvBind != pInfo->m_pvBindAddress)
			{
				TRACE1("Error: CString buffer (column %u) address has changed!\n",
					nField);
				ASSERT(FALSE);
			}
#endif // _DEBUG
		}
		return;
		
	case CFieldExchange::StoreField:
		AfxStoreField(*pFX->m_prs, nField, &value);
		return;
		
	case CFieldExchange::AllocCache:
		{
			CFieldInfo* pInfo = &pFX->m_prs->m_rgFieldInfos[nField - 1];
			pInfo->m_pvDataCache = new CString;
			pInfo->m_nDataType = AFX_RFX_TEXT;
		}
		return;
		
#ifdef _DEBUG
	case CFieldExchange::DumpField:
		*pFX->m_pdcDump << "\n" << szName << " = " << value;
		return;
#endif // _DEBUG
		
	}
}



Comments

  • How to execute a single Query in Visual Studio 2010 instead of calling a store Procedure? guys pl help out..

    Posted by karthik on 06/30/2013 05:28am

    is there any way to execute a single query directly in visual studio 2010 insteas of calling stored procedure(group of queries)?

    Reply
  • Founder

    Posted by James Harris on 02/06/2013 08:23pm

    Thanks.... doing some code in low level C++ and needed to call a stored procedure. Key point to get return code from stored procedure was the name of the parameter RETURN_VALUE and that you need to override the Move method for stored procedures that do NOT return result sets. Also... make sure the parameters are defined IN THE ORDER declared in the SQL command.. including starting with the RETURN_VALUE first.

    Reply
  • Engineer

    Posted by Ts Hawkins on 12/21/2012 01:21pm

    Here's a link to an article about using the ATL OLE DB Consumer wizard in VS 2012 to accomplish the same thing. http://solutionstek.blogspot.com/

    Reply
  • how to create stored procedure for the following.

    Posted by skrajendran on 12/17/2007 03:05am

    1. Create Two Tables a. Deptmaster ( deptid int, deptname varchar(50) b. Empmaster ( Empid int, empname varchar(50) , deptid int, salary ) c. Errormaster ( Errorid int, ErrorCode varchar(20)) ; ( 1 - Sucessfully Inserted , 2 - Sucessfully Updated, 3- Can't Delete the data , 4-DeptName Already Exists, 5- Empname Already Exists ) // Create a SP for insert Deptmaster. a. Deptmaster.deptname is unique. b. If I pass same input once again , Show ErrorId - 4 , If its inserted into the table then show Error_id = 1 )

    Reply
  • OCI Stored Procedure : How to bind variables to

    Posted by Legacy on 09/03/2003 12:00am

    Originally posted by: Nguyen Nhat Quang

    My stored procedure to get a person name written in PL/SQL environment is following:
    
    

    FUNCTION GET_PERSON_NAME
    ( PERSON_ID IN NUMBER)
    RETURN VARCHAR2 IS

    F_NAME VARCHAR(30);
    L_NAME VARCHAR(30);
    NAME VARCHAR(60);

    BEGIN
    SELECT FIRST_NAME, LAST_NAME INTO F_NAME,L_NAME FROM PERSON WHERE ID = PERSON_ID ;
    NAME := F_NAME || ' ' || L_NAME;
    RETURN NAME;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RETURN 'NONAME_BYFUNCTION' ;
    END;

    I wrote a small VC++ program to get the result for checking. I created PL/SQL block and then carefully prepared it :

    BEGIN
    :result := GET_PERSON_NAME(:person_id);
    END;

    I have two variables in VC++ program:

    char result[32];
    unsigned int per_id;

    and want to get the person name in to the variable 'result'. So, I bound the variables in my VC++ program to the PL/SQL function by calling the OCI function OCIBindByPos():

    OCIBindByPos(...,1,result,SQLT_CHR,strlen(result),...);
    OCIBindByPos(...,2,&per_id,SQLT_NUM,sizeof(per_id),...);

    However, after executing the PL/SQL block, the result that i received is not as expected. There are more than one person name in the result string. The length of string becomes larger than the actual length that I previously obtained by calling the same function in SQL Navigator 4.0, as following:

    normal: GET_PERSON_NAME(2) ---> the result is: "NGUYEN VIET HUNG"
    my case: GET_PERSON_NAME(2) ---> the result is: "NGUYEN VIET HUNG...NONAME_BYFUNCTION...NONAME....."

    I think I made a fault in the function OCIBindByPos() but I can't find where my fault is.
    How can I get the actual length of the result string so I can display it correctly? Pls help me. Thanks in advance.

    Reply
  • Blob, SQL, and ODBC

    Posted by Legacy on 04/01/2003 12:00am

    Originally posted by: Chuck Iyer

    I have an image (as a column) in a database. It contains just binary data. I would like to get the size of this blob, and also be able to get at each byte of the data using MS SQL, and ODBC. If anyone out there has a model, I would be ever thankful.

    Reply
  • need source code plz

    Posted by Legacy on 02/17/2003 12:00am

    Originally posted by: Uno tursadi

    I need the source code plz
    Thank u very much
    God bless you :-)

    Reply
  • The Order Clause as Parameter to Stored Procedure, MS SQL

    Posted by Legacy on 12/30/2002 12:00am

    Originally posted by: Chuck Iyer

    Hi,

    I have a VC++ program which gathers user input to get results from a database using MS SQL. A stored procedure is called and is passed many parameters and everything works fine. Now I want to give the user the ability to sort on the basis of columns as specified in the input window of the user's interface. The program will then create an ORDER BY clause.

    I would like to send the ORDER BY CLAUSE parameters as an
    input parameter to the stored procedure. The stored
    procedure will have something like

    SELECT ......
    FROM .....
    WHERE ....
    ORDER BY "input string"

    I want to specify the "input string" as a parameter as well
    in addition to the parameters that the stored procedure is
    already being passed.

    This approach does not work, and the message I get when I
    do syntax checking is a pop-up screen with the
    title "Microsoft SQL-DMO(ODBC SQL State:42000) and the text
    of the error message is "Error 1008: The SELECT item
    identified by the ORDER BY number 1 contains a variable as
    part of the expression identifying a column position.
    Variables are only allowed when ordering by an expression
    referencing a column name."

    Is there a way I can specify the ORDER BY clause string as
    a parameter to the stored procedure? Help would be
    appreciated. Regards.

    Chuck Iyer

    Reply
  • how to program "Extended Stored Proc"

    Posted by Legacy on 09/17/2002 12:00am

    Originally posted by: litowen

    I must write a program about "Extended Stored Proc" to transfer data from a SQL Server to a file.But I don't know how to write it.Visual c++ has "Extended Stored Proc Wizard",but I can't use it.Who can help me?

    Reply
  • Shit like ODBC

    Posted by Legacy on 09/10/2002 12:00am

    Originally posted by: Manoj Singh

    Its surprising to note that ODBC provides such an entangled method of calling stored procedures. I think its a microsoft tactics to develop ODBC API in such a way that calling stored procedure becomes extremely difficult. Result, programers community will avoid calling stored procedures. Side effect, Not calling stored procedure will leave only one option open - Type in SQL statements in C++ code. The moment you type in SQL statements in C++ code, your code becomes DBMS (Oracle, MSSQL, etc) dependant. Because SQL is not same on all leading DBMS in market. For example in SELECT statement for Oracle database we often use TO_CHAR and TO_DATE functions, which are not executable on MSSQL database. Its really high time, we should stop using shit like technology called ODBC in application development. Again the new ADO classes are so difficult to use in VC++ that your ADO based VC++ program will either stuck during compile time or run time.

    Reply
  • Loading, Please Wait ...

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

Top White Papers and Webcasts

  • The explosion in mobile devices and applications has generated a great deal of interest in APIs. Today's businesses are under increased pressure to make it easy to build apps, supply tools to help developers work more quickly, and deploy operational analytics so they can track users, developers, application performance, and more. Apigee Edge provides comprehensive API delivery tools and both operational and business-level analytics in an integrated platform. It is available as on-premise software or through …

  • Live Event Date: September 10, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild". This loop of continuous delivery and continuous feedback is …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds