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 name to access the MS SQL Db, we recommend consuming it as workspace variable e.g. {{$env.DB_USER}})
• Password - password for accessing the database, we recommend consuming it as workspace variable e.g. {{$env.DB_PASSWORD}})
• Connection String -the connection string to connect to 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.

• SQL - SQL statement to execute on the database. (e.g. SELECT id FROM users WHERE email = @email)
• Binds - Object of variables that need to be inserted into query. (e.g. { "email": "test@test.com" })
• Target path - 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"
  }
}