SQLMail User Guide

The SQLMail server is a database server that allows user to perform database operations through email messages. It can be understood as an asynchronous database messaging (ADM) service that operates on any ODBC data sources.

To use the service, a user would send a message to a designated email account. The message includes desired database operation, data record (for inserting and updating), and optional authentication information. The SQLMail server connects to the email account in certain time interval and looks for database request messages. It then performs the specified database operations and sends the result to the requester.

There are some advantages using asynchronous database operations over traditional synchronized operations:

High Mobility: Both the client and server are very thin, and thus can be hosted on any desktop or laptop computers. Users can use any electronic devices, such as handheld computers and smart phones, to send email commands and retrieve live database information. Database operation can be performed anywhere an email connection can be established.

Easy Replication: Multiple SQLData servers can be used for connecting to the same email account and each performs database operations independently. The same database can be replicated with no additional effort.

No Time Restriction: As long as the database requests reach to the mail server, users of SQLMail have the option to decide when the requests should be processed. There is no information lost even the requests are processed days later.

No Geographic Restriction: The SQLMail server can be hosted anywhere there is an email connection. If your ISP hosts your web server, you can host your database server at home, in your office or even in your car. There is really no restriction on the speed of the connection.

Scalability: There is no restriction on the scale of your databases and the amount of requests you can handle. You can use the server to track a simple contact information table or to process customer orders during holiday season. You can set up multiple email accounts and have multiple SQLMail servers to balance the load with no special configuration necessary. Interestingly, there is even no down time to talk about, because of the asynchronous nature of the operation.

No training: This sounds very trivial, but often ends up to be the most important factor to consider. If you develop a user interface for a large information system, usability and training are the keys for deployment. Using a mail client or a web browser reduces the training task almost to nothing.

The SQLMail server is a unique product that is designed based on the demands of the information industry. In this section, we list some of the common applications and illustrate how SQLMail can be used for solving some common problems.

If you have ever designed a web site or developed a web application, you know you need some kind of database service so that information posted through HTML forms can be stored permanently. There has been a general lack of direct and easy interfaces from web servers to database servers and verse versa.

Situation gets even worse if you don't have a database server. If your web is hosted by ISPs (Internet Service Provider), most of them do not provide database services. Even they do, there are programming work involved (often CGI or other scripting languages), and how do you maintain the database and retrieve information comes immediate after that.

SQLMail server can be used for processing HTML forms on your local. When a user summits a form to your web site, a email message is sent to you along with all field names and values. The SQLMail server checks the email account, retrieves relevant messages and perform specified database operation locally. All you need to do is creating a table with columns that matches to the HTML form. The SQLMail takes care of the rest..

A simple example is an offline customer registration application that only inserts record into databases. It may have the following fields (sample values included) in your HTML form:

When visitors fill in the form and click the SUBMIT button. Your web server can be configured to send the message to a email account. The SQLMail will retrieve the message and insert the record into the Customer table.

Have you ever called an automatic telephone system? Response like press 1 for accounting and press 2 for customer support led you nowhere simply because it can not do any smart information retrieval.

The SQLMail server can be used for getting what your customer needs with no hassle. Suppose that you are to design an automatic response system for a used car dealer, which allows potential customers to get price quotes through emails. Users can specify selection criteria by sending a message similar to the following:

SQLMail server would execute a query and sends an email with a list of Lincoln Town car, priced less than $20000 and made after year 1994, to the customer automatically.

The server allows you to set up complicated SQL queries as custom reports. SQLMail executes these reports based on the Subject in the email. Your customer, in such cases, needs only type in the Subject in the email to get the listing. To get a list of Lincoln Towncar may be as simple as sending:

Subject : BestDealTownCar

to a specified email address.

Data collection often involves large amount of data. To enter the data into databases is a very tedious task.

SQLMail server can be used to greatly simplify the task. Users can use a light device, such as a PDA or a handhold computer that is capable of sending electronic mails, for sending collected data to the data center or warehouse right away. There will be no paper involved. The SQLMail server reads from the email server and performs all requested database operations. The feature would also be very useful for agents or sells persons who works outside of office.

One feature of the SQLMail Server is that it can send information from database in predefined intervals. The server formats result set using a template file and sends to a group of recipients. If your bosses want to keep informed about some important data in the database, you may want to set up a special report that will be automatically executed by the SQLMail server and mailed it to them everyday or once per-week.

The server configuration information is stored in a configuration file, SQLMAIL.INI, under the CONFIG subdirectory where the server is installed. This section shows you how to set up the server for basic operations.

The first thing is to create an email account for the server. The server uses the account for both receiving requests and sending response. It sends messages using the Simple Mail Transfer Protocol (SMTP), and retrieve messages using the Post Office Protocol version 3 (POP 3). Your mail server should support both protocols. The SMTP server and POP 3 server do not have to be on the same host.

It is recommended that a dedicated email account be established for the SQLMail operations for efficiency reasons, but this is not a requirement. The SQLMail server processes only those emails that contain specific database requests in the mail subject header or other mail header fields.

The SMTP server is specified in the configuration file as follows:

where MailServer specify the server name of the SMTP service. Check with your network administrator for the name of your SMTP server. SenderAddress is a valid email address on the mail server. . This is the email account SQLMail server used for sending messages. For example, you can use the following setting for testing:

where sqlmail@sqldata.com is a dedicated mail account reserved for testing SQLMail server.

POP 3 Account information includes the server name, the account user name and password. SQLMail server uses this account for retrieving email messages. The three items are listed in the server configuration file as follows:

You should replace the values with your account information. There can be more than one POP 3 accounts separated by ';'. The server will check database requests from all accounts defined in PopAccount.

There are other settings in the configuration file that may affect the server operation. They are:

Most of the email users do not understand SQL and database operations. So, commands such as,

are more meaningful and understandable. However, the server needs to know more, such as ODBC data sources and table names, in order to carry out the specified database operations. The keyword-mapping table, called Mail Map, is used for translating words in the email message into specific database commands. The table is stored in a Microsoft Access database under the DATA subdirectory where the server is installed. It has the following columns:

Let's use some examples to demonstrate how the server works. Suppose the server is to support access of customer information, and it allows users to query customer information and to add new customers. Two filters can be defined as follows:

Now, if user sends a message

which matches to the filter of Example 1. The server will fetch data using the select statement and sends a response message using the template file CutomerInfo.txt that contains the result set.

When an email arrives with a subject header AddCustomer, the SQLMail server will perform database operation using the customer table. To see how this works, we need to use a real email message:

The email requests an insert operation to the customer table. The server looks up the map file and finds the SQL statement associated with the subject. It connects to MyODBC database, opens the table using the SQL command, and adds the record in a customer table.

The subject string in the mapping file can be of any readable characters excluding space.

The mapping table provides flexibility of changing database tables with no impact to users. For instance, if for some reason, the customer table needs to be changed to MyCustomer table. The only thing needs to be modified is the SQL statement associated with the CustomerInfo subject.

In order to successfully carry out database operations, the email message must follow certain formats. This section defines the protocol for the database requests.

There is very little restriction for query request, because a query doesn't contain new information to the database. A query can be issued as long as the keywords used in the message are defined in the mail map file.

For experienced SQL users, SQL select commands can be issued directly to the server using the following in the email message body:

SQLStatement: YourQueryStatement

For example, to get a customer listing in the United Kingdom, a user may send the following message to the server:

To add a new record into a table, the following conditions must be met:

Unlike the Insert operation, the Delete operation must uniquely identify a record. This requires search criteria in the SQL statement so that only one record is deleted at a time. This is easy if there is a primary key in the table. For example,

select * from customer where CUSTID = 'WANX'

The statement takes advantage of the primary key , CUSTID, in the customer. Then the question is how to construct a SQL statement that can be used for all delete messages of the same table? SQLMail server allows you to build template SQL commands with variables in it. These variables are substituted by the values in the email message. All variables in the template SQL command starts with a '$'.

For instance, if a keyword, RemoveMe, is defined as,

The following email message would remove WANX from the customer table.

The server replaces variable $CUSTID in the SQL statement with WANX.

The field values supplied in the DELETE operation are used for locating the record only. As always, each field name and value pair must be on the single line.

Update is the most complicated operation discussed so far. The email message must satisfy the following condition:

The SQLMail server can send information from databases to a group of recipients in a predefined time interval. In order to use the automatic mailer, you need to specify how the task should be scheduled, the trigger time, and the tasks. These should be specified in the MailScheduler table. The table is part of the SQLDataSystem database under the DATA subdirectory. It is defined as follows:

ScheduleType is an integer that specifies how the tasks should be scheduled, and the TriggerTime defines exactly when the task should be executed. For instance, if a ScheduleType is weekly, then triggertime should be the day in a week. The following table shows possible values for ScheduleType and the associated meaning of the TriggerTime:

The SQLMail server uses template files for formatting email responses. You can have you own template file for each entry in the MailMap file. For example, if you wish to have customer information formatted using MyTemplate.txt file, you may change the CustomerInfo entry to the following:

If no template defined for a mail map, the server uses the template file defined in the server configuartion file (SQLMAIL.INI):

QueryPage = record_browser.html

Since anyone could send a message to the server and possibly perform an illegal operation. Server security and user authentication becomes very important. This section discuss ways to deal with such issues.

The SQLMail server offers several ways to tighten the security.

A message filter is a string that defines what message should be rejected or ignored. SQLMail server allows you to use regular expressions to construct the filter. For example, to reject messages from John and Joe, one would define:

From = *John*;*Joe*

in the filter file where * matches any number of characters. Multiple regular expressions are separated by semicolon.

Message filter can be stored in a text file, the file is then referenced by a setting, PopFilter, in the configuration file:

where MyFilter.ini is the name of the file that contains all the message filters.

You can also enable user authentication by setting the Authentication flag to 1 in the configuration file. Users who want to access the service must also include user name and password in the message body when authentication is required. Using the previous example, a user who wants to access the customer table would have to send a message similar to the following:

Although user authentication can limit unwanted access, the method is not secure for highly sensitive information because the user name and password are often sent as clear text over the network.

The SQLMail server provides similar access control to the SQLData Enterprise Server. It allows you to specify who have what access rights to the database services. For further information, please reference to the SQLData Server User Guide.

In most of the applications, users can perform database queries. It is a good idea to remove message mappings that cause update, deletion or insertion from the message-mapping file. The SQLMail server will only perform operations defined in the message-mapping file and ignore all other messages.

The first thing to setting up SQLMail Server is to create your own databases and database tables. We assume that you already have your DBMS properly configured. The following is a general procedure for setting up the SQLMail server:

The SQLMail Server installs sample ODBC data sources in your system. These data sources can be used for performing basic tests. The SQLMail server should work with the test database without any modification. We have also set up a dedicated email account for testing the SQLMail server. The account information is as follows:

User Name : sqlmail

Password : tester

You can test your server after installation with the following procedure:

You should see activities in your server window when it checks emails and sends response. Wait for couple of minutes, and then check your mails. If you see a list of customers in your email, then the server has passed the connectivity testing.

You can run the SQLMail server either as a standalone application or as a Windows NT service. It is a good idea to run the server in a command prompt first to make sure the server is configured properly.

To start the server in console mode, execute the SQLDATA.EXE file under the BIN subdirectory where the server is installed:

You can start the server in debugging mode for tracking down server problems:

The server produces verbose logging in the debug mode. The log files are under the LOGS subdirectory.

To start the server as a Windows NT service, go to the control panel and double click the Services icon, then select SQLMail Service from the service list and click the Start button. You can set the Startup Type of the service to Automatic so that the server will be started when the machine is turned on.