لطفا منتظر باشید ...
A Simple Example
If you're new to C/C++ development, the preceding overview of extended procedure development may have been a bit daunting. It's really not as bad as it might seem, though. Studying the source code to a simple extended procedure will likely allay any concerns you might have, so that's what we'll do shortly.
Creating your own extended stored procedure is as easy as selecting File|New|Extended Proc wizard in the Visual C++ development environment. The wizard will construct an extended procedure workspace for you, complete with INCLUDE statements for the ODS header files and a sample extended stored procedure. You can then modify this code to do what you need.
The routine that follows in Listing 20-6 is an extended procedure that returns the contents of a text file as a result set. It takes one parameterthe name of the fileand returns a result set containing two columns: a line number and the text for each line. Here's the code:
Listing 20-6 The entire xp_listfile routine.
RETCODE __declspec(dllexport) xp_listfile(SRV_PROC *srvproc)
{
DBINT i = 0;
DBCHAR ColName[MAXCOLNAME];
DBCHAR LineText[MAXTEXT];
DBCHAR FileName[MAXFILENAME];
FILE *f;
// STEP 1: Process parameters
// Check number of parameters.
if ( srv_rpcparams(srvproc) != 1) {
// Send error message and return.
srv_sendmsg(srvproc, SRV_MSG_ERROR, LISTFILE_ERROR,
SRV_INFO, (DBTINYINT) 0,
NULL, 0, 0,
"Error executing extended proc: 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);
}
// Check parameter for correct type.
if (srv_paramtype(srvproc, 1) != SRVVARCHAR) {
// Send error message and return.
srv_sendmsg(srvproc, SRV_MSG_ERROR, LISTFILE_ERROR,
SRV_INFO, (DBTINYINT) 0,
NULL, 0, 0,
"Error executing extended proc: 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);
}
// Terminate parameter string with NULL.
memcpy(FileName, srv_paramdata
(srvproc, 1), srv_paramlen(srvproc, 1));
FileName[srv_paramlen(srvproc, 1)] = '\0';
//STEP 2: Set up the column names
wsprintf(ColName, "LineNo");
srv_describe(srvproc, 1, ColName, SRV_
NULLTERM, SRVINT4, sizeof(DBINT), SRVINT4,
sizeof(DBINT), 0);
wsprintf(ColName, "Text");
srv_describe(srvproc, 2, ColName, SRV_
NULLTERM, SRVCHAR, MAXTEXT, SRVCHAR, 0, NULL);
// STEP 3: Read the text file and send back results, line by line
if( (f = fopen( FileName, "r" )) != NULL ) {
while (!feof(f)) {
if (fgets(LineText,MAXTEXT,f) != NULL) {
srv_setcoldata(srvproc, 1, (void *)&++i);
// Remove CR/LF at EOL
if (LineText[strlen(LineText)-1]=='\n') {
LineText[strlen(LineText)-1]='\0';
}
if (LineText[strlen(LineText)-1]=='\r') {
LineText[strlen(LineText)-1]='\0';
}
if (strlen(LineText)==0) { //Filter out NULLs
LineText[0]=' ';
LineText[1]='\0';
}
srv_setcoldata(srvproc, 2, LineText);
srv_setcollen(srvproc, 2, strlen(LineText));
// Send the row
if (srv_sendrow(srvproc) == FAIL) {
srv_sendmsg(srvproc, SRV_MSG_ERROR,
LISTFILE_ERROR,
SRV_INFO, (DBTINYINT) 0,
NULL, 0, 0,
"Error sending extended proc results.",
SRV_NULLTERM);
return (XP_ERROR);
}
}
}
fclose(f);
}
else {
// Send error message and return.
srv_sendmsg(srvproc, SRV_MSG_ERROR, LISTFILE_ERROR,
SRV_INFO, (DBTINYINT) 0,
NULL, 0, 0,
"Error executing extended stored procedure: Unable to open file.",
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 4: Return the number of rows processed
srv_senddone(srvproc, SRV_DONE_MORE | SRV_
DONE_COUNT, (DBUSMALLINT)0, i);
return XP_NOERROR ;
}
I've broken the code into four distinct steps. Examine the comments in the source code to see where each step begins and ends.
In step 1 we process the parameters passed into the routine. If we receive the wrong number of parameters, or one that's an unexpected type, we send the client an error message and abort.
In step 2 we set up the column names for the result set. As I mentioned earlier, we do this in ODS using the srv_describe() function. Once we've set up the columns, we're ready to start loading them with data and sending that data back to the client.
In step 3 we iterate through the text file, loading each line into a row and sending that row to the client as we go. We also send a line number with each row to make ordering the result set easy for the client.
In step 4 we return the total number of rows processed to the client. All we have to do here is send the final value of the line count variable we incremented as we processed the file.
Once we compile and link the extended procedure, we copy its DLL to SQL Server's BINN folder. We can then add the procedure to the master database using sp_addextendedproc, like this (Listing 20-7):
Listing 20-7 Adding an extended procedure.
USE master
GO
EXEC sp_addextendedproc 'xp_listfile','xp_listfile.dll'
Once the procedure is available in the master, we can call it from Transact-SQL like any other procedure:
EXEC master.dbo.xp_listfile 'C:\ WINNT\ sqlstp.log'
(Results abridged)
LineNo Text
----------- ----------------------------
1 23:44:44 Begin Setup
2 23:44:44 8.00.194
3 23:44:44 Mode = Normal
4 23:44:44 ModeType = NORMAL
5 23:44:44 GetDefinitionEx
returned: 0, Extended: 0x0
6 23:44:44 ValueFTS returned: 1
7 23:44:44 ValuePID returned: 1
8 23:44:44 ValueLic returned: 0
9 23:44:44 System: Windows NT WorkStation
10 23:44:44 SQL Server
ProductType: Personal Edition [0x2]
11 23:44:44 Begin Action: SetupInitialize
12 23:44:44 End Action SetupInitialize
13 23:44:44 Begin Action: SetupInstall
14 23:44:44 Reading Software\Microsoft\Windows\...
15 23:44:44 CommonFilesDir=C:\Program Files\Common Files
16 23:44:44
Windows Directory=C:\WINNT17 23:44:44
Program Files=C:\Program Files18
23:44:44 TEMPDIR=C:\WINNT\TEMP19
23:44:44 Begin Action: SetupInstall
20 23:44:44 digpid size : 256
21 23:44:45 digpid size : 164
22 23:44:45 Begin Action: CheckFixedRequirements
23 23:44:45 Platform ID: 0xf000000
24 23:44:45 Version: 5.0.2195
25 23:44:45 File Version -
C:\WINNT\System32\shdocvw.dll: 5.0.2920.0
26 23:44:45 End Action: CheckFixedRequirements
27 23:44:45 Begin Action: ShowDialogs
Dropping the procedure is as easy as adding it. You just call sp_dropextendedproc (Listing 20-8):
Listing 20-8 Dropping an extended procedure.
USE master
GO
EXEC sp_dropextendedproc 'xp_listfile'
If you need to replace an extended procedure after it has been loaded by SQL Server, you'll first have to free the DLL that hosts it using DBCC dllname(FREE), like this (Listing 20-9):
Listing 20-9 Unloading an extended procedure's DLL.
USE master
GO
DBCC xp_listfile(FREE)
NOTE
The undocumented procedure xp_readerrorlog can also read a plain text file (not just an error log) and return it as a result set. See Chapter 22 for more info.
