Solved

Stored Procedure Return Parameter

Posted on 2002-04-22
8
1,239 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SetCurrentDirectory path limit 7 131
how to understand recursion 12 229
I could not build boost code, 10 94
Embarcadero C++ builder XE10.1 Berlin TRegistry declaration 1 39
This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
Container Orchestration platforms empower organizations to scale their apps at an exceptional rate. This is the reason numerous innovation-driven companies are moving apps to an appropriated datacenter wide platform that empowers them to scale at a …
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

840 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