Microsoft SQL Server 6.5 and 7 have the powerful capability to make functions in DLL's available as stored procedures. Microsoft calls them Extended Stored Procedures. If you've read this article, you know what Extended Stored Procedures are, what you can do with them, and how to install them on a SQL Server. You should also be able to use the object-oriented framework I wrote, which makes writing Extended Stored Procedures with Delphi extremely easy.
I assume you are familiar with SQL Server and with the concept of stored procedures. The code and examples in this article apply both to SQL Server 6.5 and SQL Server 7.
Extended Stored Procedures (called xp's afterwards) are part of Microsoft's Open Data Services (ODS) for SQL Server. With ODS you can do three things:
In the following figure a graphical overview of the ODS architecture is given.
In this article I discuss the art of writing stored procedures with Delphi. Technically this DLL is part of SQL server, therefore programmer errors may corrupt your SQL Server, so it's not an art without danger.
Making parts of your application available on the server has some advantages, for example:
Xp's live in DLL's and can therefore be written in any language which can produce DLL's like Delphi can. Before going into detail about how to write xp's, first some examples from a user's point of view. Let's assume we have an xp called xp_incbyone1 which increments a given number by one. We can call xp_incbyone1 as followings:
declare @mynumber integer select @mynumber = 1 exec master..xp_incbyone1 @mynumber output select @mynumber
The declare statement declares a variable @mynumber of type integer. Next we set it to one, pass it to the xp and allow the xp to modify it by appending output to the parameter. Finally we display the number with a select statement to see if it has been updated. The result should be 2 of course.
In this example we have an xp which returns an output parameter. Xp's can also return a result set. The example xp_incbyone2 returns the number as a result set. The code to call it would be:
declare @mynumber integer select @mynumber = 1 exec master..xp_incbyone2 @mynumber
xp_incbyone2 will return a table of just one column and one row containing the value 1.
Both xp_incbyone1 and xp_incbyone2 are described in detail in the next section where I present the framework.
As you see, for users extended stored procedures work exactly like stored procedures. Just like stored procedures, extended stored procedures can return parameters and/or result sets.
Each implementation of an xp needs to do the same things:
Step 1 is necessary because, unless normal stored procedures, it is up to the programmer to validate any user-specified parameters for xp's. Step 2 and 3 are optional, and are applicable only if you return a result set. Step 4 is also optional, and applies only if you return output parameters.
The C programmer who wants to develop xp's has to install the SQL Server 7 development tools. This option can be turned on when installing SQL Server 7. In the directory \MSSQL7\devtools\ you will find all the required header files and demo-programs. Unfortunately, Inprise did not supply a translation of these header files with Delphi. Therefore I had to translate the most important parts by hand to Delphi. This means that you don't need to install the SQL Server 7 development tools if you use this framework to write xp's. If you want to add more pieces you will need this resource kit though. Or you can ask me if I've time to expand the framework a bit to cover the missing pieces. Note: in previous version of SQL Server the development tools were part of the the BackOffice resource kit.
In the previous paragraph 6 steps were mentioned each xp has to do. The framework makes step 1 through 4 easier by taking care of details. You also can use Delphi types, because the framework does type translation between SQL Server types and Delphi types. The framework takes entirely care off step 5 and 6.
You use this framework as follows:
To make this more concrete, let's implement the xp_incbyone1 stored procedure. The 1st step is to create a new object based on TSQLXProc and implement its Execute method. It's header looks like this:
type TXPIncByOne1 = class(TSQLXProc) function Execute: Boolean; override; end;
The Execute method looks like this:
function TXPIncByOne1.Execute: Boolean; begin Params := Params + 1; Result := True; end;
The 2nd step is to write a procedure that calls this object. This is the procedure that SQL Server is actually calling. For xp_incbyone1 it looks like this:
function xp_incbyone1(srvproc: PSRV_PROC): SRVRETCODE; cdecl; const ExpectedParams = 1; var xp: TSQLXProc; begin xp := TXPIncByOne1.Create(srvproc, ExpectedParams); Result := xp.Run; xp.Free; end;
It's that easy!
Let's look in more detail to the first step. The only thing you'll ever need to do is to implement the Execute method. This function returns True or False. If False is returned, an error is returned to the calling application or user. Exceptions are caught by the code that calls your Execute method and a similar error is returned to the calling application or user.
You have access to the parameters of a stored procedure by using the variant array Params. Parameters are numbered from one onwards. As noted earlier SQL Server does no type checking on xp parameters. The framework returns parameters as variants, so it's a bit more robust against different parameters, but variant conversion errors may occur if a parameter type mismatches. You might want to use the ODS API call srv_paramtype to explicitly retrieve and check parameter types, but so far I've not found a need this. Another solution for checking parameter types is to use the VarType function. See Table 1 for a list of Transact-SQL data types and corresponding Delphi data types.
If a parameter is Null, the Params property returns the variant type Null. Equally, if you want to return Null, set the corresponding parameter in Params to Null.
Let's look in more detail to the second step. This step will probably always be the same except for the value of the ExpectedParams const and the particular object to instantiate. This procedure is called by SQL Server with one parameter: srvproc. We pass this parameter to the instantiated object and we pass it the number of parameters to expect. If the actual number of parameters is different from this an error message will be send back to the calling application/user. Pass zero if you don't want to check for the number of parameters, for example to support a variable number of parameters.
Next we call the Run method of the instantiated object, which in turn will call our Execute method (surrounded by for example a try..except block). Finally we free the object.
Now let's tackle an xp which returns a result set. It's header is this:
type TXPIncByOne2 = class(TSQLXProc) function Execute: Boolean; override; end;
It's body is this:
function TXPIncByOne2.Execute: Boolean; var myint: integer; begin DescribeColumn('my column name', SRVINT4, 4, SRVINT4, 4, @myint); Myint := Params + 1; SendRow; Result := True; end;
And the procedure to call this object is this:
function xp_incbyone2(srvproc: PSRV_PROC): SRVRETCODE; cdecl; const ExpectedParams = 1; var xp: TSQLXProc; begin xp := TXPIncByOne2.Create(srvproc, ExpectedParams); Result := xp.Run; xp.Free; end;
We now have a bit more complicated Execute method. In case we want to return a result set, we need to describe every row in the resulting table: its column name, its destination type, its destination length, its source type, its source length and a pointer to the source data. You should call DescribeColumn for every column in the result table. The next step is to fill the source data, that's the assignment to myint. The row is now complete, so we can send it to SQL Server using SendRow. You should prepare source data and call SendRow for every row in the result table. And finally just return True and exit. After that SQL Server will send the entire result table to the client.
The xp_incbyone2 procedure is still a simple call the object and exit. In the remaining examples I will omit this procedure.
|ODS constant||TSQL data type(s)||Delhi data type(s)|
|SRVINTN||tinyint, smallint, int||shortint,smallint,integer|
|SRVFLTN||real, float||single, double|
|SRVMONEYN||smallmoney, money||n/a (integer, DBMONEY)|
I implemented two xp's from the sample xp's which Microsoft implemented in xp.c. The first one simply copies the contents of the first parameter to the second parameter. The second one returns the free space from every drive available on the SQL Server computer.
To avoid name clashes I called the first xp xp_delphiecho instead of xp_echo. The second one is called xp_delphidisklist instead of xp_disklist. Especially xp_echo looks ways more elegant than the Microsoft's sample program. You really should have a look at xp.c!
The code for xp_delphiecho is:
function TXPEcho.Execute: Boolean; begin Params := Params; Result := True; end;
The code for xp_delphidisklist is:
function TXPDiskList.Execute: Boolean; var drivename: char; space_remaining: Int32; drivenums: Int32; rootname: string; SectorsPerCluster, BytesPerSector, NumberOfFreeClusters, TotalNumberOfClusters: dword; function IsDrive(drive: char): Boolean; begin IsDrive := (drivenums and (1 shl (Ord(drive) - Ord('A')))) <> 0; end; begin DescribeColumn('drive', SRVCHAR, 1, SRVCHAR, 1, @drivename); DescribeColumn('bytes free', SRVINT4, 4, SRVINT4, 4, @space_remaining); drivenums := GetLogicalDrives; for drivename := 'C' to 'Z' do begin if IsDrive(drivename) then begin rootname := drivename + ':\'; GetDiskFreeSpace( PChar(rootname), SectorsPerCluster, BytesPerSector, NumberOfFreeClusters, TotalNumberOfClusters); space_remaining := SectorsPerCluster * NumberOfFreeClusters * BytesPerSector; SendRow; end; end; Result := True; end;
In the first two lines the description of the result table is given. The result table consists of two columns 'drive' and 'bytes free'. Next for every drive we fill the variables drivename and space_remaining and send back the row using SendRow.
The framework itself is in the unit odsxp.pas. In the following figure you see how this framework fits within the ODS architecture.
SQL Server loads and calls the DLL. You have written a simple method which creates an object of type TSQLXProc. You call its Run method.
The Run method does some checks and calls you back on a method you have written, the Execute method. When you are finished, you return to Run, which in return sends the results back to SQL Server.
All of the material in this section can also be found in the Microsoft SQL Programmers Toolkit or in the Microsoft Transact-SQL reference.
Installing xp's differs between SQL Server 6.5 and SQL Server 7.0. Everything that works under SQL Server 6.5 also works under SQL Server 7.
When you have compiled your DLL you have to install it in the appropriate directory. Copy the file to the same directory as the standard SQL Server DLL files. Usually this directory is something like c:\mssql\binn, note binn with two n's not the bin directory with a single n which also exists! As with other DLL's, once the extended stored procedure DLL is placed in the appropriate directory and the appropriate paths are set, you can make its functions available to users immediately. It is not necessary to restart the server.
For each function provided in an extended stored procedure DLL, a SQL Server system administrator must run the sp_addextendedproc system procedure, specifying the name of the function and the name of the DLL in which that function resides. For example:
sp_addextendedproc 'xp_delphiecho', 'xpdelphi.dll'
This command registers the function xp_delphiecho, located in the file xpdelphi.dll, as a SQL Server extended stored procedure. You must run sp_addextendedproc in the master database.
To drop individual extended stored procedures, a system administrator uses the system procedure sp_dropextendedproc.
Once a system administrator has added an extended stored procedure, users can find out what new functions are available by using the system procedure sp_helpextendedproc. When used without an argument, sp_helpextendedproc displays all extended stored procedures that are currently registered with the master database. If you specify an extended stored procedure name as an argument, sp_helpextendedproc verifies whether that function is currently available.
Extended Stored Procedures are subject to the same security mechanisms as regular stored procedure. For example to give every right on the xp_delphiecho xp, run the following command in the master database:
grant exec on xp_delphiecho to public
Every user can now call xp_delphiecho from every database by prefixing xp_delphiecho with 'master..'. For example to call xp_delphiecho from the pubs database you say:
exec master..xp_delphiecho @paramin, @paramout output
SQL Server loads an extended stored procedure DLL as soon as a call is made to one of the DLL's functions. The DLL remains loaded until the server is shut down or until the system administrator uses the DBCC command to unload it. For example:
This command unloads xpdelphi.dll, allowing the system administrator to copy in a newer version of this file without shutting down the server. You probably will need this command quite a lot to debug your xp's!
The following source files are provided:
A framework has been presented which makes writing Microsoft SQL Server Extended Stored Procedures a breeze. Four sample xp's have been shown and discussed. Let me know which interesting applications you have developed using this package!