FromSQL plugin is intended for merger of database and mail list. FromSQL works with database through ODBC, so it can interact with both Microsoft Excel file and big database under Microsoft SQL Server or Oracle control. Read “Internal SMTP server and mailboxes” article carefully if you don’t know ADR plugin ideology.

This article is intended for advanced users who have skills of work with DBMS or Microsoft Excel and who know basis of SQL. This article is about plugin purposes and its setting. Also there are solutions of practical tasks with FromSQL plugin help.

For example, you have such table:

customers

------------------------------------------

name                email                        account

------------------------------------------

Smith        recycled0@pisem.net        756320

Doe                recycled1@pisem.net        89201

Green        recycled2@pisem.net        103990

------------------------------------------

and you must send the following message each month for all users:

Dear Mr. Smith,

 

thank you for your interest to our project.

Your account is $756320 now.

 

Support Team,

support@support.com

 

FromSQL plugin can help you to automate this process. Just create mail box, add to it mail list with recipients addresses and FromSQL plugin. The setting of FromSQL plugin is realized through FromSQL.INI file, which is in the same directory as plugin.

In settings you must indicate name of ODBC data source, name and password for data access. User’s password and name may be empty. You must configure SQL-request for receiving data from database. Set the name of file with SQL-request in Request parameter in FromSQL.INI.  If there is no file path so it is in the same directory as plugin.

The request in this case must be like this:

SELECT name, account FROM customers WHERE email='%recipient%'

%recipient% macro will be replaced in request by recipient email. Plugin replace %%1%%, %%2%% and etc macro in the message by the first, second and etc column value of the first line of SQL-request result. For example, the initial message:

Dear Mr. %%1%%,

 

thank you for your interest to our project.

Your account is $%%2%% now.

 

Support Team,

support@support.com

 

after request:

SELECT name, account FROM customers WHERE email='recycled0@pisem.net'

will look like the message at the beginning of article. Pay attention that if SQL-request return several lines then all lines except the first will be ignored.

Microsoft Excel file may be used as data source. For that ODBC drivers for Excel (from Microsoft Office) must be installed. In directory with plugin there is FromSQL.XLS file which is for our example demonstration.

Choose “ODBC Data Source” item in “Control Panel”. Choose Excel driver and create user DSN. Call data source as “FromSQL” and indicate workbook as FromSQL.XLS, which is in directory with plugin. During installation the program have created fromsql mailbox which is constructed for our example. You have to only create “FromSQL” data source to completely set this example.

Now you can send message to fromsql@localhost address and make sure that the setting is correct. You can see also the created messages in MAIL\OUT directory of Advanced Direct Remailer.

Another important note about Excel documents. Before use such document as database table give name for each list area which play a part of table. So in FromSQL.XLS file the customers name is given for $A$1:$C$33 cells ("Insert" -> "Name" -> "Define"). The names of table columns are the values of the first cells of named area, for our example A1, B1, C1 cells.

We don’t give examples for a large DBMS such as Microsoft SQL Server because the administrators of such bases can configure FromSQL plugin without any assistance. Otherwise apply to us and we'll try to help you.

Starting with 1.1 ADR version you can use not only early formed mailing lists, but mailing lists formed on fly by database SQL-request. Read  “Mailing list formats” article to know more about SQL-lists.

About SQLTable plugin, About FromCSV plugin, Internal SMTP server and mailboxes, Mailing list formats