SP_COM_generator.pl - generates COM wrapper for MS SQL stored procedures
Version 2.00 by Jenda@Krynicky.cz
c:\Project\COMwrapper> SP_COM_generator.pl MyProject.ini
This script will read an INI file, connect to the server and database specified in the INI file and generate a VB source for a COM wrapper. You will need to create an ActiveX DLL project in VB and add the generated file to it.
The available options and their syntax is described in SP_COM_generator.ini.
The instalation contains:
SP_COM_generator.pl - the script
SP_COM_generator.ini - example project INI file
typeconversion.ini - SQL to VB (and back) type conversion table
(the user defined types ARE supported!)
Win32\IniHash.pm - module for reading INI files
HASH\Insensitive.pm - module defining insensitive hashes
HASH\WithDefaults.pm - module defining insensitive hashes with defaults
(the modules contain embeded POD documentation)
Example.vbp - an example VB project
Functions.bas - one version of the lowlevel functions
FunctionsConnected.bas - another version
FunctionsErrorObj.bas - another version.
you may use whichever suits you best
if you have any performance data on what options for recordsets and what
implementation of ExecuteCommand__ and OpenRecordset__ work best
under what conditions please let me know.
You may specify which set of ``lowlevel'' functions that do the actual DB access and error handling do you want to use. Keep in mind that this might change the behaviour of the object. Especialy with regard to the error handling and reporting.
Currently there are three prepared function sets:
The methods return Empty on success and ``ErrNumber||ErrDescription'' on failure.
The methods return Empty on success and ``ErrNumber||ErrDescription'' on failure.
Additional methods:
Connected__ = tests whether the object is connected. Returns 1 for Connected and 2 for Disconnected.
Connect__ = reopends the database connection. Returns 1 on success, 0 on failure.
DisConnect__ = closes the database connection.
SetFakeSession__ = sets an object reference to a Dictionary object to functions as a fake L<Session>.
DeleteFakeSession__ = deletes the reference
The COM object returns Empty on success and ``ERROR'' on failure. The object contains those properties: obj.ErrNum = error number. This variable is set to zero on successful call. obj.ErrNative = native MS SQL Server error number. This variable is set to zero on successful call. obj.ErrType = type of the error. Either ``DB'' or ``VB''. obj.ErrDesc = error description obj.ErrDescComplete = complete error description in format ``ErrNative||ErrDescription'' vbCRLF ``ErrNative||ErrDescription'' vbCRLF ... Contains all database errors from the last call. obj.ErrSource = name of procedure in which the error occured. For errors related to the arguments contains ``ProcedureName(XX-th argument)''. Please keep in mind that only ErrNum and ErrNative are reset to zero in case of success. All other properties keep the values set by last error.
Empty on success and something else
on failure. In case the function returns anything but Empty, no return values, output parameters or recordsets
are copied and the method returns whatever was the return value.
If you need help with creating your set of ``lowlevel'' functions do not hesitate and contact me at Jenda@Krynicky.cz
The object was meant to be used mainly from ASPs, and allows you to pass some data behind the scenes, from the Session. So for example if you set that @UserId parameter should be taken from session, all stored procedures that require this parameter (for permission checking and row level filtering) will get it from Session. That way you may add the premissions checking and filtering to your stored procedures gradualy without having to change your ASPs. Please see the SP_COM_generator.INI on how to set this up!
The object of course MUST be useable from other contexts than just ASPs. Thus you may create a ``fake'' Session and tell the object to use that one instead of trying to find real Session.
The functions for this are named SetFakeSession__ and DeleteFakeSession__. Normal ussage is something like this :
Dim db As YourProject.SP
Dim Session As Dictionary
Set Session = New Dictionary
FakeSession("userid") = 1
FakeSession("usertype") = "SA"
db.SetFakeSession__ FakeSession
db.CallAProcedure Some, Params
...
Set db = Nothing
Set Session = Nothing
The changes you make to the ``fake'' session after you set the reference to it in the object by SetFakeSession__ of course ARE visible to the object. So you may first make the reference and then set the options.
!!! The objects created using Functions.bas do NOT support fake sessions. Those objects are totaly stateless !!!
You may easily change this. Just create a new FunctionsXXX.bas from Functions.bas and copy SetFakeSession__, DeleteFakeSession__ and GetSessionVar__ from FunctionsConnected.bas.
Numeric(x,y) or Decimal(x,y) types. It works OK
though if you use a ``User defined type'' based on these.
CREATE PROCEDURE Something( @param as Decimal(6,2)) ...
will not work, but
EXEC sp_addtype N'COMMISSION', N'numeric(6,2)', N'null'
CREATE PROCEDURE Something( @param as COMISSION) ...
will.
The reason is that it's not easily (if at all) possible to get the scale and precission of Numeric and Decimal procedure parameters.
I believe you should be using the User defined types anyway, instead of entering the scale and precission directly in all stored procedures and table definitions.
[:sp_groups]
with_recordset=^Fetch
[with_recordset]
recordset=1
will suffice.
Jenda@Krynicky.cz http://Jenda.Krynicky.cz
This program is FREE. Keep in mind, however, that NOTHING IS GUARANTEED to work and everything you do is AT YOUR OWN RISK - I will not take responsibility for any damage, loss of money and/or health that may arise from the use of this program!
This program is distributed under the terms of Larry Wall's Artistic License.
There is only one additional requirement. I want to get your comments. Why do you like it, why don't you like it, why are you going to use it or not, what features would you like added, etc. etc. etc. Pretty please!