Help Center

Menu

Microsoft SQL Server

The Microsoft SQL Server component integrates Microsoft SQL Server through Squiz Connect.

Authentication

The following properties are available when configuring a connection to your Microsoft SQL Server database:

Properties to configure a connection

Other types of configuration properties are also supported, which are described in more detail in the Microsoft SQL Server client for Node.js npm package documentation.

Environment Variables

LOG_LEVEL - controls verbosity of logger. Valid values:

  • trace

  • debug

  • info (default)

  • warn

  • error

Triggers

SELECT trigger and action

Use the Microsoft SQL Server select keyword trigger and action to fetch data from the database, for example:

SELECT Trigger and Action - configure input

When configuring this trigger and action, you can also see the mapping results immediately:

SELECT Trigger and Action - configure input

1 Click the 'view' icon to see the mapping result.

2 The mapping result itself.

There are no limitations on the number of rows which this trigger and action operates on. You can expect to retrieve all rows via sequential fetching implemented within the node.js mssql driver.

Polling

The Microsoft SQL component remembers the last execution timestamp and lets you build queries from this information, for example:

select * from Leads where Created >= '%%LAST_POLL%%'

where just before executing the statement the %%LAST_POLL%% is replaced with the ISO date of the last execution, for example 2017-08-25T07:43:48.127Z.

During the first execution, the date is equal to Epoch time - 1970-01-01T00:00:00.000Z.

Actions

SELECT action

Refer to SELECT trigger and action (above).

INSERT/DELETE/UPDATE action

This component supports the Microsoft SQL Server insert, delete and update keywords for its action:

INSERT/DELETE/UPDATE Action - configure input

When configuring this action, you can also see the mapping results immediately:

INSERT/DELETE/UPDATE Action - configure input

1 Click the 'view' icon to see the mapping result.

2 The mapping result itself.

This action performs operations that do not generate rows of output but instead, performs data manipulations within the database, for example, INSERT, UPDATE or DELETE statements.

Internally, statements are prepared before being sent to the database, so all incoming data is validated against SQL injection. However, since a built connection between JavaScript types and Microsoft SQL Server data types is required, then when preparing a statement, add the relevant :type syntax to each prepared statement variable.

For example, if you have a following SQL statement:

INSERT INTO
  Test2.dbo.Tweets
(Lang, "Text", id, CreatedAt, Username, ScreenName)
VALUES
(@lang, @text, @id, @created_at, @username, @screenname)

Add the relevant :type syntax to each @parameter, so your SQL query would look something like:

INSERT INTO
  Test2.dbo.Tweets
(Lang, "Text", id, CreatedAt, Username, ScreenName)
VALUES
(@lang, @text, @id:bigint, @created_at:date, @username, @screenname)

The following types (i.e. representing the :type syntax), are supported:

  • string (also default type if type is omitted)

  • number (will be converted to MSSQL int)

  • bigint

  • boolean (will be converted to MSSQL bit)

  • float

  • date (will be converted to DateTime2)

  • money

The Microsoft SQL Server component supports dynamic incoming metadata. As soon as your query is in place, the query is parsed and incoming metadata is generated accordingly.