The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

xp_array.dll

Let's begin by taking a tour through xp_array.dll because it contains the code that does the real array processing. As you'll see in a moment, I've wrapped these procedures in system functions to make them easier to use, but you can still call the procedures directly if you wish. You can find xp_array.dll (and its full source code) on the CD accompanying this book. Although it's compiled with a beta version of Visual Studio 7, it will compile just as easily in Visual Studio 6. I've used nothing from the .NET Frameworks or anything that's Visual Studio specific in it.

Xp_array.dll exports five extended procedures: xp_createarray, xp_setarray, xp_getarray, xp_destroyarray, and xp_listarray. To make these routines available on your SQL Server, copy xp_array.dll to the \BINN folder under your SQL Server instance folder (be sure this is the BINN folder associated with your SQL Server instance and not the one in which the tools binaries are storedyou should see other xp*.dll files here). Once the DLL is copied, run the following script:

EXEC sp_addextendedproc 'xp_createarray','xp_array.dll'
EXEC sp_addextendedproc 'xp_setarray','xp_array.dll'
EXEC sp_addextendedproc 'xp_getarray','xp_array.dll'
EXEC sp_addextendedproc 'xp_destroyarray','xp_array.dll'
EXEC sp_addextendedproc 'xp_listarray','xp_array.dll'

This will make the extended procedures contained in xp_array.dll available from Transact-SQL. Table 23-1 lists their functions.

xp_createarray

The best way to understand each of the routines in xp_array is to examine its source code. Let's begin with xp_createarray. Here's its source code:

RETCODE __declspec(dllexport) xp_createarray(SRV_PROC *srvproc)
{
int nParams;
int size;
char sizestr[30];
PBYTE*      array      = NULL;
BYTE  pbType;
ULONG  pcbMaxLen;
ULONG  pcbActualLen;
BOOL  pfNull;
nParams = srv_rpcparams(srvproc);
// Check number of parameters
if (nParams != 2) {
// Send error message and return
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_ARRAY_ERROR, SRV_INFO,
(DBTINYINT)0,
NULL, 0, 0, "Error executing extended
 stored procedure: Invalid number of
parameters",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_
FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
if (!IntParam(1)) {
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_ARRAY_ERROR, SRV_INFO,
(DBTINYINT)0,
NULL, 0, 0,
"Error executing extended stored procedure: Invalid Parameter Type",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
if (!IntParam(2)) {
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_
ARRAY_ERROR, SRV_INFO, (DBTINYINT)0,
NULL, 0, 0,
"Error executing extended stored procedure: Invalid Parameter Type",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
srv_paraminfo(srvproc,2,&pbType, &pcbMaxLen,
 &pcbActualLen, (BYTE *)&size, &pfNull);
++size;  // Add one for the length element
/* Step 1 -- Allocate a buffer for the array */
array = (PBYTE*) malloc(size * sizeof(PBYTE));
/* Step 2 -- Clear the array */
memset(array, 0, size * sizeof(PBYTE));
/* Step 3 -- Set the first element to length of the array */
itoa(size,sizestr,10);
setelement(array,0,sizestr);
/* Step 4 -- Return a pointer to the array in the output param */
srv_paramsetoutput(srvproc, 1, (BYTE *)&array, 4, FALSE);
return XP_NOERROR ;
}

Xp_createarray takes two parameters: an integer output parameter, in which it will store the handle (a pointer) to the newly created array, and an integer that specifies the size of the array. It begins by allocating a buffer of the appropriate size by multiplying the number of elements requested by the size of a pointer (4 bytes) because each element will be a pointer to a string when it's allocated (Step 1). Next, it initializes the array with zeros so that it will be able to distinguish between allocated and unallocated elements (Step 2). Third, it sets the first element of the array (element zero) to a string containing the length of the array. In this way, the array is self-contained; it knows how large it is. This will come in handy when it's time to deallocate the array. Fourth, and last, it returns a pointer to the array in the procedure's output parameter.

xp_setarray

Next up is xp_setarray. It allows us to set an array element to a specific value. Here's its source code:

RETCODE __declspec(dllexport) xp_setarray(SRV_PROC *srvproc)
{
int         nParams;
DBINT       paramtype;
int index;
int                 handle;
TCHAR  szValue[8000+1];
PBYTE*      array      = NULL;
BYTE  pbType;
ULONG  pcbMaxLen;
ULONG  pcbActualLen;
BOOL  pfNull;
nParams = srv_rpcparams(srvproc);
// Check number of parameters
if (nParams != 3) {
// Send error message and return
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_
ARRAY_ERROR, SRV_INFO, (DBTINYINT)0,
NULL, 0, 0, "Error executing extended stored procedure: Invalid
number of parameters",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
if (!IntParam(1)) {
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_
ARRAY_ERROR, SRV_INFO, (DBTINYINT)0,
NULL, 0, 0,
"Error executing extended stored procedure: Invalid Parameter Type",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
if (!IntParam(2)) {
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_
ARRAY_ERROR, SRV_INFO, (DBTINYINT)0,
NULL, 0, 0,
"Error executing extended stored procedure: Invalid Parameter Type",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
paramtype = srv_paramtype(srvproc, 3);
if (paramtype != SRVVARCHAR) {
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_
ARRAY_ERROR, SRV_INFO, (DBTINYINT)0,
NULL, 0, 0,
"Error executing extended stored procedure: Invalid Parameter Type",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
srv_paraminfo(srvproc,1,&pbType, &pcbMaxLen,
 &pcbActualLen, (BYTE *)&handle,
&pfNull);
srv_paraminfo(srvproc,2,&pbType, &pcbMaxLen,
 &pcbActualLen, (BYTE *)&index,
&pfNull);
srv_paraminfo(srvproc,3,&pbType, &pcbMaxLen,
 &pcbActualLen, (BYTE *)szValue,
&pfNull);
array=(PBYTE *)handle;
/* Step 1: Check the element index to be sure it's valid */
if (index>(getarraysize(array)-1)) {
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_ARRAY_ERROR, SRV_INFO,
(DBTINYINT)0,
NULL, 0, 0,
"Error executing extended stored procedure: Array index out of range",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
/* Step 2: Null-terminate the value */
szValue[pcbActualLen]='\ 0';
/* Step 3: Set the element value */
return setelement(array,index,szValue);
}

Xp_setarray takes three parameters: the array handle, the element number to set, and a string containing the value to which to set it. After it validates the number of types of parameters, it begins by checking the specified element index to be sure that it's not passed the end of the array. If it's an invalid index, an error message is returned and the procedure exits. Next, xp_setarray null-terminates the value it has received from the user. Because xp_array processes array elements as strings, it's important that they're properly terminated. We find the start of each array element's data by computing its offset in the array buffer, and we find its end by searching for its null termination character.

Last, xp_setarray sets the element via a call to the setelement() internal function and returns the result to the client. Setelement() simply copies the string specified by the user into the array at the specified index.

xp_getarray

Xp_getarray retrieves a value from an array previously set by xp_setarray. Here's its source code:

RETCODE __declspec(dllexport) xp_getarray(SRV_PROC *srvproc)
{
int         nParams;
DBINT       paramtype;
int                 index;
int                 handle;
TCHAR  szValue[8000+1] = ";
PBYTE*      array      = NULL;
BYTE  pbType;
ULONG  pcbMaxLen;
ULONG  pcbActualLen;
BOOL  pfNull;
nParams = srv_rpcparams(srvproc);
// Check number of parameters
if (nParams != 3) {
// Send error message and return
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_ARRAY_ERROR, SRV_INFO,
(DBTINYINT)0,
NULL, 0, 0, "Error executing extended stored procedure: Invalid number of
parameters",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
if (!IntParam(1)) {
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_ARRAY_ERROR, SRV_INFO,
(DBTINYINT)0,
NULL, 0, 0,
"Error executing extended stored procedure: Invalid Parameter Type",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
if (!IntParam(2)) {
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_
ARRAY_ERROR, SRV_INFO, (DBTINYINT)0,
NULL, 0, 0,
"Error executing extended stored procedure: Invalid Parameter Type",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
paramtype = srv_paramtype(srvproc, 3);
if (paramtype != SRVVARCHAR) {
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_
ARRAY_ERROR, SRV_INFO, (DBTINYINT)0,
NULL, 0, 0,
"Error executing extended stored procedure: Invalid Parameter Type",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
srv_paraminfo(srvproc,1,&pbType, &pcbMaxLen,
 &pcbActualLen, (BYTE *)&handle,
&pfNull);
srv_paraminfo(srvproc,2,&pbType, &pcbMaxLen, 
&pcbActualLen, (BYTE *)&index,
&pfNull);
array=(PBYTE *)handle;
/* Step 1: Get the value requested by the user */
if (array[index]!=NULL) {
strcpy(szValue,(char *)array[index]);
}
/* Step 2: Return the value in the output parameter */
srv_paramsetoutput(srvproc, 3, (BYTE *)szValue, strlen(szValue), FALSE);
return XP_NOERROR;
}

Xp_getarray takes three parameters: an array handle, the index of the element to get, and an output parameter to receive the element value. Once its parameters are validated, xp_getarray begins by copying the value the user has requested into a character buffer (Step 1). It then copies this buffer into the output parameter (Step 2).

xp_destroyarray

As its name implies, xp_destroyarray deallocates an array that was previously created with xp_createarray. Here's its source code:

RETCODE __declspec(dllexport) xp_destroyarray(SRV_PROC *srvproc)
{
int         nParams;
int                 index;
int                 handle;
int                 size;
char msg[255];
PBYTE*      array      = NULL;
BYTE  pbType;
ULONG  pcbMaxLen;
ULONG  pcbActualLen;
BOOL  pfNull;
nParams = srv_rpcparams(srvproc);
// Check number of parameters
if (nParams != 1) {
// Send error message and return
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_ARRAY_ERROR, SRV_INFO,
(DBTINYINT)0,
NULL, 0, 0, "Error executing extended stored procedure: Invalid
number of parameters",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
if (!IntParam(1)) {
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_ARRAY_ERROR, SRV_INFO,
(DBTINYINT)0,
NULL, 0, 0,
"Error executing extended stored procedure: Invalid Parameter Type",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
srv_paraminfo(srvproc,1,&pbType, &pcbMaxLen, &pcbActualLen, (BYTE
*)&handle, &pfNull);
array=(PBYTE *)handle;
size=getarraysize(array);
/* Step 1: Free all array elements */
for (index = 0; index < size; index++)
if (array[index]!=NULL) free(array[index]);
/* Step 2: Free the array itself */
free(array);
return XP_NOERROR ;
}

Xp_destroyarray takes one parameter: the handle of the array that is to be deallocated. This is an integer that must have been returned by an earlier call to xp_createarray. Once it validates its lone parameter, xp_destroyarray begins by freeing all the elements in the array. It knows how many elements exist in the array by examining element 0 (getarraysize() returns the contents of element 0 as an integer). Once all elements have been freed, xp_destroyarray frees the array itself. You'll recall that the array itself is simply a collection of pointers. These pointers are initially null. An array element doesn't point to anything nor is any memory allocated for it until xp_setarray is called to set its value. This keeps the memory use of the array to a minimum while allowing for extremely large arrays.

xp_listarray

Xp_listarray returns the contents of an array as a rowset. Although it would certainly be possible to call xp_getarray in a loop to retrieve all the elements in an array one at a time, xp_listarray is more efficient because it grabs all the elements at once. Here's its source code:

RETCODE __declspec(dllexport) xp_listarray(SRV_PROC *srvproc)
{
int         nParams;
int                 index;
int                 handle;
int                 size;
int                 len;
char* emptystr = ";
PBYTE*      array       = NULL;
PBYTE*      ppData         = NULL;
BYTE  pbType;
ULONG  pcbMaxLen;
ULONG  pcbActualLen;
BOOL  pfNull;
nParams = srv_rpcparams(srvproc);
// Check number of parameters
if (nParams != 1) {
// Send error message and return
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_ARRAY_ERROR, SRV_INFO,
(DBTINYINT)0,
NULL, 0, 0, "Error executing extended stored procedure: Invalid number of
parameters",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
if (!IntParam(1)) {
srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_ARRAY_ERROR, SRV_INFO,
(DBTINYINT)0,
NULL, 0, 0,
"Error executing extended stored procedure: Invalid Parameter Type",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an extended stored procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
srv_paraminfo(srvproc,1,&pbType, &pcbMaxLen, &pcbActualLen, (BYTE
*)&handle, &pfNull);
array=(PBYTE *)handle;
size=getarraysize(array);
len = 255;
/* Step 1: Set up a result set */
for (index = 1; index < size; index++)
{
srv_describe(srvproc,
1,
"idx",
SRV_NULLTERM,
SRVINT4,               // Dest data type.
(DBINT) sizeof(SRVINT4),      // Dest data length.
SRVINT4,               // Source data type.
(DBINT) sizeof(SRVINT4),      // Source data length.
(PBYTE) &index);
srv_describe(srvproc,
2,
"value",
SRV_NULLTERM,
SRVVARCHAR,               // Dest data type.
(DBINT) len,      // Dest data length.
SRVVARCHAR,               // Source data type.
(DBINT) len,      // Source data length.
(PBYTE) NULL);
/* Step 2: Copy the array data into a row to send to the client */
if (array[index]!=NULL) srv_setcoldata(srvproc, 2, array[index]);
else srv_setcoldata(srvproc, 2, emptystr);
/* Step 3: Send the row to the client */
if (srv_sendrow(srvproc) != SUCCEED) goto safeexit;
}
safeexit:
/* Step 4: Indicate that the result set is complete */
if (index > 0)
srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT, (DBUSMALLINT)0,
index);
else
srv_senddone(srvproc, SRV_DONE_MORE, (DBUSMALLINT)0, (DBINT)0);
return XP_NOERROR ;
}

Xp_listarray takes one parameter: the handle of the array to list. It begins by setting up a result set in which it can return the array (Step 1). This result set has two columns: idx and value. The idx column will contain the index number of each array element; value will contain its string value.

Once the result set is initialized, xp_listarray iterates through the array, setting the result set columns to the appropriate array element values as it goes (Step 2). Once each result set row has been filled with column values, xp_list array returns it to the client via a call to srv_sendrow() (Step 3). Once all rows are sent, xp_listarray marks the result set as complete and exits. As you'll see in a moment, even though we can easily set up a table-valued UDF to return an array as a table, calling xp_listarray is much more efficient and fast.

To see how these work together, run the following script. It creates an array, sets an element, retrieves the element, lists the array, then destroys it. Here's the code:

DECLARE @hdl int, @siz int set @siz=1000
EXEC master..xp_createarray @hdl OUT, @siz
SELECT @hdl AS ArrayHandle
EXEC master..xp_setarray @hdl,998,'test5'
DECLARE @value varchar(30)
EXEC master..xp_getarray @hdl,998,@value OUT
SELECT @value AS ArrayValue
EXEC master..xp_listarray @hdl
EXEC master..xp_destroyarray @hdl

(Results abridged)

ArrayHandle
-----------
13910056
ArrayValue
------------------------------
test5
idx         value
----------- --------------------------------------------------------------
1
2
3
4
5
6
...
996
997
998         test5
999
1000