eiConsole v.24R1
Tutorial & Interface

eiConsole – Database Tutorials

SQLXML – Selecting Rows

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

This Interface, accompanied by its tutorial, provides a comprehensive introduction to SQLXML—a database-independent, XML-based language utilized within the eiConsole for querying and updating databases. The tutorial delves into the fundamentals of SQLXML, focusing on the “Select” instruction. It guides users through the processes of setting up a database, configuring its tables, and fetching data from them using SQLXML. As this tutorial builds upon the foundational concepts of eiConsole topology and configuration, it’s recommended that users are acquainted with these topics before embarking on this tutorial.

Before You Begin

SQLXML Selecting Rows
Working Directory

Download the SQLXML-Selecting-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 Selecting Rows where {USER_NAME} is the user’s name.

Open the eiConsole, browse to your SQLXML Selecting Rows directory, and open it. The fully configured SQLXML – Selecting 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) at the end of the tutorial.

Preparing the Database

We’ll start by preparing a database for this and other SQLXML-related exercises. You can use any database that is accessible via JDBC drivers, we will be using the H2 database.

Go to the tools menu and select H2 Database.

Click the Start the Database button.

You should see the database connection screen in your browser as shown below.

We can provide values for the user name and password if we wish to change them, but for this tutorial, we’ll leave them at their defaults – sa for the user name and leave password blank.

Click the Connect button.

The main H2 screen will be launched.

We can enter SQL commands into the text area and then click the Run (Ctrl + Enter) button to execute them. To start, we’ll use the content of the create_people.sql file to create a PEOPLE table. The file can be found in: c:\Users\{USER _NAME}\PilotFish eiConsole Working Directories\SQLXML Selecting Rows\data

        CREATE TABLE PEOPLE(
PERSON_ID INTEGER PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME VARCHAR(100),
MIDDLE_NAME VARCHAR(100),
LAST_NAME VARCHAR(100),
GENDER VARCHAR(20),
SSN VARCHAR(15),
BIRTHDATE VARCHAR(15),
OCCUPATION VARCHAR(100),
VEHICLE_MAKE VARCHAR(20),
VEHICLE_MODEL VARCHAR(20),
ADDRESS_LINE_1 VARCHAR(100),
ADDRESS_LINE_2 VARCHAR(100),
CITY VARCHAR(40),
STATE VARCHAR(4),
ZIP VARCHAR(10)
);


Once executed, we should have a table definition visible:

Populating Our Table

Now we’ll populate our table with PEOPLE entries. The file insert_people.sql contains 100 such entries. The file can be found in: c:\Users\{USER _NAME}\PilotFish eiConsole Working Directories\SQLXML Selecting Rows\data

Copy the files contents to the text area and execute it. Afterward, run the following SQL command to verify the output:

SELECT * FROM PEOPLE

You should see ~100 rows displayed.

Next, we’ll copy a few pieces of information for subsequent use: the URL, Driver, Username, and Password. Copy these to a simple text editor for reference. Here are our values:

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

You can find the URL at the top left of the H2 browser.

Creating a New Route

Press the Return to the eiConsole button.

Create a new Route called SQLXML - Selecting Rows by clicking the Add Route button and entering the name.

Double-click SQLXML - Selecting Rows.

The main route grid will open.

To interact with our database, we have a few standard components available to us: the Database Polling SQL Listener, the Database SQL Transformation Module, and the Database SQL Transport. Each of these uses a PilotFish language called SQLXML, which can be summarized as an XML-based, database agnostic set of SQL instructions.

Each module can be configured using an Input File referencing a file containing SQLXML or, if this is not provided, by assuming that the contents of any transaction reaching that module is SQLXML. In either case, the SQLXML received will be interpreted and executed against a configured database connection, which can be a “Data Source” or JDBC-driven.

Configuring a Basic Database Polling SQL Listener

We’ll start by configuring a basic Database Polling SQL Listener. First, select that Listener from the Listener Type drop-down:

Start by selecting the Connection tab and entering our previously copied values in the User name, JDBC driver and JDBC URL fields.

Click the Test Connection button to ensure that our values are correct and that our database is functioning.

Back on the Basic tab, we’ll need to first provide a Polling Interval, which is how often our SQLXML is executed against the provided database connection. Since this is a tutorial, we’ll assume 5 seconds is sufficient.

Next, we’ll need to create our SQLXML file. Type sql.xml into the Input file field, then click the Edit button.

Select In XML editor from the drop-down.

This will open the XML Editor. Here we can write out the body of our SQLXML.

Writing Out the Body of Our SQLXML

As this is an XML file, we’ll start by providing the basic XML header:

<?xml version="1.0" encoding="UTF-8"?>

Our next line will be the root element, SQLXML. There’s a particular namespace associated with SQLXML, so we’ll define that using the xmlns attribute:

<SQLXML xmlns="http://pilotfish.sqlxml">
</SQLXML>


We can now provide a number of different SQLXML instructions inside the SQLXML body, including Execute, Select, Insert, Update, and Delete. We’ll start with Select. The Select element has a required attribute called “into” which will specify the name of a variable for the selected rows to be placed in. We’ll call ours “records”:

<Select into="records">
</Select>


Our output should look something like this so far:

The structure of the Select instruction is pretty simple; it expects a single child element sharing names with the table to select from. Please note that different databases may impose different restrictions on case sensitivity. Underneath the table name element, we’ll provide empty tags matching the names of columns we’re interested in retrieving. We’ll provide all of them as follows:

<?xml version="1.0" encoding="UTF-8"?>
<SQLXML xmlns="http://pilotfish.sqlxml">
<Select into="records">
<PEOPLE>
<FIRST_NAME />
<MIDDLE_NAME />
<LAST_NAME />
<GENDER />
<SSN />
<BIRTHDATE />
<OCCUPATION />
<VEHICLE_MAKE />
<VEHICLE_MODEL />
<ADDRESS_LINE_1 />
<ADDRESS_LINE_2 />
<CITY />
<STATE />
<ZIP />
</PEOPLE>
</Select>
</SQLXML>


Our output should look like this:

The Select instruction should now create a SQL query fetching the various provided columns from the PEOPLE table. The results will be stored in a variable called records. Now we simply need to output this variable to XML. To do so, we’ll add another instruction next to Select called XMLOut with an attribute called var specifying our records variable:

<XMLOut var="records" />


Our output:

Now we need to save our file. Click the Save button and save the file as sql.xml then close the XML Editor by selecting OK.

There is one more configuration value that needs to be changed.  This is located under the Advanced tab. Enable the Use Single Output Stream option by selecting the checkbox:

This will cause all selected rows to come back in a single eiConsole transaction. Unchecked, and by default, the Listener will produce a separate transaction for each row selected.

Set up a Directory File Transport

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

The Transport directory can be found in:

c:\Users\{USER _NAME}\PilotFish eiConsole Working Directories\SQLXML Selecting Rows\out

Save the route and switch to the eiConsole’s Testing Mode. 

Select the Execute Test icon at the Listener stage. Select the Start Test Here and End Test After Here checkboxes as show below:

Click on the Stop button and then the View Stage Output button to view the output.

Your output should look like below when selecting the XML Format:

The Select instruction created a root element called EIPData. We then have a RECORDS tag, created from our variable name (records). Each row then becomes a DATA element with the corresponding columns underneath.

Suppose we need to specify the equivalent of a SQL where clause. That is, we need to select only particular rows. Return to your sql.xml file in the XML Editor. To use a column in such a way, you’ll need to provide a key attribute set to true, as well as the value to check against as the column text. Modifying the query to grab only those PEOPLE rows with Gender = Female:

<GENDER key="true">Female</GENDER>


Our output in the XML Editor:

Once again, execute this as a test and review the output.

Review the Output

If you review the results, you’ll see all entries have the GENDER value as Female.

Testing the Route End-to-End

With that, we’ve now completely configured our Route, as well. So let the test run all the way through.

Uncheck the End Test After Here checkbox and click Execute Test.

Look into the output directory for output.xml:

c:\Users\{USER _NAME}\PilotFish eiConsole Working Directories\SQLXML Selecting Rows\out

Your output file should contain the same data we checked with the Stage Output Viewer.

If it does, that’s it. You’ve successfully finished this tutorial.