# MSSQL
# 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
- Go to Add more nodes option on the palette section.
- Check for the MSSQL node and click install.
- Once installed, the designer is required to be restarted.
# Installation from command line
- Go to your project workspace where you can see your
package.json
file. - 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 1433Operation
(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 callrowsAffected
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
: idparameterParameter Type
: String/Integer or any relevant type corresponding to some_table.idParameter Value
: msg.id or any other reference whereidparameter
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.