Syntax for calling a SQL Server Stored Proc

[Technokrat]

Could anybody tell me (or provide an example of) the proper syntax for calling a SQL Server Stored Procedure from a Visual C++ 6.0 application?

Thanks in advance.

 

[BlackDice]

You should be able to use "EXEC mySproc". It might depend on if you're using ODBC directly or an MFC class. If this doesn't help, let me know and I'll try to dig up some of my old code

bdiamond

 

[Technokrat]

Could you provide specific examples for each? Is one method better than the other(speedwise/security)?

 

[BlackDice]

I use ADO myself because I'm using to using the ADO in VB. In order to use ADO you have to add the following to your stdafx.h file at the bottom:


#import "C:\Program Files\CommonFiles\System\ado\msado15.dll" \
no_namespace \
rename("EOF", "adoEOF")


you'll have to find wherever your dll is since your path may be different. This makes header files for your Ado classes (a .tli and a .tlh file)

Then you set up a connection object like this:

_ConnectionPtr g_Cnn;
CoInitialize(NULL);
g_Cnn.CreateInstance(__uuidof(Connection));
strconn = "Provider=SQLOLEDB.1;Password=bugreporter;Persist Security Info=True;User ID=bugreporter;Initial Catalog=BugReporter;Data Source=GLITTER";

//open connection
HRESULT hr = g_Cnn->Open(strconn.AllocSysString(),"","",0);
if(FAILED(hr))
{
AfxMessageBox("Could not open connection.\r\nApplication will now terminate.");
return FALSE;
}

of course your connection string will be different also. then, to open a recordset, do the following. This is kinda sloppy I know, but I'm still working on it. Hopefully it will provide you with some help, though!! Don't worry about the 'row' stuff, that's some stuff I used for putting the strings into a grid control I have.



_RecordsetPtr rst;
VARIANT *var = NULL;
int row,nRowToSelect,nLastBugID;
_variant_t vStatus,vReturnTime,vStatusID;
rst.CreateInstance(__uuidof (Recordset));
rst->CursorLocation = adUseClient;

CString strDescription,strSeverity,strToName,strFromName,strReturnStatus,strTimeSubmitted,strTimeReturned;
int nBugID,nProjectID,nStatusID,nSubmittedToID,nSubmittedByID;
BOOL bResolved;

rst = theApp.g_Cnn->Execute(strSQL.AllocSysString(),var,0L);
FieldsPtr pFields ;
FieldPtr pBugID,pDescription,pSeverity,pToName,pFromName,pReturnStatus,pReturnReasonNote,pTimeSubmitted,pTimeReturned
,pResolved,pProjectID,pStatusID,pSubmittedToID,pSubmittedByID;
if(!rst->adoEOF)
{

pFields = rst->Fields;
}
int nUseColors = theApp.GetProfileInt("Settings","UseColors",1);
nLastBugID = theApp.GetProfileInt("Settings","LastBugID",0);
nRowToSelect = 0;
while(!(rst->adoEOF))
{
pSubmittedByID = pFields->GetItem("SubmittedByID");
pSubmittedToID = pFields->GetItem("SubmittedToID");
pBugID = pFields->GetItem("BUGID");
pDescription = pFields->GetItem("Description");
pSeverity = pFields->GetItem("Severity");
pToName = pFields->GetItem("ToName");
pFromName = pFields->GetItem("FromName");
pReturnStatus = pFields->GetItem("ReturnStatus");
pReturnReasonNote = pFields->GetItem("ReturnReasonNote");
pTimeSubmitted = pFields->GetItem("TimeSubmitted");
pTimeReturned = pFields->GetItem("TimeReturned");
pResolved = pFields->GetItem("Resolved");
pProjectID = pFields->GetItem("ProjectID");
pStatusID = pFields->GetItem("ReturnStatusID");

strTimeSubmitted.Format("%s",(char*)_bstr_t(pTimeSubmitted->Value));
strDescription.Format("%s",(char*)_bstr_t(pDescription->Value));
strSeverity.Format("%s",(char*)_bstr_t(pSeverity->Value));
strToName.Format("%s",(char*)_bstr_t(pToName->Value));
strFromName.Format("%s",(char*)_bstr_t(pFromName->Value));
vStatus = pReturnStatus->Value;

strReturnStatus = (vStatus.vt == VT_NULL ? "" char*)_bstr_t(vStatus));

vReturnTime = pTimeReturned->Value;
strTimeReturned = (vReturnTime.vt == VT_NULL ? "" : (char*)_bstr_t(vReturnTime));

int nResolved = (int)pResolved->Value;
nProjectID = (int)pProjectID->Value;
nBugID = (int)pBugID->Value;
vStatusID = pStatusID->Value;
nStatusID = (vStatusID.vt == VT_NULL ? 0 : (int)pStatusID->Value);
nSubmittedToID = (int)pSubmittedToID->Value;
nSubmittedByID = (int)pSubmittedByID->Value;
row = m_lvw.InsertRow(strTimeSubmitted);
}

Another thing to note is the use of the _variant_t variables. if you know that your values could never be null, you can just format the CString as I have done for some of mine, but if the value is NULL, this will crash on you


bdiamond