SQL Query Source

The SQL Query Source 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’ll 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’ll have to get an SQL Query Source object from the Toolbox onto the dataflow designer. To do so, go to Toolbox > Sources > SQL Query Source. In case you’re 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 because we are yet to configure it.

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 from the context menu.

image-20200909103140884

This window is where you configure the properties for the SQL Query Source object.

4. On the Database Connection screen, enter the 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 is successful. Close it by clicking OK, and then click Next.

image-20200909103557542

image-20200909103631391

5. The next screen 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 on the right side of the screen 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’ve entered the SQL query, click Next.

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

image-20200909104001143

  • The Trim Trailing Spaces option, if checked, will refine the dataset by removing the 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. If checked, it will automatically enable two other suboptions.

    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 screen will present 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 on the top-left of the screen. In this example, we’ve moved EmployeeID to second place by using the Move up icon, thus shifting CustomerID to third place. You can move other fields up or down in a similar manner, consequently allowing you to modify the entire order of the fields present in the table.

image-20200909104420073

Once you’re done customizing your layout, click Next.

8. On the new Config Parameters screen, you can define certain parameters for the SQL 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. At the end, a General Options screen 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.