SQL Server Extended Procedures Leveraging TSQL to centralize

  • Slides: 19
Download presentation
SQL Server Extended Procedures Leveraging T-SQL to centralize server functions

SQL Server Extended Procedures Leveraging T-SQL to centralize server functions

What are Extended Stored Procedures? Dynamic extensions to T/SQL ¡ Can be written in

What are Extended Stored Procedures? Dynamic extensions to T/SQL ¡ Can be written in any language that can produce DLLs ¡ Allows passing of data back and forth between the XP and the calling SP ¡ Can be a security risk ¡

Microsoft-supplied Sample XPs Available from Query Analyzer online help (Books Online) ¡ Useful reference

Microsoft-supplied Sample XPs Available from Query Analyzer online help (Books Online) ¡ Useful reference to start from ¡ Very C-oriented ¡ Requires large quantities of code to accomplish anything useful ¡ No examples of how to issue queries/commands from the XP ¡ See Item 1: Microsoft-provided Sample XP

software. AB Extended Procedures Can be used to perform any functions difficult or impossible

software. AB Extended Procedures Can be used to perform any functions difficult or impossible in T/SQL ¡ Make transferring files between the client application and the SQL Server easy and convenient ¡ Features extensible generic C++ class to interface with SQL Server ¡

Inside the XP ¡ Each DLL should have a version function exported: extern "C"

Inside the XP ¡ Each DLL should have a version function exported: extern "C" ULONG __Get. Xp. Version() { //lint !e 765 return ABR: : Sql. Server: : version() ; } //__Get. Xp. Version ¡ Exported functions should avoid C++ name-mangling and always receive a pointer to SRV_PROC: extern "C" __declspec(dllimport) int Some. Func. Name(SRV_PROC*);

The software. AB Approach ¡ ¡ ¡ Segregate SQL-Server specific header files into a

The software. AB Approach ¡ ¡ ¡ Segregate SQL-Server specific header files into a single module (helps to avoid namespace clashes). Avoid C: Develop a C++ class for each supported XP; within the C exported function create a class instance and thunk down immediately. Avoid multi-threading issues by defining a minimal number of global variables.

The C++ Assumptions ¡ ¡ ¡ software. AB uses a shallow, crossplatform, multi-threaded C++

The C++ Assumptions ¡ ¡ ¡ software. AB uses a shallow, crossplatform, multi-threaded C++ wrapper library to handle common functions (pipe interactions, debugging message support, process creation, etc. ) All software. AB C++ objects within our own DLLs are derived from a common base class. software. AB avoid multiple inheritance, threading, and direct memory management except when absolutely required. See Item 2: abr_common and abr_regex

SQL Server Integration ¡ ¡ ¡ Details are hidden using the Sql. Server class.

SQL Server Integration ¡ ¡ ¡ Details are hidden using the Sql. Server class. Class makes it easy to accept parameters from SP callers and to return result sets. All SQL Server data types are hidden via typedefs; prevents unwanted problems. Error messages from the XP are trapped and returned to the caller as SQL Server messages (display in the “Messages” window in Query Analyzer). Error message output controlled dynamically by registry settings. See Item 3: software. AB’s SQL Server XP Wrapper

General Tasks ¡ ¡ ¡ Ensure that logging messages can be sent (aids troubleshooting).

General Tasks ¡ ¡ ¡ Ensure that logging messages can be sent (aids troubleshooting). Access/store parameters from caller. Define the result set for the caller (where appropriate) Set output parameters for the caller (where appropriate). Ensure that an appropriate return code is registered with SQL Server

Logging Messages Back to SQL Server ¡ ¡ ¡ 10 14 16 17 ¡

Logging Messages Back to SQL Server ¡ ¡ ¡ 10 14 16 17 ¡ ¡ SQL Server Messages are broken down by message type, message number, message class, message state, and line number. software. AB's XP always uses SRV_MSG_INFO for the message type and ABR_XP_MSGNUM_ERROR for the message number. The message class is actually interpreted as “severity level” by SQL Server; software. AB uses the following: – – – - Informational Warning Error Fatal For the line number, software. AB always uses __LINE__ from the C++ source file and the C++ source file name as the first part of the actual message text. Use srv_sendmsg to send the message to SQL Server.

Storing Parameters from the calling SP ¡ ¡ Use srv_rpcparams to get the number

Storing Parameters from the calling SP ¡ ¡ Use srv_rpcparams to get the number of parameters. Store each parameter and its attributes using the following: srv_paramtype – returns one of SRVXXX (e. g. SRVVARCHAR). srv_parammaxlen – Max length of a parameter (such as 255 for a VARCHAR). srv_paramlen – Actual length of a paremeter. srv_paramdata – Raw data; must be typecast to the appropriate C++ type based on the parameter type. For NULL values, an actual NULL is passed to the C program by SQL Server. srv_paramstatus – Or against SRV_PARAMRETURN to determine if a parameter is OUTPUT or not. srv_paramname – Actual parameter name. Only passed if the calling SP provided the name. Typically, XPs use parameter ordering rather than parameter names. ¡ software. AB has a distinct class Sql. Server. Parm to hold these values.

Define result set for caller The software. AB Sql. Server object makes it easy

Define result set for caller The software. AB Sql. Server object makes it easy to define an output result set. Sample code: // define the Result. Set returned to the caller srv(). rs_col_describe( "n. Row. Number" , Sql. Server. Col: : type_int , Sql. Server. Col: : data_length ( Sql. Server. Col: : type_int ) ) ; srv(). rs_col_describe( "s. File. Name" , Sql. Server. Col: : type_string , 255 ) ; srv(). rs_col_describe( "d. Created" , Sql. Server. Col: : type_datetime , Sql. Server. Col: : data_length ( Sql. Server. Col: : type_datetime ) ) ;

Add Items to a Result. Set Sample Code: Sql. Server. Datetime dt. Created( fileinfo.

Add Items to a Result. Set Sample Code: Sql. Server. Datetime dt. Created( fileinfo. st_ctime ) ; Sql. Server. Datetime_raw. Ptr dt. Created_ptr = dt. Created. raw_ptr() ; srv(). rs_col_setdata( 1, row ) ; srv(). rs_col_setdata( 2, s. File. Name. c_str(), static_cast<int>(s. File. Name. size()) ) ; srv(). rs_col_setdata( 3, *dt. Created_ptr ) ; srv(). rs_send() ;

Feature Notes: Result. Set Processing ¡ ¡ rs_col_setdata is overloaded for all the standard

Feature Notes: Result. Set Processing ¡ ¡ rs_col_setdata is overloaded for all the standard C++ types. SQL Server datetime fields have special support for initialization from multiple time types. Time cracking utilities are important both for performance and configuration reasons; see Item 4: Time Conversion in the attached packet. Calling srv(). rs_send() sends the next row of data back to SQL Server using the srv_sendrow API. Only one row of data may be returned at a time.

Set Output Variables ¡ ¡ An XP call can have both output variables and

Set Output Variables ¡ ¡ An XP call can have both output variables and a result set. The software. AB class Sql. Server. Parm supports a set_output_data() method, which is overloaded for all necessary C++ types. Internally, Sql. Server. Parm uses the srv_paramset API to set the raw data bytes appropriately. Once set, no other work is required by the XP.

Setting XP Return Code to SQL Server ¡ ¡ Overall, SQL Server supports either

Setting XP Return Code to SQL Server ¡ ¡ Overall, SQL Server supports either an error or not an error through the srv_senddone API. The software. AB Sql. Server wrapper allows the caller to specify whether an error occurred or not. Note: The first parameter to srv_senddone has a number of possible bit settings; be sure never to use SRV_DONE_FINAL for extended procs since SQL Server itself will send the SRV_DONE_FINAL on your behalf.

General Notes ¡ ¡ ¡ XPs must use DBLIB (or some other client-side library

General Notes ¡ ¡ ¡ XPs must use DBLIB (or some other client-side library such as ODBC) to issue queries back to SQL Server. The software. AB Sql. Server class provides functionality to issue either queries or commands and to process results. Any connection made to SQL Server must differentiate between integrated security and SQL Server security. When using integrated security, the XP should use srv_impersonate_client to connect back to the named database as the connecting user who invoked the XP. DBLIB client-side functions do *not* support VARCHAR > 255 chars, although SQL Server supports up to VARCHAR(4000).

Installing Extended Procs ¡ ¡ They always install to the master database. Execute permission

Installing Extended Procs ¡ ¡ They always install to the master database. Execute permission must be explicitly granted to the roles you want allowed. By default, the DLL is placed in the SQL Server Binn directory. DLLs can’t be overwritten if they are in use; software. AB works around this by naming each XP DLL based on the version number. The software. AB install programs also check the version of the new DLL against the currently installed DLL and won’t install if the currently installed DLL has the same or greater version number. See Item 5: PHAMS Extended Proc Install Script

Invoking an Extended Procedure ¡ ¡ All calls must be made specifying the master

Invoking an Extended Procedure ¡ ¡ All calls must be made specifying the master database. Other than that, XP calls look exactly like SP calls. alter function uu_sp_encrypt( @s. Key_in varchar(255) , @s. Data_in varchar(255) ) returns varchar(255) as begin declare @s. Result varchar(255) if isnull(@s. Data_in, '') = '' set @s. Result = '' else exec master. dbo. uu_xp_Crypt_Encrypt @s. Key_in, @s. Data_in, @s. Result output return @s. Result end go