Related articles |
How to link to an Excel Spreadsheet
Connecting to a MySQL Database
|
| |
Overview |
With the introduction of GroupMail 5 it is now possible to link to external databases that contain your mailing list contact details and access
these as a group in GroupMail.
This functionality is only available in GroupMail Business Edition. The main objective is to use your existing corporate database when
managing your email campaigns instead of having to import the data into GroupMail and repeatedly synchronize the copy after new additions and removals from the
underlying list. Depending on the database being used and the way it is setup it may also be possible to amend recipient data directly from GroupMail if this is desirable.
Typical databases that are accessible through GroupMail include
- Microsoft Access®
- Microsoft FoxPro®
- Microsoft SQL Server®
- MySQL®
- Oracle®
- Clipper / DBase
In addition to databases it is also possible to connect to other file formats and contact management systems, however it may not be possible
to update the data. Examples of some of the formats that can be used include
- Microsoft® Excel® spreadsheets
- FileMaker®
- GoldMine®
- Most data sources that provide an ODBC driver
The advantages of this approach are
- There is no need to import your mailing list/customer database into GroupMail prior to sending your campaign
- SQL statements can be used to filter the exact recipients required for the current mailing
- Recipients stored in web based databases can be accessed directly
- Opt-In and Opt-Out requests only need to be reflected in one data source
Some items to consider when using Groups that use Data Connections
- Is the data connection configured appropriately for the GroupMail user ? If configured, data can be updated / deleted in the
underlying database directly from the GroupMail user interface.
- If the underlying database does not provide update privileges to GroupMail, how will opt-in, opt-out and bounced emails be
handled in your mailing list ?
In summary to use contacts stored in a database from GroupMail you need to create a database connection that defines the type of
database, then create a group that uses this connection and specify the data to return.
|
| |
| Back To Top |
| |
Setting up a Database Connection |
|
To create a database connection open GroupMail v5, go to the Tools menu and choose Database Connections Manager, the
screen shown in Figure 1 is displayed
|
| |
Figure 1
|
| |
|
Choose New to create a connection. In this case we will connect to a Microsoft SQL Server® database called
Infacta on a server called SpongeBob. This server is on the local network, however it could just as easily be a server located remotely on your web server.
|
| |
Figure 2
|
| |
|
Specify a Name for the connection, and click on the Wizard button to specify the connection string, the Data Link
Properties screen is shown in Figure 3 below
|
| |
Figure 3
|
| |
|
By default the Microsoft OLE DB Provider for ODBC Drivers is selected in the list, however this should only be used if there is not a
specific driver for your database. The table below lists some of the more commonly used databases together with the driver that should be selected.
|
| |
| Database Name |
Recommended Provider |
| Microsoft® Access® |
Microsoft Jet 4.0 OLE DB Provider |
| Microsoft® FoxPro® |
Microsoft OLE DB Provider for ODBC Drivers |
| Microsoft® SQL Server® |
Microsoft OLE DB Provider for SQL Server |
| MySQL® |
Microsoft OLE DB Provider for ODBC Drivers |
| Oracle® |
Microsoft OLE DB Provider for Oracle |
| Clipper / DBase |
Microsoft OLE DB Provider for ODBC Drivers |
|
| |
If the required provider is not displayed in the list you may need to download and install either an OLE DB Provider for your database
or if one is not available then an ODBC Driver. These downloads should be available from your database producers website.
Select the Microsoft OLE DB Provider for SQL Server and choose Next, the Connection tab is displayed. The
available settings on this screen will vary depending on the provider selected previously, the properties for Microsoft® SQL Server® are
shown in Figure 4 below.
|
| |
Figure 4
|
| |
In section 1 a valid SQL Server must be specified, this can either be a windows network computer name a url or an IP address depending on
your network configuration and where your server resides.
If a password is specified to log into the server, GroupMail requires that the Allow saving password option is ticked.
Once the server, log on details and the database to use are specified, the Test Connection button can be used to verify that these
settings are valid as shown below.
|
| |
Figure 5
|
| |
|
Choose OK on the Microsoft Data Link screen, and OK on the Data Link Properties screen to return to the
Create a New Database Connection screen shown in Figure 6
|
| |
Figure 6
|
| |
The connection string to your database is now specified. There are a number of other options available, the two important ones are Cursor
Type and Lock Type.
For most configurations set Cursor Type = Static and Lock Type = Optimistic. If this does not work then try ticking the
Client Cursor option.
|
| |
|
Cursor Type
|
| |
Forward Only - This is the default setting, this option is supported by most databases, however is not recommended for use with
GroupMail as while it can be used to display the data returned from the database, attempting to perform searches and/or modifying the data will result in errors.
Static - (Recommended) This cursor type offers the best performance when communicating with the database so if the database
supports it this should be used.
KeySet - Should only be used if Static option is not supported by the database.
Dynamic - Should only be used if Static option is not supported by the database.
|
| |
|
Lock Type
|
| |
Optimistic - (Recommended) Use this setting if you want to give GroupMail users the ability to modify the underlying data in the
database. Note that using this setting does not guarantee updating capability as it may not be possible due to the way the group is defined, underlying security privileges
or that it is simply not possible using the current data source.
Read-Only - This setting is useful when you do not wish the GroupMail user to update the underlying data in the database.
Batch Optimistic - Behaves in the same manner as the optimistic setting, if the underlying database supports this option.
Pessimistic - No noticeable difference when using GroupMail than using the optimistic setting if the underlying database supports it.
It is recommended that the other settings are left at their default values i.e.
Client Cursor - Off
Asynchronous - Off
Command Type - Text
Choose OK to complete the configuration of this database connection to SQL Server. The connection is listed in the Manage
Database Connections screen as shown below.
|
| |
Figure 7
|
| |
|
Now that we have created the data connection, we can now create one or more groups that use this data connection to retrieve the data we
require.
|
| |
| Back To Top |
| |
Create an External Database Group |
We will now create an external database group that uses the database connection created above. This will allow us to view the recipients
contained in the database, send them mailings and also possibly modify their details etc.
Open GroupMail 5, click on the Groups button in the toolbar and select the New Group link, the Create a New
Group screen is displayed as shown in Figure 8 below.
|
| |
Figure 8
|
| |
|
Choose the External Database ... option and choose Next to continue. As can be seen in Figure 9 the database connection
created previously is listed.
|
| |
Figure 9
|
| |
|
Select the required database connection and choose Next to continue.
|
| |
Figure 10
|
| |
This step determines the contents of the group. There are two options available namely the group is to be made up from a table in the
database or from the results of an SQL statement.
There are a number of points to consider here
- For some data sources the Specify Database Table option is not available as the underlying data source does not
provide a list of tables (for example Microsoft® Excel®)
- If you require to be able to update data in the database this will most likely be possible if a table name is specified, without any
filters as opposed to specifying an SQL statement
- If you wish to use a View or Query stored in the database use the SQL option with a statement "Select * from
Viewname"
Choose the required Table / specify an SQL statement and choose Next to continue.
|
| |
Figure 11
|
| |
|
Select the field from the database that contains the email address field and choose Next to continue.
|
| |
Figure 12
|
| |
|
Specify a name for the Group and choose Create to create the Group. The group will now be displayed in the groups list in GroupMail
5 as shown in Figure 13.
|
| |
Figure 13
|
| |
Double clicking on the Group will automatically display the View & Manage Recipients screen, where recipient details are displayed
and also possibly modified.
If the group contents are not required or the database connection details are not correct then these can be changed by right-clicking on the
Group in the list and choosing Modify to display the Modify Database Group screen.
|
| |
Figure 14
|
| |
The group name, underlying database connection, group contents and email address field can be modified from this one screen.
Note that there can be a number of groups associated with each database connection created. Above we use the Customers table,
we could also add another group that uses a Refunds table another that uses an SQL statement such as "Select * from Customers where Mailing = 'Yes'" etc.
This completed the creation of the database group that retrieves its contents from Microsoft® SQL Server®.
|
| |
| Back To Top |
