Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Executing stored procedure

Posted on 2000-04-27
8
Medium Priority
?
365 Views
Last Modified: 2013-11-20
How do I execute a stored procedure (from MS SQL Server 7)?
Can it be done using CRecordset or CDatabase?
0
Comment
Question by:ranmanor
8 Comments
 

Expert Comment

by:eknguyen
ID: 2756410
Yes you can use CDatabase e.g.,

CDatabase db;
LPCTSTR lpszSQL = _T("YOUR STORED PROCEDURE NAME");

try
{
    db.Open(NULL); //or use OpenEx(...) member
    db.ExecuteSQL(lpszSQL);
}
catch(CDBException * pE)
{
    pE->ReportError();
    pE->Delete();
}

Hope this will help
0
 

Expert Comment

by:eknguyen
ID: 2756422
Yes you can use CDatabase e.g.,

CDatabase db;
LPCTSTR lpszSQL = _T("YOUR STORED PROCEDURE NAME");

try
{
    db.Open(NULL); //or use CDatabase OpenEx(...) member function
    db.ExecuteSQL(lpszSQL);
}
catch(CDBException * pE)
{
    pE->ReportError();
    pE->Delete();
}

Hope this will help
0
 

Author Comment

by:ranmanor
ID: 2757327
How do I use parameters?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Expert Comment

by:eknguyen
ID: 2757358
CDatabase db;
CString lpszSQL;
int nParam1,nParam2;
lpszSQL.Format(_T("YourStoredProcedure '%d', '%d'"),nParam1, nParam2);

try
{
    db.Open(NULL); //or use CDatabase OpenEx(...) member function
    db.ExecuteSQL(lpszSQL);
}
catch(CDBException * pE)
{
    pE->ReportError();
    pE->Delete();
}
0
 

Expert Comment

by:eknguyen
ID: 2757438

The above example is for a stored procedure that do not return any value.  If you want to execute a stored procedure that return values, refer to Recordset: Parameterizing a Recordset (ODBC) in Visual C++ Programmer's Guide.

0
 
LVL 1

Expert Comment

by:ch_vasu
ID: 2758276
CDatabase db;
CString lpszSQL;
int nParam1,nParam2;
lpszSQL.Format(_T("EXECUTE  YourStoredProcedure '%d', '%d'"),nParam1, nParam2);

try
{
    db.Open(NULL); //or use CDatabase OpenEx(...) member function
    db.ExecuteSQL(lpszSQL);
}
catch(CDBException * pE)
{
    pE->ReportError();
    pE->Delete();
}
0
 
LVL 2

Accepted Solution

by:
christophm earned 400 total points
ID: 2860906
I wrote this sometime ago to document a NUMBER! of MFC-SQL topics, I have tried to remove everything EXCEPT the parameterized query stuff, I think I got it cleaned up pretty well.     ----   christophm

You will find following in this order :
1 a (parameterized) stored procedure
2 changes to the recordset class derived from CRecordset, created by the Wizard
3 changes to the implementation file (.CPP) for the user Recordset
4 changes to the ‘DoDataExchange()’ function.
5 the implementation code to make it happen


FIRST - Here is the stored procedure. ‘project_id’ is the parameter I will pass from my program when I invoke this stored procedure.

CREATE PROCEDURE sp_Area
  @projectid char(10)
AS
(
select  area,
        Labor, Material,Purchase,
        description as description1,
  from area      
where area.project = @projectid
) order by area


SECOND - Here is the Recordset class - these are changes to the recordset class .H file.  ** NOTE that ‘m_ProjectParameter’ is added AFTER the  “//}}AFX_FIELD”  that is, it is added after the Wizard stuff !

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

// Field/Param Data
//{{AFX_FIELD(spAreaGroupDetail, CRecordset)
Cstring           m_Area;
CString           m_Labor;
CString           m_Material;
CString           m_Purchase;
CString           m_Description1;
//}}AFX_FIELD
CString m_ProjectParameter;  // added by ME


THIRD – Recordset implementation file (.CPP) changes

Note - There is no initialization for m_ProjectParameter (though having one would not cause any problem, it merely contributes nothing as later I will set m_ProjectParameter to a meaningful value).  
Note - 'm_nParams' is set to 1 at the end.  'm_nParams'  is a member of CRecordset and its default value is zero.  You won't find m_nParams in the class definition for spAreaGroupDetail, it is a data member of CRecordset.  'm_nParams' is the count of parameters, this example has only a single parameter, "m_ProjectParameter" so ' m_nParams' is one.
Note - 'm_nDefaultType'  is set to snapshot it is immaterial what it is set to, anything the compiler will take is OK – since I override it explicitly in the 'Open()' for the recordset..

spAreaGroupDetail::spAreaGroupDetail(CDatabase* pdb)
           : CRecordset(pdb)
{
//{{AFX_FIELD_INIT(spAreaGroupDetail)
m_Area = _T("");
m_Labor = _T("");
m_Material = _T("");
m_Purchase = _T("");
m_Description1 = _T("");
m_nFields = 5;
//}}AFX_FIELD_INIT
m_nParams  = 1;
m_nDefaultType = snapshot;
}


I made no no chages to the ‘GetDefaultSQL()’ function member – I am going to override it anyway in the ‘Open()’


FOURTH Changes to 'DoFieldExchange()' .  This is another place where I made the changes outside the wizard bracketed code; i.e., the change follows “//}}AFX_FIELD_MAP”  BTW - I checked the wizard after making all these changes and though the wizard would not do this work for me it accepts it and displays correctly.

After the closing {{AFXxxxx}} demarcation/comment you see there are two added lines, the 'pFX->SetFieldType()' to change the ‘FieldType’ from 'outputColumns'  to 'param' and the 'RFX_Text()' to map the parameter(s).
 
These two statements :    pFX->SetFieldType(CFieldExchange::param);
                          RFX_Text(pFX, "project", m_ProjectParameter);

The parameter "project" in the ‘RFX_Text’ statement exists (only?) to satisfy the syntax requirements of the 'RFX_Text()' function - I never use it.


void spAreaGroupDetail::DoFieldExchange(CFieldExchange* pFX)
{
//{{AFX_FIELD_MAP(spArea)
pFX->SetFieldType(CFieldExchange::outputColumn);

RFX_Text(pFX, _T("[Area]"), m_Area);
RFX_Text(pFX, _T("[Purchase]"), m_OBGT_Purchase);
RFX_Text(pFX, _T("[Labor]"), m_OBGT_Labor);
RFX_Text(pFX, _T("[Material]"), m_OBGT_Material);
RFX_Text(pFX, _T("[Description1]"), m_Description1);
//}}AFX_FIELD_MAP
 
pFX->SetFieldType(CFieldExchange::param);
RFX_Text(pFX, "project", m_ProjectParameter);
}
 
FIFTH - Here is the code for the gimme-the-data from the database.  Note the strange syntax of the SQL string, there must be one '(?)' for each replaceable parameter.

// instantiate a recordset object matching the recordset we clunked up
spArea     spRecordset(NULL);

// define a SQL string NOTE the syntax !!
CString           csSQL = "{CALL sp_AreaGroupDetail (?)}";

// set the parameter(s)
spRecordset.m_ProjectParameter = pApp->m_cCurrentProject;

// open the recordset (note that the type - snapshot - and the SQL string
// are explicitly passed as parameters thus bypassing the default recordset
// type and bypassing the default connect SQL,
spRecordset.Open(CRecordset::snapshot, csSQL);

// loop through the recordset until EOF
while (!spRecordset.IsEOF())
{
                      … do loop processing here …

  // get the next row from the rowset
  spRecordset.MoveNext();
}

// put the recordset away
    spRecordset.Close();


It took me a while to make this work --  good luck !  -  christophm
0
 
LVL 2

Expert Comment

by:christophm
ID: 2860922
I have several occurrences of
  'spAreaGroupDetail'  
in the above discussion, please change them to
  'spArea'

thanks  ! - christoph-m
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I'll describe -- and show pictures of -- some of the significant additions that have been made available to programmers in the MFC Feature Pack for Visual C++ 2008.  These same feature are in the MFC libraries that come with Visual …
Introduction: Database storage, where is the exe actually on the disc? Playing a game selected randomly (how to generate random numbers).  Error trapping with try..catch to help the code run even if something goes wrong. Continuing from the seve…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question