NAME - generates COM wrapper for MS SQL stored procedures

Version 2.00 by


c:\Project\COMwrapper> 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:  - 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\     - module for reading INI files
        HASH\  - module defining insensitive hashes
        HASH\ - 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 original version. The object created using this set IS NOT connected ... that is it opens a new connection for each call and closes it immediately after executing the statement.

The methods return Empty on success and ``ErrNumber||ErrDescription'' on failure.

The object created using this set IS connected ... that is it opens a connection when you create the object and keeps it until you destroy the object.

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

Similar to FunctionsConnected.bas. Checks the connection before each database call and reconnects if necessary.

The object created using this set IS connected ... that is it opens a connection when you create the object and keeps it until you destroy the object.

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.

you own FunctionXXX.bas
You may write your own ``lowlevel'' functions and use them instead of those preprepared. Keep in mind though that you have to keep the function signatures (number and type of arguments) and that ExecuteCommand__ and OpenRecordset__ MUST return 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


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.


The script is not able to generate wrappers for stored procedures with parameters with 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) ...

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.

If you want to get a resultset/recordset from a stored procedure you must say that in the INI file. If you name your stored procedures consistently it should not be a big problem. Something like

will suffice.



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!