 |
MS SQL Server 7.0/2000
Extended Stored Procedure class for Visual C++
A Better Way To Write Your Own Extended Stored Procedures
|
Class feature overview
- Supported datatypes for input/output procedure parameters are:
bit, datetime, float, int, money, varbinary, varchar, nvarchar.
Datatype numeric (decimal) is also supported but with the limited precision.
Note: SQL Server 2000 introduces a new datatype — bigint.
It is recognized by Xp as numeric (decimal).
- Supported datatypes for recordset fields are the same as for the I/O parameters.
Plus supported BLOB fields such as
text, ntext, and image.
Note: SQL Server 7.0 doesn't support returning of text/ntext/image
fields from the Xp recordset while SQL 2000 (and later) does.
- Producing of arbitrary number of recordsets.
- Access to I/O parameters and recordset fields either by the index and by the name.
Number and datatypes of parameters may vary even for the particular procedure!
- Access to values of the Parameters and Fields using simple methods such
as
GetInt(), SetMoney(), etc.
- Small size of the compiled DLL since the
CXProc class doesn't using huge MFC Library.
Sample DLL takes about 56KB in VC++ 6.
- It is thread safe (but your code should be thread safe too).
Simple Sample
VC++ code
This code deals with two optional I/O parameters
(first them is varchar and second is int), modifies first (when present),
and then creates a recordset of 3 columns: int, datetime, and image.
[+] Let's see how it looks!
// Listing xprocdemovc.cpp: Using the CXProc class
#include "xproc.h"
#ifdef __cplusplus
extern "C" {
#endif
__declspec(dllexport) RETCODE xp_Class(SRV_PROC *srvproc);
ifdef __cplusplus
}
#endif
using namespace XProc;
RETCODE xp_Class(SRV_PROC *srvproc)
{
CXProc xproc(srvproc); // Initialize
CxpParams& p = xproc.Params(); // Remember the INPUT/OUTPUT params collection for future reference
size_t cnt = 0;
if (p.size()) { // Check if procedure parameters are present
char* s = p[0].GetAnsiText(); // Read first procedure parameter (assume it is VARCHAR & OUTPUT)
cnt = 21 + strlen(s);
char* answer = new char[cnt];
memset(answer, 0, cnt);
strcat(answer, "You've just passed: ");
p[0].SetVarchar(strcat(answer, s)); // Send the modified text back to OUTPUT parameter
delete answer; delete s;
}
// Let's make a recordset of 20 rows and 3 columns: INT, DATETIME, and IMAGE
CxpFields& fd = xproc.Fields(); // Remember the fields collection for future reference
cnt = 300; // Let IMAGE field's length is 300 bytes
fd[2].SetName("300 bytes of BLOB"); // Give an arbitrary name to an IMAGE field column
// Just a dummy integer multiplicator value from 2nd INPUT parameter (optional)
int nDummy = 1;
if ((p.size() > 1) && (p[1].DataType() == ftInteger)) {
nDummy = p[1].GetInt();
}
SYSTEMTIME st;
for (int i = 0; i < 20; i++) {
// Field #1
fd[0].SetInt(i * nDummy);
// Field #2
GetSystemTime(&st); fd[1].SetDateTime(st);
// Field #3
void* p = malloc(cnt); memset(p, i, cnt); fd[2].SetImage(p, (ULONG)cnt); free(p);
// Send a recordset row back to SQL Server
fd.Next();
}
return 1;
};
|
SQL scripts
Place the xprocdemovc.dll into the SQL Server's BINN\ directory,
then open and execute the xprocdemovc.sql script from the Query Analyzer.
Here is described step-by-step what the script does.
[+] Register and unregister the extended procedure
|
First we need to register our procedure with the SQL Server. It can be only registered with the Master database:
use master
go
sp_addextendedproc 'xp_Class', 'xprocdemovc.dll'
go
In case if we're need to unregister our procedure:
use master
go
sp_dropextendedproc 'xp_Class'
go
While debug, it is often needed to replace the DLL locked by SQL Server.
To do so execute the following command from the Query Analyzer:
use master
go
DBCC xprocdemovc(FREE)
go
Or, if DLL is still locked, stop SQL Server and start it again. Now DLL can be replaced.
|
[+] Test script results
|
Let's execute the following test script from the Query Analyzer (note using of the master.. prefix):
declare @txt varchar(255)
set @txt = 'hello, procedure!'
exec master..xp_Class @txt OUTPUT
select @txt as StrValue
go
The test script output results returned by xp_Class should look like this:
1 2 300 bytes of BLOB
----------- --------------------------- -------------------------------
0 2002-05-21 16:12:20.000 0x00000000000000000...
1 2002-05-21 16:12:20.000 0x01010101010101010...
2 2002-05-21 16:12:20.000 0x02020202020202020...
...
20 2002-05-21 16:12:20.000 0x13131313131313131...
StrValue
---------------------------------------------
You've passed: hello, procedure!
(1 row(s) affected)
|
Compiling in VC++
Ensure ODS API header (Srv.h) and it's library (opends60.lib) are up-to-date (from MSSQL 2000);
Include xproc.h into your DLL project;
Add opends60.lib and xproc.lib to Settings (Alt+F7) - Link - Object/library modules.
Debug in VC++
Install SQL 7.0/2000 on your workstation
(or install VC++ on workstation where SQL Server is installed).
Register your Xp with SQL Server.
In VC++ 6, from main menu Project -> Settings (or Alt+F7).
Select Debug tab, Category: General.
In the Executable for debug session field, specify a pathname to your
SQL Server executable (sqlservr.exe). For SQL 7.0, this is enough.
For SQL 2000, in the Program arguments field specify a command
line parameter "-sYOUR_SQL_NAME", where YOUR_SQL_NAME
is the name you assign to SQL Server during the installation.
Set breakpoints in your VC++ code and run project.
SQL Server will start as a console application.
You can execute your Xp from Query Analyzer and trace code in VC++.
To exit application, press Ctrl+Pause in the SQL Server console window.
CXProc class reference
[+] Types
FieldType
Set of the Extended Stored Procedure datatypes.
typedef enum FieldType { // maps to MS SQL datatype:
ftUnknown,
ftInteger, // INT
ftBoolean, // BIT
ftBytes, // BINARY, VARBINARY
ftDateTime, // DATETIME
ftFloat, // FLOAT
ftCurrency, // MONEY
ftBCD, // NUMERIC, DECIMAL, and BIGINT
ftString, // CHAR, VARCHAR
ftWideString, // NCHAR, NVARCHAR
ftMemo, // TEXT
ftWideMemo, // NTEXT
ftBlob // IMAGE
};
|
CXProc
Create an instance of the CXProc to work with Extended Stored Procedure.
[+] Member Functions
CXProc
Constructor, creates an instance of Xp class.
CXProc(SRV_PROC* Handle);
- Handle
- [in] A handle for a client connection. Passed from the SQL Server
Remarks
Constructor also creates the associated collections of
Fields and Params.
|
~CXProc
Destructor, frees allocated Xp object.
virtual ~CXProc();
Remarks
Finalizes the pending recordset and destroys Fields and
Params collections.
|
Handle
Read-only value of the Extended Stored Procedure handle.
SRV_PROC* Handle();
Remarks
This handle is used for low-level operations with the ODS API.
|
Params
Collection of the input/output parameters of type CxpItem.
CxpParams& Params();
Remarks
Params are created automatically upon creation of the CXProc class.
|
Fields
Collection of the recordset fields of type CxpItem.
CxpFields& Fields();
Remarks
Set fields values to define the data row.
Call Fields.Next() to send current row to the SQL Server.
Call Fields.Done() to finish the current recordset and prepare
to start new
(Note: when you create a single recordset, you should not to
call Fields.Done(). It is called automatically when the
CXProc being destroyed.
In other words, there is no need to call Fields.Done()
when only one recordset is returning).
Field items are created automatically while you accessing them by their indexes.
Fields describes the data row what must be returned back to the SQL Server.
Column types of the returning recordset are determined by the
Field.DataType() during the first call to
Fields.Next() and can not be changed until Fields.Done().
|
CxpItems
A base class both for Params and Fields collections. It is maintains a collection of CxpItem*.
[+] Member Functions
operator []
Returns an item from collection by index.
CxpItem& operator [] (int nIndex);
Remarks
When nIndex is out of bounds, nil is returned.
Normally you access to items of Params and
Fields directly:
Fields[1].SetInt(2 * Params[0].GetInt(i));
|
ByName
Provides access to fields and params by their names.
CxpItem* ByName(const char* Value);
Remarks
Accessing to I/O parameters by the name is possible only if the
param's name was specified during the Xp call from the SQL Server.
Field's name defines the column's name in the resulting recordset.
|
Clear
Deletes all items from the list.
void Clear();
|
size
Returns the number of items in the list.
int size();
|
CxpParams
Class CxpParams is derived from CxpItems. It is used to access to I/O procedure parameters.
Class CxpParams has only overriden the CxpItems's constructor and destructor.
When instantiating CXProc, then it's constructor creates CxpParams,
which constructor, in turn, reads data and attributes of the I/O parameters.
When CXProc destroys, then it calls to CxpParams destructor,
which, in turn, sends data of output parameters back to SQL Server.
Note: Parameters of the particular procedure are not fixed either by their number, datatype, and position.
Any number of parameters, of any valid datatypes, and in any order can be passed to the same procedure.
This gives us a kind of great flexibility, but here is also our responsibility to handle the input parameters properly.
CxpFields
Class CxpFields is derived from CxpItems.
Fields are used to create and return recordsets from the Xp.
[+] Member Functions
operator []
Returns field at index nIndex.
CxpItem& operator [] (int nIndex);
Remarks
If the field does not exists, it is being created.
Also all not existing fields from range 0 to nIndex-1
are created too.
This makes its possible to assign values to the fields in any order.
Next
Send data row to the SQL Server and then set all field values to NULL.
void Next();
Done
Finalizes sending the recordset and deletes the field definitions.
void Done();
Remarks
This method is called automatically during the execution of ~CXProc
This mean you has no need to call to Fields.Done() when you're returning a single recordset.
CxpItem
A base class for the items of the Params and Fields collections.
Provides basic properties and methods to read and write data values.
[+] Member Functions
Size
Read-only. Returns length of the allocated data, in bytes.
ULONG Size();
|
DataType
Read-only value of the datatype that the item holds.
FieldType DataType();
|
Output
Read-only. Returns True when the parameter has OUTPUT
modifier while executing Xp from the SQL Server.
bool Output();
Remarks
Valid only for Params.
|
IsNull
Read-only. Returns True when item has no data
bool IsNull();
|
Clear
Frees item's data and sets item value to NULL.
void Clear();
|
GetName
Get value of the item's name.
char* GetName();
|
SetName
Get value of the item's name.
void SetName(const char* Value);
|
GetBit
Get value of SQL type bit.
bool GetBit();
|
SetBit
Set value of SQL type bit.
void SetBit(bool Value);
|
GetBinary
Get value of SQL type binary, varbinary or field of image.
void GetBinary(void** Value);
void* GetBinary();
Remarks
This methods allocates memory, writes item's data to it, and returns pointer
to that copy of the item's data buffer. You must deallocate this memory after use.
Call item's Size() to read the item's data buffer length.
There are difference between these two methods.
GetBinary(void** Value) frees memory of variable passed to it,
allocates new one and then returns a copy of item's data there.
void* GetBinary() only allocates memory and then returns a copy of item's data there.
Note: GetAnsiText and GetUnicodeText uses the same technique.
|
SetVarbinary and SetImage
SetVarbinary sets value of SQL type binary and varbinary.
SetImage sets value of field of SQL type image.
void SetVarbinary(const void* Value, ULONG nSize);
void SetImage(const void* Value, ULONG nSize);
- Value
- [in] Buffer containing the data to be set.
- nSize
- [in] Length of the data in Value
Remarks
Setting image value is only valid for Fields.
You need to allocate memory buffer, fill it with data, and pass that buffer
and it's length to SetImage().
You must deallocate that your buffer at any time after call to SetImage().
If you need to free item's data, call item's Clear() method.
|
GetInt
Get value of SQL type int.
int GetInt();
|
SetInt
Set value of SQL type int.
void SetInt(const int Value);
|
GetFloat
Get value of SQL type float, money, and numeric.
double GetFloat();
|
SetFloat
Set value of SQL type float.
void SetFloat(const double Value);
|
SetMoney
Set value of SQL type money.
void SetMoney(const double Value);
|
SetNumeric
Set value of SQL type numeric.
void SetFloat(const double Value);
Remarks
Datatype numeric is currently supported with the limited precision
due to the conversion problems.
It is uses precision of 38 (maximum allowed) and scale of 10.
SQL Server 2000 inroduces a new datatype — bigint.
It is also recognized by Xp as numeric (decimal).
|
GetDateTime
Get value of SQL type datetime.
void GetDateTime(SYSTEMTIME& Value);
|
SetDateTime
Set value of SQL type datetime.
void SetDateTime(SYSTEMTIME Value);
|
GetAnsiText
Get value of SQL type varchar and text.
void GetAnsiText(char** Value);
char* GetAnsiText();
Remarks
Fields of type ftString and ftMemo are always
returned to SQL Server in Unicode (as nvarchar and ntext).
See remarks for GetBinary.
|
SetVarchar
Set value of SQL type varchar.
void SetVarchar(const char* Value);
|
SetText
Set value of SQL type text.
void SetText(const char* Value);
Remarks
See remarks for GetUnicodeText.
|
GetUnicodeText
Get value of SQL type nvarchar and ntext.
void GetUnicodeText(WCHAR** Value);
WCHAR* GetUnicodeText();
Remarks
Returning and setting values of ntext is only possible for Fields,
since I/O parameters of text/ntext aren't supported by ODS API.
See remarks for GetBinary.
|
SetNVarchar
Set value of SQL type nvarchar.
void SetNVarchar(const WCHAR* Value);
|
SetNText
Set value of field of SQL type ntext.
void SetNText(const WCHAR* Value);
Remarks
See remarks for GetUnicodeText.
|
Download Demo
| Demo Package | Download Now! |
- Manual in HTML (is what you currently read)
- Class header file,
XPROC.H (no full source code)
- Sample project (source code, compiled DLL, test T-SQL script)
- License Agreement
|
xprocdemovc.zip (45K) |
Order Product
| Product To Order |
Volume Discount Prices |
Purchase |
XPROC.CPP - Extended stored procedure class for Visual C++ 6,7
(full functional, WITH FULL SOURCE CODE) |
Show |
Order Now |
LicenseThe product is licensed on per-developer basis.
For example, team of 3 developers of extended stored procedures needs to purchase 3 copies of the product.
DeliveryBy the email in the short time since you have to order.
Bonus Offers
LicenseThe bonus product is licensed for the same number of licenses as for the master product.
For example, when order 3 copy of the CXProc class, then receive 3 licenses for "MC FolderJump" as well.
|
