Jump to: navigation, search

Difference between revisions of "HowTo:Update Data via OLEDB with a Workflow"

(→‎Example)
 
(27 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
First you need to establish a OLEDB Connection t o a Oracle Database.
+
Updating data in an Oracle or SQL database via a workflow can be done by using a set of activities.
−
See [[HowTo:Connect_To_Oracle_With_A_Workflow]] for details.
+
−
<br/><br/>
+
−
Once we are able to connect we start modelling the data structures we want to udpate in the workflow editor.
+
−
<br/>
+
−
1. Use a "CreateOLEOracleScope" Activity to configure the connection.<br/>
+
−
2. Place either a "CreateOLETableScope" or a "CreateOLEQueryScope" inside the scope acticity to define the table you want to fetch data from.<br/>
+
−
<br/>
+
−
{{Attention|When you are using a CreateOLEQueryScope to fetch data you want to update, you '''have to''' define a valid tablename!}}
+
  
−
3. Iterate throw the fetched rows by using e.g. a FOR - EACH activity.<br/>
+
= Instructions =
−
4. Once you found the row you want to update, use an "OLEEditRow" to make it editable.<br/>
+
# Establish a connection [[HowTo:Connect_To_Oracle_With_A_Workflow|to an Oracle]] or SQL database. Once connected, it is necessary to model the table / column definitions, of the data to be udpated, in the workflow. Use [[Activity:CreateOLEOracleScope (Activity)|CreateOLEOracleScope]] or [[Activity:CreateOLESQLScope (Activity)|CreateOLESQLScope]] to set up the connection.
−
5. Define the primary key data structure by placing "EditOLEValue" activities inside the OLEEditRow. You need to specify the column name in the "DisplayName" property and the data type in the "DataType" property. Check the "IsPrimary" checkbox as this column is the key column.<br/>
+
# Place either a [[Activity:CreateOLETableScope (Activity)]] or a [[Activity:CreateOLEQueryScope (Activity)]] within the scope acticity to define the table you want to udate data.
−
<br/>
+
# Iterate throw the fetched rows by using e.g. a '''for each''' activity.
−
{{Attention|The primary key column "must be defined" when updating data via OLE DB.}}
+
# Once you determine the row to be updated, use [[Activity:EditOLERow (Activity)]] to start editing.
 +
# Define the primary key column(s) by using [[Activity:EditOLEValue (Activity)]] activities within EditOLERow. You need to specify the column name in the '''DisplayName''' property and the data type in the '''DataType''' property. Check the '''IsPrimary''' checkbox for all columns defined in this step (key columns).
 +
# Define the data column(s) to be updated by using EditOLEValue activities within the EditOLERow. Again, you need to specify the column name in the '''DisplayName''' property and the data type in the '''DataType''' property.  Specify the '''new value''' for the cell in the property '''Value'''.
  
−
6. Define the data cells you want to update by placing "EditOLEValue" activities inside the OLEEditRow. You need to specify the column name in the "DisplayName" property and the data type in the "DataType" property. Specify the new value for the cell in the evaluation property "Value".<br/>
+
{{Attention|If you are using a CreateOLEQueryScope to fetch data you want to update, you '''have to''' define a valid tablename!}}
  
 +
{{Attention|The primary key column '''must be defined''' when updating data via OLE DB.}}
 +
 +
=Example=
 +
Connecting to an Oracle database and updating data.<br />
 +
{{FileLink|Example_Workflow_ORACLE_UPDATE_STATEMENT.uwf|Download Workflow example}}
  
−
'''Example Workflows:'''
 
−
<br/><br/>
 
−
Connecting to oracle DB and reading data with a CreateOLETableScope: [[File:Example_Workflow_ORACLE_UPDATE_STATEMENT.uwf|220px|border|alt=Example Workflow|Example Workflow]]
 
−
<br/><br/>
 
 
[[File:UI_Activity_UpdateViaOLEDB.PNG|440px|border|alt=Example Workflow|Example Workflow]]
 
[[File:UI_Activity_UpdateViaOLEDB.PNG|440px|border|alt=Example Workflow|Example Workflow]]
−
<br/><br/><br/>
 
−
 
−
 
  
−
[[Category:How-To]]
+
[[Category:How-To|Update Data via OLEDB with a Workflow]]
−
[[Category:Workflow]]
+
[[Category:OLE|Update Data via OLEDB with a Workflow]]
−
[[Category:UBIK Studio]]
+
−
[[Category:Activities]]
+
−
[[Category:Interfacing]]  
+
−
[[Category:OLE]]
+

Latest revision as of 09:18, 20 May 2015

Updating data in an Oracle or SQL database via a workflow can be done by using a set of activities.

[edit]

Instructions

  1. Establish a connection to an Oracle or SQL database. Once connected, it is necessary to model the table / column definitions, of the data to be udpated, in the workflow. Use CreateOLEOracleScope or CreateOLESQLScope to set up the connection.
  2. Place either a CreateOLETableScope (Activity) or a CreateOLEQueryScope (Activity) within the scope acticity to define the table you want to udate data.
  3. Iterate throw the fetched rows by using e.g. a for each activity.
  4. Once you determine the row to be updated, use EditOLERow (Activity) to start editing.
  5. Define the primary key column(s) by using EditOLEValue (Activity) activities within EditOLERow. You need to specify the column name in the DisplayName property and the data type in the DataType property. Check the IsPrimary checkbox for all columns defined in this step (key columns).
  6. Define the data column(s) to be updated by using EditOLEValue activities within the EditOLERow. Again, you need to specify the column name in the DisplayName property and the data type in the DataType property. Specify the new value for the cell in the property Value.
IC Attention.pngIf you are using a CreateOLEQueryScope to fetch data you want to update, you have to define a valid tablename!
IC Attention.pngThe primary key column must be defined when updating data via OLE DB.

Example

Connecting to an Oracle database and updating data.
IC FileLink Download.png Download Workflow example

Example Workflow

Retrieved from "https://wiki.augmensys.com/index.php?title=HowTo:Update_Data_via_OLEDB_with_a_Workflow&oldid=15639"