Using ODSOLE to Automate Custom Objects
In this section, I'll show you how to automate COM objects you create yourself. We'll walk through automating a library of Visual Basic functions that I've wrapped in a COM object named VBODSOLELib. Let's begin by looking at Listing 15.10, the Visual Basic source code for VBODSOLELib. (You can find the complete source code in the VBODSOLELib subfolder in the CH15 folder on the CD accompanying this book.)
Listing 15.10. The Source Code for VBODSOLELib
Option Explicit
Dim GlobalArray() As Variant
Dim lGlobalArraySize As Long
'String functions
Public Function VBInStrRev(strCheck As String, strMatch As String)
As String
VBInStrRev = InStrRev(strCheck, strMatch)
End Function
Public Function VBStrReverse(strIn As String) As String
VBStrReverse = StrReverse(strIn)
End Function
Public Function VBFormat(vExpr As Variant, strFormat As String)
As String
VBFormat = Format(vExpr, strFormat)
End Function
Public Function VBHex(vExpr As Variant) As String
VBHex = Hex(vExpr)
End Function
Public Function VBOct(vExpr As Variant) As String
VBOct = Oct(vExpr)
End Function
Public Function VBLike(strMatch As String, strExpr As String)
As Boolean
VBLike = (strMatch Like strExpr)
End Function
Public Function VBScriptRegEx(strPattern As String, strMatch
As String) As Long
Dim regEx, Match, Matches
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = strPattern
regEx.IgnoreCase = True
Set Matches = regEx.Execute(strMatch)
If Not IsEmpty(Matches) Then
For Each Match In Matches
VBScriptRegEx = Match.FirstIndex + 1 'Zero-based
Exit For
Next
Else
VBScriptRegEx = 0
End If
End Function
Public Function VBScriptRegExTest(strPattern As String, strMatch
As String) As Boolean
Dim regEx, Match, Matches
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = strPattern
regEx.IgnoreCase = True
VBScriptRegExTest = regEx.Test(strMatch)
End Function
' Misc
Public Function VBShell(strCommandLine As String, Optional
iWindowStyle As Variant) As Double
VBShell = Shell(strCommandLine, IIf(IsMissing(iWindowStyle),
vbNormalFocus, iWindowStyle))
End Function
' Financial functions
Public Function VBFV(nRate As Double, nPer As Double, nPmt
As Double, Optional vPv As Variant, Optional vType As Variant)
VBFV = FV(nRate, nPer, nPmt, IIf(IsMissing(vPv), 0, vPv),
IIf(IsMissing(vType), 0, vType))
End Function
Public Function VBIPmt(nRate As Double, nPer As Double,
nPmtPeriods As Double, nPV As Double, Optional vFv As
Variant, Optional vType As Variant)
VBIPmt = IPmt(nRate, nPer, nPmtPeriods, nPV,
IIf(IsMissing(vFv), 0, vFv), IIf(IsMissing(vType), 0, vType))
End Function
Public Function VBNPer(nRate As Double, nPmt As Double, nPV
As Double, Optional vFv As Variant, Optional vType As Variant)
VBNPer = nPer(nRate, nPmt, nPV, IIf(IsMissing(vFv), 0, vFv),
IIf(IsMissing(vType), 0, vType))
End Function
Public Function VBPmt(nRate As Double, nPer As Double, nPV
As Double, Optional vFv As Variant, Optional vType As Variant)
VBPmt = Pmt(nRate, nPer, nPV, IIf(IsMissing(vFv), 0, vFv),
IIf(IsMissing(vType), 0, vType))
End Function
Public Function VBPPmt(nRate As Double, nPer As Double,
nPmtPeriods As Double, nPV As Double, Optional vFv As Variant,
Optional vType As Variant)
VBPPmt = PPmt(nRate, nPer, nPmtPeriods, nPV,
IIf(IsMissing(vFv), 0, vFv), IIf(IsMissing(vType), 0, vType))
End Function
Public Function VBPV(nRate As Double, nPer As Double, nPmt
As Double, Optional vFv As Variant, Optional vType As Variant)
VBPV = PV(nRate, nPer, nPmt, IIf(IsMissing(vFv), 0, vFv),
IIf(IsMissing(vType), 0, vType))
End Function
' Routines
Public Sub VBAppActivate(strTitle As String, Optional bWait
As Variant)
AppActivate strTitle, IIf(IsMissing(bWait), False, bWait)
End Sub
Public Sub VBSendKeys(strKeys, Optional bWait As Variant)
SendKeys strKeys, IIf(IsMissing(bWait), False, bWait)
End Sub
Public Sub VBAppActivateAndSendKeys(strTitle As String, strKeys
As String, Optional bWait As Variant)
AppActivate strTitle, IIf(IsMissing(bWait), False, bWait)
SendKeys strKeys, IIf(IsMissing(bWait), False, bWait)
End Sub
Public Sub VBFileCopy(strSource As String, strDestination
As String)
FileCopy strSource, strDestination
End Sub
Public Sub VBFileErase(strFileName As String)
Kill strFileName
End Sub
Public Sub VBMkDir(strDirName As String)
MkDir strDirName
End Sub
Public Sub VBRmDir(strDirName As String)
RmDir strDirName
End Sub
|
I won't go through all of these functions. You should already have a pretty good idea of how to access them from T-SQL using the sp_OA procs by now. Once you've registered the object's DLL via a call to regsvr32, you access it from T-SQL just as you would any other COM object and exactly as we have done in the other examples in this chapter.
Most of these functions do not exist in Transact-SQL, and you may find some of them quite useful. There are financial functions (e.g., VBIPmt, a function to compute the interest payment on an annuity), string manipulation functions (e.g., VBInStrRev, a reverse string search function), system functions (e.g., VBAppActivate; you will need to run SQL Server as a console app to use some of these), regular expression functions (e.g., VBScriptRegEx and VBScriptRegExTest), and many others. The VBODSOLELib subfolder on the CD includes several sample T-SQL scripts that demonstrate how to use these. Listing 15.11 shows an example that calls VBInStrRev.
Listing 15.11. Using VBInStrRev
declare @obj int declare @hr int declare @songs varchar(255) set @songs='Sister Christian, Dance, Boys of Summer, The Dance' declare @pos int exec @hr=sp_OACreate 'VBODSOLE.VBODSOLELib', @obj OUT IF (@hr <> 0) BEGIN EXEC sp_displayoaerrorinfo @obj, @hr RETURN END exec @hr=sp_OAMethod @obj, 'VBInStrRev', @pos OUT, @songs, 'Dance' IF (@hr <> 0) BEGIN EXEC sp_displayoaerrorinfo @obj, @hr RETURN END select @pos exec @hr=sp_OADestroy @obj IF (@hr <> 0) BEGIN EXEC sp_displayoaerrorinfo @obj, @hr RETURN END (Results) ----------- 46 |
Arrays in T-SQL via COM Objects
One section of VBODSOLELib that I will cover in detail, however, is the set of VB array functions. These functions provide basic array services to Transact-SQL. In my last book, The Guru's Guide to SQL Server Stored Procedures, XML, and HTML, I added array support to Transact-SQL using xprocs and system functions. In this book, I'll take a different approach. I'll add array support to T-SQL via our VBODSOLELib COM object and some system functions. Let's start by looking at the array code from VBODSOLELib (Listing 15.12).
Listing 15.12. The Array Code from VBODSOLELib
Public Function VBCreateArray(lSize As Long) As Long
Dim vArray()
ReDim vArray(lSize)
If IsEmpty(lGlobalArraySize) Then
lGlobalArraySize = 0
Else
lGlobalArraySize = lGlobalArraySize + 1
End If
ReDim Preserve GlobalArray(lGlobalArraySize)
GlobalArray(lGlobalArraySize) = vArray()
VBCreateArray = lGlobalArraySize
End Function
Public Function VBGetArray(lGlobalIndex As Long, lIndex As Long)
As Variant
VBGetArray = GlobalArray(lGlobalIndex)(lIndex - 1)
End Function
Public Sub VBSetArray(lGlobalIndex As Long, lIndex As Long, vVal
As Variant)
GlobalArray(lGlobalIndex)(lIndex - 1) = vVal
End Sub
Public Sub VBDestroyArray(lGlobalIndex As Long)
Set GlobalArray(lGlobalIndex) = Null
End Sub
Public Function VBCreateArraySplit(strIn As String, Optional
strDelim As Variant) As Long
If IsEmpty(lGlobalArraySize) Then
lGlobalArraySize = 0
Else
lGlobalArraySize = lGlobalArraySize + 1
End If
ReDim Preserve GlobalArray(lGlobalArraySize)
GlobalArray(lGlobalArraySize) = Split(strIn,
IIf(IsMissing(strDelim), " ", strDelim))
VBCreateArraySplit = lGlobalArraySize
End Function
Public Function VBArrayJoin(lGlobalIndex As Long, Optional
strDelim As Variant) As String
VBArrayJoin = Join(GlobalArray(lGlobalIndex),
IIf(IsMissing(strDelim), " ", strDelim))
End Function
Public Function VBListArray(lGlobalIndex As Long) As Variant
VBListArray = GlobalArray(lGlobalIndex)
End Function
Public Function VBArrayLen(lGlobalIndex As Long) As Long
VBArrayLen = UBound(GlobalArray(lGlobalIndex))
End Function
|
This code presents eight functions:
VBCreateArraySplit— creates an array by breaking a delimited string into elements.
VBArrayJoin— returns the elements in an array as a delimited string.
The purpose of each of these should be pretty self-explanatory. Essentially, I've taken VB's base array functions and wrapped them in a COM object so that they're accessible from T-SQL. Listing 15.13 shows a sample script that demonstrates how to call them from T-SQL.
Listing 15.13. Calling Base Array Functions
declare @obj int declare @hr int declare @arr int exec @hr=sp_oacreate 'VBODSOLE.VBODSOLELib', @obj OUT IF (@hr <> 0) BEGIN EXEC sp_displayoaerrorinfo @obj, @hr RETURN END exec @hr=sp_oamethod @obj, 'VBCreateArray', @arr OUT, 10 IF (@hr <> 0) BEGIN EXEC sp_displayoaerrorinfo @obj, @hr Goto Cleanup END exec @hr=sp_oamethod @obj, 'VBSetArray', NULL, @arr, 3, 'foo' IF (@hr <> 0) BEGIN EXEC sp_displayoaerrorinfo @obj, @hr Goto Cleanup END declare @val varchar(30) exec @hr=sp_oamethod @obj, 'VBGetArray', @val OUT, @arr, 3 IF (@hr <> 0) BEGIN EXEC sp_displayoaerrorinfo @obj, @hr Goto Cleanup END SELECT @val DECLARE @len int exec @hr=sp_oamethod @obj, 'VBArrayLen', @len OUT, @arr IF (@hr <> 0) BEGIN EXEC sp_displayoaerrorinfo @obj, @hr Goto Cleanup END SELECT @len DECLARE @dummy int exec @hr=sp_oamethod @obj, 'VBListArray', @dummy OUT, @arr IF (@hr <> 0) BEGIN EXEC sp_displayoaerrorinfo @obj, @hr Goto Cleanup END Cleanup: exec @hr=sp_oadestroy @obj (Results abridged) ------------------------------ foo ----------- 10 Column0 Column1 Column2 Column3 ----------- ----------- ----------- ----------- 0 0 foo 0 |
There are a couple of interesting points to be made about this code. First, note the use of an integer index as the array's handle. Those of you who read my last book may be wondering why we're returning an index for the array handle rather than a pointer to the array itself as we did in the xproc array treatment in that book. The reason is that ODSOLE, in an attempt to help the developer, doesn't allow you to return an array type from an Automation method. If you return an array, ODSOLE automatically NULLs the output parameter that would have received the value and translates the array into a TDS result set. As I mentioned earlier, if the array is a single-dimensional array, you get a single row with a column for each element in the array. If the array has two dimensions, you'll get a multirow result set. And if the array has more than two dimensions or stores complex types such as structs, an error will be raised.
So, since we can't return an array from our COM methods without ODSOLE turning it into a result set, we allocate an “array of arrays”—an array of variants that each will store arrays—in the VBODSOLELib object. Each time a new array needs to be allocated, we ReDim this master array to include another slot, then allocate the new array at the new slot. In many ways, this master array resembles a two-dimensional array that supports jagged edges.
Once we successfully Dim a new array, we return the index to its slot in the class's master array. This index serves as the array's handle. Each time we access the array, we always index it in the master array using this handle.
Note that we use ODSOLE's propensity for translating arrays into result sets to our advantage in the VBListArray method. VBListArray doesn't actually list the array—it merely returns the variant containing the array. ODSOLE, on seeing this array return value, translates it into a result set, thus producing our list.
The ability to access this functionality via sp_OA calls is powerful enough, but as with my xproc array treatment, I've wrapped these sp_OA calls in system UDFs so they can be easily used across the server. Listing 15.14 presents the source to the script that sets up these UDFs.
Listing 15.14. The Array UDFs
USE master
GO
EXEC sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
DROP FUNCTION system_function_schema.fn_createobject,
system_function_schema.fn_destroyobject,
system_function_schema.fn_createarray,
system_function_schema.fn_setarray,
system_function_schema.fn_getarray,
system_function_schema.fn_destroyarray,
system_function_schema.fn_arraylen,
system_function_schema.fn_listarray
GO
CREATE FUNCTION system_function_schema.fn_createobject()
RETURNS int
AS
BEGIN
DECLARE @obj int
DECLARE @hr int
exec @hr=sp_OACreate 'VBODSOLE.VBODSOLELib', @obj OUT
IF (@hr <> 0) BEGIN
RETURN @hr
END
RETURN(@obj)
END
GO
CREATE FUNCTION system_function_schema.fn_destroyobject(@obj int)
RETURNS int
AS
BEGIN
DECLARE @hr int
exec @hr=sp_OADestroy @obj
RETURN(@hr)
END
GO
CREATE FUNCTION system_function_schema.fn_createarray(@obj int,
@size int)
RETURNS int
AS
BEGIN
DECLARE @hr int
DECLARE @hdl int
exec @hr=sp_OAMethod @obj, 'VBCreateArray', @hdl OUT, @size
IF (@hr <> 0) BEGIN
RETURN @hr
END
RETURN(@hdl)
END
GO
CREATE FUNCTION system_function_schema.fn_destroyarray(@obj int,
@hdl int)
RETURNS int
AS
BEGIN
DECLARE @hr int
exec @hr=sp_oamethod @obj, 'VBDestoryArray', NULL, @hdl
IF (@hr <> 0) BEGIN
RETURN @hr
END
RETURN 0
END
GO
CREATE FUNCTION system_function_schema.fn_setarray(@obj int,
@hdl int, @index int, @value sql_variant)
RETURNS int
AS
BEGIN
DECLARE @hr int
exec @hr=sp_OAMethod @obj, 'VBSetArray', NULL, @hdl, @index,
@value
IF (@hr <> 0) BEGIN
RETURN @hr
END
RETURN 0
END
GO
CREATE FUNCTION system_function_schema.fn_getarray(@obj int,
@hdl int, @index int)
RETURNS sql_variant
AS
BEGIN
DECLARE @hr int, @valuestr varchar(8000)
exec @hr=sp_oamethod @obj, 'VBGetArray', @valuestr OUT, @hdl,
@index
IF (@hr <> 0) BEGIN
RETURN @hr
END
RETURN(@valuestr)
END
GO
CREATE FUNCTION system_function_schema.fn_arraylen(@obj int,
@hdl int)
RETURNS int
AS
BEGIN
DECLARE @hr int, @len int
exec @hr=sp_oamethod @obj, 'VBArrayLen', @len OUT, @hdl
IF (@hr <> 0) BEGIN
RETURN @hr
END
RETURN @len
END
GO
CREATE FUNCTION system_function_schema.fn_listarray(@obj int,
@hdl int)
RETURNS @array TABLE (idx int, value sql_variant)
AS
BEGIN
DECLARE @i int, @cnt int
SET @cnt=fn_arraylen(@obj,@hdl)
SET @i=1
WHILE (@i<=@cnt) BEGIN
INSERT @array VALUES (@i, fn_getarray(@obj,@hdl,@i))
SET @i=@i+1
END
RETURN
END
GO
EXEC sp_configure 'allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO
|
By wrapping the sp_OA calls in system UDFs, we make these COM object–based array functions much easier to use. Listing 15.15 demonstrates how to use them.
Listing 15.15. Using the Array Functions
DECLARE @obj int, @hdl int, @siz int, @res int SET @siz=1000 -- Create the array and return its handle and length SET @obj=fn_createobject() SET @hdl=fn_createarray(@obj,@siz) SELECT @hdl, fn_arraylen(@obj,@hdl) -- Set elements 1, 10, 998, and 1000 SELECT @res=fn_setarray(@obj,@hdl,1,'test1'), @res=fn_setarray(@obj,@hdl,10,'test10'), @res=fn_setarray(@obj,@hdl,998,'test998'), @res=fn_setarray(@obj,@hdl,1000,'test1000') -- Get element 10 SELECT fn_getarray(@obj,@hdl,10) -- Get element 998 SELECT fn_getarray(@obj,@hdl,998) -- List the array SELECT * FROM ::fn_listarray(@obj, @hdl) WHERE value IS NOT NULL SET @res=fn_destroyarray(@obj,@hdl) SET @res=fn_destroyobject(@obj) (Results) ----------- ----------- 1 1000 ------------------------------------------------------------------ test10 ------------------------------------------------------------------ test998 idx value ----------- ------------------------------------------------------ 1 test1 10 test10 998 test998 1000 test1000 |
As you can see, creating the array in the first place, then adding elements to it and retrieving those elements is extremely easy. Note that since these are functions, we can use them to process data in tables and views, as shown in Listing 15.16.
Listing 15.16. Using the Array Functions with Table/View Data
DECLARE @o int, @h int, @res int, @arraybase int -- Create the object and the array SET @o=fn_createobject() SELECT @h=fn_createarray(@o,1000), @arraybase=10247 -- Load all the Order dates into it SELECT @res=fn_setarray(@o,@h,OrderId-@arraybase,OrderDate) FROM Northwind..orders -- List an array element SELECT idx+@arraybase AS OrderId, value AS OrderDate FROM ::fn_listarray(@o,@h) WHERE idx=10249-@arraybase -- Destroy the array and the object SET @res=fn_destroyarray(@o,@h) SET @res=fn_destroyobject(@o) (Results) OrderId OrderDate ----------- ------------------------------------------------------ 10249 NULL |
Here, we load the OrderDate column from the Northwind Orders table into our array using a SELECT statement and our fn_setarray function. Notice how we're able to load the entire table with a single SELECT statement. We then query the array like a table using the fn_listarray table-value function and filter the query using the array index.
As I'm sure you've surmised by now, there are numerous uses for an array-like facility in Transact-SQL. Even when coding in a set-oriented language, you still occasionally run into situations where an array is the right tool for the job. You can use the array code presented here to address those situations.