||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
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
* 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:
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*
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, ¶metersObjTypeInfo);
CComObjectStackWithQI<CXPParameterObj> parameterObj; \
CComPtr<ITypeInfo> parameterObjTypeInfo; \
parameterObj.GetTypeInfo(0, LOCALE_SYSTEM_DEFAULT, ¶meterObjTypeInfo);
SRVRETCODE retCode = 0; \
class _xp; \
retCode = _xp.main(srvproc, #xpName); \
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:
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
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:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter