Solved

Executing stored procedure

Posted on 2000-04-27
8
356 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Introduction: Ownerdraw of the grid button.  A singleton class implentation and usage. Continuing from the fifth article about sudoku.   Open the project in visual studio. Go to the class view – CGridButton should be visible as a class.  R…
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 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