oracle-cg-lib (Oracle Database library)

1. Introduction

The purpose of this library is execute query's to insert, update, delete or select, the query can be a single operation or operations in bulk. This library is based in the NodeJs library oracledb, it is important mention that this library is only to interact to Oracle databases.

• Prerequisites

  It is recomended to review the check in the next url for install and review prerequisites

2. Library Objects and Methods

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

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

• 2.1. Methods

  The following method is available in the library:

  processOracle: This is the only method for execute single query's or bulk queries.

  The method is asynchronous, then use 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

  These are the following objects and properties available in the library

  • 2.2.1 Objects

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

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

  • 2.2.2 Properties

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

  These properties are contained in 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 with the database.

  • The password property indicates the password of the user that has grants to the database.

  • The query property indicates the SQL exoression to execute at the database, this property can be combined with the optional property content for bulk operations:

  • Example of a query in bulk format, it is required the use of the character : plus name or number to represent the places there the values will be replaced according with the content prositions to process

    INSERT INTO customers (value, value2) VALUES (:1,:2)

    INSERT INTO customers (value, value2) VALUES (:id,:name)

  • Example query to single operations

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

  ---

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

  These are optional properties contained in the object extraProps.

  • The content property indicates the values to process in bulk format allowing to have a SQL expressión in wich the values in the content will replace the corresponding variables values in the SQL expressión. This property can be arrays of array's, array of array of objects, an array with only values, a JSON object and a string with the format of a JSON object or an array.

    • Each value in the JSON object or array that will replace each symbol ? in the query according with the position, so its important mantain the correspondeing order between the values in the content and the variables in the expression.

    • Example of bulk query INSERT INTO customers (value, value2) VALUES (?,?)

    • Examples of content property expressions:

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

    • Example using this query insert INSERT INTO customers (value, value2) VALUES (:val,:val1), this example use names not ? because it is for objects, and the name of properties of the objects must be same for the query.

    • Examples of differen content property expressions:

      "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 content property can be one of following encoding: ascii, base64, base64url, binary, latin1, utf-8, utf8.

  • The limit property indicates the maxim number to arrays or objects inside the content property to be processed in batch format, by default it is 10000.
  • The port property indicates the port expose to connect with the database by default is 1521.
  • The path_client property indicates the directory that contains the files of the oracle client, this property is required if it isn't installed the oracle client in the local machine where the library will run by default value is null.

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. processOracle

   • 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 show the configuration of a sigle querey using the objectDb and extraProps objects, the content property is not required.

  let properties = {
  	...objectDb
  };
  properties.database = 'mydatabase';
  properties.host = 'myhost.com';//or will be IP
  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 processOracle({
  	data: properties
  }, {}, true);
  console.log(_data);

  ---

  In this second example also shows a single query configuration without the use of the objects described above, only setting 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: In both examples above the result will 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 property content as a base64

  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 processOracle({
  	data: properties
  }, {}, true);
  console.log(_data);

  • Example using property content 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 (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12)";
  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 processOracle({
  	data: properties
  }, {}, true);
  console.log(_data);

  • Example using content property with one data array to be inserted and having a combination of fixed values and variables in the SQL expression

  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',:1,'Carine ','?', :2, null,'Nantes',null,'44000',:3,1370,21000.00)";
  let optsP = {
  	...extraProps
  };
  optsP.content: [
      [
          "Atelier graphiquefffff",
          "40.32.2555",
          "54, rue Royale"
      ]
  ];
  properties = {
  	...properties,
  	...optsP
  };
  const _data = await processOracle({
  	data: properties
  }, {}, true);
  console.log(_data);

  • Example without using content property

  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 ','?', 000,null,'Nantes',null,'44000','ppp',1370,21000.00)";
  const _data = await processOracle({
  	data: properties
  }, {}, true);
  console.log(_data);

  • Example to delete data using 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 = :id";
  let optsP = {
  	...extraProps
  };
  optsP.content: [
      [375853],
      [375852],
      [375851],
      [375850],
      [375849],
      [375848],
      [375847],
      [375846],
      [375845],
      [375844]
  ];
  properties = {
  	...properties,
  	...optsP
  };
  const _data = await processOracle({
  	data: properties
  }, {}, true);
  console.log(_data);

  • Example using content property content with an array object

  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 processOracle({
  	data: properties
  }, {}, true);
  console.log(_data);

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

For both types, single and bulk operations the result when it is used INSERT, DELETE, UPDATE sentences will be as follows: {rowsAffected:10000}

In the case of SELECT sentences the result always be an object array or an object as follows {rows:[]}

If any error occurres during the execution the result will be as follows:

 {
  Error: ORA - 02291: integrity constraint(HR.CHILDTAB_FK) violated - parent key not found errorNum: 2291,offset: 6
 }
 Batch size exceeds the limit,
 Error executing query,
 Error missing property,
 Error with the property