Lesson 4 : Fetch Customerlist

By now you must have populated your database with many customers. Good job! But now it’s time to get them out at our command. As you have not done so, make a copy of your work, and give it a name that you can recognize later. Start your work on top of what you produced in lesson 1.

The Plan

 

  • Making a query to fetch a list of customers we were making in the former lesson.
  • Translating the server response into an xml file that can be sent back as a response.
  • Incorporating this in an Adapter.

 

 

Files

 

Create this file structure in your editor. The actual adapter resides in the file ConfigurationGetCustomerOverview.xml ; our process consists of sending a GET request to our Ibis; all the data will be returned from the database, after which these data have to be moulded into a nice list.

 

 

 

 

 

 

 

 

Planning

This time we will be focusing a little more on the output side of things. We have to translate what came from the db into a document that makes sense. Now just for fun go back to the console and find “Execute a jdbc query” behind the JDBC tab. Select xml as the output type. Execute a select query like “SELECT FROM CUSTOMERS CLIENTNR,ADDRESS,CITY”.  You now see the output that you can expect and understand that with all these rows and fieldsets we need some parsing to get to the data we really want. This we’ll do with an xsl transformation.(example sql output)
Start with filtering out the data we want from the sql response. After that we want to make sure that we are returning a valid response. So first an xsl and then an xml.

 

Destination Klantoverzicht Source (database)
Element Level M/O Type Path Element M/O Type Condition
Klanten 1
  Klant 2 For each row in customers
    Klantnummer 3 M Num10 customers ClientNr
    Naam 3 M Char100 customers if Firestname=” ” then Initials + ‘ ‘+ LastName else Firstname+’ ‘+ LastName

The problem here is the following : what to do when we have the following two records in the db:

INITIALS: “A.B” , FIRSTNAME: “Anthony” LASTNAME : “Azkenazy” 

INITIALS:”C”  , FIRSTNAME: “”  LASTNAME:  “Clarke”

Do we want to return frontname + space + lastname  or  initials + lastname ? Fortunately we have made the initials a obligatory value in our create customer adapter. Then we have the initials in any case, and when we have a first name we can use that one instead of the initials, to have some more detail. That job has to be done in our mapResponse.xsl.

 

Question Time : Create a mapResponse.xsl

 

Copy the code below to your editor. Add the missing code with as root element ‘Klant’ and with child elements ‘Klantnummer’ and ‘Naam’. Use a xsl:choose structure to distinguish between the case that we have a firstname or when we have not, and use the xslt concat method to combine initials or the first name with the last name.

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>
<xsl:template match="/">
    <Klanten>
        <xsl:apply-templates select="result/rowset/row"/>
    </Klanten>
</xsl:template>
  <xsl:template match="row">
     
        ... insert your xsl ... 
  
  </xsl:template>
</xsl:stylesheet>

Several solutions are possible, but you can check for a working solution here.

 

Planning

We need to incorporate the adapter that we are going to make in Configuration.xml. Just include it as an entity. An Entity is a reference to the location of your adapter. jmsReals -jms stands for Java Message Service–  is a reference to an external resource such as a datasource or a queue connection factory. In the last bit your adapter is actually included in the Ibis.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration [
    <!ENTITY CreateCustomer SYSTEM "ConfigurationCreateCustomer.xml">
    <!ENTITY GetOverview SYSTEM "ConfigurationGetCustomerOverview.xml"> ]>

<configuration name="YOUR CONFIGURATIONNAME HERE">
    <jmsRealms>
        <jmsRealm realmName="jdbc" datasourceName="jdbc/${instance.name.lc}" />
    </jmsRealms>
    &CreateCustomer;
    &GetOverview;
</configuration>

finally we need to make klantoverzicht.xsd , which will validate the output from our xsl.

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
    <xs:element name="Klanten">
        <xs:complexType>
            <xs:sequence maxOccurs="unbounded">
                <xs:element name="Klant">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="Klantnummer">
                                <xs:simpleType>
                                    <xs:restriction base="xs:positiveInteger">
                                        <xs:totalDigits value="10"/>
                                    </xs:restriction>
                                </xs:simpleType>
                            </xs:element>
                            <xs:element name="Naam">
                                <xs:simpleType>
                                    <xs:restriction base="xs:string">
                                        <xs:maxLength value="100"/>
                                    </xs:restriction>
                                </xs:simpleType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

 

 

 

Through the pipeline !

 

 

 

 

Let’s start coding!

 

STEP 1  Begin by creating an  adapter with just a GenericReceiver and an ApiListener in it.

<?xml version="1.0" encoding="UTF-8"?>
<module>
    <adapter name="GetCustomerOverview" description="Adapter om een klantoverzicht op te halen">
        <receiver className="nl.nn.adapterframework.receivers.GenericReceiver" name="GetCustomerOverview_Receiver">
            <listener className="nl.nn.adapterframework.http.rest.ApiListener" 
                      uriPattern="customerlist"
                      name="GetCustomerOverview_Listener"
                      method="GET"
                      />
        </receiver>      
    </adapter>
</module>

STEP 2  Start off a pipeline just after the receiver and insert a first pipe with class GenericMessageSendingPipe. Inside it we place a FixedQuerySender. Don’t forget to give the pipeline a “firstPipe” attribute.

<pipeline firstPipe="ExecuteSelectQuery">
    
    <pipe name="ExecuteSelectQuery" 
          className="nl.nn.adapterframework.pipes.GenericMessageSendingPipe">
                <sender name="ExecuteInsertQuery" 
                        className="nl.nn.adapterframework.jdbc.FixedQuerySender" 
                        queryType="select" 
                        query="SELECT ClientNr, Initials, Firstname, LastName, CreationDate FROM CUSTOMERS" 
                        jmsRealm="jdbc">
                </sender>
          <forward name="success" path="MapResponse" />
    </pipe>

</pipeline>

STEP 3  Now in the MapResponse pipe we will transform the database reply with the help of an XsltPipe.

 <pipe name="MapResponse" 
                  className="nl.nn.adapterframework.pipes.XsltPipe" 
                  styleSheetName="GetCustomerOverview/xsl/mapResponse.xsl">
                <forward name="success" path="EXIT" />
</pipe>

STEP 4  Since we want to make sure our output conforms the standard an outputvalidator is placed at the end of the pipeline. We will use our klantoverzicht.xsd for that.

<outputValidator 
    className="nl.nn.adapterframework.pipes.XmlValidator" 
    schema="GetCustomerOverview/xsd/klantoverzicht.xsd" 
    root="Klanten"
    throwException="true">
</outputValidator>

 

Testing the app

We’re ready now to post our configuration to the framework. Solve any errors on this point. Make sure to understand the console messges : the console gives log messages, but also reports about the health of our adapter. So an error that shows up in red in the log portion, but not in the health part, is of no harm to you. Use this page for common troubleshooting. As a general hint you should gratefully make use of online sql-checkers, xml-validators, etc, before you make minor changes to your code.

A Postman request now should look like this by now.

 

 

 

 

 

 

A very useful feature of the console is “test a pipeline”. Lookup your adapter in the select list and give it a go. The input box requires a random dummy value. The output of your adapter should show up here.

 

 

 

Solutions

ConfigurationGetCustomerOverview.xml

MapResponse.xsl

Klantoverzicht.xsd

troubleshooting

 

 


Leave a Comment