Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Gama and Naughter

 

The XPParameters Object

 

This object is implemented as the class “CXPParametersObj “ and is contained in the “XPParametersObj.cpp” and “XPParametersObj.h” modules. Again it is pretty much a standard ATL COM object. It implements the interface “IXPParameters” which implements a COM collection interface. The “Count” property returns the number of parameters passed to the XP and the Item method obtains the parameter at a specified index. The “FindIndex” method allows you to lookup the index of a specified parameter, which is passed by name. The class also contains pretty standard ATL code to support a COM collection. This allows “for each” syntax in VB and enumerator syntax in JScript to work correctly. Similar to the XPServer object it has a pointer member variable called “m_pESP” which ultimately is derived from the XP++ class “CExtendedStoredProcedure”. Most of its methods delegate back to methods or variables in this pointer.

 

The XPParameter Objects
 

This object is implemented as the class “CXPParameterObj” and is contained in the “XPParameterObj.cpp” and “XPParameterObj.h” modules. An object of this type is returned from the XPParameters collection. It encapsulates the details of a single XP parameter as detailed below:

 

Method or Property Name

Encapsulates

Type

Returns the data type of the parameter as returned from srv_paraminfo

MaxLen

Returns the maximum length of the parameter as returned from srv_paraminfo

ActualLen

Returns the actual length of the parameter as returned from srv_paraminfo

Data

Returns the actual data of the parameter as a COM VARIANT

IsNull

Returns whether or not this parameter is  null as returned from srv_paraminfo

IsInput

Returns whether or this parameter is an INPUT parameter as returned from srv_paramstatus

SetOutputData

Sets the data for this OUTPUT parameter via srv_paramsetoutput

IsOutput

The opposite of IsInput


Most of these functions are implemented as simply calls to the underlying ODS function but two functions are worthy of discussion.

 

If you peruse the code for SetOutputData you will see that it uses a helper function called “VariantToXPData”.  The code in that function has the following sequence:

 

     1. Convert the VARIANT data to the variant data type which is closest to the ODS data type we want to return. In some cases this is trivial for example a “SRVINT4” in ODS corresponds exactly to a “VT_I4” in a VARIANT. In other cases such as dates it is more complicated because the VARIANT data type for dates does not map directly to the ODS equivalent. In this case we use similar code to XP_ASTRO for making the appropriate conversions. The end result is a CComVariant instance stored in a local variable called “varConverted”. “CComVariant” is a standard ATL class used to hold a COM VARIANT.

     2. We then allocate some heap memory of the appropriate size to contain the ODS data type

     3. Finally the contents of the converted variant are copied into the memory, which is returned from the function.

 

The “Data” property returns the underlying data packaged up in a COM VARIANT meaning that script code or Visual Basic can operate on it easily. The code in this function operates in the reverse direction of the “VariantToXPData” function already described. In the case of this function we need to go from an ODS data type to a VARIANT. This functionality is implemented in “CXPParameterObj::get_Data”.

 

The code for “SetOutputData” is then implemented as:

 

STDMETHODIMP CXPServerObj::ParamSetOutput(long Parameter, VARIANT Data)

{

  //Validate our parameters

  ATLASSERT(m_pESP);

 

  int nRet = SUCCEED;

  if (Data.vt == VT_EMPTY)

  {

    //call the srv_paramsetoutput

    nRet = m_pESP->ParamSetOutput(Parameter, NULL, 0, TRUE);

  }

  else

  {

    //First need to determine the data type of this parameter so that we

    //can return the correct data type data to it

    BYTE bType;

    ULONG nMaxLen;

    ULONG nActualLen;

    BOOL bNull;

    if (m_pESP->ParamInfo(Parameter, &bType, &nMaxLen, &nActualLen, NULL, &bNull) != SUCCEED)

      return Error(IDS_FAIL_CALL_PARAMINFO, IID_IXPServer, E_FAIL);

 

    //Now convert the VARIANT into ODS compatible data

    BYTE* pData;

    ULONG cbLen;

    HRESULT hr = CXPParameterObj::VariantToXPData(Data, bType, nMaxLen, pData, cbLen);

    if (FAILED(hr))

      return hr;

 

    //Finally call the srv_paramsetoutput

    nRet = m_pESP->ParamSetOutput(Parameter, pData, cbLen, FALSE);

 

    //Tidy up the heap memory used before we return

    delete [] pData;

  }

 

  if (nRet != SUCCEED)

    return Error(IDS_FAIL_CALL_PARAMSETOUTPUT, IID_IXPServer, E_FAIL);

 

      return S_OK;

}

 

Note how the code needs to first determine the ODS data type for the parameter via the call to the XP++ function “CExtendedStoredProcedure::ParamInfo”. Once it has the ODS data type it calls the “VariantToXPData” function to convert the data and returns this data via “CExtendedStoredProcedure::ParamSetOutput”. Before the function returns it tidies up the heap memory allocated by the “VariantToXPData” function.


The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm
 

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter