Open Data Services

for RuBoard

The ODS API is discussed at length in the Books Online, so I won't rehash it, but here's a broad overview:

• ODS is a Call Level Interface (CLI) for building extended procedures and database gateways. Microsoft has deprecated database gateways in favor of linked servers, so these days ODS is used primarily for building extended procedures.

• By CLI, I mean that the interface is non-COM. It's just a traditional DLL interface. The DLL exports functions that applications can call.

• The ODS API is implemented in a DLL named OPENDS60.DLL. In SQL Server 2000 and later, this DLL is merely a proxy for the ODS routines themselves , which are implemented internally by the server. If you compare the size of the OPENDS60.DLL for SQL Server 7.0 with that for 2000, you'll see that the 2000 version is much smaller. This is because ODS is no longer implemented in the DLL itself.

• To make use of the ODS API, you typically link OPENDS60.LIB into a C++ project. If you use the Visual C++ Extended Procedure wizard, this is taken care of for you.

Start-up Code

Every extended procedure is passed a handle to the server process that called it. The interface to an extended procedure looks like this in C (Listing 20-1):

Listing 20-1 The interface to an extended procedure.

 RETCODE __declspec(dllexport) xp_foo(  SRV_PROC *srvproc  ) { ... } 

The dllexport directive makes the routine visible to the outside world. It will be exported from the DLL using the name of the function itself. When you execute the procedure from Transact-SQL, SQL Server calls the Win32 API LoadLibrary() routine to load the DLL (if it's not already loaded) and uses GetProcAddress() to retrieve the procedure's address in the DLL. Once it locates this address, SQL Server casts it to a function pointer matching the specification in Listing 20-1.

SRV_PROC is a C structure defined in the ODS header file srvstruc.h that looks like this (Listing 20-2):

Listing 20-2 The SRV_PROC structure.

 typedef struct srv_proc { WORD                tdsversion; WORD                status; SRV_IO              srvio; SRV_LOGINREC        login; void                *langbuff; unsigned long       langlen; unsigned int        event; void                *server; char                *threadstack; THREAD              threadID; HANDLE              threadHDL; HANDLE              iowakeup; HANDLE              exited; DBINT               rowsent; SRV_COLDESC         *coldescp; DBUSMALLINT         coldescno; BYTE                *colnamep; WORD                colnamelen; void                *userdata; void                *event_data; BYTE                serverlen; BYTE                *servername; BYTE                rpc_active; BYTE                rpc_server_len; BYTE                *rpc_server; BYTE                rpc_database_len; BYTE                *rpc_database; BYTE                rpc_owner_len; BYTE                *rpc_owner; BYTE                rpc_proc_len; BYTE                *rpc_proc_name; unsigned int        rpc_proc_number; unsigned int        rpc_linenumber; unsigned short      rpc_options; unsigned short      rpc_num_params; SRV_RPCp            **rpc_params; BYTE                non_rpc_active; unsigned short      non_rpc_num_params; SRV_RPCp            **non_rpc_params; char                temp_buffer[100]; SRV_SUBCHANNEL      *(*subprocs); TRANSLATION_INFO    translation_info; struct              srv_listentry IOListEntry; struct              srv_listentry CommandListEntry; PSRV_LISTHEAD       pNetListHead; BOOL                bNewPacket; long                StatusCrit; void                *serverdata; SRV_SUBCHANNEL      *subchannel; SRV_PEVENTS         *pre_events; SRV_PEVENTS         *post_events; void                *p_langbuff; BOOL                fSecureLogin; BOOL                fInExtendedProc; Unsigned            fLocalPost:1; unsigned            fMadeBoundCall:1; unsigned            uFill1:30; SRV_COMPORT_QUEUE   comport_queue; void                *pSF1; void                *pSF2; HANDLE              hPreHandlerMutex; HANDLE              hPostHandlerMutex; BOOL                bSAxp; }                    SRV_PROC; 

You can review srvstruc.h yourself for details, but, suffice it to say, every server process structure carries with it a large and diverse collection of data.

Extended Procedure Activities

Extended procedures usually take a parameter or parameters and return a result set or output variable to the calling connection. Often, extended procedures also generate messages that they send back to the client. Although it's possible to create extended procedures that take no parameters and produce no output, these are really pretty rare.

Sending Messages

You use the ODS srv_sendmsg() function to send a message from an extended procedure to the calling connection. Calls to srv_sendmsg() look like this (Listing 20-3):

Listing 20-3 Sending a message using ODS.

 srv_sendmsg(srvproc, SRV_MSG_ERROR, LISTFILE_ERROR,     SRV_INFO, (DBTINYINT) 0,     NULL, 0, 0,  "Error executing procedure: Invalid number of parameters   .   ",  SRV_NULLTERM); 

The text string you see in the middle of the call is the actual message. The other parameters specify attributes such as the message state, the line on which it occurred, and the message's type. See the Books Online for details.

Processing Parameters

You process the parameters passed to an extended procedure using srv_paraminfo(), srv_paramdata(), srv_paramtype(), and similar ODS API functions. The srv_paramtype() function allows you to check the data type of a given parameter, whereas srv_paramdata() retrieves the parameter's value. Here's an example of how these two functions are used (Listing 20-4):

NOTE

The srv_paraminfo() function can return all the info that srv_paramtype(), srv_paramlen(), and srv_paramdata() return in a single call. Moreover, it supports the new data types available in SQL Server 7.0 and later. Because of this, using srv_paraminfo() is preferable to calling these other functions. See the Books Online for details.

Listing 20-4 Processing parameters with ODS.

 // 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 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);     } // Terminate parameter string with NULL. memcpy(FileName,  srv_paramdata  (srvproc, 1), srv_paramlen(srvproc, 1)); FileName[srv_paramlen(srvproc, 1)] = ' 
 // 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 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);     } // Terminate parameter string with NULL. memcpy (FileName,  srv_paramdata  (srvproc, 1), srv_paramlen(srvproc, 1)); FileName[srv_paramlen(srvproc, 1)] = '\0'; 
 '; 

In each of the calls in bold type, the first parameter passed into the function is the server process handle. This is the convention with ODS API calls: The server process handle is usually the first (and sometimes the only) parameter passed into the routine. The second parameter in both examples is the parameter number for which to retrieve info. In both of the examples here, we're inquiring about the first parameter passed into the procedure.

Returning Data

Every column that is to be returned by an extended procedure must first be described using the srv_describe() function. Here's an example:

 srv_describe(srvproc,  1,  ColName, SRV_NULLTERM, SRVINT4, sizeof(DBINT),              SRVINT4, sizeof(DBINT), 0); 

Here we provide, among other things, the name and data type of the first result set column. The second parameter (in bold type) is the ordinal position of the column in the result setthe column number.

Before each row can be sent back to the server, srv_setcoldata is called to assign individual column values. Here's an example of a call to srv_setcoldata():

 srv_setcoldata(srvproc, 2, LineText); 

Here, LineText is a string variable that contains the data we want to assign to the column. Because a string in C and C++ is really a pointer to an array of characters , what we're really passing to srv_setcoldata() is the address of that array. The first two parameters passed to srv_setcoldata() should be familiar by now: They're the server process handle and the column number respectively.

Once all the column values have been set, each result set row is sent back to the server using srv_sendrow(). Listing 20-5 provides an example:

Listing 20-5 Sending result rows with ODS.

 if (  srv_sendrow(srvproc)  == FAIL) {        srv_sendmsg(srvproc, SRV_MSG_ERROR, LISTFILE_ERROR,        SRV_INFO, (DBTINYINT) 0,        NULL, 0, 0,        "Error executing extended stored procedure: Unable to send results.",        SRV_NULLTERM);        return (XP_ERROR); } 

We begin by calling srv_sendrow() and checking its return value. Notice that its only parameter is the server process handle. By the time it's called, all column values have already been added to the process structure. If the function fails, we send a message to the server indicating the problem, and return an error code from the extended procedure. If the function succeeds, we continue to send result rows to the server until we're finished, then we call srv_senddone() like this:

 srv_senddone(srvproc,  SRV_DONE_MORE  SRV_DONE_COUNT,  (DBUSMALLINT)0, i); 

Notice the bitmap value that's passed as the second parameter to the function (in bold type). Typically, extended procedures should always include SRV_DONE_MORE when calling srv_senddone(). SRV_DONE_MORE indicates that more results are pending; SRV_DONE_FINAL indicates that no more remain . It might seem that SRV_DONE_FINAL should be passed when the extended procedure has no more rows to send; however, SRV_DONE_FINAL is only used for ODS gateway applications, not extended procedures (the ODS API can be used to construct either; however, ODS gateway applications have been deprecated in favor of linked servers). When an extended procedure completes, the server itself sends SRV_DONE_FINAL, so the procedure itself must not.

for RuBoard