Last Updated: 08/11/2016Introduced in Verision: 2.0
Database entities represent external databases which can be integrated with a flow. To add a database within the portal, navigate to the folder System > Designers > Integrations > Databases, click the Add Database button, and supply the necessary details. Once a database is introduced, components to query, insert, or update data to or from the database within the Flow Designer can be used. They are found under Integration > Integration Database > [Database Name] > [Table Name].
Example
Our example flow will access a local database called Northwind, retrieve all of the records from the table Products, and display them in a data grid form.
Begin in the portal by navigating to the folder System > Designers > Integrations > Databases. Click the Integrate with Database button.
In the resulting Integrate with Database: Connection pop-up, define the details of the connection. Give this connection the same name as the database to integrate – “Northwind.”
The Connection string drop-down list provide generic strings containing basic configuration options, including user names and passwords, timeouts, and whether the connection should be made securely. Select the connection string format that is appropriate for the database.
For this example, select option Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=True, which doesn’t include a user name or password, and uses the platform’s integrated security.
Next, customize Connection string‘s generic parameters with the details of the actual database.
Then, click the Test Connection button to see if we can connect to our database with these details. If the platform can connect to the database, a confirmation screen appears. Click OK, and click Next to continue.
The platform automatically validates the connection string for errors before continuing. This prevents us from saving a connection string with critical syntax errors or missing values.
In the next pop-up, Create Database: Tables, select which tables this database entity can query against. In this example, only the Products table is selected.
By default, all fields and all components are enabled to generate for each selected table. To specify only certain fields, click on the Browse button in the Fields field and deselect the undesired fields. Additionally, to specify only certain components to generate, scroll right and deselect the undesired components. By default, the primary key is selected to be used as the unique identifier. It is also possible to change this, and add additional fields to use as keys by clicking the Keys field Browse button and making the respective changes. (Please use caution when changing keys.)
Click Next to continue.
If desired, select database views to include. As with tables, it is possible to customize the fields, keys, and components associated with each view. Click Next to continue.
In the next screen, the option to select any of the database’s stored procedures is made available. Select the desired procedures, and check the Returns Data checkbox if the procedure is to return data.
Click Save.
Our Northwind database entity is complete and ready to be integrated into the example flow.
Begin building our example flow in a Designer Folderby clicking the Create Flow button.
In the resulting pop-up, name the example flow “External DB Flow” and click OK to create it.
First, it is necessary to retrieve all records from the Products table. This is done using the component Get all from dbo_Products. Find this component in the Toolbox panel, under the category Integration > Database > Northwind (Database) > dbo_Products (Table).
Next, add a form in which records from the Products table will be displayed. From the Toolbox panel, expand Forms [Interaction] and drag and drop the [Pick or Create Form] component into the workspace.
Next, we will connect each step’s outcomes as follows:
Next, build the form. From the form Properties panel, click on Pick or Create Form. Name the form Display Products and click OK.
Make sure the form has at least one outcome, such as an OK button.
Expand the List category in the form Toolbox and drag a Data Grid component into the workspace. In the properties for the data grid, configure the Input Data > Type as the data type of the Products table, since that is the type output by the Get all from dbo_Products step. Note that you can right-click the form in the flow and browse the data upstream of the form; this is a way to check data type.
In the Input Data > Data Name field for the grid, enter AllProducts. Save and close the form.
In order for the form to display the records collected by Get all from dbo_Product, its inputs must be mapped to the Get all from dbo_Product step’s outputs.
Click on the form and in the Properties panel, click the link Show Mapping Editor.
To map Get all from dbo_Products‘s outputted collection of Products_Result objects, drag its anchor to our form’s sole input AllProducts.
This completes our mapping; click OK to close the Mapping Editor. Connect the output path from the form to the End step.
This completes our flow. We can test it by clicking the link Debug Flow at the top of the Flow Designer.
Note that in Decisions version 3.5 and above, you will need to click Test Flow rather than Debug Flow.
In the debugger, our flow displays a form grid containing all of the records in the Northwind database’s Products table.
