Integrating With Sybase Database

Last Updated: 10/30/2018 Introduced in Version: 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.

integrateWithDatabase

In the resulting window we provide our database with the Name. From the Select provider dropdown we select ODBC.

nameDatabaseAndSelectODBC

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

connectionString

Next, from Flow to get metadata dropdown we select Sybase ASE

selectFlowToGetMetadata

Then, we click Test Connection to test if we can connect to the database…

testConnection

Decisions informs us that Connection Succeeded…

connectionSucceed

We click Next to proceed to the Tables selection window…

nextToPickProcAndViews

We select Tables we want integrate with and click Next to continue…

selectTables

In the next window we can pick Views we want to integrate with… We click Next to proceed to the Procedures selection window.

selectViews

Here, we pick Stored Procedures we want to add to our Integration and click Save

selectProceduresAndSave

Our database is Saving to the Decisions

savingDatabase

In the Databases Folder in Portal our new Sybase database should be available now.

integrated

We click on this Database and locate the Table we will work with. In this example we locate dbo_stores.

navigateToStoressTable

Next, we click Edit Table from table’s options…

editStoresTable

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).

defineKeyForStoresTable

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.

addAdditionalStepsToCreateForStoresTable

Our table has 7 rows at this moment… We are going to Insert one row from Decisions to this table using Flow.

storesScreenshotFirst

To create a Flow we navigate to the Designer Folder and click Create Flow button from Folder Actions panel.

createFlow

Next, we name the Flow and click Create to proceed to the Flow Designer.

nameFlow

In the Flow Designer we expand Integrations > My Integrations > Database > SybaseTest(Database) > dbo_stores(Table) category in the Quick Add Step dialog and add Get all from dbo_stores step to our Flow.

getAllFromStoresStep

We connect Failed outcome from Get all from dbo_stores step to the End Step in our Flow. Then, we use Quick Add Step dialog 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.addFirstPickOrCreateForm

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.

nameFirstForm

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.

addDataGridToTheFirstForm

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.

defineTypeForTheFormStores

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  Quick Add Step dialog and connect it to the Done outcome from our Form.

addInsertRowStep

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.

addSecondGetAll

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.

addSecondPickOrCreateForm

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…

connectStepsInFlow

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.

showMappingForFirstForm

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.

mappingForFirstFormFinished

Back in the Flow Designer we call Show Mapping Editor for our Insert to dbo_stores step.

showMappingForInserRowStep

 

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.

buildDataToInsert

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.

mappingForInsertFinnished

Back in the Flow Designer we call Show Mapping Editor option for our Second Form step…

showMappingForSecondForm

And configure its data mapping exactly the same as we did for the First Form. Click Ok to save and close Mapping Editor.

mappingForSecondFormFinnished

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.

3.5 Test Flow Shot

debugFlow

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

firstFormOpensInTheDebugger

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

secondFormOpensInTheDebugger

In the Diagram View in the Debugger we can see that our Flow ran to the End step with no issues.

flowRanWithNoIssues

If we query our database outside of Decisions we can see that a new row was added to the stores table.

rowAddedToTheDatabase

Additional Resources