Integrating With Sybase DatabaseLast Updated: 02/28/2016 Introduced 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 Integrate With 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.
Example:
In this Example we are going to show how to Integrate with Sybase ODBC database. Next we are going to create a Flow to view a table data from Sybase database and add a Row to the table in this database.
First, we need to integrate with Sybase database. In Portal, we navigate to System > Integrations > Databases folder and click Integrate With Database button from the Folder Actions panel.
In the resulting window we provide our database with the Name. From the Select provider dropdown we select ODBC.
Next, we need to provide a valid connection string for the database. In this Example we use a connection string of the following format:
Driver={ODBC Driver’s Name Here};server=nameOfTheServer;port=portNumber;db=databaseName;uid=login;pwd=password
Next, from Flow to get metadata dropdown we select Sybase ASE
Then, we click Test Connection to test if we can connect to the database…
Decisions informs us that Connection Succeeded…
We click Next to proceed to the Tables selection window…
We select Tables we want integrate with and click Next to continue…
In the next window we can pick Views we want to integrate with… We click Next to proceed to the Procedures selection window.
Here, we pick Stored Procedures we want to add to our Integration and click Save…
Our database is Saving to the Decisions…
In the Databases Folder in Portal our new Sybase database should be available now.
We click on this Database and locate the Table we will work with. In this example we locate dbo_stores.
Next, we click Edit Table from table’s options…
In the resulting popup window we check Is Key checkbox for the Primary Key in this table to obtain a full control over the Table (Insert, Delete, Update).
New Steps that we can create to use in the Flow are now available for us… We check the desirable steps to be created and click Ok to save the changes and close this window.
Our table has 7 rows at this moment… We are going to Insert one row from Decisions to this table using Flow.
To create a Flow we navigate to the Designer Folder and click Create Flow button from Folder Actions panel.
Next, we name the Flow and click Create to proceed to the Flow Designer.
In the Flow Designer we expand Integrations > My Integrations > Database > SybaseTest(Database) > dbo_stores(Table) category in the Flow Designer Wizard and add Get all from dbo_stores step to our Flow.
We connect Failed outcome from Get all from dbo_stores step to the End Step in our Flow. Then, we use Flow Designer Wizard to connect Done outcome from Get all from dbo_stores step to [Pick or Create Form] step from Flows, Rules, Forms and Reports > Forms[Interaction] section.
We click Pick or Create Form link in the resulting window. Next, we name our Form and click Create to proceed to the Form Designer.
For our Form we add a control Button from Actions category and rename it as Done. Next, we add Data Grid component from the List category in the ToolBox.
We use Type Picker to provide Type for our Data Grid. In the resulting Pick Type window we use a search bar to locate SybaseTest_dbo_stores [TableDefinition.Types] type.
This completes our Form Design. We can save the Form and close Form Designer to return back to the Flow Designer.
Back in the Flow Designer we locate Insert to dbo_stores step from Integrations > My Integrations > Database > SybaseTest(Database) > dbo_stores(Table) category in the Flow Designer Wizard and connect it to the Done outcome from our Form.
Next, we connect Failed outcome from Insert to dbo_stores step to the End Step in our Flow. Then we add another Get all from dbo_stores step to our Flow and connect it to the Done outcome from Insert to dbo_stores step.
Again, we connect Failed outcome from Get all from dbo_stores step to the End Step in our Flow. Then, we add another Form to the Done outcome from Get all from dbo_stores step using [Pick or Create Form] step.
This Form is designed exactly in the same manner as First Form we have created in this tutorial. When we finish designing this form we save it and close Form Designer. Next, we connect the steps in our Flow in the following manner…
We select Show Mapping Editor option from First Form’s Actions menu to configure the Input Data into the Data Grid component in our Form.
In the Mapping Editor we connect stores_Result (the outcome from our Get all from dbo_stores step) to the Data Grid variable in our Form. We click Ok to close Mapping Editor.
Back in the Flow Designer we call Show Mapping Editor for our Insert to dbo_stores step.
In the Mapping Editor we use Build Array Mapping Type for the Inserted Objects. Next, we use Build Data option for the first element in the Array.
Next, we can define all fields in the database row as Constant and provide with desirable values… Click Ok when finished to save and close Mapping Editor.
Back in the Flow Designer we call Show Mapping Editor option for our Second Form step…
And configure its data mapping exactly the same as we did for the First Form. Click Ok to save and close Mapping Editor.
This completes our Flow. We click Debug Flow link from the top panel in the Flow Designer to test our Flow.
Note that in Decisions version 3.5 and above, you’ll need to click on the Test Flow link to access the Debugger.
The First Form opens and we can observe that all 7 rows from our Table are displayed in the Data Grid on the Form. Click Done…
Second Form opens after the Row was inserted behind the scenes as we have designed. We can confirm that the new Row is present in the Data Grid on the Form. We click Done to terminate our Flow…
In the Diagram View in the Debugger we can see that our Flow ran to the End step with no issues.
If we query our database outside of Decisions we can see that a new row was added to the stores table.
