Getting data from a database by providing parameters

If you need to create a dataset with data coming from a database you can do it just by using a Table Input step. If the SELECT statement that retrieves the data doesn't need parameters, you simply write it in the Table Input setting window and proceed. However, most of the times you need flexible queries; queries that receive parameters. This recipe will show you how to pass parameters to a SELECT statement in PDI.

Assume that you need to list all products in Steel Wheels for a given product line and scale.

Getting ready

Make sure you have access to the sampledata database.

How to do it...

1. Create a transformation.

2. Before getting the data from the database, you have to create the stream that will provide the parameters for the statement.

3. Create a stream that builds a dataset with a single row and two columns: the product line parameter and the scale parameter. For this exercise, you can do it just by adding a Data Grid step or a Generate Rows step. Doing a preview on the last step of your stream you should see something like this:

   

4. Now drag to the canvas a Table Input step, and create a hop from the last step of the stream created above, towards this step.

5. Now you can configure the Table Input step. Double-click it, select the connection to the database, and type the following statement:

   SELECT productline 
        , productscale
        , productcode
        , productname
   FROM   products p
   WHERE productline  = ?
   AND   productscale = ?

   Tip

   Downloading the example code

   You can download the example code fles for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the fles e-mailed directly to you.

6. In the Insert data from step list, select the name of the step that is linked to the Table Input step. Close the window.

7. Select the Table Input step and do a preview of the transformation. You will see a list of all products that match the product line and scale provided in the incoming stream:

   

How it works...

When you need to execute a SELECT statement with parameters, the first thing you have to do is to build a stream that provides the parameter values needed by the statement. The stream can be made of just one step; for example a Data grid with fixed values, or a stream made up of several steps. The important thing is that the last step delivers the proper values to the Table Input step.

Then, you have to link the last step in the stream to the Table Input step where you will type the statement. What differentiates this statement from a regular statement is that you have to provide question marks. When you preview or run the transformation, the statement is prepared and the values coming to the Table Input step are bound to the placeholders; that is, the place where you typed the question marks.

Note that in the recipe the output of the stream was a single row with two fields, which is exactly the same number of question marks in the statement.

Note

The number of fields coming to a Table Input must be exactly the same as the number of question marks found in the query.

Also note that in the stream the product line was in the first place and the scale in the second place. If you look at the highlighted lines in the recipe, you will see that the statement expected the parameter values exactly in that order.

Note

The replacement of the markers respects the order of the incoming fields.

Finally, it's important to note that question marks can only be used to parameterize value expressions just as you did in the recipe.

Keywords or identifiers (for example; table names) cannot be parameterized with the question marks method.

If you need to parameterize something different from a value expression you should take another approach as explained in the next recipe.

There's more...

There are a couple of situations worth discussing.

Parameters coming in more than one row

In the recipe you received the list of parameter values in a single row with as many columns as expected parameter values. It's also possible to receive the parameter values in several rows. If instead of a row you had one parameter by row, as shown here:

The behavior of the transformation wouldn't have changed. The statement would have pulled the values for the two parameters from the incoming stream in the same order as the data appeared. It would have bound the first question mark with the value in the first row, and the second question mark with the value coming in the second row.

Note that this approach is less flexible than the previous one. For example, if you have to provide values for parameters with different data types you will not be able to put them in the same column and different rows.

Executing the SELECT statement several times, each for a different set of parameters

Suppose that you not only want to list the Classic Cars in 1:10 scale, but also the Motorcycles in 1:10 and 1:12 scale. You don't have to run the transformation three times in order to do this. You can have a dataset with three rows, one for each set of parameters, as shown below:

Then, in the Table Input setting window you have to check the Execute for each row? option. This way, the statement will be prepared and the values coming to the Table Input step will be bound to the placeholders once for each row in the dataset coming to the step. For this example, the result would look like this:

See also

• Getting data from a database by running a query built at runtime. This recipe gives you an alternative way for using parameters in a SELECT statement.