Database connector enables data synchronization between email server and database server. There are many built-in events in the DBConnector which can be called by executing a single/multiple SQL statement(s) in specified event. With this feature, you can synchronize user/domain data between server and database server easily. You can also store email message to database for archive and statistic purpose.
Data Connection String
To use DBConnector, you need to specify a Data Connection String. This connection string is used by DBConnector to connect the database server and execute SQL statement. After the connection string is defined, you should use "Test Connection" function to verify the database connection.
Connection String Reference
Warning: incorrect data connection string will degrade the performance of EmailArchitect Server.
For other connection strings, please contact our support team.
Using desktop database engine, such as MS Access, is not recommended. They are too slow and in turn degrade the server performance.
Database Server Driver
In Data Connection String, you should specify a database driver to connect database. You can open "Control Panel" -> "Administrative Tools" - > "ODBC Data Sources" - "Drivers" to check current installed database drivers.
Common SQL Driver Download
If SQL Server is installed on a remote server, and you don't have SQL driver installed on local machine, then you need to download and install corresponding driver on local machine.
MS Access Database Driver Download
Microsoft Access Database Engine 2010 Redistributable
Thread Model
If Apartment Thread Model is checked, DBConnector creates independent data connection for each thread; if this option is not checked, DBConnector creates and shares data connections in different threads automatically.
Message Event: When an email is ready to delivery
This event occurs when an email is ready to deliver to a local/remote recipient.
Every incoming/outgoing email fires this event. You can set single/multiple SQL
statement in this event.
The following variables are available in SQL statement.
| Name | Description |
| {$dbvar_size} | The email size in bytes. |
| {$dbvar_sender} | The email current sender email address, the value can be null and the maximum length is 128. |
| {$dbvar_rcpt} | The email current recipient email address, the maximum length is 128. |
| {$dbvar_messageid} | Message-ID of current email, the value can be null and maximum length is 128. |
| {$dbvar_ipaddress} | The sender IP address, the value can be null and maximum length is 30. |
| {$dbvar_authuser} | If this email is sent by an authorized user, the value is the email address of the user; otherwise the value is null, the value maximum length is 128. |
| {$dbvar_subject} | The email subject, the value can be null and maximum length is 250. |
| {$dbvar_subject:[length]} | The email subject in limited length, for example: {$dbvar_subject:64} returns the subject with maximum 64 characters. |
| {$dbvar_bodyformat} | The email body format, the value can be "", "text/plain" or "text/html". |
| {$dbvar_headers} | The email headers, the value can be null and maximum length is 1000. |
| {$dbvar_headers:[length} | The email headers in limited length, for example: {$dbvar_headers:256} returns the subject with maximum 256 characters. |
| {$dbvar_header:[headername]} | Specified email header value, the value can be null and maximum length is 250, for example: {$dbvar_header:X-Mailer} returns the X-Mailer header value. |
| {$dbvar_bodytext} | The email body text, the value can be null and maximum length is unlimited. |
| {$dbvar_bodytext:[length]} | The email body text in limited length, for example: {$dbvar_bodytext:4000} returns the subject with maximum 4000 characters. |
| {$dbaction_savefolder:[path]} | DBConnection saves current *.eml to the specified path if this variable is declared, for example, {$dbaction_savefolder:c:\archive} means the *.eml will be saved to c:\archive. You can also use #YYYY, #MM, #DD in [path], #YYYY will be replaced by current year (for example 2007), #MM will be replaced by current month (01-12), #DD will be replaced by current day (01-31). |
| {$dbvar_filename} | If the {$dbaction_savefolder:[path]} is declared, then the variable returns the file name of the *.eml. |
| {$dbvar_fullpath} | If the {$dbaction_savefolder:[path]} is declared, then the variable returns the file name of the *.eml with full path. |
| {$dbvar_messagehash} | If the {$dbaction_savefolder:[path]} is declared, then the variable returns the email content SHA1 hash string. |
Usage Example
//this sql statement inserts the following record to messages table.
INSERT INTO messages ( sender, rcpt, subject, messagesize, fromIP ) VALUES ( '{$dbvar_sender}', '{$dbvar_rcpt}', '{$dbvar_subject}', {$dbvar_size}, '{$dbvar_ipaddress}' )
//DBConnector saves the eml file to c:\archive folder and insert
the following record to messages table.
{$dbaction_savefolder:c:\archive}INSERT INTO messages ( sender, rcpt, subject, messagesize, fromIP, filename ) VALUES ( '{$dbvar_sender}', '{$dbvar_rcpt}', '{$dbvar_subject}', {$dbvar_size}, '{$dbvar_ipaddress}', '{$dbvar_fullpath}' )
The following condition filters are available in SQL statement.
| Name | Description |
| {$dbfilter_sender:[string]} | Executes the SQL statement if the sender contains specified string. |
| {$dbfilter_rcpt:[string]} | Executes the SQL statement if the receipient contains specified string. |
| {$dbfilter_senderrcpt:[string]} | Executes the SQL statement if the sender or receipient contains specified string. |
| {$dbfilter_subject:[string]} | Executes the SQL statement if the subject contains specified string. |
Usage Example
//This SQL statement only inserts the following record to messages
table with the message sender contains *@mydomain.
{$dbfilter_sender:*@mydomain}INSERT INTO messages ( sender, rcpt, subject, messagesize, fromIP ) VALUES ( '{$dbvar_sender}', '{$dbvar_rcpt}', '{$dbvar_subject}', {$dbvar_size}, '{$dbvar_ipaddress}' )
Message Event: When an email is delivered to a local user
This event occurs when an email is delivered to a local recipient mailbox. Every
incoming email to user mailbox fires this event. You can set single/multiple SQL
statement in this event.
Variables: Please refer to the variables in "When an email is ready to delivery"
Filters: Please refer to the variables in "When an email is ready to delivery"
Message Event: When an email is delivered to a remote host
This event occurs when an email is delivered to a remote recipient mailbox. Every
outgoing email to remote recipient fires this event. You can set single/multiple
SQL statement in this event.
Variables: Please refer to the variables in "When an email is ready to delivery". There is one more variable {$dbvar_host} available in this event, it returns the remote host address.
Filters: Please refer to the variables in "When an email is ready to delivery"
Message Event: When an email is failed to deliver to a local user
This event occurs when an email is failed to deliver to a local user mailbox. You
can set single/multiple SQL statement in this event.
Variables: Please refer to the variables in "When an email is ready to delivery". There is one more variable {$dbvar_desc} available in this event, it returns an error description. The maximum length is 250.
Filters: Please refer to the variables in "When an email is ready to delivery".
Message Event: When an email is failed to deliver to a remote host
This event occurs when an email is failed to deliverto a remote email address. You
can set single/multiple SQL statement in this event.
Variables: Please refer to the variables in "When an email is delivered to a remote host" . There is one more variable {$dbvar_desc} available in this event, it returns an error description. The maximum length is 250.
Filters: Please refer to the variables in "When an email is ready to delivery".
Authentication Event: When a user is successful to login
This event occurs when a local user is successful to login WEB/SMTP/POP/IMAP service.
The following variables are available in SQL statement.| Name | Description |
| {$dbvar_ipaddr} | The IP address that the user login. |
| {$dbvar_conntype} | connection type, it can be "smtp", "web", "pop3" and "imap4" |
| {$dbvar_domain} | The domain name, it can be null (system user) and maximum length is 64. |
| {$dbvar_user} | The user name without domain name, maximum length is 64. |
| {$dbvar_email} | The user email address, maximum length is 128. |
| {$dbvar_firstname} | The user first name, it can be null and maximum length is 64. |
| {$dbvar_lastname} | The user last name, it can be null and maximum length is 64. |
| {$dbvar_md5password} | The user password hashed by MD5, the maximum length is 32. |
| {$dbvar_isenabled} | This variable indicates if the user is active, the value is 0 or 1. |
| {$dbvar_maxmessagesize} | The maximum size of the message to the user mailbox. |
| {$dbvar_maxmbxsize} | The maximum size of the user mailbox. |
| {$dbvar_isalias} | This variable indicates if the user is an alias, it can be 0 or 1. |
| {$dbvar_flags} | The user flags. |
| {$dbvar_isadmin} | This variable indicates if the user is a domain administrator, it can be 0 or 1. |
| {$dbvar_changepassword_enabled} | This variable indicates if the user can change password, it can be 0 or 1. |
| {$dbvar_accessweb_enabled} | This variable indicates if the user can access web mail, it can be 0 or 1. |
| {$dbvar_accesssmtp_enabled} | This variable indicates if the user can access smtp service, it can be 0 or 1. |
| {$dbvar_accesspop3_enabled} | This variable indicates if the user can access pop3 service, it can be 0 or 1. |
| {$dbvar_accessimap4_enabled} | This variable indicates if the user can access imap4 service, it can be 0 or 1. |
Authentication Event: When a user is failed to login
This event occurs when a user is failed to login SMTP/POP3/IMAP4/WEB service.
Variables: Please refer to the variables in "When a user is successful to login".
Connection Event: When a connection to SMTP/POP3/IMAP4 is finished
This event occurs when a connection to SMTP/POP3/IMAP4 is finished.
The following variables are available in SQL statement.| Name | Description |
| {$dbvar_ipaddr} | The IP address of the connection. |
| {$dbvar_conntype} | Connection type, it can be "smtp", "pop3" and "imap4" |
| {$dbvar_size} | This variable indicates how many data is transferred in this connection. |
| {$dbvar_user} | This variable indicates the authorized user name in the connection, if none is login, this value is null. |
| {$dbvar_user} | This variable indicates the authorized user domain in the connection, if none is login, this value is null. |
| {$dbvar_email} | This variable indicates the authorized user email address in the connection, if none is login, this value is null. |
Domain Event: When a local/remote domain is created
This event occurs when a local/remote domain is created on the server.
The following variables are available in SQL statement.| Name | Description |
| {$dbvar_domain} | The domain name, the maximum length is 64. |
| {$dbvar_isenabled} | This variable indicates if the domain is active, it can be 0 or 1. |
| {$dbvar_maxusers} | This variable indicates maximum users of the domain. |
| {$dbvar_maxmbxsize} | This variable indicates maximum size of mailbox in this domain. |
| {$dbvar_isalias} | This variable indicates if the domain is an alias, it can be 0 or 1. |
| {$dbvar_option} | For local domain, this variable returns the nobody alias of the domain; for remote domain, this variable returns the target address of the remote domain, the maximum length is 128 |
| {$dbvar_flags} | The domain flags. |
Domain Event: When a local/remote domain is deleted
This event occurs when a local/remote domain is deleted.
Variables: Please refer to the variables in "When a local/remote domain is created".
Domain Event: When a local/remote domain is updated
This event occurs when a local/remote domain is updated.
Variables: Please refer to the variables in "When a local/remote domain is created" . There is one more variable named {$dbvar_oldname}, it returns the domain old name.
User Event: When a user is created
This event occurs when a user is created.
Variables: Please refer to the variables in "When a user is successful to login" . {$dbvar_ipaddr} and {$dbvar_conntype} are not available in this event.
User Event: When a user is deleted
This event occurs when a user is deleted.
Variables: Please refer to the variables in "When a user is created".
User Event: When a user is updated
This event occurs when a user is updated.
Variables: Please refer to the variables in "When a user is created". There is one more variable named {$dbvar_oldname}, it returns the user old name.
See Also
Quick Tutorial User Permissions Domain Administration User Administration Services Administration SMTP Service POP3 Service IMAP4 Service Remote Object Call Service Webmail Service SSL Configuration Realtime Black List Anti-Spam Anti-Virus List Administration Traffic Control DBConnector Mail Archive DomainKeys and DKIM signature Storage and User Mailbox Incoming/outgoing Filters Advanced Functions in Filter Templates
EmailArchitect Server
Website
EmailArchitect Server Community
MS Access:
Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\mydatabase.accdb;Uid=;Pwd=;
ORACLE:
Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;
MySQL:
DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=myDatabase;USER=myUsername;PASSWORD=myPassword;OPTION=3;
Driver={MySQL ODBC 5.1 Driver};Server=myServerAddress;Database=myDatabase;User=myUsername;Password=myPassword;Option=3;
MS SQL Server 2005 Native provider:
Provider=SQLNCLI;Server=serveraddress;Database=database;Uid=user;Pwd=password;
MS SQL Server 2008:
Driver={SQL Server Native Client 10.0};Server=serveraddress;Database=database;Uid=myUsername;Pwd=myPassword;
MS SQL Server 2008 Native provider:
Provider=SQLNCLI10;Server=serveraddress;Database=database;Uid=user;Pwd=password;
MS SQL Server 2012:
Driver={SQL Server Native Client 11.0};Server=serveraddress;Database=database;Uid=myUsername;Pwd=myPassword;
MS SQL Server 2012 Native provider
Provider=SQLNCLI11;Server=serveraddress;Database=database;Uid=user;Pwd=password;