Inserting and Retrieving records in to database(sql) using IBM BPM

In this blog I will show you how we can insert a record in to sql table and also retrieving the record from table.

Inserting data into database :

First we need to start with creating the service. Here we need to create Integration service.The values which we need to insert in to table will be obtained as an input to service.
input

Here I have only single complex object as input to service, but we can even have multiple input variables as input.Coming to the diagram , first the sql query needs to be written. For that I have used server scriptlet and I’ve binded that to sql variable. So that we can use that for bindings later.

sqlquery.png

Here I have used internal DB so there is no need of any schema for CRUD operations . But if we are connecting to external sql server we need to have db schema.

NOTE :  As a good practice DB schema needs to created as an epv variable and can be referenced in the service. So that any change in the schema details needs updation just at one place.

<# var schema = String(tw.epv.MHV_CONSTANT.INFLIGHT_DB_SCHEMA_NAME) + "." + String(tw.epv.MHV_CONSTANT.INFLIGHT_DB_GROUP); #>

INSERT INTO <#= schema #>.H_EmployeeDetails (*,*,*) VALUES (?,?,?)

Then we need  to initialize parameters as List of  SQL Parameter type as we need to pass that as input to SQL Execute Statement nested service.Then in this parameters we need to have initialization for all the values that we pass in our SQL query.

tw.local.parameters = new tw.object.listOf.SQLParameter();

tw.local.parameters[0] = new tw.object.SQLParameter();
tw.local.parameters[0].value = tw.local.employeeDetails.employeeId;
tw.local.parameters[0].mode = "IN";

tw.local.parameters[1] = new tw.object.SQLParameter();
tw.local.parameters[1].value = tw.local.employeeDetails.name;
tw.local.parameters[1].mode = "IN";

tw.local.parameters[2] = new tw.object.SQLParameter();
tw.local.parameters[2].value = tw.local.employeeDetails.place;
tw.local.parameters[2].mode = "IN";

setparams

Then we need to have the SQL Execute Statement Nested service. Then  we need to do the necessary bindings

binding

Now the service is ready for the insertion of data in to sql table.

Retrieving data from database :

Coming to retrieving data from table, It is similar to insertion but we need to mention the returnType , how we are expecting the result. It may be some business object type, XMLElement, NameValuePair etc., If it is other than business object type then we need to parse that response before sending that as output. I will show both the ways

  1. If return type is set as business Object type then results and output variable should also be of same business object type.retrieveinput.png

Initializing the params is same.We need to initialize the input variable here

tw.local.parameters = new tw.object.listOf.SQLParameter();

tw.local.parameters[0] = new tw.object.SQLParameter();
tw.local.parameters[0].value = tw.local.empId;
tw.local.parameters[0].mode = "IN";

bomapping

So we will have the response in results variable. Then you need to assign that to your output variable.It can done in the Post-execution Assignment.

tw.local.employeeData = tw.local.results[0]

2.If the return type is of type XMLElement , please check here

Please do comment if you have any questions . Hope this helps someone and have a good day!
Advertisements

Script to Parse XML in IBM BPM

We use XML parsing a lot during database calls. For retrieving data from a table it may return result of type “XMLElement”. In such cases we need to parse that in to an business object . Here I will show the same.

In my retrieve data integration service Im expecting result of type EmployeeDetails

Then in the Sql Excute nested service the return type is “XMLElement”

This above will return result of below type

This needs to be parsed as Business object to be sent as output

 

Parsing Script

if(tw.local.results != null && tw.local.results.childNodes && tw.local.results.childNodes.length > 0 ){
    var firstelement = tw.local.results.childNodes.item(0);
     tw.local.employeeData = new tw.object.EmployeeDetails();
     tw.local.employeeData.employeeId = firstelement.xpath("record/column[@name='EMPID']")[0].getText();
     tw.local.employeeData.name = firstelement.xpath("record/column[@name='EMPLOYEENAME']")[0].getText();
     tw.local.employeeData.place = firstelement.xpath("record/column[@name='PLACE']")[0].getText();
     tw.local.employeeData.phone = firstelement.xpath("record/column[@name='PHONE']")[0].getText();
     tw.local.employeeData.designation = firstelement.xpath("record/column[@name='DESIGNATION']")[0].getText();
     tw.local.employeeData.department = firstelement.xpath("record/column[@name='DEPARTMENT']")[0].getText();
     tw.local.employeeData.qualification = firstelement.xpath("record/column[@name='QUALIFICATION']")[0].getText();
     tw.local.employeeData.address = firstelement.xpath("record/column[@name='ADDRESS']")[0].getText();
}

This results in below output variable

 Same way we can parse for other return types such as NameValuePair etc.

Please do comment if you have any questions . Hope this helps someone and have a good day!

Auto Conversion of BPM TwObject to Json and Json to TwObject

Mostly in any development project we are using json for the data interchanging. Let it be with in the application or calling any external application. It is no difference in BPM .If there is any need to call external system it would be easy to use json rather than Tw objects,XML and vice versa for external systems to call BPM.

If you are using BPM 8.6 version there is an JSAPI  available for conversion of TWObject to JSON

Check Here

toJSONString( boolean formatted) Returns the JSON representation of the object

tw.local.json = tw.local.data.toJSONString(true);

But this api may not work for the lower BPM versions. Moreover there is no JSAPI available to convert JSON object to TWObject.

To solve this problem we can use BPM JSON helper toolkit to convert TWObjects to JSON and JSON to TwObjects and it is based on json2.js To keep our Process App size down,instead of entire helper kit we can just add 2 files to server as dependencies.

json2.js

BPM-JSON-Utils.js
Can be downloaded here : Git Download

server

Once we add these files now we can easily do what we required.

To convert TwObject to Json we need to call function convertTwToJSON(object) which is in Utils.js file

tw.local.json = BPMJSON.convertTwToJSON(tw.local.data);

Similarly to convert JSON to Twobject we use the below function

tw.local.data = BPMJSON.convertJSONToTw(tw.local.json);

Below is one sample example:

Initially I’m creating two integration services

service1

Here json variable is the output variable and data is the input variable to that service

service2

Here data is the output variable and json is the input variable to this service.
Then Ive created a simple human service so that I will call these services.

coach

Human service Diagram

 

coachoutput

Human service output

Here on click of convert to json the , JSONConvertor service is called and it returned the json. Same way we can use the json and convert to UI and directly bind that to UI.

NOTE : The point to be noted while converting json to Twobject , the output Tw object should exactly match with json structure or else it will throw an error.

This way we can easily work with Json even for complex business objects in bpm.

Hope it helps someone !

Connecting to MongoDB service on bluemix using Node JS application and inserting data

In this blog,I will show you how to connect to Compose for MongoDB service on bluemix from our Node js application and then how to insert some data in to a collection.
First thing is that we need to create a new Compose for Mongo DB service On Bluemix.

mongodb.PNG

In the credentials you will have uri which is important and you need only that to connect from your node application

uri

Once you have  these you can start with your Node js application.
You require mongodb npm package to connect to this.So you need to import this module other than the default modules.
We need to use MongoClient constructor to connect to mongodb

var express = require('express');
var router = express.Router();
var bodyParser = require('body-parser');
var MongoClient = require('mongodb').MongoClient;
var collectionName = "";
var options = {
  mongos: {
    ssl: true,
    sslValidate: false,
  }
}
var mongo=process.env.VCAP_SERVICES;
var port = process.env.PORT || 3030;
router.use(bodyParser.json());
var conn_str = "";
var mongoDbConn;

As I told earlier ,we need mongo uri to connect.We can directly hardcode that in our code or else we can bind a custom provided service(cups) to your bluemix application and access that value using VCAP_SERVICES.Second option is the best one as dont need any hardcoding.
So here we need to bind that service to our application on bluemix. In above code mongo is directly accessing that service. Now we need to capture the uri value from that and connect to mongo using the MongoClient which is done below

if (mongo) {
  var env = JSON.parse(mongo);
  if (env['user-provided']) {
    mongo = env['user-provided'][0]['credentials'];
    console.log(mongo);
    if (mongo.uri) {
      conn_str = mongo.uri;

      MongoClient.connect(conn_str, options, function (err, db) {
        if (err) {
          res.send("Mongo DB with conn ${conn_str} has error ${err}");
        } else {
          mongoDbConn = db;
        }
      });

    } else {
      console.log("No mongo found");
    }
  } else {
    console.log('No mongo found');
  }
} else {
  console.log('No mongo found');
}

You can also directly hardcode your value for mongo.uri value if required.
So once you have your mongo connection,you don’t need to connect every time when you need to add some data or insert anything using rest call .You just need to use mongoDbConn .
In below example I will show you a POST call which will insert data in to a collection using mongoDbConn

router.post('/insertnewevent', function (req, res) {
  var eventdetails = req.body;
  var data = { "customer": "harish", "transactionID":"3405123","transdate":"11/06/17"};
  
  var collectionName = "logdetails";

  if (mongoDbConn) {
    // list messages
    mongoDbConn.collection(collectionName).insert(data, function (err, cursor) {
      if (err) {
        res.send('mongodb message list failed');
      } else {
        res.send("Inserted the event successfully and reference ID is " + data._id);
      }
    });
  }
});

So this way can easily connect to your MongoDB on bluemix service from your node js application.
Note:You can combine all the 3 code snippets in order and it will work.
If you face any issues in connecting you can comment here.
Hope it helps someone!

Creating new database and user in mongodb on bluemix using Mongo CLI on windows

One we have Compose for Mongodb service on bluemix we want to create different users and add the permission levels according to database.Creating users directly from bluemix GUI is not possible.We need to use Mongo CLI for this.
On MAC,it is quite easy but on Windows it is difficult and we need to install SSL enabled mongodb to connect to mongodb on bluemix.

The software can be downloaded from the following link

Once the software is installed , open CLI and go to folder where mongodb is installed.
Then to connect to mongodb we need to paste uri_cli which is the ssl uri which u will find in credentials

mongossl.PNG

Then you will be connected to mongodb service on bluemix

mongocoonected

Now to see the available databases ,

showdbs

To create a new database
newdb.png
To create a new user and assign a role to the user use the following command

db.createUser(
  {
    user: "UserName",
    pwd: "Password",
    roles: [
       
       { role: "readWrite", db: "testdb" }
    ]
  }
);

usercreate

This way we can easily create user and assign permissions.To list the users use command >show users

Hope it helps someone!

Setup and Commands for Uploading/Downloading files from Objectstorage in Windows using Swift CLI

On Mac it is quite simple to upload and download files from object storage in bulk ,but on windows it is quite difficult since we dont have any available documentation for this.
I use Windows 8 and once I need to do a backup of all the files from object storage container .There are around 5000 pdf files and there is no option where we can directly download all these in a single go from object storage.We need to select one by one.
So then I thought of using Swift CLI and after long struggle I succeeded on windows.So hope this helps someone.

First we need to have Swift CLI set up on your local machine.
For that we need to install Python,PIP,Swift client and keystone client.

First we need to set up virtualenv since on windows it doesnt support the openstack version 3 and Object storage is based on V3 .
But fortunately, python provides a way to install various version of command via “virtualenv” mechanism. It is possible to run various version of python in a virtual environment. And surely it worked for Bluemix Object Storage service too.

First we need to download python on your local machine and then install it and set the path

For window python installation you can refer this

https://github.com/BurntSushi/nfldb/wiki/Python-&-pip-Windows-installation

Then we need to install pip. Even for that you can refer the above link.

Then open cmd in the browse to the folder where you have pip installed and then u need to install virtualenv

>pip install virtualenv

>virtualenv virt

Then go to virt folder location

>cd virt

Capture1

Then install python swift client,python-keystoneclient and ndg-httpsclient module.

>pip install python-swiftclient

>pip  install python-keystoneclient

>pip install –upgrade ndg-httpsclient

Capture2

Capture3

Environment is set now and we can use the swift commands.

Firsy we need to  set the environment variables to access the object storage which you can get those from object storage service credentials

osmask

The values like <user-id>, <password> and <project-id> can be obtained from Bluemix Object Storage service UI. Once the details are correct u are authenticated and connected to object storage.

Then we can see the containers list using the following command

swift list

Capture6

Below are the Swift commands for downloading and uploading

Download a particular file from container

>swift download <container-name> <file-name>

 

Download all the files with in a container

>swift download <container-name> .

 

Uploading file in to a container

Go to the folder location where you have these files

>swift upload <container-name> <filename>

 

Uploading all the files in a folder to a container

>swift upload <container-name>

 

 

 

Uploading a file in to object storage container using node js application

In the earlier blog I’ve showed  how to generate authentication token for our object storage .In this blog I will show how to upload file in to object storage using that token.
So once we have our token we can use that to store files in the container.
In this blog I will create an api , using which we can upload a file in to object storage container.
For uploading any file through api , it is better that the file data should be passed to api as base64.Then it should be decoded and stored in object storage.

For uploading a file to objectstorage an api is provided which is,

API_ENDPOINT + ‘/’ + pdf_container_name + ‘/’ + pdfFileName
METHOD : PUT
headers: {


‘X-Auth-Token’: token,


‘Content-Type’: ”

}
Here API_ENDPOINT is mentioned in the previous blog

Here token is the one which we generated and Content-Type depends on the file type.

First we will create a function which would accept the base64 data and the name with which it should be stored in object storage(eg: document.pdf)

function storepdf(basedata, pdfName) {
    var filename = pdfName;
    var promise = new Promise(function(resolve, reject) {
        var pdf_container_name = "accountdocs"; //containername in objectstorage
        var bufferString = basedata; 
        var pdfFileName = filename;              
        getAuthToken().then(function(token) {
            getStringAsStream(bufferString).pipe(Base64.decode()).pipe(request({
                url: API_ENDPOINT + '/' + pdf_container_name + '/' + pdfFileName,
                method: 'put',
                headers: {
                    'X-Auth-Token': token,
                    'Content-Type': 'application/pdf'
                }
            }, function(error, response, body) {
                if (!error && response.statusCode == 201) {
                    return (pdfFileName);
                } else {
                    return (error);
                }
            }));
        });
    });
}

We are passing the base64 data as Stream and then we are decoding the base64 data and pipe() that data to our request.
Then in our main api we will call the above function as below :

app.post('/storepdf', function(req, res) {
    var data = req.body;
    var basedata = data.base64;
    var filename = data.filename;
    var fileUploadtime = (new Date).getTime();
    var file = filename.split(".")[0] + "_" + (new Date).getTime() + "." + filename.split(".")[1];
    storepdf(basedata, file);
    res.send(file);
});

Here we are appending timestamp to the filename ,storing the file in object storage and then we are sending that as response to this rest call.

Then we call use this api at our client side and server side and then upload your document.

Hope it helps someone.