Tutorial: creating application to read/write customer data in DB over HTTP using Flink-ESB Editor (part 3)

Flink-ESB Tutorials

Use case: create "customer-api" application to store/retrieve data in DB over HTTP (part 3)

Short description: Create Flink-ESB application offering 4 HTTP services to read / insert / update / delete customer data in the database.

Customer data is stored in the database in one table, called CUSTOMER.

Part 3: add a service to update data in DB using HTTP PUT request

In the third part of the tutorial we will:

create a route called "update-customer" to update data in the database >>

set up a Validate component within a route to parse incoming XML against schema file and to check presence of numeric "id" message property >>

set up a Data writer component within a route to run UPDATE SQL statement, injecting values of bind variables from incoming XML message using "xpath" expression and from message property "id" >>

test a route in Flink-ESB Admin Console >>

set up an Exclusive Gateway to check status of DB update, and either send a message to "Transformer" (if data was updated), or to "Throw Exception" component >>

set up a Throw Exception component within a route to raise an exception in case no data in DB is found >>

copy request XML into some message property to store the content of request payload >>

set up a Transformer component within a route to output data in XML format >>

set up a Catch Exception and a Transformer components within a route to create an error handler outputting data in custom format >>

create a route, handling HTTP POST request and calling another route, which inserts data into the database >>

set up a REST Receiver starting component within a route to start route on every incoming HTTP PUT request >>

set up a Call Subflow component within a route to call "update-customer" subflow >>

test HTTP PUT service >>

add "Content-Type" HTTP header to HTTP PUT response >>

Following Flink-ESB components are used in this part of tutorial:

First level components:

Route : represents a sequence of activities, performed upon the message. Route contains route node components connected with links. Route node activities and links define the functionality of the route. Route can either have a starting event (received HTTP request, timer etc.), or it can be called from other routes (in this case such route is called subflow).

Route nodes:

Subflow : starting route node, used to mark beginning of the subflow. Subflow is kind of route, which does not have its own trigger (HTTP request, timer etc.). Every route, that does not have a specific starting event, must start with this component.

Validate : route node, used to validate a message. Can parse XML against schema file, and/or validate against one or multiple custom rules.

Transformer : used to transform a message in different format.

Data writer : used to run INSERT, UPDATE or DELETE SQL statements in the database. Must refer to "DB Connection Pool".

Throw Exception : used within a route to throw a custom exception. It is always the last route node component within a sequence flow. It does not have outgoing connection links.

Catch Exception : used within a route to create a separate processing flow, which is triggered in case exception is thrown.

REST Receiver : starting route node, used always at the begin of the route as starting event. Must refer to "HTTP Server". Triggers after receiving HTTP request with configured HTTP method + URI

Call Subflow : used to call a subflow (another route, which has a "Subflow" component as a starting event).

Step 1: creating a route to update data in the database

Go to Flink-ESB Editor. After finishing part 2 of the tutorial you should have an application looking similar to the one on picture below:

You should have a DB connection pool, HTTP server, HTTP connector and four routes with names "get-customer" "insert-customer", "http-get-customer" and "http-insert-customer".

Click on "ESB General" palette drawer to expand it, and place a new "Route" on editor canvas.

Give a route name "update-customer":

Place following components from left to right onto the route: "Subflow" and "Validate" from "ESB General" palette, "Data writer" from "Database Adapter" palette. Connect them as on picture below:

This service is going to use message property "id" (as in "get-customer") to identify CUSTID we want to update, and also an XML message, containing data (as in "insert-customer"). So let us configure "Validate" component to check the presence of integer positive "id" message property, and parse incoming XML against schema file. We will use the same schema file, as in "insert-customer" route:

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<xsd:element name="customer" type="customerType"/>
	<xsd:complexType name="customerType">
		<xsd:all>
			<xsd:element name="name" type="xsd:string"/>
			<xsd:element name="city" type="xsd:string"/>
			<xsd:element name="zip" type="xsd:string"/>
		</xsd:all>
	</xsd:complexType>
</xsd:schema>

Select "Validate" component in the Editor and provide its properties as on picture below:

Press "Save Changes", and click on "Rules" tab. Click "Add Rule" and type property['id']!=null in "Rule-Expression" field. Click "Add Rule" again and type the second expression: Integer.valueOf(property['id'])!=null. Click "Add Rule" again and provide the third expression: property['id']>0:

Press "Save Changes". Select "Data writer" and enter its properties as below: "DB-Connection-Ref" : db, "SQL" : 'update customer set name=?, city=?, zip=? where custid=?':

Click "Save Changes", then click "Bind Variables" tab, press "Add Parameter" button and enter data: "Param-Type" : VARCHAR, "Param-Value" : xpath(payload,'//name').

Press "Add Parameter" button again, and add the second bind variable: "Param-Type" : VARCHAR, "Param-Value" : xpath(payload,'//city').

Press "Add Parameter" button again, and add the third bind variable: "Param-Type" : VARCHAR, "Param-Value" : xpath(payload,'//zip').

Press "Add Parameter" button again, and add the fourth bind variable: "Param-Type" : NUMERIC, "Param-Value" : property['id'].

Bind variables should look like on picture below:

Click "Save Changes", save the file and restart the application.

Step 2: testing "update-customer" route in Flink-ESB Admin Console

Go to browser any type following URL: http://localhost:4545/customer?id=2. You should get a response from "http-get-customer" route as on picture below:

No go to Flink-ESB Admin Console in the browser, use for this following URL: https://localhost:8443/console/start. Login to Flink-ESB Admin Console with default username/password: admin/admin. Click on "Application Overview" to expand it, then click on triangle on the left side of instance name. Now you should see an application with name "customer-api". Click on the small triangle to expand components of the project. Click on "Routes" to expand it. Now you should see a route with name "update-customer". Click on it to see its details on the right side of Admin Console:

Click "Call Route" tab. Paste following message into "Payload" textbox:

'<customer>
	<name>Lucky Luke</name>
	<city>Cloud City</city>
	<zip>54321</zip>
</customer>'

Enter following expression in "Properties" textbox: [ 'id' : 2 ].

Press "Call" button. You should get response, as on picture below:

Now try again URL: http://localhost:4545/customer?id=2. You should see now updated customer data:

Now let us try calling "update-customer" for some non-existing CUSTID, for example: 1000:

Now try calling "http-get-customer" for CUSTID=1000 calling this URL: http://localhost:4545/customer?id=1000. You should get following response:

Apparently we have a problem here: "update-customer" for CUSTID=1000 have not raised any error, XML message with customer data was returned, but service "http-get-customer" cannot find that customer with CUSTID=1000. So what could be wrong?

Check again the response from "update-customer" service. Message property "db.update.count" has value 0. This means, no data in DB was updated. Which is correct, since non-existing CUSTID=1000 was used. Then why did it return an XML message with customer data?

Actually, it has just passed input XML to the output, because "Data writer" component does not modify message payload. It just sets "db.update.customer" message property. So it is up to you to have a right handler for possible situations.

Step 3: modifying "update-customer" to output XML in case data was updated, or to raise an exception

Go to Editor. Add "Exclusive Gateway" and two "Transformer" components from "Mediation" palette, now add "Throw Exception" and "Catch Exception" from "ESB General" palette to the route. Place and connect all route nodes as on the picture below:

Select connection link between "Exclusive Gateway" and "Throw Exception", select "gateway Condition" tab in properties window, unselect "Default condition" checkbox, and type following into the textbox: property['db.update.count']==0

Press "Save changes". See, how the shape of connection link changes: a small diamond should appear, which means, there is a condition for passing this link.

Select "Throw Exception" component and type following expression in "Message" textbox: 'No customer found for id='+property['id']:

Press "Save changes".

Now select a "Validate" component, click on "Message Properties" tab in properties window, and then click on "Add Property Modification" button. Enter data as on picture below: set check box value on the left side to ON START, "Prop-Name" : initial.request, "Prop-Value" : payload:

This will copy input XML message into "initial.request" message property. So in case an input gets modified as the message gets processed along the route, we will still have an initial XML message saved in message property, so that we can use it to build a response.

Press "Save changes".

Now select a "Transformer" connected with an "Exclusive Gateway" and enter its properties like following: "Type" : custom, "Custom-Expression" :

'<UpdateCustomerResult>
	<custid>'+property['id']+'</custid>
	<name>'+xpath(property['initial.request'],'//name')+'</name>
	<city>'+xpath(property['initial.request'],'//city')+'</city>
	<zip>'+xpath(property['initial.request'],'//zip')+'</zip>
	<status>OK</status>
</UpdateCustomerResult>'

As you can see, we use message property "id" as a value of <custid>, and we use XML saved in "initial.request" message property to get all other values.

Press "Save Changes". Now select a "Transformer" component connected to "Catch Exception". Enter its properties like following: "Type" : custom, "Custom-Expression" :

'<UpdateCustomerResult>
	<custid>'+property['id']+'</custid>
	<status>ERROR</status>
	<error>'+escapeXml(error.getMessage())+'</error>
</UpdateCustomerResult>'

Press "Save Changes", save file and restart the application.

Go to Flink-ESB Admin Console, select "update-customer" route, click "Call Route" tab, paste following message into "Payload" textbox:

'<customer>
	<name>Luke Skywalker</name>
	<city>Cloud City</city>
	<zip>54321</zip>
</customer>'

Enter following expression in "Properties" textbox: [ 'id' : 2 ].

Now press "Call" button and check the result.

Repeat the test typing non-existing CUSTID in "Properties" textbox, for example 1000:

Now repeat the test using invalid XML request:

'<customer>
	<name>Luke Skywalker</name>
	<city>Cloud City</city>
</customer>'

Step 4: creating a new route, handling HTTP PUT request

Go to Flink-ESB Editor and create another route. Call it "http-update-customer".

Place "REST Receiver" from "HTTP Adapter" palette, and then "Call Subflow" from "ESB General" palette within a new route. Connect them as on picture below:

Select "REST Receiver" and enter its properties as on picture below: "HTTP-Server-Ref" : http-server, "Url" : /customer, "HTTP-Method" : PUT:

Press "Save changes". Now select "Call Subflow" component and enter its properties as below: "Flow-Ref" : update-customer:

Press "Save changes" and save the file

Restart the application in Flink-ESB Editor.

Step 5: testing HTTP PUT service

To test HTTP PUT request we could use either some browser plugin, or SoapUI, or any other tool, which is able to call HTTP PUT. I will use "curl" command.

To call HTTP PUT service with "curl" use this command:

curl -k -i -X PUT http://localhost:4545/customer?id=4 -d '
<customer>
        <name>Master YODA</name>
        <city>Naboo</city>
        <zip>11111</zip>
</customer>
'

Calling this command you should get a valid XML response with status OK:

Check now "http-get-customer" service, to see whether data was updated. To do this, type following URL in the browser: http://localhost:4545/customer?id=4. You should get updated data, as on pictute below:

Now try curl command using non-existing CUSTID (CUSTID=1000), for example this one:

curl -k -i -X PUT http://localhost:4545/customer?id=1000 -d '
<customer>
        <name>Master YODA</name>
        <city>Naboo</city>
        <zip>11111</zip>
</customer>
'

Check the content of response XML:

Step 6: adding "Content-Type" HTTP header to response from "http-put-customer" service

Now the only thing missing in the response is "Content-Type" HTTP header, telling client application what kind of message it is getting from the server, and what is the character encoding. So let's fix this.

Go to Flink-ESB Editor and select "REST Receiver" within "http-update-customer" route. Click "HTTP Headers" tab in properties window, and press "Add Header" button. Enter data as on the picture below: "Header-Name" : Content-Type, "Header-Value" : 'text/xml; charset=UTF-8':

Press "Save changes", save the file and restart the application.

Now call curl command again:

curl -k -i -X PUT http://localhost:4545/customer?id=4 -d '
<customer>
        <name>Master Yoda</name>
        <city>Naboo</city>
        <zip>11111</zip>
</customer>
'

This time you should get a proper XML response, containing "Content-Type" HTTP Header:

So now we have 3 working services to read customer data with HTTP GET, created in part 1 of the tutorial; to insert new customer data with HTTP POST, created in part 2 of the tutorial; and to update existing customer data with HTTP PUT.

The application in Flink-ESB Editor should look now similar to the one on the picture below: