mmsql-cg-lib (Mysql-MariaDb library)

1. Introduction

The purpose of this library is execute query's to insert, update, delete or select data from a MariaDB/MySQL database. The query can be a single operation or bulk operations, in this library it is used the library mariadb, it is important to mention that this library applies only for Mysql and MariaDb databases and this library is based in Nodejs languaje.

2. Library Objects and Methods

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

npm install mmsql-cg-lib, npm i mmsql-cg-lib or yarn install mmsql-cg-lib

• 2.1. Methods

  The following method is available in the library:

  processQuery: This is the only one method for execute single or bulk 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

    • objectDb: this object contains the required properties for the method processQuery
    • extraProps: this object contains the optional properties for the method processQuery

  • 2.2.2 Properties

    {
        database: null,
        host: null,
        password: null,
        query: null,
        user: null
    }

  These properties are contained into the object objectDb.

  • The database property indicates the name of the database.
  • The host property indicates name of the host or IP to connect with the database.
  • 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 query property indicates the SQL expression to be executed in the database (SELECT, INSERT, UPDATE, DELETE), this property is related with the optional property "content" for bulk operations:
    • Example of query to be executed in a bulk operation, it is required to use the ? character or give variable names to be replaced for each value in the query to be processed.

      INSERT INTO customers (value, value2) VALUES (?,?)

    • Example of a single query operations

      INSERT INTO customers (value, value2) VALUES ('To first column','To second column')

  ---

  {
      content: null,
      limit: 10000,
      port: 3306
  }

  These are optional properties contained in the object extraProps.

  • The limit property indicates the maxim number to arrays or objects inside the property "content" to be processed in bulk, by default it is set to 10000 but can be modified it.

  • The port property indicates the port to connect to the database, by default it is set to 3306.

  • The content property indicates the data that will be processed in bulk 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 sigle array (only values inside), an JSON object and an array of JSON objects.

    • Each value in a JSON object or an array will replace each ? character in the query according with the position, so its important to keep the order of the values in the content property.

    • Example of an insert query using ? in the expression: INSERT INTO customers (value, value2) VALUES (?,?)

    • Examples of content property for this case:

      "content":["Ford", "BMW"];//array
      "content":[["Ford", "BMW"],["Mustang", "Honda"]];//arrays of arrays
      "content":"W1siRm9yZCIsICJCTVciXSxbIk11c3RhbmciLCAiSG9uZGEiXV0=";//base64
      "content":"[\"Ford\",\"BMW\"]";//string format

    The content values will replace the ?. In the first case it'll be one execution replacing the two ? characters with the values (in the corresponding order). In the second case, the query will be executed two times with the corresponding replacement. The next examples are the same but expressed in different ways, one in base64 format and the other in a string format.

    • Example of an insert query using variables in the expression: INSERT INTO customers (value, value2) VALUES (:val,:val1), this example uses variables names instead of ? because objects will be used instead of an array, and the variable name must correspond with the key values of the properties in the object.

    • Examples of content property for this case:

      "content":[{val:"Ford", val1:"BMW"}];//array
      "content":[{val:"Ford", val1:"BMW"},{val:"Mustang", val1:"Honda"}];//arrays of objects
      "content":"W3t2YWw6IkZvcmQiLCB2YWwxOiJCTVcifV0=";//base64
      "content":"[{val:\"Ford\",val1:\"BMW\"},{val:\"Mustang\",val1:\"Honda\"}]";//string format

      The library allows to process the "content" property in the following encodings: ascii, base64, base64url, binary, latin1, utf-8, utf8.

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 objectDb and, if required, the extraProps object to send and replace the default values.

   • Examples

   In this first example the information is set using the objectDb and extraProps objects, in this scenario is not required the "content" property.

   let properties = {
   	...objectDb
   };
   properties.database = 'mydatabase';
   properties.host = 'myhost.com';//or the IP address
   properties.user = 'myuser';
   properties.password='mypassword';
   properties.query = 'select * from customers where id > 1500';
   let optsP = {
   	...extraProps
   };
   optsP.port = '5580';
   properties = {
   	...properties,
   	...optsP
   };
   const _data = await processQuery({
   	data: properties
   }, {}, true);
   console.log(_data);

   ---

   In this second example the information is set directly in the properties:

   let properties = {
      database:'mydatabase',
      host:'myhost.com',//or will be IP
      user:'myuser',
      password:'mypassword';
      query:'select * from customers where id > 1500',
   };
   properties.content = fs.readFileSync(tempFilePath, {
      encoding: 'base64'
   });
   const _data = await processQuery({
      data: properties
   }, {}, true);
   console.log(_data)

   Resultant sample: For both examples above, the result should be the same array:

   [
     {
       "customerNumber": 1501,
       "customerName": "Emmet",
       "contactLastName": "acceptable",
       "contactFirstName": "acceptable",
       "phone": "11.12.1973",
       "addressLine1": "Comprehensive St 2241, Hobucken, Benin, 597151",
       "addressLine2": "Comprehensive St 2241, Hobucken, Benin, 597151",
       "city": "Partridge",
       "state": "Qatar",
       "postalCode": "1661",
       "country": "Qatar",
       "salesRepEmployeeNumber": 1216,
       "creditLimit": 1661.00
     },
     {
       "customerNumber": 1502,
       "customerName": "Ezequiel",
       "contactLastName": "disco",
       "contactFirstName": "disco",
       "phone": "26.17.2121",
       "addressLine1": "Shareware Street 6477, Woodlake, Albania, 397153",
       "addressLine2": "Shareware Street 6477, Woodlake, Albania, 397153",
       "city": "Minot Afb",
       "state": "Brunei Darussalam",
       "postalCode": "9695",
       "country": "Brunei Darussalam",
       "salesRepEmployeeNumber": 1216,
       "creditLimit": 9695.00
     }
   ]

   Advanced examples:

   • Example using the "content" property in a base64 format:

   let properties = {
   	...objectDb
   };
   properties.database = 'mydatabase';
   properties.host = 'myhost.com';//or will be IP
   properties.user = 'myuser';
   properties.password='mypassword';
   properties.query: "INSERT INTO customers (customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country, salesRepEmployeeNumber, creditLimit) VALUES (:customerName,:contactLastName,:contactFirstName,:phone,:addressLine1,:addressLine2,:city,:state,:postalCode,:country,:salesRepEmployeeNumber,:creditLimit)";
   let optsP = {
   	...extraProps
   };
   optsP.content: "WwogIHsKICAgICJjdXN0b21lck51bWJlciI6IDcyNTg3LAogICAgImN1c3RvbWVyTmFtZSI6ICJEYXZpIiwKICAgICJjb250YWN0TGFzdE5hbWUiOiAicmFkaWF0aW9uIiwKICAgICJjb250YWN0Rmlyc3ROYW1lIjogInJhZGlhdGlvbiIsCiAgICAicGhvbmUiOiAiMTEuMTEuMjExOCIsCiAgICAiYWRkcmVzc0xpbmUxIjogIkxvb2tlZCBTdHJlZXQgMzExMiwgIiwKICAgICJhZGRyZXNzTGluZTIiOiAiTG9va2VkIFN0cmVldCAzMTEyLCBXaGl0ZSBPYWssIEdlcm1hbnksIDg4NTkxOCIsCiAgICAiY2l0eSI6ICJQYXZsb2RhciIsCiAgICAic3RhdGUiOiAiQW5nb2xhIiwKICAgICJwb3N0YWxDb2RlIjogIjE3MTQiLAogICAgImNvdW50cnkiOiAiQW5nb2xhIiwKICAgICJzYWxlc1JlcEVtcGxveWVlTnVtYmVyIjogMTIxNiwKICAgICJjcmVkaXRMaW1pdCI6IDE3MTQuMDAKICB9LAogIHsKICAgICJjdXN0b21lck51bWJlciI6IDcyNTg2LAogICAgImN1c3RvbWVyTmFtZSI6ICJLZWxseSdzR2lmdFNob3AiLAogICAgImNvbnRhY3RMYXN0TmFtZSI6ICJTbm93ZGVuIiwKICAgICJjb250YWN0Rmlyc3ROYW1lIjogIlRvbnkiLAogICAgInBob25lIjogIis2NDk1NTU1NTAwIiwKICAgICJhZGRyZXNzTGluZTEiOiAiQXJlbmFsZXMxOTM4MydBJyIsCiAgICAiYWRkcmVzc0xpbmUyIjogbnVsbCwKICAgICJjaXR5IjogIkF1Y2tsYW5kIiwKICAgICJzdGF0ZSI6IG51bGwsCiAgICAicG9zdGFsQ29kZSI6IG51bGwsCiAgICAiY291bnRyeSI6ICJOZXdaZWFsYW5kIiwKICAgICJzYWxlc1JlcEVtcGxveWVlTnVtYmVyIjogMTYxMiwKICAgICJjcmVkaXRMaW1pdCI6IDExMDAwMC4wMAogIH0KXQ=="
   properties = {
   	...properties,
   	...optsP
   };
   const _data = await processQuery({
   	data: properties
   }, {}, true);
   console.log(_data);

   • Example using the "content" property with one array:

   let properties = {
   	...objectDb
   };
   properties.database = 'mydatabase';
   properties.host = 'myhost.com';//or will be IP
   properties.user = 'myuser';
   properties.password='mypassword';
   properties.query: "INSERT INTO customers (customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country, salesRepEmployeeNumber, creditLimit) VALUES (:customerName,:contactLastName,:contactFirstName,:phone,:addressLine1,:addressLine2,:city,:state,:postalCode,:country,:salesRepEmployeeNumber,:creditLimit)";
   let optsP = {
   	...extraProps
   };
   optsP.content: [
       "Atelier graphique",
       "Schmitt",
       "Carine ",
       "40.32.2555",
       "54, rue Royale",
       null,
       "Nantes",
       null,
       "44000",
       "France",
       1370,
       21000.00
   ];
   properties = {
   	...properties,
   	...optsP
   };
   const _data = await processQuery({
   	data: properties
   }, {}, true);
   console.log(_data);

   • Example using the "content" property with one array to insert a combination of fixed values and values set from variables that will be replaced from the values in the content:

   let properties = {
   	...objectDb
   };
   properties.database = 'mydatabase';
   properties.host = 'myhost.com';//or will be IP
   properties.user = 'myuser';
   properties.password='mypassword';
   properties.query: "INSERT INTO customers (customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country, salesRepEmployeeNumber, creditLimit) VALUES ('Atelier graphiqueppuiuiuiui',?,'Carine ','?', ?, null,'Nantes',null,'44000',?,1370,21000.00)";
   let optsP = {
   	...extraProps
   };
   optsP.content: [
       [
           "Atelier graphiquefffff",
           "40.32.2555",
           "54, rue Royale"
       ]
   ];
   optsP.port = '5580';
   properties = {
   	...properties,
   	...optsP
   };
   const _data = await processQuery({
   	data: properties
   }, {}, true);
   console.log(_data);

   • Example without the "content" property (sigle operation):

   let properties = {
   	...objectDb
   };
   properties.database = 'mydatabase';
   properties.host = 'myhost.com';//or will be IP
   properties.user = 'myuser';
   properties.password='mypassword';
   properties.query: "INSERT INTO customers (customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country, salesRepEmployeeNumber, creditLimit) VALUES ('Atelier graphiqueppuiuiuiui','15','Carine ','5533441122', 000,null,'Nantes',null,'44000','ppp',1370,21000.00)";
   const _data = await processQuery({
   	data: properties
   }, {}, true);
   console.log(_data);

   • Example to delete data using the "content" property:

   let properties = {
   	...objectDb
   };
   properties.database = 'mydatabase';
   properties.host = 'myhost.com';//or will be IP
   properties.user = 'myuser';
   properties.password='mypassword';
   properties.query: "DELETE FROM  customers WHERE customerNumber = ?";
   let optsP = {
   	...extraProps
   };
   optsP.content: [
       [375853],
       [375852],
       [375851],
       [375850],
       [375849],
       [375848],
       [375847],
       [375846],
       [375845],
       [375844]
   ];
   properties = {
   	...properties,
   	...optsP
   };
   const _data = await processQuery({
   	data: properties
   }, {}, true);
   console.log(_data);

   • Example using the "content" property with objects array:

   let properties = {
   	...objectDb
   };
   properties.database = 'mydatabase';
   properties.host = 'myhost.com';//or will be IP
   properties.user = 'myuser';
   properties.password='mypassword';
   properties.query: "INSERT INTO customers (customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country, salesRepEmployeeNumber, creditLimit) VALUES (:customerName,:contactLastName,:contactFirstName,:phone,:addressLine1,:addressLine2,:city,:state,:postalCode,:country,:salesRepEmployeeNumber,:creditLimit)";
   let optsP = {
   	...extraProps
   };
   optsP.content: [
     {
       "customerNumber": 1501,
       "customerName": "Emmet",
       "contactLastName": "acceptable",
       "contactFirstName": "acceptable",
       "phone": "11.12.1973",
       "addressLine1": "Comprehensive St 2241, Hobucken, Benin, 597151",
       "addressLine2": "Comprehensive St 2241, Hobucken, Benin, 597151",
       "city": "Partridge",
       "state": "Qatar",
       "postalCode": "1661",
       "country": "Qatar",
       "salesRepEmployeeNumber": 1216,
       "creditLimit": 1661.00
     },
     {
       "customerNumber": 1502,
       "customerName": "Ezequiel",
       "contactLastName": "disco",
       "contactFirstName": "disco",
       "phone": "26.17.2121",
       "addressLine1": "Shareware Street 6477, Woodlake, Albania, 397153",
       "addressLine2": "Shareware Street 6477, Woodlake, Albania, 397153",
       "city": "Minot Afb",
       "state": "Brunei Darussalam",
       "postalCode": "9695",
       "country": "Brunei Darussalam",
       "salesRepEmployeeNumber": 1216,
       "creditLimit": 9695.00
     }
   ]
   properties = {
   	...properties,
   	...optsP
   };
   const _data = await processQuery({
   	data: properties
   }, {}, true);
   console.log(_data);

   If it is required to send more than ten thousand records to the bulk operations the limit property must be set with the correct value, for example: "limit":20000.

   For the single or bulk operations the result for the sentences INSERT, DELETE, UPDATE will be as follows: {affectedRows:10000, insertId:10001}.

   In the case of the SELECT sentence the result always be an object array corresponding with the rows in the resultset.

   If any error occurrs the result should look like as follows:

   Batch size exceeds the limit,
   Error executing query,
   Error missing property,
   Error with the property