Solved

Stored Procedure Return Parameter

Posted on 2002-04-22
8
1,228 Views
Last Modified: 2012-05-07
Hi Guys,
I am executing an SQL Server Stored procedure from C++.
This stored procedure returns a value of type int(the queue handle of an SQL Server Profiler Trace).
This is the T-SQL of Stored Procedure:
===============================================
Stored Procedure - sp_chkCSPFeed
================================================
CREATE Procedure sp_chkCSPFeed as
DECLARE @column_value int
SET @column_value =  67108864|1
DECLARE @queue_handle int
EXEC master..xp_trace_addnewqueue 11000, 10000, 95, 90, @column_value,  @queue_handle OUTPUT
EXEC master..xp_trace_seteventclassrequired @queue_handle, 11 ,1 -- RPC:Starting
EXEC master..xp_trace_seteventclassrequired @queue_handle, 10 ,1 -- RPC:Ending
EXEC master..xp_trace_seteventclassrequired @queue_handle, 31 ,1 -- Select

EXEC master..xp_trace_setappfilter @queue_handle, 'Parser', 'SQL Server Profiler%'

EXEC master..xp_trace_setqueuedestination @queue_handle, 2,1, NULL, 'e:\CSPFeed.trc'

EXEC master..xp_trace_startconsumer @queue_handle

return @queue_handle
==========================================================
I am 100% confident that the above Stored Procedure is working correctly as I am successfully executing it from a VB application and reading the return value(i.e. the queue handle).

However in C++ I am not fully clear on the best way to read in the return value.

See my C++ code below to execute the stored procedure(it is successfully executing the stored procedure)

Basically what I am trying to do is to execute my Stored Procedure which executes an SQL Server Profiler trace and returns a queue handle to that trace.
Then I want to subsequently pass this queue handle to another stored procdure which stops and destroys the Profile Trace.

I think where I am faltering is I am not correctly setting up a variable to include in my CreateParameter statement and also I don't think I am correctly reading this variable after the command is executed.

When I debug the program iQHndl is zero.
Any help would be appreciated - let me know if any point needs to be clarified or explained better

Cheers

==========================================================
Stored Procedure - sp_stopchkCSPFeed
==========================================================
// COM initialization
      CoInitialize (NULL);

    // Establishing a connection to the datasource
      
      try
      {
         HRESULT hr = m_pConn.CreateInstance (__uuidof(Connection));
      
         if (FAILED (hr))
         {
               wsprintf( szMsg, "CreateInstance failed." );
                  MessageBox( NULL, szMsg, "main", MB_OK );
         }

            if (FAILED (m_pConn->Open (_bstr_t ("Provider=MSDASQL;DSN=Support;UID=sa;PWD=elijah;"),
                              _bstr_t (""), _bstr_t (""), adModeUnknown)))
            {
                  wsprintf( szMsg, "Cannot open data source" );
                  MessageBox( NULL, szMsg, "main", MB_OK );
            }
      }
      catch ( _com_error &e )
      {
            _bstr_t bstrSource (e.Source());
            _bstr_t bstrDescription (e.Description());
            
      }
            
      try
      {
            _CommandPtr pCommand;
            _ParameterPtr   Param1;
            _RecordsetPtr rs;
            _CommandPtr pCommand2;

            HRESULT hr = pCommand.CreateInstance(__uuidof(Command));

            if (FAILED(hr))
            {
                  wsprintf( szMsg, "CreateInstance failed." );
                  MessageBox( NULL, szMsg, "main", MB_OK );
                  return;
            }

            pCommand->ActiveConnection = m_pConn;
            pCommand->CommandType = adCmdStoredProc;
            pCommand->CommandText = "sp_chkCSPFeed";
            
          _variant_t vQHndl_Id;
             vQHndl_Id.ChangeType(VT_INT);


          Param1 = pCommand->CreateParameter(_bstr_t("o_id"),adInteger,adParamReturnValue,sizeof(int), vQHndl_Id);
        pCommand->Parameters->Append(Param1);      

            pCommand->Execute(NULL, NULL, adCmdStoredProc);
            Param1 = pCommand->Parameters->Item["o_id"];
            int iQHndl = Param1->GetValue().lVal;
            

                  wsprintf( szMsg, "CreateInstance failed." + iQHndl );
                  MessageBox( NULL, szMsg, "main", MB_OK );

            HRESULT hr2 = pCommand2.CreateInstance(__uuidof(Command));

            if (FAILED(hr))
            {
                  wsprintf( szMsg, "CreateInstance failed." );
                  MessageBox( NULL, szMsg, "main", MB_OK );
                  return;
            }

            pCommand2->ActiveConnection = m_pConn;
            pCommand2->CommandType = adCmdStoredProc;
            pCommand2->CommandText = "sp_stopchkCSPFeed";
            
            
            
            _ParameterPtr   Param2;
            Param2 = pCommand2->CreateParameter(_bstr_t("i_id"), adInteger, adParamInput, sizeof(int), _variant_t( (int) iQHndl));
            Param2->Value = _variant_t( (int) iQHndl );
             pCommand2->Parameters->Append(Param2);

            rs = pCommand2->Execute(NULL, NULL, adCmdStoredProc);
      
      
      }      

      catch( _com_error &e )
      {
            _bstr_t bstrSource(e.Source());
            _bstr_t bstrDescription(e.Description());
            
      }
 
0
Comment
Question by:Barry Cunney
  • 4
  • 3
8 Comments
 
LVL 49

Expert Comment

by:DanRollins
ID: 6961139
You don't need to mess with parameters -- they always confuse the issue.  If you need to pass in some parameters, just make them part of the SQL command text.

The Execute function returns a recordset.  In this case, it will be a one-column, one-row recordset containing the datum you want.

-- Dan
0
 
LVL 6

Expert Comment

by:Triskelion
ID: 6961547
DR> You don't need to mess with parameters
Dan, I agree with you only when not using MFC.

There are some efficiencies gained when parameterized CRecordsets are used.

BCUNNEY, can you use (or) have you used MFC with this?
If you can, it will shift the complication to an area that has some associated documentation.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6962115
>>I agree with you only when not using MFC.
I don't see how MFC's wrappers make any difference; it is always possible to specify a complete SQL statement (typically a SELECT statement) whether using MFC CRecordset or ADO or OLEDB.  

>>There are some efficiencies gained when parameterized CRecordsets are used
The efficiency gain would be related on having to pass fewer quote marks and letting the DBMS do a little less parsing.  These gains are 'lost in the noise' in comparison to the I/O time and actual data access time.

-- Dan
0
 
LVL 17

Author Comment

by:Barry Cunney
ID: 6963388
Hi Guys,
I actually figured out how to do this late last night
Below is the code - The key to the whole thing was how to set up VARIANT to read in return parameter

int iQHndl;
VARIANT vtQHndl;
vtQHndl.vt = VT_I2;      

and also reading in return value from parmeter to variant
to pass to second stored procedure
pCommand->Execute(NULL, NULL, adCmdStoredProc);
                  
iQHndl = Param1->GetValue().iVal;
vtQHndl.iVal = iQHndl;



I have also put the whole thing in a thread as I am trying to write a trace app the will be running a trace at regular intervals.

========================================================
Code
========================================================
#include <process.h>
#include <stdio.h>
#include <windows.h>
#include <conio.h>

#import "msado15.dll" \
            no_namespace \
            rename( "EOF", "adoEOF" )


_ConnectionPtr m_pConn;
char szMsg[80];
int a[ 5 ];

// Thread to execute stored procedure
void ExecuteTrace( void* pParams )
{
      
      while(TRUE)
      {

            // COM initialization
            CoInitialize (NULL);

            // Establishing a connection to the datasource
      
            try
            {
                  HRESULT hr = m_pConn.CreateInstance (__uuidof(Connection));
      
                  if (FAILED (hr))
                  {
                        wsprintf( szMsg, "CreateInstance failed." );
                        MessageBox( NULL, szMsg, "main", MB_OK );
                  }

                  if (FAILED (m_pConn->Open (_bstr_t ("Provider=MSDASQL;DSN=Support;UID=sa;PWD=elijah;"),
                              _bstr_t (""), _bstr_t (""), adModeUnknown)))
                  {
                        wsprintf( szMsg, "Cannot open data source" );
                        MessageBox( NULL, szMsg, "main", MB_OK );
                  }
            }
            
            catch ( _com_error &e )
            {
                  _bstr_t bstrSource (e.Source());
                  _bstr_t bstrDescription (e.Description());
            
            }
            
            try
            {
                  _CommandPtr pCommand;
                  _ParameterPtr   Param1;
                  _RecordsetPtr rs;
                  _CommandPtr pCommand2;

                  HRESULT hr = pCommand.CreateInstance(__uuidof(Command));

                  if (FAILED(hr))
                  {
                        wsprintf( szMsg, "CreateInstance failed." );
                        MessageBox( NULL, szMsg, "main", MB_OK );
                        return;
                  }

                  pCommand->ActiveConnection = m_pConn;
                  pCommand->CommandType = adCmdStoredProc;
                  pCommand->CommandText = "sp_chkTenforeFeed";
            
                  int iQHndl;
                  VARIANT vtQHndl;
                  vtQHndl.vt = VT_I2;      


                  Param1 = pCommand->CreateParameter(_bstr_t("o_id"),adInteger,adParamReturnValue, sizeof(int), vtQHndl);
                  pCommand->Parameters->Append(Param1);      

                  pCommand->Execute(NULL, NULL, adCmdStoredProc);
                  
                  iQHndl = Param1->GetValue().iVal;
                  vtQHndl.iVal = iQHndl;

                  Sleep(30000);
            
                  HRESULT hr2 = pCommand2.CreateInstance(__uuidof(Command));

                  if (FAILED(hr))
                  {
                        wsprintf( szMsg, "CreateInstance failed." );
                        MessageBox( NULL, szMsg, "main", MB_OK );
                        return;
                  }

                  pCommand2->ActiveConnection = m_pConn;
                  pCommand2->CommandType = adCmdStoredProc;
                  pCommand2->CommandText = "sp_stopchkTenforeFeed";
                        
                  _ParameterPtr   Param2;
                  Param2 = pCommand2->CreateParameter(_bstr_t("i_id"), adInteger, adParamInput, sizeof(int), vtQHndl);
                  Param2->Value = vtQHndl ;
                   pCommand2->Parameters->Append(Param2);

                  rs = pCommand2->Execute(NULL, NULL, adCmdStoredProc);
      
      
            }      

            catch( _com_error &e )
            {
                  _bstr_t bstrSource(e.Source());
                  _bstr_t bstrDescription(e.Description());
            
            }

            
            //////////////////////////////////////////////////////////////////////////////////////
            // Move file to local drive interrogate file
                  system("move J:\TenforeFeed.trc C:");
            //////////////////////////////////////////////////////////////////////////////////////

            // Interrogate file
            

            //////////////////////////////////////////////////////////////////////////////////////

            
            
            
            
            system("del C:\TenforeFeed.trc");
            //////////////////////////////////////////////////////////////////////////////////////


            Sleep(60000);
      }
}
 
int main( void )
{

  // Execute trace thread      Thread;
  _beginthread( ExecuteTrace, 0, NULL );
   
 
              
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 49

Expert Comment

by:DanRollins
ID: 6963645
Good fix; useful info aabout setting up the adParamReturnValue.  

I suggest that you have Community Support PAQ this question and refund the points since you solved it yourself.

-- Dan
0
 
LVL 17

Author Comment

by:Barry Cunney
ID: 6963680
Yes DanRollins, I didn't think it would so tricky dealing with the return variable.
I have done the exact same thing in VB and it's very simple you just simply specify return type of adInteger, read it into an integer variable and then subsequently pass this variable to another paramter object for stored procedure which has input paramter of type int.
...but the C++ CreateParamter is a bit trikier.

I was worried that I wasn't explaining the specific root of my problem well enough on EE.


0
 
LVL 49

Accepted Solution

by:
DanRollins earned 100 total points
ID: 6963937
I think that the problem is that in BASIC, all variables are variants.  

I'd also like to clarify my post:  In my SPs, I usually return values via a SELECT statement; eg.,

    SELECT @queue_handle

rather than RETURN.  That way, I get back a one-column, one-field Recordset which is easy to handle.

-- Dan
0
 
LVL 17

Author Comment

by:Barry Cunney
ID: 6965206
Thanks Dan,
I didn't fully catch your point at first re: returning recordset.
...but yes, now I understand where you are coming from.
makes perfect sense what you are saying and certainly the recordset would be a lot easier to handle.
... It's just I had used Parameters in VB and I was determined to figure out how to use them in C++.


Cheers





0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In days of old, returning something by value from a function in C++ was necessarily avoided because it would, invariably, involve one or even two copies of the object being created and potentially costly calls to a copy-constructor and destructor. A…
IntroductionThis article is the second in a three part article series on the Visual Studio 2008 Debugger.  It provides tips in setting and using breakpoints. If not familiar with this debugger, you can find a basic introduction in the EE article loc…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

707 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

12 Experts available now in Live!

Get 1:1 Help Now