Content from SQL database in templates

Description

You can use dynamic content from external data tables, which can be stored on MySQL, PostgreSQL, MSSQL, Oracle, Hive and ClickHouse database servers.

Connecting to SQL server

To connect to your database you would need an account with proper access privileges. The process depends on your version of Altcraft Marketing:

• If you are a cloud account user — send access credentials to our support — support@altcraft.com. You can also contact this address for any questions related to connecting and configuring external services and databases.

• If you have an On-premise installation, platform administrator should create a database connector in Administrative panel — and assign it to your platform account.

Creating query to external data table

To create a query, go to Data —> Template queries and create a new query:

In general settings, you need to set Query name and Short name for using in the templates editor. If necessary, you can add a description, set groups to restrict other users from accessing the request, and attach tags for quick search.

Select the previously created database connector and enter query body depending on SQL management system you have. When done - you can preview the result:

tip

Also, you can create the query by selecting Data tables in the Data menu. Select Save as template query.

Using SQL query in message templates

In the message template queries are inserted like this: query.your_query_name. To insert query contents into a template use variables menu (</>).

tip

You can also include queries into template loops and logical expressions.

Then you can style your query to be displayed properly:

{for $index $el = query.products}
  <tr width="300" style="padding-bottom: 20px; float: left">
    <td  width="300" style="padding-bottom: 20px; float: left">
      <table cellpadding="0" cellspacing="0">
        <tr>
          <td align="left" valign="top" style="width:30%;"><center>
            <img src="{$el.picture}" width="100%" style="display: block; border: 0;" /></center>
          </td>
          <td class="description" align="left" valign="middle" style="font-family: arial,sans-serif; font-size: 16px; color: #333; padding: 0 16px;">
            <strong style="line-height: 30px !important;">{$el.type} </strong> <br>
            <span style="line-height: 30px !important;">Model: </span>{$el.model}<br>
            <span style="line-height: 30px !important;">Price: </span>$ {$el.price} <br>
                        <a href="https://example.com style="display: block; text-decoration: none; font-weight: bold; margin-top: 15px; text-shadow: 0 2px 15px #000000; color: #313131" name="link1" >Buy now!</a>
          </td>
        </tr>
      </table>
    </td>
  </tr>
{else}
if empty array
{end}