How To Build a MyCOM

Author: Emmanuel KARTMANN <emmanuel.kartmann@lutecia.info>

Date: 29 October 2003

Abstract:

This document describes how to make a DLL module that will be accessible from both MySQL and ASP code. The DLL must implement a COM object (for ASP access) and MySQL User-Definable Functions (for SQL access).

 


Table of Contents:

1.       Introduction. 3

2.       Architecture. 4

3.       Building the MySQL and COM Module. 4

3.1.        Building the Project with Microsoft Visual Studio .NET.. 4

3.2.        Building the Core Functions. 6

3.3.        Building the COM Layer 12

3.4.        Building the MySQL UDF Layer 17

3.5.        Adding a Post-Build Step to your project 20

4.       User Guide. 20

4.1.        Using Core Functions from COM... 20

4.1.1.       COM Object Installation. 20

4.1.2.       Calling Core Functions from COM... 21

4.1.3.       Test Function from HTML Page. 22

4.1.4.       Uninstalling COM object 23

4.2.        Using Core Functions from MySQL. 23

4.2.1.       Installation. 23

4.2.2.       Calling Core Functions. 24

4.2.3.       Uninstalling SQL Function. 24

5.       Debugging MyCOM Object 24

5.1.        Debugging COM Object 24

5.2.        Debugging MySQL UDF. 25

6.       Reference. 26

 

 


1.          Introduction

The project I’m working on, where MyCOM objects have been developed, is based on the following Web technologies:

This document describes the process of building a software component (a MyCOM) that can be accessed from two different sources: Microsoft ASP code and MySQL. Several components of this type are in production in the project right now.

As such, the component consists in a DLL which is both a COM object (for ASP access) and a MySQL User‑definable Function (UDF).

This document assumes that you have access to the following tools:

Since there are many different versions of these tools, you should stick to these versions in order to create a functional software component.

In addition, the document describes a sample module in charge of address standardization (for the sake of the demonstration). It should be easy to adapt the sample to your own needs.


2.          Architecture

A component for use from ASP and MySQL consists in three sub-modules (as shown on diagram below):

  1. The Core Functions, where the real implementation is located
  2. The COM Layer, providing access to the Core Functions from ASP/COM
  3. The MySQL UDF Layer, providing access to the Core Functions from MySQL

All sub-modules are embedded in one single DLL module (for ease of deployement/shipping). This way the Core Functions are implemented once (one code line, one compilation, one DLL to ship) and are available from both the Front-End and the Back-End of the system.

 

3.          Building the MySQL and COM Module

3.1.             Building the Project with Microsoft Visual Studio .NET

·         In menu, select File > New > Project…

·         In Name, type your project name (e.g. MyCOMAddressNormalizer)

·         Click on OK button

·         The ATL Project Wizard starts:

·         Uncheck Attributed and check Allow merging of proxy/stub code
Optionally, if your Core Functions requires MFC (e.g. CString), check option Support MFC.
Please note that for components accessed by ASP code, it’s safer not to use MFC and use Microsoft Active Template Library (ATL) instead.

3.2.             Building the Core Functions

We will use a real example of Core Functions in this document – an address normalization function that takes a string as input (containing an address as entered by a user, with possible errors and no formatting) and provides a set of string with identified information (street number, street type, street name, postal code, city name, country name, etc…).

The core function signature is as follows:

bool AddressNormalize(const char *strInput, char *strStreetNumber, char *strStreetType, char *strStreetName, char *strPostalCode, char *strCityName, char *strCountryName, char *SpecialDelivery, char *UnidentifiedComponents)

To create and include the source code for Core Functions in your project, proceed as follows:

·         In Solution Explorer, select tab Class View

·         Right-click on module name (e.g. “MyCOMAddressNormalizer”), select Add > Add Class…

·         In Add Class dialog box, select Generic C++ Class

·         In Generic C++ Class Wizard, enter your Core Functions class name (e.g. “CoreAddressNormalizer”)
By default, the Wizard assumes (!) that your class name starts with a “C” prefix, and it removes the “C” prefix to build file names (e.g. file name is by default “oreAddressNormalizer.cpp”). Feel free to change the name of your source files (I put back the “C” character; my file names are “CoreAddressNormalizer.cpp” and “CoreAddressNormalizer.h”).
If your class is derived from (inherits) from another class, enter the parent class name.

·         Click on Finish. The class is created, as well as a constructor and destructor for this class (both empty).

·         In Class View, Right-click on your class name (e.g. “CoreAddressNormalizer”),
and select Add > Add Function…

·         The Add Member Function Wizard starts

·         Enter your function parameters and return type:
Return Type: bool
Function Name: AddressNormalize
Parameters: const char *strInput, char *strStreetNumber, char *strStreetType, char *strStreetName, char *strPostalCode, char *strCityName, char *strCountryName, char * strSpecialDelivery, char * strUnidentifiedComponents
Optionally, enter a comment for your function.

·         Click on Finish

·         Implement your function (in Class View, double-click on your method name, e.g. AddressNormalize).
For the sake of the demo, you can use this “dumb” implementation:


// Normalize an adress (splits into identified components)
bool CoreAddressNormalizer::AddressNormalize(const char * strInput,
                                             char * strStreetNumber,
                                             char * strStreetType,
                                             char * strStreetName,
                                             char * strPostalCode,
                                             char * strCityName,
                                             char * strCountryName,
                                             char * SpecialDelivery,
                                             char * UnidentifiedComponents)
{
  bool blnSuccess = false;

  // TODO Implement REAL function; this is just a demo
  blnSuccess = true;
  if (strStreetNumber)
  {
    strcpy(strStreetNumber, "STREET_NUMBER");
  }
  if (strStreetType)
  {
    strcpy(strStreetType, "STREET_TYPE");
  }
  if (strStreetName)
  {
    strcpy(strStreetName, "STREET_NAME");
  }
  if (strPostalCode)
  {
    strcpy(strPostalCode, "POSTAL_CODE");
  }
  if (strCityName)
  {
    strcpy(strCityName, "CITY_NAME");
  }
  if (strCountryName)
  {
    strcpy(strCountryName, "COUNTRY_NAME");
  }
  if (SpecialDelivery)
  {
    strcpy(SpecialDelivery, "SPECIAL_DELIVERY");
  }
  if (UnidentifiedComponents)
  {
    strcpy(UnidentifiedComponents, "UNIDENTIFIED");
  }

  return(blnSuccess);
}

·         In menu, select Build > Build Solution.
You should have no compilation or build error; if you have, fix them before proceeding with next step.


3.3.             Building the COM Layer

To build the COM layer, you have to create a COM object in your module, and then create methods and/or properties for this COM object. Use Microsoft Visual Studio’s ATL Simple Object wizard to do this:

·         In Solution Explorer, select tab Class View

·         Right-click on module name (e.g. “MyCOMAddressNormalizer”), select Add > Add Class…


// Compiler COM Support (Standard classes: _bstr_t, _variant_t, etc...)
#include <comdef.h>


// Convert BSTR into _bstr_t (wraps the BSTR into object)
_bstr_t bInput(strInput);
// Cast _bstr_t into (const char *)
const char *lpstrInput = (LPCTSTR)bInput;

_variant_t vStreetNumber;
char *lpstrStreetNumber = (char *)malloc(255);

// TODO Here you can update/set value in lpstrStreetNumber

vStreetNumber = lpstrStreetNumber;
*pvStreetNumber = vStreetNumber.Detach();

3.4.             Building the MySQL UDF Layer

In order to build the MySQL UDF layer, you should proceed as follows:

o         mysql-4.0.16\sql\udf_example.cpp (rename this one to “MyAddressNormalizer.cpp”)

o         mysql-4.0.16\include\config-win.h

o         mysql-4.0.16\include\mysql.h

o         mysql-4.0.16\include\mysql_com.h

o         mysql-4.0.16\include\mysql_version.h

o         mysql-4.0.16\include\my_alloc.h

o         mysql-4.0.16\include\my_dbug.h

o         mysql-4.0.16\include\my_global.h

o         mysql-4.0.16\include\my_pthread.h

o         mysql-4.0.16\include\my_sys.h

o         mysql-4.0.16\include\m_ctype.h

o         mysql-4.0.16\include\m_string.h

o         mysql-4.0.16\include\raid.h


#include
"stdafx.h"

o        In Additional Include Directories, add current directory ( . )

o        Click on OK

3.5.             Adding a Post-Build Step to your project

In order to debug the MySQL component, you need to copy the DLL into a directory which is scanned by MySQL for DLL. You can copy the DLL into any directory in the PATH environment variable. I would recommend copying the DLL into the MySQL runtime directory (C:\mysql\bin by default). You can add a custom build step in your Visual Studio project to make sure that the copying is done every time you build the DLL:

Thus, the latest version of the DLL will always be loaded by MySQL.

4.          User Guide

4.1.             Using Core Functions from COM

4.1.1.                 COM Object Installation

To install a COM object, you need to call a specific program, regsvr32.exe with the DLL name as a command line argument:

·         Type the registration command
(note that the Debug version of the component is built in a “Debug” sub-directory while the Release version is in a “Release” sub-directory)


regsvr32.exe Debug\MyCOMAddressNormalizer.dll

4.1.2.                 Calling Core Functions from COM

COM Object can be used from a variety of languages: VBScript, Javascript, Visual Basic, C++, etc… The procedure is the same no matter what language you use:

Example (VBScript):


  ' Create COM Object
  Set objMyCOM = CreateObject("Replay.AddressNormalizer.1")
 
  ' Call COM Object's method
  objMyCOM.AddressNormalize strInput, _
                            strStreetNumber, _
                            strStreetType, _
                            strStreetName, _
                            strPostalCode, _
                            strCityName, _
                            strCountryName, _
                            strSpecialDelivery, _
                            strUnidentified

 

4.1.3.                 Test Function from HTML Page

You can create a simple HTML page to test your COM access to your component (no need for IIS/ASP/VB Compiler). Just create the COM object and call its method in Javascript (client-side):

<html>
<
head>
<
script language="vbscript">
Function
NormalizeAddress()
  ' Declare variables
  Dim strInput, objMyCOM
  Dim strStreetNumber, strStreetType, strStreetName, strPostalCode, strCityName, strCountryName
  Dim strSpecialDelivery, strUnidentified
 
  ' Read input string from field
  strInput = document.forms(0).all("INPUT_ADDRESS").value
 
  ' Create COM Object
  Set objMyCOM = CreateObject("Replay.AddressNormalizer.1")
 
  ' Call COM Object's method
  objMyCOM.AddressNormalize strInput, _
                            strStreetNumber, _
                            strStreetType, _
                            strStreetName, _
                            strPostalCode, _
                            strCityName, _
                            strCountryName, _
                            strSpecialDelivery, _
                            strUnidentified

  ' Display output strings
  OUTPUT_STREET_NUMBER.innerText = strStreetNumber
  OUTPUT_STREET_TYPE.innerText = strStreetType
  OUTPUT_STREET_NAME.innerText = strStreetName
  OUTPUT_POSTAL_CODE.innerText = strPostalCode
  OUTPUT_CITY_NAME.innerText = strCityName
  OUTPUT_COUNTRY_NAME.innerText = strCountryName
  OUTPUT_SPECIAL_DELIVERY.innerText = strSpecialDelivery
  OUTPUT_UNIDENTIFIED.innerText = strUnidentified
  ' Return value "False" to stop processing callbacks (thus the form is NOT submitted!!!)
  NormalizeAddress = False
End
Function
</
script>
</
head>
<
body>
<
h2>Test MyCOM Object - HTML/VBScript/COM</h2>
<
form>
Input Address:<br/>
<
textarea id="INPUT_ADDRESS" name="INPUT_ADDRESS" rows="5" cols="75">REPLAY
602 chemin du clos de Brasset
06560 Valbonne
France
</textarea>
<
input type="submit" onclick="javascript:return(NormalizeAddress())"/>
<
hr/>
Street Number:&#160;<span id="OUTPUT_STREET_NUMBER"></span><br/>
Street Type:&#160;<span id="OUTPUT_STREET_TYPE"></span><br/>
Street Name:&#160;<span id="OUTPUT_STREET_NAME"></span><br/>
Postal Code:&#160;<span id="OUTPUT_POSTAL_CODE"></span><br/>
City Name:&#160;<span id="OUTPUT_CITY_NAME"></span><br/>
Country Name:&#160;<span id="OUTPUT_COUNTRY_NAME"></span><br/>
Special Delivery:&#160;<span id="OUTPUT_SPECIAL_DELIVERY"></span><br/>
Unidentified:&#160;<span id="OUTPUT_UNIDENTIFIED"></span><br/>
</
form>
</
body>
</
html>


4.1.4.                 Uninstalling COM object

To install a COM object, you need to call a specific program, regsvr32.exe with the DLL name as a command line argument:

·         Type the registration command
(note that the Debug version of the component is built in a “Debug” sub-directory while the Release version is in a “Release” sub-directory)


regsvr32.exe /u Debug\MyCOMAddressNormalizer.dll

4.2.             Using Core Functions from MySQL

4.2.1.                 Installation

To install the MyCOM component for use from MySQL, proceed as follows:

·         Use SQL Command CREATE FUNCTION to load the DLL:


CREATE FUNCTION ADDRESS_SEARCH RETURNS STRING SONAME 'MyCOMAddressNormalizer.dll'

4.2.2.                 Calling Core Functions

To use a UDF function, simply call it from SQL:


SELECT ADDRESS_NORMALIZE('REPLAY\n602 chemin du clos de Brasset\n06560 Valbonne\nFrance')
;

4.2.3.                 Uninstalling SQL Function

To uninstall the MyCOM component for use from MySQL, proceed as follows:

·         Use SQL Command DROP FUNCTION to force MySQL to unload the DLL:


DROP FUNCTION ADDRESS_SEARCH

5.          Debugging MyCOM Object

5.1.             Debugging COM Object

To debug your MyCOM component from a COM application (like a Javascript code in Internet Explorer, as defined in section 4.1), proceed as follows:

5.2.             Debugging MySQL UDF

To debug your MyCOM component from a SQL application, proceed as follows:


net stop mysql

·         Execute a SQL request (for example from mysql.exe command line program)


C:\mysql\bin\mysql.exe --execute="SELECT ADDRESS_NORMALIZE('REPLAY\n602 chemin du clos de Brasset\n06560 Valbonne\nFrance')"


6.          Reference

For more about ASP COM Objects, please refer to:

For more about MySQL User-definable Functions, please refer to: