||SQL Server Tips by Gama and Naughter
Extending to support Visual
The final XP in XP_RUNSCIPT is
XP_RUN_ACTIVEX and allows you to implement an XP in any development
environment, which can produce a standard ActiveX DLL. The most
obvious example for this would be Visual Basic 6. Here we use COM’s
late bound interface “IDispatch” to directly run a specified
function in a ActiveX / COM DLL with a specified ProgID. The code
for this XP is implemented in the class “CRunActiveXStoredProcedure”
which is also derived from “CRunScriptStoredProcedure” like the two
script XP’s already discussed. This XP takes two parameters, the
first parameter is the ProgID of the COM object to call and the
second parameter is the function in that COM object to run. Since we
do not have the concept of a “Named item” in a non-script language,
we instead insist that the function we call have a specific syntax.
In Visual Basic you need to write code of the following format:
Function Function1(SQL As XPServer) As
SQL.SendInfoMsg "Hello from Visual Basic"
Function1 = True
Note that to be able to use object types
such as XPServer and all the constants for the ODS data type defined
in XP_RUNSCRIPT, you need to add a reference to the XP_RUNSCRIPT
type library. You can do this in Visual Basic 6 using the standard
“Project -> References” Menu item and checking the tick box for
“XP_RUNSCRIPT 1.0 Type Library”. You should return True from the
function to indicate success and False to indicate a failure. This
will become the return value from the XP via “CExtendedStoredProcedure::Run”.
By naming the parameter as “SQL”, you can make the VB code look
pretty much the same as the equivalent VBScript code for no extra
The code in “CRunActiveXStoredProcedure::Run”
can be broken down into the following steps:
Intialize COM using the “CCOMInitialize”
class just like the other 2 XP’s in XP_RUNSCRIPT.
Obtain the 2 parameters for the XP using
standard XP++ code.
Initialize the “m_pParametersCOMArray” array
for the object model wrappers for the parameters array.
Initialize a CComVariant with a pointer to
the SQLServer object. This will become the “SQL” object in the
“Function1” example above.
Given the ProgID of the COM object to run,
convert this to a COM CLSID via the function CLSIDFromProgID.
Create the COM object using CoCreateInstance.
We use the built in ATL class “CComDispatchDriver” to encapsulate
Call “IDispatch::GetIDsOfName” via
“CComDispatchDriver” to convert the function name we want to run
into a COM DISPID. A DISPID is a simple numeric identifier for a
Call the function in the COM object via
“CComDispatchDriver.Invoke1”. We pass the SQLServer object as the
Examine the return value from the COM object
and return this back as the return value.
That completes the core code for the
“CRunActiveXStoredProcedure” class. The final piece of code required
is to expose this class as an XP from the DLL. This is achieved
using the following code in the module “XP_RUNSCRIPT.cpp”:
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter