Home

	Expand Search

Transact-SQL User's Guide

Chapter 15 Using Extended Stored Procedures

Overview

Overview

Extended stored procedures provide a way to dynamically load and execute external procedural language functions from within Adaptive Server. Each ESP is associated with a corresponding function, which is executed when the ESP is invoked from Adaptive Server.

An ESP allows Adaptive Server to perform a task outside Adaptive Server in response to an event occurring within Adaptive Server. For example, you could create an ESP function to sell a security (a task performed outside Adaptive Server). This function is invoked in response to a trigger that is fired when the price of the security reaches a certain value. Or you could create an ESP function that sends an e-mail notification or a network-wide broadcast in response to an event occurring within the relational database system.

For the purposes of ESPs, "a procedural language" is a programming language that is capable of calling a C language function and manipulating C language datatypes.

After a function has been registered in a database as an ESP, it can be invoked just like a stored procedure from isql, from a trigger, from another stored procedure, or from a client application.

ESPs can:

• Take input parameters

• Return a status value indicating success or failure and the reason for the failure

• Return values of output parameters

• Return result sets

Adaptive Server supplies some system ESPs. For example, one system ESP, xp_cmdshell, executes an operating system command from within Adaptive Server. You can also write your own ESPs using a subset of the Open Server application programming interface (API).

XP Server

Extended stored procedures are implemented by an Open Server application called XP Server, which runs on the same machine as Adaptive Server. Adaptive Server and XP Server communicate through remote procedure calls (RPCs). Running ESPs in a separate process protects Adaptive Server from a failure resulting from faulty ESP code. The advantage of using ESPs instead of RPCs is that the ESP runs in Adaptive Server the same way a stored procedure runs; you do not need to have Open Server to run the ESP.

XP Server is automatically installed when Adaptive Server is installed.

XP Server must be running for Adaptive Server to execute an ESP. Adaptive Server starts XP Server the first time an ESP is invoked and shuts down XP Server when Adaptive Server itself exits.

On Windows NT, if the start mail session configuration parameter is set to 1, XP Server automatically starts when Adaptive Server starts.

Dynamic Link Library Support

The procedural functions that contain the ESP code are compiled and linked into dynamic link libraries (DLLs), which are loaded into XP Server memory in response to an ESP execution request. The library remains loaded unless one of the following occurs:

• XP Server exits

• The sp_freedll system procedure is invoked

• The esp unload dll configuration parameter is set using sp_configure

Open Server API

Adaptive Server uses the Open Server API, which allows users to run the system ESPs provided with Adaptive Server. Users can also implement their own ESPs using the Open Server API.

Table 15-1 lists the Open Server routines required for ESP development. For complete documentation of these routines, see the Open Server Server-Library/C Reference Manual.

Example of creating and using ESPs

After an ESP function has been written, compiled, and linked into a DLL, you can create an ESP for the function using the as external name clause of the create procedure command:

create procedure procedure_name [parameter_list]
     as external name dll_name

procedure_name is the name of the ESP, which must be the same as the name of its implementing function in the DLL. ESPs are database objects, and their names must follow the rules for identifiers.

dll_name is the name of the DLL in which the implementing function is stored.

The following statement creates an ESP named getmsgs, which is in msgs.dll. The getmsgs ESP takes no parameters. This example is for a Windows NT Adaptive Server:

create procedure getmsgs 
as external name "msgs.dll"

On a Solaris Adaptive Server, the statement is:

create procedure getmsgs 
as external name "msgs.so"

This reflects the Solaris naming conventions.

The next statement creates an ESP named getonemsg, which is also in msgs.dll. The getonemsg ESP takes a message number as a single parameter.

create procedure getonemsg @msg int
as external name "msgs.dll"

The platform-specific naming conventions for the DLL extension are summarized in Table 15-2.

When Adaptive Server creates an ESP, it stores the procedure's name in the sysobjects system table, with an object type of "XP" and the name of the DLL containing the ESP's function in the text column of the syscomments system table.

Execute an ESP as if it were a user-defined stored procedure or system procedure. You can use the keyword execute and the name of the stored procedure, or just give the procedure's name, as long as it is submitted to Adaptive Server by itself or is the first statement in a batch. For example, you can execute getmsgs in any of these ways:

getmsgs

execute getmsgs

exec getmsgs

You can execute getonemsg in any of these ways:

getonemsg 20

getonemsg @msg=20

execute getonemsg 20

execute getonemsg @msg=20

exec getonemsg 20

exec getonemsg @msg=20

ESPs and Permissions

You can grant and revoke permissions on an ESP as you would on a regular stored procedure.

In addition to the normal Adaptive Server security, you can use the xp_cmdshell context configuration parameter to restrict execution permission of xp_cmdshell to users who have system administration privileges. Use this configuration parameter to prevent ordinary users from using xp_cmdshell to execute operating system commands that they would not have permission to execute directly from the command line. The behavior of the xp_cmdshell configuration parameter is platform-specific.

By default, a user must have the sa_role to execute xp_cmdshell. To grant permission to other users to use xp_cmdshell, use the grant command. You can revoke the permission with revoke. The grant or revoke permission is applicable whether xp_cmdshell context is set to 0 or 1.

ESPs and Performance

Since both Adaptive Server and XP Server reside on the same machine, they can affect each other's performance when XP Server is executing a function that consumes significant resources.

You can use sp_configure to set two parameters, esp execution priority and esp unload dll, to control the impact of XP Server on Adaptive Server by setting priorities for ESP execution and by freeing XP Server memory.

Use esp execution priority to set the priority of the XP Server thread high, so the Open Server scheduler runs it before other threads on its run queue, or low, so the scheduler runs XP Server only when there are no other threads to run. The default value of esp execution priority is 8, but you can set it anywhere from 0 to 15. See the discussion of multithread programming in the Open Server Server-Library/C Reference Manual for information about scheduling Open Server threads.

You can minimize the amount of memory XP Server uses by unloading a DLL from XP Server memory after the ESP request that loaded it terminates. To do so, set esp unload dll so that the DLLs are automatically unloaded when ESP execution finishes. If esp unload dll is not set, you can free DLLs explicitly by using sp_freedll.

You cannot unload DLLs that support system ESPs.