Sending to Database

Extending the ingest booking adapter

The Frank!Framework can send data to external systems. This is done by pipe <SenderPipe>. Within this pipe, you add a sender to specify the destination.

As an example, we extend the New Horizons case by writing the “booking” table introduced in section New Horizons Requirements. We use sender <FixedQuerySender> to specify that we are targeting the database. Please do the following:

  1. Please open file Configuration.xml again.

  2. The <XmlValidatorPipe> you included in the previous section points to path Exit now. Update it to point to the pipe that will write the database. The highlighted line shows the update:

    ...
    root="booking"
    schema="booking.xsd">
  <Forward name="success" path="insertBooking" />
  <Forward name="failure" path="makeInvalidBookingError" />
</XmlValidatorPipe>
...

  3. Insert the new pipe after the </FixedResultPipe>:

    ...
      <Forward name="success" path="BadRequest"/>
    </FixedResultPipe>
    <SenderPipe
        name="insertBooking">
      <FixedQuerySender
          name="insertBookingSender"
          query="INSERT INTO booking VALUES(?, ?, ?, ?)"
          datasourceName="jdbc/${instance.name.lc}">
        <Param name="id" xpathExpression="/booking/@id" />
        <Param name="travelerId" xpathExpression="/booking/travelerId" />
        <Param name="price" xpathExpression="/booking/price" />
        <Param name="fee" xpathExpression="/booking/fee" />
      </FixedQuerySender>
      <Forward name="success" path="Exit" />
    </SenderPipe>
  </Pipeline>
</Adapter>
...

You see that we send an INSERT query to the database with parameters. The parameters appear with question marks in the SQL statement. The values of the parameters are provided through the <Param> tags. These tags contain XPath expressions to select the values from the incoming message. Xpath is explained here: https://www.w3schools.com/xml/xpath_intro.asp. You can see that elements are selected through / while attributes are selected through /@. We know that the incoming message is a valid booking, because it passed the <XmlValidatorPipe>.

The database is selected in an indirect way; the line datasourceName="jdbc/${instance.name.lc}" does the trick. Its meaning is explained in depth in section The Database. If you are reading this section for the first time, please finish it before studying database details.

Named query parameters

In the update above, you used query string INSERT INTO booking VALUES(?, ?, ?, ?). Instead of using anonymous parameters with ?, you can also reference the parameters by the name you give them in the <Param> elements. If you want to try this, you can update your adapter as shown:

...
</FixedResultPipe>
<SenderPipe
    name="insertBooking">
  <FixedQuerySender
      name="insertBookingSender"
      query="INSERT INTO booking VALUES(?{id}, ?{travelerId}, ?{price}, ?{fee})"
      useNamedParams="true"
      datasourceName="jdbc/${instance.name.lc}">
    <Param name="id" xpathExpression="/booking/@id" />
    <Param name="travelerId" xpathExpression="/booking/travelerId" />
    <Param name="price" xpathExpression="/booking/price" />
    <Param name="fee" xpathExpression="/booking/fee" />
...

This update is optional; it does not change the results produced by your adapter. It presents an alternative approach however that is useful in more advanced queries. This update is not applied in the solution download of this tutorial.

Testing (Windows and Linux)

Please test your work as follows:

  1. Copy the booking XML presented in New Horizons Requirements to some file.

  2. Edit that file to update the id attribute of the <booking> element. This corresponds to the primary key of database table “booking”. You need a value that differs from the values you applied so far.

  3. In the Frank!Console, go to Testing | Test Pipeline. Run the “IngestBooking” adapter with your booking XML. See section Testing Pipelines for details.

The response should be something like the following:

<results>
  <result item="1">
    <result><rowsupdated>1</rowsupdated></result>
  </result>
</results>

The output message is no longer the incoming booking XML. Please remember this when you add more pipes to the adapter.

  1. In the Frank!Console, click “JDBC” (number 1 in the figure below). That link will expand:

    ../_images/jdbcExecuteQuery.jpg

  2. Click “Execute Query” (number 2). You see the following screen:

    ../_images/executeJdbcQuery.jpg

  3. You see you are in the execute screen (number 1). Select “Datasource” “jdbc/frank2manual” (number 2). If you want, you can choose to see comma-separated output instead of XML (number 3).

  4. Leave the Query Type to “Auto”. You only need to change it for complicated queries for which the Frank!Framework cannot sort out whether a value is returned or not.

  5. Type the following query: SELECT * FROM booking (number 4).

  6. Press “Send” (number 5). Check that the query result (number 6) shows some of the data you entered in step 2, in particular the “id” you chose.

The presented version of the ingest booking adapter only inserts in table “booking”. In the coming sections, you will extend the adapter to also insert into table “visit”.

solution

If you are having troubles, you can download the solution.