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';
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',
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';
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';
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';
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';
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';
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';
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