IMail - How do I use the ODBC connector

Question/Problem: How do I use the ODBC connector that is available with IMail?

Answer/Solution: IMail provides a means of interfacing with external database through a dynamic link library that is defined for each host. This external user database is designed to be used with ODBC, but in reality it can support any method of external database by modification of the "odbcuser.dll".

Download 2006.2 ODBC User Source (Update 3/13/2007)
Download 2006.2 ODBC User Source
Download 2006.1 ODBC User Source

(An unsupported odbcuser.dll with third-party modifications for Oracle version 8 is also available: ftp.ipswitch.com/ipswitch/Product_Support/IMail/odbc/oracle_odbc_dll.zip)
Note: The odbcuser.dll has changed in version 8.14. Thus, third party DLLs will need to be modified to work with version 8.14 or later.

NOTE: The ODBCuser.dll source code is provided as a tool, not as part of the IMail product. As provided, it works with SQL and with MS Access. If you have the development skill and know-how to work with it, you can modify it to use other databases. At this time we do not have the resources to help with C programming and debugging.

Here is a link to get the 8.20 odbcuser.zip:

ftp://ftp.ipswitch.com/Ipswitch/Product_Support/IMail/odbcuser.zip

IMail supports the use of up to 10 different DLL's. Using our provided DLL, you can have an unlimited number of tables within a DLL. Each DLL is loaded on demand and remains loaded after first load until all applications using IMAILSEC.DLL are stopped.

Configuration (See also: IMail - Configure SQL Server for use with IMail)

Go to, IMail Administrator | localhost | Add Host.  For each defined host you have a choice of one of three user databases:

1.) IMail User Database
2.) local NT User Database
3.) External User Database

When you select the "External User Database", the "Configure..." button is activated. Pressing the configure button presents a dialog where the external database is defined.

The first option is where you specify the FULL pathname to the DLL that exports the following functions:

GetUserEntry
SetUserEntry
DeleteUserEntry
AuthorizeUser
GetFirstUserEntry
GetNextUserEntry
(refer to the "odbcuser.h" file for full explanation of these functions.)

The second option is where you specify the ODBC System Data Source Name for the database where the user information is stored.

The third option is where you specify the table name for the data. This defaults (if the field is blank or contains "[default]") to the host name with periods replaced by underscores.

These three options are written to a registry entry under the domain named "AuthDatabaseDLL". The three entries are concatinated with an asterisk (*) as the seperator.

Default ODBC Tables

The default ODBC table consists of 9 entries:
USERID - text
PASSWORD - text
FULLNAME - text
USERDIR - text
MAILADDR - text
MAXSIZE - integer
MAXMSGS - integer
FLAGS - integer
TYPE - integer

See below for changes in version 7.0.

The provided implementation expects these entries to be in sequence. Additional information (after flags) MAY exist in the table.

Modification of the ODBCUSER.DLL

The source for ODBCUSER.DLL is provided in the ZIP file "odbcuser.zip".

Any language may be used to write the ODBCUSER.DLL provided it exports the necessary routines with the proper arguments and calling stack type. The provided source and project files are written for MS Visual C++ 4.1. Please refer to "odbcuser.h" and "codbc.cpp" for additional information.

Table modifications may be made in "CreateTable", "InsertUser", "UpdateUser", and "GetUser". Note that entry sequence is defined in "CreateTable" and "GetUser".

Note: It is recommended that you save your custom DLL to the IMail directory using a filename other than odbcuser.dll. The new filename should be eight characters or less with a 3 character extension (mysql.dll for example). You must specify this new filename in IMail Administrator's External Database Configuration window.

If you have domains that use different external databases (such as SQL and Access), you will need a separate odbcuser.dll for each database, one can be named odbcuser.dll and the other DLL(s) renamed to designate the other database(s).

See also: Configure SQL Server for use with IMail

External Database Changes in version 7.0:
When installing IMail v7.0 over any earlier version, in which one or more hosts are configured to use an external user database, new columns must be added to the database tables. This is due to additional user-level data which must be stored for use with the new Web Messaging features and for Web Calendaring. These new columns must be added to the user table for each IMail host configured to use an external database. 

NOTE: If a custom ODBC driver was used with an earlier version of IMail, the driver must be modified to accommodate the new columns. Source code for the basic ODBCUser.dll driver (tailored for SQL Server and Access) may be obtained by download from the IMail Support Center:

ftp://ftp.ipswitch.com/Ipswitch/Product_Support/IMail/odbcuser.dll

These database column additions must be made before installing IMail v7.0. Also, if a custom ODBC database driver will be used with IMail, it must be created and ready for use. The new columns and their types are as follows:

Database Column Default
Name Type Value
------------------------ --------
NUMTRIES INTEGER 0
ATTEMPTTIME INTEGER 0
NUMTIMESSUSP INTEGER 0
LASTLOGIN INTEGER 0
SPNDUSRACCT INTEGER 0
IWOPTIONS INTEGER 0
SPELLCHKG INTEGER 0
ICALENBL INTEGER -1
SPELLENBL INTEGER -1
NUMSGSTNS INTEGER -1
IWDEPTH INTEGER -1
PRVWSIZE INTEGER -1
STRTDAY INTEGER -1
ENTRYGRNL INTEGER -1
STARTTIME INTEGER -1
ENDTIME INTEGER -1
MAXEVNTDISP INTEGER -1
MAXTSKDISP INTEGER -1
MAXNOTEDISP INTEGER -1
MAXDLYRCURCNT INTEGER -1
MAXWKLYRCURCNT INTEGER -1
MAXMONRCURCNT INTEGER -1
MAXYRLYRCURCNT INTEGER -1
DISPCMPLTDTSKS INTEGER -1
HOURSCLOCK INTEGER -1
MAXSRCHRESPPG INTEGER -1
MAXEVENTTITLELEN INTEGER -1
MAXTASKTITLELEN INTEGER -1
CONFIRMONDELETE INTEGER -1
TIMEZONE VARCHAR(255) "" (empty string)
DEFVIEW VARCHAR(40) ""
DEFCALNM VARCHAR(40) ""

These database column additions must be made before 
installing IMail v7.1. IMail Server v7.1 New Columns:

Database Column Default
Name Type Value
------------------------ --------
NUMNOTIFY INTEGER 0
LASTNOTIFY INTEGER 0

For all existing user records, these columns should be set to the default values shown in the table above.

Please note that when all domains use the IMail and/or NT user databases, these changes do not apply. Also, when creating a new external database, these columns will be automatically generated as the table is created.

There are no ODBC changes between version 7.1x and version 8.1x