SQL Query Source

The SQL Query Source object enables you to retrieve data from a database using an SQL query or a stored procedure. You can specify any valid SELECT statement or a stored procedure call as a query. In addition, you can parameterize your queries dynamically, thereby allowing you to change their values at runtime.

In this article, we will be looking at how you can configure the SQL Query Source object and use it to retrieve data in Astera Centerprise.

Configuring the SQL Query Source

1. Before moving on to the actual configuration, we will have to get the SQL Query Source object from the Toolbox onto the dataflow designer. To do so, go to Toolbox > Sources > SQL Query Source. In case you are unable to view the Toolbox, go to View > Toolbox or press Ctrl + Alt + X.

image-20200909102843082

2. Drag-and-drop the SQL Query Source object onto the designer.

image-20200909102952277

The source object is currently empty as we have not configured it yet.

3. To configure the SQL Query Source object, right-click on its header and select Properties from the context menu. Alternatively, you can double-click the header of the source object.

image-20200909103051317

A new window will pop up when you click on Properties in the context menu.

image-20200909103140884

In this window, we will configure properties for the SQL Query Source object.

4. On this Database Connection window, enter information for the database you wish to connect to.

  • Use the Data Provider drop-down list to specify which database provider you want to connect to. The required credentials will vary according to your chosen provider.

image-20200909103434605

  • Provide the required credentials. Alternatively, use the Recently Used drop-down list to connect to a recently connected database.
  • Test Connection to ensure that you have successfully connected to the database. A separate window will appear, showing whether your test was successful. Close this window by clicking OK, and then, click Next.

image-20200909103557542

image-20200909103631391

5. The next window will present a blank page for you to enter your required SQL query. Here, you can enter any valid SELECT statement or stored procedure to read data from the database you connected to in the previous step.

The curly brackets located on the right side of the window indicate that the use of parameters is supported, which implies that you can replace a regular value with one that is parameterized and can be changed during runtime.

image-20200909103759183

In this example, we will be reading the Orders table from the Northwind database.

image-20200909103903460

Once you have entered the SQL query, click Next.

6. The following window will allow you to check or uncheck certain options that may be utilized while processing the dataset, if needed.

image-20200909104001143

  • When checked, The Trim Trailing Spaces option will refine the dataset by removing extra whitespaces present after the last character in a line, up until the end of that line. This option is checked by default.

  • The Dynamic Layout option is unchecked by default. When checked, it will automatically enable two other sub-options.

    o Delete Field In Subsequent Objects: When checked, this option will delete all fields that are present in subsequent objects.

    o Add Fields In Subsequent Objects: When checked, this option will add fields that are present in the source object to subsequent objects.

Choose your desired options and click Next.

7. The next window is the Layout Builder. Here, you can modify the layout of the table that is being read from the database. However, these modifications will only persist within Centerprise and will not apply to the actual database table.

image-20200909104231405

  • To delete a certain field, right-click on its serial column and select Delete from the context menu. In this example, we have deleted the OrderDate field.

image-20200909104329700

  • To change the position of a field, click its serial column and use the Move up/Move down icons located in the toolbar of the window. In this example, we have moved up the EmployeeID field using the Move up icon, thus shifting the CustomerID field to the third row. You can move other fields up or down in a similar manner, allowing you to modify the entire order of the fields present in the table.

image-20200909104420073

Once you are done customizing your layout, click Next.

8. In the Config Parameters window, you can define certain parameters for the SQL Query Source object.

These parameters facilitate an easier deployment of flows by excluding hardcoded values and providing a more convenient method of configuration. If left blank, they will assume the default values that were initially assigned to them.

image-20200909104536465

Enter your desired values for these parameters, if any, and click Next.

9. Finally, aGeneral Options window will appear. Here, you are provided with:

  • A text box to add Comments.
  • A set of General Options that have been disabled.

image-20200909104741156

To conclude the configuration, click OK.

You have successfully configured the SQL Query Source object. The fields are now visible and can be mapped to other objects in the dataflow.