Example: SELECT with Parameters

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Data Sources and Targets > Databases and MapForce > SQL SELECT Statements as Virtual Tables >

Example: SELECT with Parameters

This example shows you how to create a MapForce mapping which reads data from a Microsoft Access database and writes it to a CSV file. In particular, the mapping described in this example uses a custom database SELECT query with a parameter. The SELECT statement combines data from multiple tables. Then, the results are supplied to the mapping for further processing.

 

The example is accompanied by a mapping design (.mfd) file available at the following path: <Documents>\Altova\MapForce2019\MapForceExamples\Tutorial\select-component.mfd. You might want to open this sample file and analyze it first, or follow the steps below to create it from scratch.

 

Although this example uses a Microsoft Access database, the process works in the same way for other database types. For information about connecting to other databases, see Connecting to a Database.

 

The goals are as follows:

 

1.We must select from the database only those orders where the number of ordered items exceeds a custom value. This custom value should be supplied as a parameter to the mapping. To achieve this goal, we will create a custom database SELECT statement that takes an input parameter.
2.In the Access database, the date format is YYYY-MM-DD HH-MI-SS. In the CSV file, the time part must be stripped, so the format should be YYYY-MM-DD. To achieve this goal, we use the date-from-datetime function available in MapForce.
3.The resulting CSV file must have the name OrdersReport.csv.

 

Step 1: Add the SELECT structure

1.On the Insert menu, click Database.
2.Select Microsoft Access (ADO), and follow the wizard steps to connect to the altova-products.mdb file available in the <Documents>\Altova\MapForce2019\MapForceExamples\Tutorial\ folder.

ex_select_01

3.On the Insert Database Objects dialog box, click Add/Edit SELECT Statement, and enter the following query:

SELECT *, (Quantity * UnitPrice) AS Price
FROM Orders
INNER JOIN Products
ON Orders.ProductID = Products.ProductID
WHERE Orders.Quantity > :Quantity

This query uses a join between the Orders and Products tables, and retrieves all fields (*), and a computed value (AS Price). The query also specifies the :Quantity parameter in the WHERE clause.

ex_select_02

4.Click Add SELECT statement.

ex_select_03

5.Click OK. The altova-products component has now been added to the mapping area.
6.On the altova-products component, click selectSprocButton and select Insert Call with Parameters.

ex_select_04

A new structure (SELECT_Statement) is now available on the mapping. It is split into two parts: the left part supplies input connectors and the right part supplies output connectors. Notice that the left part also includes the Quantity parameter defined previously.

ex_select_05

 

Step 2: Add the input parameter

1.On the Insert menu, click Insert Input.
2.Type "Quantity" as name.
3.Under Design-time Execution, enter a parameter value to be used for executing the mapping during the design phase (in this example, "2"). For more information, see Supplying Parameters to the Mapping.

ex_select_06

You can now connect the input parameter to the database call structure, as shown below.

ex_select_07

 

Step 3: Add the target CSV component

1.On the Insert menu, click Text File.
2.Select Use simple processing for standard CSV..., and then click Continue.
3.On the Component Settings dialog box, click Append Field and add nine new fields. It is recommended that you give to the CSV fields the same name as the name of the database fields, as shown below. This will help you save time later when drawing mapping connections. For more information about these settings, see Setting the CSV Options.

ex_select_08

4.Create a connection between the result node of the SELECT structure and the Rows node of the CSV component.

ex_select_09

Because most of the fields in the CSV component have the same name as their database equivalent, mapping connections will likely be drawn automatically when you connect result to Rows. If this does happen, select the Connection menu and make sure that the Auto Connect Matching Children option is enabled. The only mapping item that you have to connect manually is ProductID, since there is no field with this name in the SELECT structure.

 

Step 4: Convert the date

In the Libraries window, search for the date-from-datetime function and drag it to the mapping area. Then connect its input and output as shown below.

ex_select_10

 

Step 5: Set the name of the output file

To set the name of the output file to OrdersReport.csv, double-click the CSV component, and enter the value in the Output File box.

ex_select_11


© 2019 Altova GmbH