Forum Discussion

didonato's avatar
didonato
Creator II
9 years ago

Federation Data

I'm trying to duplicate the code from an article that was posted in "Knowledge Base" called "Further Example of Federation". After following the instructions and changing the connection to my database I get a " [object Object]" error. Below is my method: Any help would be appreciated. Thanks   // 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 masterpart; masterpart = res.getProperty("item_number"); // Connect to external (in this case SQL Server) database System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(@"Provider=sqloledb; Data Source=server; Initial Catalog=Test; Integrated Security=SSPI;"); // construct sql Select statement to retrieve data from external database. string sqltext = "Select partnum,totalstock,descrip from Sales Where Partnum = '" + masterpart + "';"; 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",masterpart); // 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 Part Inventory 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;  

1 Reply

  • eli_donahue's avatar
    eli_donahue
    Community Manager
    9 years ago
    Hi didonato, One way to test your database connection is to set up a static sample with a known result, run the sample with the Run Server Event in the Aras Method Editor, and then confirm that the connection results match your expected result. For example, 
    
// for testing only
string masterpart = "SOME PART NUMBER";
// end testing value

// Connect to external (in this case SQL Server) database
System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(@”Provider=sqloledb; Data Source=server; Initial Catalog=Test; Integrated Security=SSPI;”);

// construct sql Select statement to retrieve data from external database.
string sqltext = “Select partnum,totalstock,descrip from Sales Where Partnum = ‘” + masterpart + “‘;”;
System.Data.OleDb.OleDbCommand com = new System.Data.OleDb.OleDbCommand(sqltext);

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

myDataReader.Read();
string res = myDataReader.ToString();
myConnection.Close();

return res;
    This should give you a starting point for debugging your code. If your database connection and SQL string are correct, your sample should return your expected result. If you don't get your expected result, you may need to tweak your connection string or your SQL query. Note - I haven't tested the above code, I just rewrote a piece of your original code into a smaller test. Eli
    Eli Donahue Aras Labs Software Engineer