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

 

Adding XP and Active Scripting Support

 

With the basic ATL classes now in place to support the object model we now turn out attention to implementing the Scripting support code. We follow the same steps as we did in the XP_SERVERREACHABLE chapter to add in support for XP’s to the DLL.

 

We will design the DLL to export 3 XP’s. One XP will be called “XP_RUNSCRIPT_DISK” and will allow a specific script file from disk to execute. The code for this XP will be in the class “CRunScriptDiskStoredProcedure”. The second XP will be called “XP_RUNSCRIPT_PARAMETER” and will take the script to execute from one of the parameters passed to it. This will be implemented in the class “CRunScriptParameterStoredProcedure”. Because the only real difference between these two XP’s is where they take their script code from, we will derive both of these classes from a base class called “CRunScriptStoredProcedure” which implements the main scripting support code. The final XP will be called “XP_RUN_ACTIVEX” which we will discuss later when we talk about adding Visual Basic support to the DLL.

 

To provide support for running scripts in our DLL, we need to integrate support for Microsoft Active Scripting into the code. Please note that if you want to compile the code for XP_RUNSCRIPT, then at this stage you must download the appropriate header files for Active Scripting. This is available by searching for the online article “FILE: Scriptng.exe Provides Files to Add Active Debugging to Hosts and Engines” on the MSDN web site. The Scripting framework is COM based and uses three key COM interfaces, namely “IActiveScript”, “IActiveScriptParse” and “IActiveScriptSite”.  “IActiveScript” is the interface used to control the script, while “IActiveScriptParse” is used to specify the script to parse and run. “IActiveScriptSite” needs to be implemented in XP_RUNSCRIPT as it provides a call-back interface which gets called as various situations and errors occur during the lifetime of the script engine.

 

The XP_RUNSCRIPT implementation of  “IActiveScriptSite” is contained in the modules “XPRunScriptObj.cpp” and “XPRunScriptObj.h”. It is implemented as a standard early bound ATL class and is called “CXPRunScriptSite”. Again like some of the Object model classes we have already discussed it has a pointer member variable to the controlling “CRunScriptStoredProcedure” variable. Most of the methods simply return “E_NOTIMPL” as we are not interested in most of the notifications. The two methods worthy of interest are “OnScriptError” and “GetItemInfo”. “OnScriptError” is called when any parsing or runtime errors occur in the script we are running. The XP_RUNSCRIPT implementation simply reports the error to clients of the XP using the XP++ function “SendErrorMsg”. “GetItemInfo” will be discussed when we describe the code required to run a script via Active Script.

 

The class “CRunScriptStoredProcedure” is the core class in the DLL, which handles the execution of scripts in the XP_RUNSCRIPT DLL. It contains one key function called “CRunScriptStoredProcedure::RunScript”. One of the parameters to this function is the actual script text to execute, encapsulated in an ATL CComBSTR parameter. “CComBSTR” is a standard class provided by ATL, which encapsulates a COM automation string AKA BSTR.

 

The steps involved in running a script via Microsoft Active Script are:

 

     * Each script language is implemented as its own COM object and as such we need to specify which script engine to use. Windows Script determines this from the extension of the file passed to it while ASP for example specifies it by using a specific string identifier in the ASP file. The approach XP_RUNSCRIPT uses is to specify the scripting language to use by passing the language to use as a parameter to “XP_RUNSCRIPT_DISK” and “XP_RUNSCRIPT_PARAMETER”. This in turn is passed as a parameter to the “RunScript” function along with the actual script text to execute. Normally you would use the text “VBScript” or “JScript” to specify the two built-in languages of VBScript and JScript respectively.

     * Given the Script language above, we convert this to a COM CLSID via the function CLSIDFromProgID.

     * Create the script engine via a call to CoCreateInstance. We use the ATL wrapper class “CComPtr” to ensure that the COM interface, which it holds will be automatically cleaned up when the variable goes out of scope.

      * Query for the “IActiveScriptParse” interface from the script engine object just created.

      * Tell the script engine about our implementation of “IActiveScriptSite” via “IActiveScript::SetScriptSite”. Our implementation corresponds to a COM member variable of “CRunScriptStoredProcedure” which corresponds to the “CXPRunScriptSite” class already discussed.

     * Initialize the script parser by calling its “InitNew” method.

     * Inject a “Named item” called “SQL” into the script namespace. This corresponds to the “SQL” object we have already mentioned in the object model that XP_RUNSCRIPT provides. What happens is that any time the script encounters a reference to a “SQL.” item, it calls back into our code through the script site object and into the method “GetItemInfo”. In this implementation we need to provide the script engine with a pointer to our implementation of the “SQL” named item. If you now refer to the “CXPRunScriptSite::GetItemInfo” code it should make a whole lot more sense. Basically it checks to see if the script engine wants to know about an object called “SQL” and if so it hands back the IUnknown interface for the implementation of the “CXPServerObj” object in the class “CRunScriptStoredProcedure”. 

     * Next the script is parsed by calling “IActiveScriptParse::ParseScriptText”.

     * Finally the script is run by setting its state to started with a call to “SetScriptState(SCRIPTSTATE_STARTED)”. This function will not return until the script has finished executing or a runtime error has occurred causing the script to terminate.

 

A few other points about the code in “CRunScriptStoredProcedure” are worth discussing. This class contains a number of member variables declared as follows:

 

CComObjectStackWithQI<CXPServerObj>     m_serverObj;  CComObjectStackWithQI<CXPRunScriptSite> m_scriptSiteObj;  CComObjectStackWithQI<CXPParameterObj>* m_pParametersCOMArray;

CComObjectStackWithQI<CXPParametersObj> m_parametersObj;

 

We have already mentioned the “m_serverobj” and how it is used in the GetItemInfo implementation. Similarly the “m_parametersObj” is used when any script requests the Parameters property in the Object model. Finally the “m_pParametersCOMArray” is an array of objects, which implement the object model for each individual parameter in the parameters array. These objects are used when the Item method of the parameters object is called. This array is dynamically allocated at the start of the “RunScript” and the memory is automatically freed in the class destructor.

 

Each of these member variables uses a class called “CComObjectStackWithQI”. This is a class provided in XP_RUNSCRIPT, which provides custom creation and reference counter for ATL based COM objects. Normally if you were developing a standard COM DLL with ATL you would never need to create the COM objects yourself as the default class factory provided by ATL would create the objects for you. ATL normally uses the class “CComObject” for this creation. This class allocates the object on the heap and when the reference count reaches 0, it automatically destroys the object using the code “delete this”. In XP_RUNSCRIPT we are subverting COM to do our bidding and the normal COM lifetime rules do not apply. Once the call to “SetScriptState(SCRIPTSTATE_STARTED)” returns we know for certain that no COM clients can still be executing. In this case we can automatically destroy the objects since they now have no outstanding clients. For this scenario ATL provides the class “CComObjectStack” where the object is created on the stack. The one quirk with this built in class is that it deliberately fails all calls to IUnknown::QueryInterface based on the assumption that this would lead to an incrementing reference count and the possibility that a COM object is destroyed before its reference count reaches zero. This is no good in XP_RUNSCRIPT as the script engine will most certainly query for a number of interfaces. The class “CComObjectStackWithQI” addresses this issue by allocating on the stack just like “CComObjectStack” but also providing QueryInterface support just like “CComObject” does.

 

Initially the code used the standard ATL “CComObject” class but during testing when a script was terminated early, the XP++ framework flagged up memory leak problems. This was due to COM objects not being properly released by the script engine. Normally this would not be too much of an issue in a problem which hosts the scripting engine but these memory leaks can be devastating if left unbound when run as a SQL Server XP. Using “CComObjectStackWithQI” solved some of these reported memory leaks. Hopefully you might find this class useful in your own projects where you want to have tight control over the lifetime of your COM objects. Another memory leak, which the XP++ framework flagged up was tracked down to the implementation of “IDispatchImpl” in ATL. This class is provided by ATL to implement support for late bound COM objects. What happens in this class when a COM object is being called is that some internal static memory array data is dynamically allocated to hold cached type library information. Normally ATL handles the de-allocation of this memory when the DLL is being unloaded from memory so it is not a memory leak in the true sense of the word, but because the XP++ framework sees a difference in the memory allocations before and after calling the XP, it flags up a memory leak. This became enough of a problem during the testing of XP_RUNSCRIPT that I looked into finding a fix for this phantom memory leak. The solution was to implement a custom “IMPLEMENT_XP” macro. It is similar to the standard macro in the XP++ framework expect that it ensures the memory for all the objects which use “IDispatchImpl” are pre-allocated before we do the standard memory checks. The macro is implemented as follows:

 

#define IMPLEMENT_XP_RUNSCRIPT(xpName, class) \

  extern "C" SRVRETCODE __declspec(dllexport) xpName(SRV_PROC* srvproc) \

  { \

    CComObjectStackWithQI<CXPServerObj> serverObj; \

    CComPtr<ITypeInfo> serverObjTypeInfo; \

    serverObj.GetTypeInfo(0, LOCALE_SYSTEM_DEFAULT, &serverObjTypeInfo); \

    \

    CComObjectStackWithQI<CXPParametersObj> parametersObj; \

    CComPtr<ITypeInfo> parametersObjTypeInfo; \

    parametersObj.GetTypeInfo(0, LOCALE_SYSTEM_DEFAULT, &parametersObjTypeInfo); \

    \

    CComObjectStackWithQI<CXPParameterObj> parameterObj; \

    CComPtr<ITypeInfo> parameterObjTypeInfo; \

    parameterObj.GetTypeInfo(0, LOCALE_SYSTEM_DEFAULT, &parameterObjTypeInfo); \

    \

    XP_MEM_STATE(beginMemState) \

    \

    SRVRETCODE retCode = 0; \

    { \

      class _xp; \

      retCode = _xp.main(srvproc, #xpName); \

    } \

    \

    XP_MEM_DIFF(beginMemState) \

    \

    return retCode; \

  }

 

At this stage we have almost all the code in place for the first two of the three XP’s in XP_RUNSCRIPT.

 

The code to export each XP from the DLL is as follows:

 

IMPLEMENT_XP_RUNSCRIPT(XP_RUNSCRIPT_DISK,      CRunScriptDiskStoredProcedure);

IMPLEMENT_XP_RUNSCRIPT(XP_RUNSCRIPT_PARAMETER, CRunScriptParameterStoredProcedure);

 

Both of these classes are implemented in the modules “XP_RUNSCRIPTProcs.h” and “XP_RUNSCRIPTProcs.cpp” and use standard XP++ code to define two input string parameters. The first parameter for both is the Scripting language to use. The second parameter for XP_RUNSCRIPT_DISK is the name of the script file to run. The code then simple loads up the contents of this file into a string parameter and pass it to the “RunScript” function. XP_RUNSCRIPT_PARAMETER simple passes the second parameter directly to the “RunScript” function. Both of the XP’s also initialize and deinitialize COM using a simple helper class called “CCOMInitialize” implemented in “XP_RUNSCRIPTProcs.cpp”.

 

At this stage in the discussion we’ve completed all the code required and you should be able to compile and link XP_RUNSCRIPT if you were implementing the code from scratch. Assuming we had a simple JScript file located at “c:\temp\test.js” which had the following contents:

 

SQL.SendInfoMsg("Hello from JScript");

 

We could execute this script from Query Analyzer (assuming we have performed the standard XP registration) using the following code:

 

EXEC master..XP_RUNSCRIPT_DISK 'JScript', 'c:\temp\test.js'

 

Assuming everything went ok, you should see the text “Hello from JScript” in the Messages Window. If we wanted to execute the same script from a parameter we could use the following:

 

EXEC master..XP_RUNSCRIPT_PARAMETER 'JScript', 'SQL.SendInfoMsg("Hello from JScript called via XP_RUNSCRIPT_PARAMETER")'

 

Again you should see some text appear in the messages window when this XP completes running.

 

There are 2 sample script files included in a “Test” subdirectory underneath the XP_RUNSCRIPT code along with a SQL file you can use to test these scripts. Please note that you will probably need to change the script paths to suit your particular machine setup. Hopefully these examples will get you thinking on how you can take advantage of XP_RUNSCRIPT_DISK and XP_RUNSCRIPT_PARAMETER.
 


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