Solved

Executing stored procedure

Posted on 2000-04-27
8
349 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mixString challenge 36 120
Folder Comparison 12 54
My project did see openJDK that I installed. What could be the problem 7 140
Capture logon name 13 70
Introduction: Load and Save to file, Document-View interaction inside the SDI. Continuing from the second article about sudoku.   Open the project in visual studio. From the class view select CSudokuDoc and double click to open the header …
Introduction: Dialogs (2) modeless dialog and a worker thread.  Handling data shared between threads.  Recursive functions. Continuing from the tenth article about sudoku.   Last article we worked with a modal dialog to help maintain informat…
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 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