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

{
  "id": "b14c5333-c74b-4a62-8c92-9c424b5283d7",
  "name": "createEmployeeDetailsTable",
  "flow": {
    "name": "createEmployeeDetailsTable",
    "description": "",
    "steps": [
      {
        "stepType": "mssql-db",
        "color": "rgb(95, 206, 165)",
        "displayName": "MS SQL",
        "isSelected": true,
        "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",
        "textColor": "black"
      }
    ]
  },
  "inputSchema": {},
  "createDate": "2022-08-19T07:57:00.315Z",
  "tags": ["general"],
  "inputSchemaLabel": "",
  "version": 1
}

Result

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

Insert

Insert into table flow example

{
  "id": "2b6bf6cc-24a2-4c53-86f9-4bb9399ff54e",
  "name": "insertIntoEmployeeDetailsTable",
  "flow": {
    "name": "insertIntoEmployeeDetailsTable",
    "description": "",
    "steps": [
      {
        "stepType": "mssql-db",
        "color": "rgb(95, 206, 165)",
        "displayName": "MS SQL",
        "isSelected": true,
        "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",
        "textColor": "black"
      }
    ]
  },
  "inputSchema": {},
  "createDate": "2022-08-19T08:01:15.909Z",
  "tags": ["general"],
  "inputSchemaLabel": "",
  "version": 1
}

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

{
  "id": "c9c713fe-05ed-45d9-84fb-cdcf47f2763f",
  "name": "selectEmployeeDetailsTableExample",
  "flow": {
    "name": "selectEmployeeDetailsTableExample",
    "description": "",
    "steps": [
      {
        "stepType": "mssql-db",
        "color": "rgb(95, 206, 165)",
        "displayName": "MS SQL",
        "isSelected": true,
        "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",
        "textColor": "black"
      }
    ]
  },
  "inputSchema": {},
  "createDate": "2022-08-19T08:06:20.460Z",
  "tags": ["general"],
  "inputSchemaLabel": "",
  "version": 1
}

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

{
  "flow": {
    "name": "updateEmployeeDetailsTableExample",
    "description": "",
    "steps": [
      {
        "stepType": "mssql-db",
        "color": "rgb(95, 206, 165)",
        "displayName": "MS SQL",
        "isSelected": true,
        "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",
        "textColor": "black"
      }
    ]
  },
  "tags": ["general"],
  "inputSchema": {},
  "inputSchemaLabel": "",
  "version": 1,
  "id": "eada8fca-8c5a-4436-ae95-3ce539c3a511"
}

Result

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