Stored Procedures with Output Parameters

[Mitch Wittneben]

I have a problem calling a stored procedure that contains output parameters from a VC 6.0 app. I normally call an sp with the following syntax:

CString strSQL="{Call spTest(1)}";
m_App->m_Database->ExecuteSQL(strSQL);

this works fine, but now I need to call an sp with output parameters. My sp is defined as follows:

create procedure spAddTransaction
@CustomerID int, @record int output
as
insert Transactions (CustomerID) values (@CustomerID)
select @record=@@identity
go

and I am trying to call it in VC with the following code:

CString strSQL="{Call spAddTransaction(1,test)}";
try{
m_App->m_Recordset->Open(CRecordset::dynaset,m_App->m_strSQL,CRecordset::none);
}
catch (CDBException* e){
AfxMessageBox(e->m_strError,MB_OK | MB_ICONINFORMATION);
e->Delete();
return;
}
while (!m_App->m_Recordset->IsEOF()){
m_App->m_Recordset->GetFieldValue((short)0,m_App->m_varField);
m_App->m_Recordset->MoveNext();
}
m_App->m_Recordset->Close();

but it does not work complaining that test is an undefined cursor. Is there a way without deriving a class for the sp to call an sp without ouput
parameters. Thanks in advance.

--mitch

[Srinath Reddy]

I think this will help u

bye
Srinath Reddy
for ex:
spAddTransaction test;
create a variable and call function
ExecDirect(); after that the output is caught in a output variable

// spAddTransaction.cpp : implementation file
//

#include "StdAfx.h"
#include "spAddTransaction.h"

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

/////////////////////////////////////////////////////////////////////////////
// spAddTransaction

IMPLEMENT_DYNAMIC(spAddTransaction, CRecordset)

spAddTransaction::spAddTransaction(CDatabase* pdb)
: CRecordset(pdb)
{
// Param Init
m_RETURN_VALUE = -1;
m_CustomerIDParam = 0;
m_recordParam = 0;
m_nParams = 3;

m_nDefaultType = snapshot;
}

CString spAddTransaction::GetDefaultConnect()
{
return ODBC_DSN_INFO;
}

CString spAddTransaction::GetDefaultSQL()
{
return _T("{? = CALL spAddTransaction (?,?)}");
}

void spAddTransaction::DoFieldExchange(CFieldExchange* pFX)
{
// RFX output param mapping
pFX->SetFieldType(CFieldExchange::outputParam);
RFX_Long(pFX, _T("[RETURN_VALUE]"), m_RETURN_VALUE);

// RFX input param mapping
pFX->SetFieldType(CFieldExchange::inputParam);
RFX_Long(pFX, _T("[CustomerID]"), m_CustomerIDParam);

// RFX input_output param mapping
pFX->SetFieldType(CFieldExchange::inoutParam);
RFX_Long(pFX, _T("[record]"), m_recordParam);
}

void spAddTransaction::Move(long lRows, WORD wFetchType)
{
m_bBOF = m_bEOF = TRUE; // By pass CRecordset
}

BOOL spAddTransaction::ExecDirect()
{
return Open(AFX_DB_USE_DEFAULT_TYPE, NULL, CRecordset::executeDirect);
}

/////////////////////////////////////////////////////////////////////////////
// spAddTransaction diagnostics

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

void spAddTransaction::Dump(CDumpContext& dc) const
{
CRecordset::Dump(dc);
}
#endif //_DEBUG
header file
#if !defined(SPCW_SPADDTRANSACTION_H__FD99A3F0_2FB0_11D3_9D61_0008C7F34B67__INCLUDED_)
#define SPCW_SPADDTRANSACTION_H__FD99A3F0_2FB0_11D3_9D61_0008C7F34B67__INCLUDED_

#if _MSC_VER >= 1000
#pragma once
#endif // _MSC_VER >= 1000

// spAddTransaction.h : interface file
//

/////////////////////////////////////////////////////////////////////////////
// spAddTransaction

class spAddTransaction : public CRecordset
{
public:
spAddTransaction(CDatabase* pDatabase = NULL);
DECLARE_DYNAMIC(spAddTransaction)

// Param data
long m_RETURN_VALUE;
long m_CustomerIDParam;
long m_recordParam;

BOOL ExecDirect(); // For one time execution

// Overrides
virtual CString GetDefaultConnect(); // Default connection string
virtual CString GetDefaultSQL(); // Default SQL for Recordset
virtual void DoFieldExchange(CFieldExchange* pFX); // RFX support
virtual void Move(long lRows, WORD wFetchType = SQL_FETCH_RELATIVE);

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

#endif // !defined(SPCW_SPADDTRANSACTION_H__FD99A3F0_2FB0_11D3_9D61_0008C7F34B67__INCLUDED_)