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.

**Table 15-2: Naming conventions for DLL extensions**
Platform	DLL extension
Digital UNIX	.so
HP 9000/800 HP-UX	.sl
Sun Solaris	.so
Windows NT	.dll

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