Microsoft SQL Server: Client

Summary

This module contains actions for running queries & stored procedures on a Microsoft SQL Server.

Running Queries

See Using Placeholders before using the select, insert, & update actions.

Placeholders are not required when using the stored_procedure action.

Actions

mssql.client.

input_table

Display a list of records in a table, & allow the task operator to make a selection.

Minimum Plugin Version: 2.0.0

Input

• text: the title of the table

• records: a list of dictionaries to display in the table

• minimum: The minimum number of acceptable selections (Optional)

• maximum: The maximum number of acceptable selections (Optional)

Output

A list of dictionaries.

Tip

• If neither a minimum or maximum is provided, the task operator will be able to submit 0 selections.

• If minimum or maximum are provided, the operation will repeat until a valid number of selections is made.

Example

Getting all records from a users table, displaying it and waiting for a single selection:

- mssql.client.select:
    query: SELECT * FROM users
  load:
    mssql: mssql_secrets
  save: users

- mssql.client.output_table:
    text: Select a user
    minimum: 1
    maximum: 1
  load:
    records: users

mssql.client.

insert

Run an INSERT SQL query.

Minimum Plugin Version: 2.0.0

Input

• mssql: an MSSQLServer dictionary

• query: the SQL Query to run

• placeholders: Optional Placeholders to format in to the SQL query before it is executed.

Output

Nothing is outputted by this action.

Example

- mssql.client.insert:
    query: INSERT INTO users(username, name) VALUES(?, ?)
    placeholders:
      - "{{ username }}"
      - "{{ name }}"
  load:
    mssql: mssql_secrets

mssql.client.

output_table

Display a list of records in a table.

Minimum Plugin Version: 2.0.0

Input

• text: the title of the table

• records: one or more records outputted by select

Output

Nothing is outputted by this action.

Example

Getting all records from a users table and displaying it:

- mssql.client.select:
    query: SELECT * FROM users
  load:
    mssql: mssql_secrets
  save: users

- mssql.client.output_table:
    text: All Users
  load:
    records: users

mssql.client.

select

Run a SELECT SQL query.

Minimum Plugin Version: 2.0.0

Input

• mssql: an MSSQLServer dictionary

• query: the SQL Query to run

• placeholders: Optional Placeholders to format in to the SQL query before it is executed.

Output

Either a:

• List of dictionaries containing the query results

• Empty list if the query returned no data

Tip

For more information regarding the output from this action, see Running Select Queries.

Example

- mssql.client.select:
    query: SELECT * FROM users WHERE name LIKE '%?%'
    placeholders:
      - "{{ name }}"
  load:
    mssql: mssql_secrets
  save: users

mssql.client.

stored_procedure

Run a stored procedure.

Minimum Plugin Version: 2.0.0

Input

• mssql: an MSSQLServer dictionary

• name: the name of the stored procedure (must start with sp_)

• parameters: Optional dictionary of parameters to pass in to the stored procedure

Filtered Column Types

Only columns with the following data types are outputted by this action:

• text (string)
• numbers (integer & float)
• boolean (true & false)
• null

Columns such as SID are removed as PPA receives them in bytes format, which is not JSON compatible.

Output

A list of dictionaries.

Example 1 - Without Parameters

- mssql.client.stored_procedure:
    name: sp_helplogins
  load:
    mssql: mssql_secrets
  save: user_logins

Example 2 - With Parameters

- mssql.client.stored_procedure:
    name: sp_helpuser
    parameters:
      name_in_db: domain\example.user
  load:
    mssql: mssql_secrets
  save: user_info

mssql.client.

update

Run an UPDATE SQL query.

Minimum Plugin Version: 2.0.0

Input

• mssql: an MSSQLServer dictionary

• query: the SQL Query to run

• placeholders: Optional Placeholders to format in to the SQL query before it is executed.

Output

Nothing is outputted by this action.

Example

- mssql.client.update:
    query: UPDATE users SET surname = ? WHERE id = ?
    placeholders:
      - "{{ surname }}"
      - "{{ user_id }}"
  load:
    mssql: mssql_secrets