MS SQL

Overview

MS SQL step is used to access Microsoft SQL Database and make basic queries on the database (inserting, selecting, updating, deleting and joins).

Configuration

• User Name (user) - user name to access the MS SQL DB, we recommend consuming it as workspace variable e.g. {{$env.DB_USER}})
• Password (password) - password for accessing the database, we recommend consuming it as workspace variable e.g. {{$env.DB_PASSWORD}})
• Connection String (connectionString) - connection string to MS SQL database (the recommended way of usage would be to specify a global connection string and access it here through e.g. {{$env.DB_CONNECTION}}).

note

If either of user name or password (optional fields) values are given in fields and connection string, the ones in the fields are prioritized.

Actions

Actions allows creating SQL queries that need to be run when the step is executed.

• Actions (actions) - SQL queries to execute when the step is run
  • SQL (actions[].sql) - SQL statement to execute on the database (e.g. SELECT id FROM users WHERE email = @email)
  • Binds (actions[].binds) - object of variables to insert into the query (e.g. { "email": "test@test.com" })
  • Target Path (actions[].targetPath) - doc node on which to place the query results

Examples

Create table

Create table flow example

tags:
  - general
steps:
  - stepType: mssql-db
    config:
      actions:
        - sql:
            create table employee_details (emp_id int, emp_name varchar(50), emp_age
            int);
          binds: {}
          targetPath: result
      connectionString: Server=localhost,1433;Database=database;User
        Id=username;Password=password;Encrypt=true
    description: Create Employee details table
    name: MS SQL
    condition: ""
sampleData: {}

Result

{
  "doc": {
    "result": {
      "recordsets": [],
      "output": {},
      "rowsAffected": []
    }
  },
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "mssql-db",
        "executionTime": 278
      }
    ],
    "executionTimeOfFlow": 279,
    "timeMetric": "ms"
  }
}

Insert

Insert into table flow example

tags:
  - general
steps:
  - stepType: mssql-db
    config:
      actions:
        - sql:
            insert into employee_details (emp_id, emp_name, emp_age) values (@emp_id,
            @emp_name, @emp_age)
          binds:
            emp_id: 10
            emp_name: first
            emp_age: 25
          targetPath: result
      connectionString: Server=localhost,1433;Database=database;User
        Id=username;Password=password;Encrypt=true
    description: Insert into Employee Details Table example
    name: MS SQL
    condition: ""
sampleData: {}

Flow Result

{
  "doc": {
    "result": {
      "recordsets": [],
      "output": {},
      "rowsAffected": [1]
    }
  },
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "mssql-db",
        "executionTime": 130
      }
    ],
    "executionTimeOfFlow": 130,
    "timeMetric": "ms"
  }
}

Select

Select from table flow example

tags:
  - general
steps:
  - stepType: mssql-db
    config:
      actions:
        - sql: select * from employee_details where emp_id=@emp_id
          binds:
            emp_id: 10
          targetPath: result
      connectionString: Server=localhost,1433;Database=database;User
        Id=username;Password=password;Encrypt=true
    description: Select from Employee details table example
    name: MS SQL
    condition: ""
sampleData: {}

Result

{
  "doc": {
    "result": {
      "recordsets": [
        [
          {
            "emp_id": 10,
            "emp_name": "first",
            "emp_age": 25
          }
        ]
      ],
      "recordset": [
        {
          "emp_id": 10,
          "emp_name": "first",
          "emp_age": 25
        }
      ],
      "output": {},
      "rowsAffected": [1]
    }
  },
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "mssql-db",
        "executionTime": 67
      }
    ],
    "executionTimeOfFlow": 67,
    "timeMetric": "ms"
  }
}

Update

Update table flow example

tags:
  - general
steps:
  - stepType: mssql-db
    config:
      actions:
        - sql: update employee_details set emp_name=@emp_name where emp_id=@emp_id
          binds:
            emp_id: 10
            emp_name: first last
          targetPath: result
      connectionString: Server=localhost,1433;Database=database;User
        Id=username;Password=password;Encrypt=true
    description: Update Employee details table example
    name: MS SQL
    condition: ""
sampleData: {}

Result

{
  "doc": {
    "result": {
      "recordsets": [],
      "output": {},
      "rowsAffected": [1]
    }
  },
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "mssql-db",
        "executionTime": 143
      }
    ],
    "executionTimeOfFlow": 144,
    "timeMetric": "ms"
  }
}