Advertisement

04.07.2008 at 10:17AM PDT, ID: 23302039
[x]
Attachment Details

How to properly get a String output parameter from Stored Procedure

Asked by alexatsearidge in Windows MFC Programming, MS SQL Server

Tags:

I want to obtain a string value as an output parameter from a Stored procedure.
It seems that i have to use SysAllocString with a variant_t. After lots of experiments, I got this piece of code working. However I do not understand why I cant use the input to the CreateParameter to get data from that parameter (variant_t = parameter->value)

I think my code looks messy and I want to know the real answer to buildint the SP command, and getting data from it without causing leaksStart Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:

INT CDbControl::GetAppConfig_NeedRestart(INT AppID, BOOL& needRestart, CString& originator, CString& comment)
{
	INT id = -1;
	ADODB::_CommandPtr p_cmd;
 
	if (Create_a_SP_Command(p_cmd, m_main_Connection, _T("dbo.spGetRestartFlag")) == FALSE)
		return id;
	variant_t vOriginatorInput, vCommentInput;
	vOriginatorInput.vt = VT_BSTR;
	vOriginatorInput.bstrVal = SysAllocString(L"");
	
	vCommentInput.vt = VT_BSTR;
	vCommentInput.bstrVal = SysAllocString(L"");
	
	try
	{	
		//@Application ID - Input
		VARIANT vApp_Id;
		vApp_Id.vt = VT_INT;
		vApp_Id.intVal = AppID; 
		p_cmd->Parameters->Append(p_cmd->CreateParameter(_bstr_t(_T("AppId")),ADODB::adInteger,ADODB::adParamInput,-1,vApp_Id));
		
		//@Need Restart - Output
		VARIANT vNeedRestart;
		vNeedRestart.vt = VT_BOOL;
		vNeedRestart.boolVal = FALSE;
		ADODB::_ParameterPtr p_needRestartParm = p_cmd->CreateParameter(_bstr_t(_T("@NeedRestart")),ADODB::adBoolean,ADODB::adParamOutput,-1,vNeedRestart);
		p_cmd->Parameters->Append(p_needRestartParm);
		
		//@Originator - Output	
		ADODB::_ParameterPtr p_originatorParm = p_cmd->CreateParameter(_bstr_t(_T("@Originator")),ADODB::adVarChar,ADODB::adParamOutput,255,vOriginatorInput);
		p_cmd->Parameters->Append(p_originatorParm);
		
		//@Comment ID - Output
		ADODB::_ParameterPtr p_commentParm = p_cmd->CreateParameter(_bstr_t(_T("@Comment")),ADODB::adVarChar,ADODB::adParamOutput,4000,vCommentInput);
		p_cmd->Parameters->Append(p_commentParm);
		
		p_cmd->Execute(NULL, NULL , adCmdStoredProc); 
		
		if(p_originatorParm->Value.vt != VT_NULL && p_originatorParm->Value.vt != VT_EMPTY)
		{
			variant_t vOriginatorOutput;
			vOriginatorOutput.vt = VT_BSTR;
			vOriginatorOutput.bstrVal = SysAllocString(L"");
			vOriginatorOutput = p_originatorParm->Value; 
			originator = CString(vOriginatorOutput.bstrVal).Trim();
			SysFreeString (vOriginatorOutput.bstrVal);
			vOriginatorOutput.bstrVal = NULL;
		}
		if(p_commentParm->Value.vt != VT_NULL && p_commentParm->Value.vt != VT_EMPTY)
		{	
			variant_t vCommentOutput;
			vCommentOutput.vt = VT_BSTR;
			vCommentOutput.bstrVal = SysAllocString(L"");
			vCommentOutput = p_commentParm->Value;
			comment = CString(vCommentOutput.bstrVal).Trim();
			SysFreeString (vCommentOutput.bstrVal);
			vCommentOutput.bstrVal = NULL;
		}
		if(p_needRestartParm->Value.vt != VT_NULL && p_needRestartParm->Value.vt != VT_EMPTY)
		{
			vNeedRestart = p_needRestartParm->Value;
			needRestart = vNeedRestart.boolVal;
		}
 
		id = 0;
	}
	catch(_com_error& e)
	{
		CString err;
		_bstr_t bstrSource(e.Source());
		_bstr_t bstrDescription(e.Description());
 
		// Print COM errors. 
		err.Format(_T("Error\n"));
		err.AppendFormat(_T("\tCDbControl::GetAppConfig_NeedRestart Code = %08lx\n"), e.Error());
		err.AppendFormat(_T("\tCode meaning = %s\n"), e.ErrorMessage());
		err.AppendFormat(_T("\tSource = %s\n"), (LPCSTR) bstrSource);
		err.AppendFormat(_T("\tDescription = %s\n"), (LPCSTR) bstrDescription);
		TRACE(err);
		theLog.Log(err, EVENTLOG_WARNING_TYPE, DATABASE, 0, WARNING);
		id = -1;
	}
	catch (...)
	{
		CString err;
		err.Format(_T("Unknown error GetAppConfig_NeedRestart"));
		theLog.Log(err, EVENTLOG_ERROR_TYPE, DATABASE, 0 , WARNING);	
		id = -1;
	}
 
	SysFreeString (vOriginatorInput.bstrVal);
	SysFreeString (vCommentInput.bstrVal);
	
	CloseAndClean_Command(p_cmd);
 
	return id;
}
[+][-]04.07.2008 at 01:08PM PDT, ID: 21300123

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.08.2008 at 01:59PM PDT, ID: 21309554

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.08.2008 at 05:42PM PDT, ID: 21310959

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Windows MFC Programming, MS SQL Server
Tags: C++, MFC
Sign Up Now!
Solution Provided By: LordOfPorts
Participating Experts: 2
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628