Further Example of Federated Data

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

// Called from ServerEvents of Federated Item ItemType "Part Inventory"

// Connects to external database via OleDb calls

// Populates on_hand_inventory and db_description fields of federated item

// This method updates the grid for the relationship item and is implemented

// as an onGet ServerEvent.

// Get the item_number, it's not passed automatically

Item qry0 = this.newItem("Part");

qry0.setAttribute("select","item_number");

qry0.setID(this.getProperty("source_id"));

Item res=qry0.apply("get");

if (res.isError()) return res;

// get the Part Number (key) that we will use in the external database

// to search on.

string partnum;

partnum = res.getProperty("item_number");

// Connect to external (in this case MS Access) database

System.Data.OleDb.OleDbConnection myConnection = newSystem.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source =\\server\directory\database.mdb;");

// construct sql Select statement to retrieve data from external database.

string sqltext = "Select Partnum,totalstock,descrip from Parts Where Partnum = '" +partnum + "';";

System.Data.OleDb.OleDbCommand com = new System.Data.OleDb.OleDbCommand(sqltext);

com.Connection = myConnection;

myConnection.Open();

System.Data.OleDb.OleDbDataReader myDataReader = com.ExecuteReader();

// create a Part Inventory item

Item res5 = this.newItem("Part Inventory");

string newid = getNewID();

res5.setID(newid);

// Call apply on the item to create it in the server. After this is done

// we can populate with the returned information from the external database

res5 = res5.apply();

res5.setProperty("part_number",partnum);

// Check if there are any rows returned and supply default information

// if they are not

if (myDataReader.HasRows != true)

{

res5.setProperty("on_hand_inventory","0");

res5.setProperty("db_description","Not Found");

}

else

{

// get the data from the dataReader and populate the PartInventory item>

myDataReader.Read();

res5.setProperty("on_hand_inventory",Convert.ToString(myDataReader.GetDouble(1)));

res5.setProperty("db_description",myDataReader.GetString(2));

};

myConnection.Close();

// return the PartInventory item to be populated in the grid.

return res5;

 

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

Leave a Reply