mssql-nxg-cg-lib (MicrosoftSQL Library)

1. Introduction

The purpose of this library is to execute queries using the mssql database. The query can be a single operation or batch operations using parameters. The library used is mssql, and it is based in NodeJS.

2. Library Objects and Methods

The library can be installed from the npm page with the following commands:

npm i mssql-nxg-cg-lib, npm install mssql-nxg-cg-lib or yarn install mssql-nxg-cg-lib

• 2.1. Methods

  The following method is available in the library:

  processQuery: This method executes single or parametric queries.

  Method is asynchronous, then it uses async/await

  Before the transformation is executed, it is validated that the JSON to be processed is a valid JSON, only if it use content property, otherwise an exception will be thrown and the flow or process will be stopped.

  Before the method returns the information, it will be validated that the response is a valid JSON or valid JSON Array. If this is correct, the response will be returned, otherwise an exception will be thrown and the flow or process will stop.

• 2.2. Objects and properties

  The next are the objects and properties available in the library.

  • 2.2.1 Objects

    • objectLib: this object contains the required properties for the method processQuery
    • addProps: this object contains the optional properties for the method processQuery

  • 2.2.2 Properties

    {
        user: null,
        password: null,
        server: null,
        database: null,
        port: null
    }

    objectLib contains these properties:

    • The user property indicates the user that has grants to connect to the database.
    • The password property indicates the password of the user that has grants to connect to the database.
    • The server property indicates the name of the mssql server.
    • The database property indicates the name of the database.
    • The port property indicates the name of the port.

    {
        content: null,
        query: null,
        values: null
    }

    addProps contains these properties.

    • The content property indicates the data that will be processed in batch operations, this data is going to replace que ? or variable names accordig with the possition and the query will be executed n times where n is the number of objects in the property times.This property can be arrays of array's, array of an object array, a single array (only values inside), an JSON object and an array of JSON objects.
    • The query property indicates the query given by the user to be executed.
    • The values property indicates the array of values given by the user to be added into the query.

3. Examples

In an implementatión of this library in an Open Integration Hub (OIH) based component the properties and data can be obtained from the msg and cfg attributes.

• Arguments:

1. The first argument is the message (msg) that will come from the OIH component, the property that contains the information in the data object.

2. The second parameter is the configuration (cfg) that will also come from the OIH component.

3. The third argument is only used to define if the library is used in test mode, by default it is false. It will be true if to test the method(s) from a OIH component without running this in a flow.

   3.1. processQuery

• Description: This method will execute a query in batch or single form.

• Object and Properties: To use this method, it is madatory to use the object called objectLib and, if required, the addProps object to send and replace the default values.

• Examples:

  In this first example, the singleQuery method is used to execute a single query to insert one set of values:

  let properties = {
      user:'myuser',
      password:'mypassword';
      server:'myserver',
      database:'mydatabase',
      port: 1433
  };
    
  let query = `SELECT * from dbo.Users`;
  
  const _data = await processQuery({
      data: properties
  }, {}, true);
  console.log(_data)

  In this second example, the parametricQuery method is used to execute multiple queries at once using parameters:

  let properties = {
     user:'myuser',
     password:'mypassword';
     server:'myserver',
     database:'mydatabase',
     port: 1433
  };
   
  let query = `INSERT INTO dbo.Users (name, age, email) VALUES ($1, $2, $3)`;
   
  let values = [['Shankar', 78, '[email protected]'], ['Deepa', 25, '[email protected]'], ['Sunny', 17, '[email protected]']];
  
  const _data = await processQuery({
     data: properties
  }, {}, true);
  console.log(_data)

  ---

  Example Results: For the first example, the result would be the following:

  {
  "result": "0 rows affected."
  }

  For the second example, the result would be the following:

  {
  "result": "3 rows affected."
  }