Further Example of Federated Data

Brian Pye — Tue, 09 Jun 2009 01:18:50 GMT

Current revision posted to Aras Innovator KnowledgeBase by Brian Pye on 6/8/2009 9:18:50 PM

Further Example of Federated Data

Filed under: Federation

Further to the main article on How To Use Federation following is a simple implementation of using Federated Data from an External Database (in this case an MS Access Database) and displaying it in a Relationship Tab.

Use Case:

We wish to display on hand inventory and part description information from an external database as a tab of the Part ItemType.

The external database is located somewhere that the Innovator Server has network access to (in the simplest case this is actually on the same server as Innovator Server).

The Part Inventory relationship will be added to the Part ItemType.

When a user selects the tab a connection to the external database will be made a on hand inventory and part description information will be displayed.

Innovator Items:

ItemType Part
- Implementation: Table
RelationshipType Part Inventory
- Source: Part
- No Related
- Relationship ItemType Implementation: Federated - No data stored in Innovator
- ServerEvents : onGet
- Properties:
  - db_description = String, 100 char
  - on_hand_inventory = Integer
  - part_number = String, 32 char

Implementation of Example:

An OleDb interface will be used as this is an external MS Access database.

The remote database contains a table "Parts" which inclues the following fields:

Partnum - Part number of item. This is the key we will search on and is the same as the values in the Part ItemType in Innovator.
TotalStock - Summary of all stock in all locations that stock might be held in.
Descrip - Description of the part.

Part Inventory, ServerEvent, onGet:

// GetOnHandInventory<o:p></o:p>

// Called from ServerEvents of Federated Item ItemType "Part Inventory"<o:p></o:p>"

// Connects to external database via OleDb calls<o:p></o:p>

// Populates on_hand_inventory and db_description fields of federated item<o:p></o:p>

// This method updates the grid for the relationship item and is implemented<o:p></o:p>

// as an onGet ServerEvent.<o:p></o:p>

// Get the item_number, it's not passed automatically<o:p></o:p>

Item qry0 = this.newItem("Part");<o:p></o:p>

qry0.setAttribute("select","item_number");<o:p></o:p>

qry0.setID(this.getProperty("source_id"));<o:p></o:p>

Item res=qry0.apply("get");<o:p></o:p>

if (res.isError()) return res;<o:p></o:p>

// get the Part Number (key) that we will use in the external database<o:p></o:p>

// to search on.<o:p></o:p>

string partnum;<o:p></o:p>

partnum = res.getProperty("item_number");<o:p></o:p>

<o:p></o:p>

// Connect to external (in this case MS Access) database<o:p></o:p>

System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source = \\server\directory\database.mdb;");<o:p></o:p>

// construct sql Select statement to retrieve data from external database.<o:p></o:p>

string sqltext = "Select Partnum,totalstock,descrip from Parts Where Partnum = '" + partnum + "';";<o:p></o:p>

<o:p></o:p>

System.Data.OleDb.OleDbCommand com = new System.Data.OleDb.OleDbCommand(sqltext);<o:p></o:p>

com.Connection = myConnection;<o:p></o:p>

myConnection.Open();<o:p></o:p>

System.Data.OleDb.OleDbDataReader myDataReader = com.ExecuteReader();<o:p></o:p>

<o:p></o:p>

// create a Part Inventory item<o:p></o:p>

Item res5 = this.newItem("Part Inventory");<o:p></o:p>

string newid = getNewID();<o:p></o:p>

res5.setID(newid);<o:p></o:p>

// Call apply on the item to create it in the server. After this is done<o:p></o:p>

// we can populate with the returned information from the external database<o:p></o:p>

res5 = res5.apply();<o:p></o:p>

res5.setProperty("part_number",partnum);<o:p></o:p>

<o:p></o:p>

// Check if there are any rows returned and supply default information <o:p></o:p>

// if they are not<o:p></o:p>

if (myDataReader.HasRows != true)<o:p></o:p>

{<o:p></o:p>

res5.setProperty("on_hand_inventory","0");<o:p></o:p>

res5.setProperty("db_description","Not Found");<o:p></o:p>

}<o:p></o:p>

else<o:p></o:p>

{<o:p></o:p>

// get the data from the dataReader and populate the PartInventory item<o:p></o:p>>

myDataReader.Read();<o:p></o:p>

res5.setProperty("on_hand_inventory",Convert.ToString(myDataReader.GetDouble(1))); <o:p></o:p>

res5.setProperty("db_description",myDataReader.GetString(2));<o:p></o:p>

};<o:p></o:p>

myConnection.Close();<o:p></o:p>

<o:p></o:p>

// return the PartInventory item to be populated in the grid.<o:p></o:p>

return res5;<o:p></o:p>

Once the connection is open it is of course possible to update the database with new information from Innovator.

Further Example of Federated Data

Brian Pye — Tue, 09 Jun 2009 01:09:43 GMT

Revision 1 posted to Aras Innovator KnowledgeBase by Brian Pye on 6/8/2009 9:09:43 PM

Use Case:

We wish to display on hand inventory and part description information from an external database as a tab of the Part ItemType.

The external database is located somewhere that the Innovator Server has network access to (in the simplest case this is actually on the same server as Innovator Server).

The Part Inventory relationship will be added to the Part ItemType.

When a user selects the tab a connection to the external database will be made a on hand inventory and part description information will be displayed.

Innovator Items:

ItemType Part
- Implementation: Table
RelationshipType Part Inventory
- Source: Part
- No Related
- Relationship ItemType Implementation: Federated - No data stored in Innovator
- ServerEvents : onGet
- Properties:
  - db_description = String, 100 char
  - on_hand_inventory = Integer
  - part_number = String, 32 char

Implementation of Example:

An OleDb interface will be used as this is an external MS Access database.

The remote database contains a table "Parts" which inclues the following fields:

Partnum - Part number of item. This is the key we will search on and is the same as the values in the Part ItemType in Innovator.
TotalStock - Summary of all stock in all locations that stock might be held in.
Descrip - Description of the part.

Part Inventory, ServerEvent, onGet:

// GetOnHandInventory<o:p></o:p>

// Called from ServerEvents of Federated Item ItemType "Part Inventory"<o:p></o:p>

// Connects to external database via OleDb calls<o:p></o:p>

// Populates on_hand_inventory and db_description fields of federated item<o:p></o:p>

// This method updates the grid for the relationship item and is implemented<o:p></o:p>

// as an onGet ServerEvent.<o:p></o:p>

// Get the item_number, it's not passed automatically<o:p></o:p>

Item qry0 = this.newItem("Part");<o:p></o:p>

qry0.setAttribute("select","item_number");<o:p></o:p>

qry0.setID(this.getProperty("source_id"));<o:p></o:p>

Item res=qry0.apply("get");<o:p></o:p>

if (res.isError()) return res;<o:p></o:p>

// get the Part Number (key) that we will use in the external database<o:p></o:p>

// to search on.<o:p></o:p>

string partnum;<o:p></o:p>

partnum = res.getProperty("item_number");<o:p></o:p>

<o:p></o:p>

// Connect to external (in this case MS Access) database<o:p></o:p>

System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source = \\server\directory\database.mdb;");<o:p></o:p>

// construct sql Select statement to retrieve data from external database.<o:p></o:p>

string sqltext = "Select Partnum,totalstock,descrip from Parts Where Partnum = '" + partnum + "';";<o:p></o:p>

<o:p></o:p>

System.Data.OleDb.OleDbCommand com = new System.Data.OleDb.OleDbCommand(sqltext);<o:p></o:p>

com.Connection = myConnection;<o:p></o:p>

myConnection.Open();<o:p></o:p>

System.Data.OleDb.OleDbDataReader myDataReader = com.ExecuteReader();<o:p></o:p>

<o:p></o:p>

// create a Part Inventory item<o:p></o:p>

Item res5 = this.newItem("Part Inventory");<o:p></o:p>

string newid = getNewID();<o:p></o:p>

res5.setID(newid);<o:p></o:p>

// Call apply on the item to create it in the server. After this is done<o:p></o:p>

// we can populate with the returned information from the external database<o:p></o:p>

res5 = res5.apply();<o:p></o:p>

res5.setProperty("part_number",partnum);<o:p></o:p>

<o:p></o:p>

// Check if there are any rows returned and supply default information <o:p></o:p>

// if they are not<o:p></o:p>

if (myDataReader.HasRows != true)<o:p></o:p>

{<o:p></o:p>

res5.setProperty("on_hand_inventory","0");<o:p></o:p>

res5.setProperty("db_description","Not Found");<o:p></o:p>

}<o:p></o:p>

else<o:p></o:p>

{<o:p></o:p>

// get the data from the dataReader and populate the PartInventory item<o:p></o:p>

myDataReader.Read();<o:p></o:p>

res5.setProperty("on_hand_inventory",Convert.ToString(myDataReader.GetDouble(1))); <o:p></o:p>

res5.setProperty("db_description",myDataReader.GetString(2));<o:p></o:p>

};<o:p></o:p>

myConnection.Close();<o:p></o:p>

<o:p></o:p>

// return the PartInventory item to be populated in the grid.<o:p></o:p>

return res5;<o:p></o:p>

Once the connection is open it is of course possible to update the database with new information from Innovator.