Solved

Stored Procedure Return Parameter

Posted on 2002-04-22
8
1,242 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

When writing generic code, using template meta-programming techniques, it is sometimes useful to know if a type is convertible to another type. A good example of when this might be is if you are writing diagnostic instrumentation for code to generat…
Templates For Beginners Or How To Encourage The Compiler To Work For You Introduction This tutorial is targeted at the reader who is, perhaps, familiar with the basics of C++ but would prefer a little slower introduction to the more ad…
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 learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

624 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