Working with ODBC data sources
Contents
You can work with many databases through ODBC. You can work with Oracle, Microsoft SQL Server and even with Excel document through ODBC drivers and SQL requests.
AMV can import mailing lists from database with the help of wizard ("File" -> "Import via ODBC"). The wizard requires to set data source name (DSN), name and password for access to source and SQL-request to extract e-mails from database. Apply to your database manual for information about SQL language. The result of request is a mailing list in format supported by AMV. Each row of SQL-request result is one line of mailing list. Some examples of requests:
SELECT email FROM customers_table
SELECT
name,'<'+email+'>'
FROM
customers_table
WHERE
email<>NULL
AMV can import up to 100000 addresses from database. It's recommended to check addresses by small parts with 5000..10000 e-mails each, because AMV requeires about 2 kilobytes of RAM for each address (to keep address,log of checking, etc.).
The export of check results ("File" -> "Export via ODBC") allow to execute SQL expression for each address loaded to AMV. On first wizard page you can choose filtering of addresses to execute requests, e.g., only for successfully checked addresses. When inputing SQL-expression %email% and %result% templates are used. When executing request this templates are replaced by e-mail and text description of its check result. Since version 4.0 of AMV three new macros are available - %code%, %log%, %comment%, which will replaced, correspondingly, by numeric code of check result, email check log (multiline string) and comment to check result.
For example, the expression:
UPDATE
customers_table
SET
email_status='%result%'
WHERE
email='%email%'
will be expanded for ag@mail.com address with "Not in DNS" check result:
UPDATE
customers_table
SET
email_status='Not in DNS'
WHERE
email='ag@mail.com'
Similarly this expression will be expanded for each of addresses and executed suitable number of times.
See also: Program overview, Program support