eiConsole v.24R1
Advanced Tutorial & Interface

eiConsole – Database Tutorials

SQLXML – Inserting and Updating Rows

Note: The Quick Start, Foundation, Topology and Data Mapping & Transformation tutorials should be completed before proceeding with the Data Mapping & Transformation Tutorials.

Overview

This interface and related tutorials demonstrate and explain the use of SQLXML, a database-agnostic, XML-based language used in the eiConsole to query and update databases. In this tutorial, we expand on SQLXML basics and introduce the “Update” and “Insert” instructions for updating and inserting rows in a database table. This tutorial expands on concepts covered in “SQLXML Selecting Rows,” so users should be familiar with that material.

Before You Begin

SQLXML Inserting and Updating Rows
Working Directory

Download the SQLXML-Inserting-and-Updating-Rows-Working-Directory.zip file with the sample Working Directory and unzip it on your computer to a convenient place. In our case, it’s c:\Users\{USER _NAME}\PilotFish eiConsole Working Directories\SQLXML Inserting and Updating Rows where {USER_NAME} is the user’s name.

Open the eiConsole, browse to your SQLXML Inserting and Updating Rows directory, and open it. The fully configured SQLXML – Inserting and Updating Rows Example route is included in the Working Directory. Your Route File Management screen will open, as shown below.

Next, follow the tutorial and walk through it step-by-step. You may check your work against the provided Route (Sample Data).

Clearing the Rows

Start the H2 database by clicking Tools>H2 Database.

Click Start the Database.

Click Connect.

We’ll start by clearing the rows from our existing People database table. Execute this query against it:

DELETE FROM PEOPLE

You should get a message indicating that rows have been affected:

Creating a New Route

Press the Return to the eiConsole button.

We’ll continue by creating a new Route called SQLXML - Inserting and Updating Rows.

Double-click SQLXML - Inserting and Updating Rows.

Click Listener and select Directory / File.

Set Up the Listener

Enter the string ..\..\in in the polling directory field.

The polling directory can be found at:

c:\Users\{USER _NAME}\PilotFish eiConsole Working Directories\SQLXML – Inserting and Updating Rows\in

Click the Post-Process tab and select Delete.

Set up a Database SQL Transport

We’ve chosen to name this particular Transport Inserting Outbound Records, as we’ll later create a second Transport to handle updates.

Click Transport and select Database (SQL).

Configure the Connection tab using the following credentials:

Username: sa
Password:
Driver: org.h2.Driver
URL: jdbc:h2:~/test

Then click the Test Connection button to verify it all works correctly.

Configuring an XSLT Document

In the previous tutorial, we configured a Listener using the Input File field to provide the SQLXML to be used. For this Transport, we’ll do things a bit differently. Since we’re expecting the people.xml sample to be picked up by our Listener, we’ll want to make use of that data. We can, therefore, configure an XSLT document to transform the people.xml format to SQLXML, which in turn will be executed by the Transport.

On the Target Transform stage, add a new Format called People XML to Inserts:

Uncheck the Use Direct Relay checkbox and click New to open the Data Mapper.

Reading in the Source Format

Use XML as the Format Reader. Read people.xml for the Source Format. The file can be found at:

c:\Users\{USER _NAME}\PilotFish eiConsole Working Directories\SQLXML Inserting and Updating Rows\data

Now your window should look like the screen below.

Reading in the Target Format

We’ll now want to read in the Target format, for this we’ll use a new type of Format Reader called the SQLXML Format Builder:

This dialog expects the same configuration values used by the Listeners and Transports we’ve covered. Provide the credentials and connectivity information, click Test to ensure they’re correct, then click Read Format.

This will raise a dialog that allows you to select from a list of available database tables. Select People and hit OK:

This will populate the Target format with a fairly complex structure.

The Target format is now a representation of the entire set of SQLXML structures. Underneath these, you’ll find your database-specific information, where applicable. For example, click on the Select we used for the last tutorial:

Working with this format is a matter of dragging & dropping its items into the mapping panel while maintaining its provided structure.

Performing an Insert for Each Person

We’ll want to perform an insert for each Person in our Source data. Begin by mapping People onto the stylesheet element.

Drag the SQLXML root from the Target onto the added People element.

Drag XSLT Structures → Flow Control → for-each onto the added SQLXML element.

Drag the Person element from the Source onto the added for-each.

Next, drag the Insert element from the Target onto the for-each element, then the table name, People, onto that.

Mapping

Map each column name you’re interested in populating (all except PERSON_ID) onto the table name.

Now we just need to map each equivalent value from the Source onto these values.

We’ve now mapped our people.xml to SQLXML, creating an Insert instruction for each Person in the source document.

Testing

Return to the eiConsole, switch to the Testing Mode and run the people.xml file all the way through.

View the Stage Output at the Listener Stage

Here we can see the original people.xml contents. In the Target Transform stage, view the output after the XSLT stage.

We can see the SQLXML generated. If you then look at the Transport stage, you’ll see that it has been executed successfully. There’s no output to view, but we can query our database with:

SELECT * FROM PEOPLE


We can see that our Insert statements worked successfully.

Set up the Second Transport

Let’s now explore the Update instructions. Add and configure a new Target similar to the first, naming this one to indicate it’s intended for updates.

Add a new Format to this Target’s Transform stage, this time called People XML to Updates.

Open the Data Mapper once again and read the same Source and Target formats in.

For this mapping, we’ll just provide one Update (not under a for-each).

We’ll update each row with a VEHICLE_MAKE value of FORD to have a FIRST_NAME value of Updated. Add both columns to the mapping.

Add an XSLT Structures → Output → text value to FIRST_NAME with the value Updated, add an attribute to VEHICLE_MAKE with the name key and a text value of true, and then add a text value to VEHICLE_MAKE of FORD .

This will now update all PEOPLE records where VEHICLE_MAKE is FORD with a FIRST_NAME of Updated.

Save the mapping, switch to the eiConsole Testing Mode, and run the people.xml just through the new Target Transform.

Once again, we’ll confirm our updates by querying the database directly. Try this query:

SELECT * FROM PEOPLE WHERE VEHICLE_MAKE = 'FORD'

You’ll see that each entry now has a FIRST_NAME of Updated.

If the results match the above you are all set. This concludes the tutorial.