Solved

Call a Stored Procedure!

Posted on 2001-08-23
12
402 Views
Last Modified: 2013-11-20
Hi,

Now I am using the CDatabase & CRecordSet classes to manipulate the database.

How can I call a stored procedure in MSSQL server in MFC?

Also, from calling that procedure, a string will be returned! How can I get this string?

Thanks!
0
Comment
Question by:cplau
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 4

Expert Comment

by:jtwine100697
ID: 6417542
Using the MSDN Library (or using the search on MS' site), search for "CRecordset AND stored AND procedure".  That should give you all of the details that you need.  (And will not cost you points!)

-=- James.
0
 
LVL 30

Expert Comment

by:SteveGTR
ID: 6417978
You've got to check out the stored procedure wizard on Code Projects. It's great for working with stored procedures in VC++/MFC:

http://www.codeproject.com/database/spcw.asp
0
 

Expert Comment

by:ranmanor
ID: 6418728
you can call a stored procedure like this:

db.ExecuteSQL("Stored_Procedure_Name Param1, Param2,....");

db is an open CDatabase variable.

I don't know how to get the returned value, sorry.

0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6418921
Here is a useful piece of code.  It obtains one value from any SQL statement.  This turns out to be useful in all kinds of situations where you don't want to define a formal recordset using the ClassWizard.

//=-=-=-=-=-=-=-=-=-=-=--=-= class Definition
#include <afxdb.h>
class CRsGetOneValue : public CRecordset
{
     public:
     CRsGetOneValue(CDatabase* pDatabase, LPCSTR szSQL );
     CString     m_sSQL;
     CString     m_sRetValue;
     virtual void DoFieldExchange(CFieldExchange* pFX);
     CString GetDefaultSQL() {return m_sSQL;};
};
//=-=-=-=-=-=-=-=-=-=-=--=-= class Implementation

CRsGetOneValue::CRsGetOneValue(CDatabase* pdb, LPCSTR szSQL )
: CRecordset(pdb), m_sSQL(szSQL)
{
     m_sRetValue="";
     m_nFields= 1;
     m_nDefaultType= snapshot;
}
void CRsGetOneValue::DoFieldExchange(CFieldExchange* pFX)
{
     pFX->SetFieldType(CFieldExchange::outputColumn);
     RFX_Text(pFX, "", m_sRetValue);
}

//=-=-=-=-=-=-=-=-=-=-=--=-= typical usage
void CD10WebDlg::OnButton1()
{
    CDatabase db;
    BOOL fRet= db.Open( "", FALSE, FALSE, "ODBC;DSN=MyDSN;UID=USER;PWD=PASSWORD", TRUE);
   
//    CString sSQL= "SELECT sNameLast FROM Inquiry";
//    CString sSQL= "SELECT COUNT(*) FROM Inquiry";
    CString sSQL= "{call OverDueAccts}";

    CRsGetOneValue rs( &db, sSQL );
    rs.Open( AFX_DB_USE_DEFAULT_TYPE, 0, CRecordset::executeDirect );

    TRACE( rs.m_sRetValue + "\r\n" );
}

=-=-=-=-=-=-=-=-=-=-=-

As you can see, to execute a stored procedure, just pass
   "{call spName}"
in the ctor (no spaces between { and call).  You can also use any SELECT statement.  The single returned value will be a string.  If you need an int, just use (for instance):

   int nRet= atoi( rs.m_sRetValue );

One other note: If you expect the return value to be larger than 255 bytes, then you need to modify the RFX_Text call:

   RFX_Text(pFX, "", m_sRetValue, 2048);

-- Dan
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6443812
hi ranmanor,
Do you have any additional questions?  Do any comments need clarification?

-- Dan
0
 

Expert Comment

by:ranmanor
ID: 6444292
no, thanks.
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.

 
LVL 4

Expert Comment

by:jtwine100697
ID: 6444642
> no, thanks.

Just a thought: now might be the time to accept DanRollins' comment as an answer... :)

-=- James.
0
 

Expert Comment

by:ranmanor
ID: 6444752
true.
tell it to cplau .
0
 
LVL 4

Expert Comment

by:jtwine100697
ID: 6444834
D'oh!

Got confused for a second there...! :O

-=- James.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6446453
hi ranmanor,
Sorry!
hi jtwine,
Sorry!

hi cplau,
Do you have any additional questions?  Do any comments need clarification?

-- Dan
0
 
LVL 49

Accepted Solution

by:
DanRollins earned 100 total points
ID: 6480644
hi cplau,
Do you have any additional questions?  Do any comments need clarification?

-- Dan
0
 
LVL 1

Expert Comment

by:Computer101
ID: 6703244
Comment from DanRollins accepted as answer.

Thank you
Computer101
Community Support Moderator
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
Excel Use VBA to get user's Mac Address for their computer 5 107
pre4 challenge 19 93
withoutTen challenge 14 97
noX challenge 17 91
Introduction: Displaying information on the statusbar.   Continuing from the third article about sudoku.   Open the project in visual studio. Status bar – let’s display the timestamp there.  We need to get the timestamp from the document s…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 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

11 Experts available now in Live!

Get 1:1 Help Now