Solved

Executing stored procedure

Posted on 2000-04-27
8
348 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Unix / Linux grid computing 5 147
iSeries DB2 Query 2 91
modThree challenge 4 79
Window placement 17 75
Introduction: Dynamic window placements and drawing on a form, simple usage of windows registry as a storage place for information. Continuing from the first article about sudoku.  There we have designed the application and put a lot of user int…
Introduction: The undo support, implementing a stack. Continuing from the eigth article about sudoku.   We need a mechanism to keep track of the digits entered so as to implement an undo mechanism.  This should be a ‘Last In First Out’ collec…
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.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now