# MSSQL

Category: Storage
|
Runtime: 2.1+
|
Available: Free

# Overview

The MSSQL Node serves as a client for establishing connections to Microsoft SQL Server databases. This component facilitates the execution of SQL queries and stored procedures, enabling seamless interaction with the database. Users can leverage this node to efficiently manage and retrieve data from the Microsoft SQL Server.

# Installation

# Installation within Kumologica Designer

  1. Go to Add more nodes option on the palette section.
  2. Check for the MSSQL node and click install.
  3. Once installed, the designer is required to be restarted.

# Installation from command line

  1. Go to your project workspace where you can see your package.json file.
  2. Run the command below:
   npm i @kumologica/kumologica-contrib-mssql

# Technical Details

# Properties

  • Server (Optional)
    Name of the server to connect.

  • Database (Required)
    Name of the database.

  • Instance (Optional)
    Instance name associated with the server.

  • Port (Optional)
    Port number of the server to connect. The default value is 1433

  • Operation (Required)

    • SQL query
    • Stored procedure
  • Query Required if selected operation is "Query".
    SQL query to execute. It can be text or reference to msg, var, env variables or jsonata expression.

  • Stored Procedure Name - Required it selected operation is "Stored Procedure"
    The name of SQL Server stored procedure to call.

  • Input Parameters - (Optional)
    List of input parameters to query or stored procedure. It is recommended to use input parameters with sql query to protect against sql injection.

    The input parameter consist of:

    • parameter name
    • parameter type (optional):
      • String (NVarChar)
      • Integer (Int)
      • Float
      • Real
      • Money
      • Boolean (Boolean)
      • Time
      • Date
      • DateTime
      • Buffer (VarBinary)
      • UniqueIdentifier
      • Table (TVP)
      • Image
      • UDT
      • Geography
      • Geometry
    • parameter value
  • Output Parameters (Optional)
    List of output parameters returned by stored procedure. The parameter consist of:

    • parameter name
    • parameter type:
      • String (NVarChar)
      • Integer (Int)
      • Float
      • Real
      • Money
      • Boolean (Boolean)
      • Time
      • Date
      • DateTime
      • Buffer (VarBinary)
      • UniqueIdentifier
      • Table (TVP)
      • Image
      • UDT
      • Geography
      • Geometry
  • Username (Optional)
    Username to authenticate.

  • Password (Optional)
    Password to authenticate.

  • Connection Timeout (Optional)
    Timeout value in milliseconds associated with the connection. Default connection timeout is 15000ms.

  • Request Timeout (Optional)
    Timeout value in milliseconds associated with each request. Default request timeout is 15000ms.

  • Encrypt (Optional)
    If you're on Windows Azure then encrypt must be enabled.

  • Pool Max (Optional)
    The maximum number of connections there can be in the pool. Default is 10.

  • Pool Min (Optional)
    The minimum of connections there can be in the pool. Default is 0.

# Throws

MSSQL node throws the following exception.

  • MSSQL Client Failed

# Returns

Payload will be overwritten by this node. Payload can be accessed in the subsequent node using the following expression msg.payload. None of the variables will be overwritten or enriched by this node.

# Successful Response Structure

The msg.payload has following items:

  • recordsets Array of recordsets resulting from the query execution.
  • recordset Array of records obtained from the query.
  • output The output of a stored procedure call
  • rowsAffected Array of numbers indicating the affected rows when executing INSERT, UPDATE, or DELETE SQL statements.

# SQL Injection

To enhance security and guard against SQL injection vulnerabilities in queries, it is recommended to use input parameters. This practice helps prevent unauthorized access and manipulation of data. Here is an example demonstrating the use of input parameters:

# Example Query

SELECT *
  FROM some_table
 WHERE id = @idparameter

# Parameter definition

  • Parameter Name: idparameter
  • Parameter Type: String/Integer or any relevant type corresponding to some_table.id
  • Parameter Value: msg.id or any other reference where idparameter is appropriately stored.

By employing input parameters in your SQL queries, you ensure a more robust and secure interaction with the database, mitigating the risks associated with SQL injection attacks. This practice is essential for maintaining the integrity and security of your database interactions.