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

 

The “SQL” object is implemented in the “CXPServerObj“ class in the “XPServerObj.cpp” and “XPServerObj.h” modules. This is a standard ATL class, which implements the IXPServer COM interface. It encapsulates the following functionality:
 

Method or Property Name

Encapsulates

Parameters

Returns the COM collection which encapsulates the parameters sent to the XP

Describe

srv_describe

BindToken

srv_GetBindToken

MessageHandler

srv_message_handler

PField

srv_pfield

SessionMessageLCID

srv_pfieldex using the constant SRV_MSGLCID

InstanceName

srv_pfieldex using the constant SRV_INSTANCENAME

SendDone*

srv_senddone

SendInfoMsg / SendErrorMsg

srv_sendmsg

SendRow

srv_sendrow

SetColumnData

srv_setcoldata, srv_setcollen

SetUserDefinedType

srv_setutype

Quit

Exits the script prematurely with a specified return value

ReturnValue

What will be the return value from the XP

 

The CXPServerObj contains a pointer member variable called “m_pESP” which ultimately is derived from the XP++ class “CExtendedStoredProcedure”. Most of the methods in CXPServerObj simply delegate their work to this pointer which in turn simple calls the corresponding ODS function as noted in the table above. An example would be the code for MessageHandler:

 

STDMETHODIMP CXPServerObj::MessageHandler(long ErrorNum, long Severity, long State, BSTR ErrorText)

{

  USES_CONVERSION;

 

  //Validate our parameters

  ATLASSERT(m_pESP);

      if (ErrorText == NULL)

            return Error(IDS_FAIL_NULL_POINTER, IID_IXPServer, E_POINTER);

 

  char* pszErrorText = W2A(ErrorText);

  if (m_pESP->MessageHandler(ErrorNum, (BYTE) Severity, (BYTE) State, 0, pszErrorText, strlen(pszErrorText), NULL, 0) != SUCCEED)

    return Error(IDS_FAIL_CALL_MESSAGEHANDLER, IID_IXPServer, E_FAIL);

  return S_OK;

}

 

An interesting challenge lies in implementing the “Describe” and “SetColumnData” functions. The issue with these functions is that they take pointers to data, which must live beyond the scope of the function call. This might seem strange at first but there is a very good reason for this design. “Describe” specifies the data types for each column in a returned recordset and the default data to use for each column, while “SetColumnData” allows you to change the column data for individual rows of the recordset. By insisting on the pointers to this data living beyond the scope of the functions, all ODS needs to do is maintain pointers to this data rather than copy the data into its own internals buffers. This is not a big issue because ODS was designed to be called from C/C++ so handling pointers is a standard job which a C/C++ developer does everyday, plus it is a big performance gain as ODS does not need to maintain its own copy of the data while the client code also has a copy of the row data. Because we are developing a COM wrapper for ODS so that it can be called by non-C/C++ code we now hit a problem. How can we maintain these pointers while at the same time allowing clients of XP_RUNSCRIPT call us with data, which is presented in a COM Variant, which may have an ill-defined life-time. The solution used in XP_RUNSCRIPT is to make a copy of the data presented to it and call the underlying functions using this data. XP_RUNSCRIPT automatically handles the deletion of this memory when it is finished via its C++ destructor. This explains the code in “Describe”:

 

STDMETHODIMP CXPServerObj::Describe(BSTR ColumnName, XP_DATATYPE DestType, long DestLen, VARIANT Data)

{

  USES_CONVERSION;

 

  //Validate our parameters

      if (ColumnName == NULL)

            return Error(IDS_FAIL_NULL_POINTER, IID_IXPServer, E_POINTER);

 

  //Convert the incoming VARIANT into an ODS data type

  BYTE* pData;

  ULONG cbLen;

  HRESULT hr = CXPParameterObj::VariantToXPData(Data, DestType, DestLen, pData, cbLen);

  if (FAILED(hr))

    return hr; 

  //call the underlying function

  int nColumn = m_pESP->Describe(0, W2A(ColumnName), SRV_NULLTERM, DestType, DestLen, DestType, cbLen, pData);

  if (nColumn == 0)

  {

    //tidy up the heap memory before we return

    delete [] pData;

 

    return Error(IDS_FAIL_CALL_DESCRIBE, IID_IXPServer, E_FAIL);

  }

 

  //If successful, add to the cached data so that the data stays around for the lifetime of this XP
 

  long nDestType = DestType;

  m_DescribeDataTypes.Add(nDestType);

  ATLASSERT(nColumn == m_DescribeDataTypes.GetSize());

 

  long nDestLen = DestLen;

  m_DescribeDataLen.Add(nDestLen);

  ATLASSERT(nColumn == m_DescribeDataLen.GetSize());

 

  m_DescribeDefaultData.Add(pData);

  ATLASSERT(nColumn == m_DescribeDefaultData.GetSize());

 

  BYTE* pRowData = NULL;

  m_DescribeRowData.Add(pRowData);

  ATLASSERT(nColumn == m_DescribeRowData.GetSize());

 

  return S_OK;

}

 

Note that because the ODS API requires an ODS compatible data type, “Describe” needs to convert from a COM VARIANT data type to a corresponding C structure for each ODS data type. This mapping is implemented in a helper function called “VariantToXPData”, which is part of the “CXPParameterObj” class. The “CXPParameterObj” class will be discussed in detail later on.

 

Also notice how after calling the underlying function via “m_pESP->Describe”, the code caches the specified column’s data type, length, and default data in 3 “CSimpleArray” arrays. “CSimpleArray” is a standard class provided by ATL, which implements dynamically growable arrays. In addition a fourth array, which is used by the “SetColumnData” is initialized with a NULL value. Each time describe is called, we end up with one new variable in each of these four arrays. After you have finished calling “Describe” for each of the columns you want to return in your recordset, you would end up with the same amount of items in each of these 4 arrays, as there are columns in your recordset.

 

“SetColumnData” performs the same trick where it caches the new columns data in the “m_DescribeRowData” array as follows:

 

See Code Depot

 

For this additional code and memory overhead in XP_RUNSCRIPT we allow JScript code to look like:

 

var defaultCol1 = 111; //This will be the default value for column1

var defaultCol2 = 222; //This will be the default value for column2

var nullvalue;

SQL.Describe("Column 1", XPDT_SRVINT4, 0, defaultCol1);

SQL.Describe("Column 2", XPDT_SRVINT4, 0, defaultCol2);

SQL.SendRow();

SQL.SetColumnData(1, 3);

SQL.SetColumnData(2, 4);

SQL.SendRow();

SQL.SetColumnData(1, 5);

SQL.SetColumnData(2, 6);

SQL.SendRow();

SQL.SetColumnData(1, 7);

SQL.SetColumnData(2, 8);

SQL.SendRow();

SQL.SetColumnData(1, nullvalue);

SQL.SendRow();

 

This will return a recordset with 2 columns, namely “Column 1” and “Column 2” which each contain a TSQL “int” data type. The recordset will contain 5 rows and will have the following form:

 

Column 1

Column 2

111

222

3

4

5

6

7

8

0

8

 

 

For development environments such as Visual Basic, XP_RUNSCRIPT provides enums for all the standard ODS data types, meaning that if you were to implement similar code to the recordset sample above in Visual Basic, Intellisense would kick in when you typed:

 

SQL.Describe(“Column 1”,

 

As this point a drop down combo box would appear with all the ODS data types ready for you to pick one. Again this is one of the niceties provided by XP_RUNSCRIPT.

 

One final function of note in CXPServerObj is the “Quit” method. This is designed to operate just like the “Quit” method provided by the Windows Script object model. Unlike the Windows Script implementation, SQL.Quit takes a COM Boolean, which will be used as the return value from the XP itself. This allows you to write script like:

 

If (SQL.Parameters.length < 4)

{

  SQL.SendErrorMsg("This XP requires at least 4 parameters");

  SQL.Quit(false);

}

 

Please note that internally “CXPServerObj::Quit” returns a custom COM error code of E_ABORT, meaning that this mechanism will only work correctly if the Quit call is inside a block of script, which is not surrounded by exception handling code. Alternatively you can just set the “SQL.ReturnValue” property and return normally from the script.
 


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