A Simple Example | The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML

for RuBoard

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 FileNewExtended 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)] = '  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 ; } 
 ';  //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]='  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 ; } 
 ';                            }                            if (LineText[strlen(LineText)-1]=='\r') {                                   LineText[strlen(LineText)-1]='  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 ; } 
 ';                            }                            if (strlen(LineText)==0) {  //Filter out NULLs                                   LineText[0]=' ';                                   LineText[1]='  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 ; } 
 ';                            }                            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:\WINNT\ 17          23:44:44 Program Files=C:\Program Files\ 18          23:44:44 TEMPDIR=C:\WINNT\TEMP\ 19          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 .

for RuBoard