Solved

Executing stored procedure

Posted on 2000-04-27
8
352 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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 200 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need a Shell script to start a service checking the port 6 52
ORA-01403: no data found 43 105
Change owner from userA to server Local Administrators 7 66
wordcount challenge 11 129
Here is how to use MFC's automatic Radio Button handling in your dialog boxes and forms.  Beginner programmers usually start with a OnClick handler for each radio button and that's just not the right way to go.  MFC has a very cool system for handli…
This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
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.

789 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