This discussion has been locked.

You can no longer post new replies to this discussion. If you have a question you can start a new discussion

DEVELOPERS FORUM - Federated Data - remote data definition

Offline Archive User over 17 years ago

Brian - Tuesday, May 5, 2009 9:34 PM:

Hi All,

I have been working through the How To Use Federation wiki (link below)

http://www.aras.com/community/wikis/kb/how-to-use-federation.aspx

and have a specific question relating to the remote data definition.

In the example in the wiki the remote data tables are defined as

Remote table definitions

CREATE TABLE [dbo].[fed_ci_inventory](

[wh] [varchar](1) NULL,

[item_number] [nvarchar](7) NULL,

[qty_oh] [int] NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[fed_ci_order](

[ordno] [int] NULL,

[wh] [char](1) NULL,

[item_number] [varchar](8) NULL,

[qty_ord] [int] NULL,

[qty_rcd] [int] NULL

) ON [PRIMARY]

and table [dbo].[fed_ci_inventory] is referenced in the SQL item.

Is there somewhere in Innovator that this table reference is resolved to a "real" datasource or is this just a convenient definition for demonstration purposes?

Either way how do I define, in the SQL item or elsewhere, where the external data is and manage connection to it etc?

In the first instance I am looking to pick up data from an MS Access database on the company network and display it as Federated Data in Innovator.

Your assistance is appreciated.

Brian.

SamsAn - Wednesday, May 6, 2009 4:19 AM:

For
"In the first instance I am looking to pick up data from an MS Access database on the company network and display it as Federated Data in Innovator."

Options to implement.
1. Create Federated implementation type ItemType, add properties to be visible in items grid, give TOC Access. Assign server-side method as OnGet event. The method should connect to MS Access db, load appropriate data, convert the data to AML (special Aras XML) and return it. So, when a user clicks Run Search in items grid, your method is executed and returns appropriate data.
2. Create Table implementation type ItemType. Similar way as 1, but OnBeforeGet, OnAfterGet events should be implemented to retrieve data from Access db.

SamsAn.
Original Mind Any Level Innovator Solutions Free-Lancer, http://sites.google.com/site/caraacc

Brian - Wednesday, May 6, 2009 6:41 PM:

Thanks for the response.

If I understand what you have said here I can't use the SQL Item to retrieve the data from the remote data source.

Instead, in the onGet method I need to connect to the external DB via normal .Net type access, retrieve the recordset and then convert the recordset to AML and return it.

Since I've only been looking at Innovator for a few weeks and have not spent much of that time looking at the AML functions an actual example of code would really help.

If I can't use the SQL Item to retrieve data from the remote data source why is it in the wiki telling people how to use Federated Items? Or have I missed something here?

Brian.

SamsAn - Thursday, May 7, 2009 3:11 AM:

Nice catch.
It is possible to use SQL Item for your case too. You can call SQL Item (select_ci_inventory in wiki example) in OnGet event method, connect to MS Access in select_ci_inventory stored procedure and return appropriate recordset from the sp. You can see this way is more complicated.
I guess you are confused by converting the recordset to AML in the proposed options, but it is pretty easy. I believe OnGet event method can be implemented in several hours.

SamsAn.
Original Mind Any Level Innovator Solutions Free-Lancer, http://sites.google.com/site/caraacc

Brian - Thursday, May 7, 2009 6:46 AM:

OK. If it is possible to connect to the remote database inside the SQL Item can you give me an example of what that would look like?

Most of the onGet procedure I think I have worked out anyway but I am still having problems actually connecting to the DB. I get the error "The Microsoft Jet database engine cannot open the file. It is already opened exclusively by another user or you need permission to view the data."

My connection string in C# is

OldDbConnection com = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source = \Serverfile.mdb; Jet OLEDB:System Database = \Serversystem.mdw; User Id = userName; Password=pword;");

I know that the database is not open exclusively by anyone (because I just opened it using Access and then closed it again). I also know that the user and password that I have used is valid.

Any suggestions where I might be missing something?

Thanks,

Brian.

SamsAn - Thursday, May 7, 2009 11:45 AM:

I cannot give you an example since it will take some time. If you are going to use the way through SQL Item you should code connection and recordset retrieving using C#, compile dll, register the dll in the db, call the dll function inside the stored procedure concerned with SQL Item. I do not recommend to use this way in your case since it is simplier to avoid the dll manipulation using the ways proposed originally.

SamsAn.
Original Mind Any Level Innovator Solutions Free-Lancer, http://sites.google.com/site/caraacc

Brian - Thursday, May 7, 2009 11:32 PM:

I agree that registering the dll etc is more work than it needs to be thanks for that explanation.

Back to the more basic problem that I have.

I am coding the onGet method in C#. My connection string to the DB is:

OleDbConnection myConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source = \servernamepathdbname.mdb;");

When the method runs as a result of clicking the tab in the Part form I get the error:

The Microsoft Jet Database engine cannot open the file '\servernamepathdbname.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

I know that the database is not opened exclusively by anyone. I also know that I should not need any permissions to view this data since this is a new database I just created to try to investigate Federation.

I also know that I can access this database from another application using essentially identical code from a VBA macro running under MS Excel, thereby proving that it isn't open exclusively and that I don't need permission to view the data.

If I can't get past this issue then a whole chunk of functionality goes out the window as far as I am concerned.

There must be something that I am missing since this should not be hard to do.

Is it anything to do with being a web application trying to access a non-web enabled database?

Has anyone else encountered this sort of problem and if so what did you do to fix it?

I would much appreciate a code example that you know works or some suggestions on what else I might try to make this work.

Thanks,

Brian.

RobMcAveney - Friday, May 8, 2009 12:43 AM:

Hi Brian -

The following code works fine for me. I just created a new Method with this code, saved and ran it via Actions->Run Server Method. Try adapting the path/table name and running it yourself.

System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source = c:db1.mdb;");
string myInsertQuery = "SELECT * FROM Table1";
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(myInsertQuery);
myCommand.Connection = myConnection;
myConnection.Open();
System.Data.OleDb.OleDbDataReader myReader = myCommand.ExecuteReader();
string res = "";
while (myReader.Read()) { res += myReader.GetString(1); }
myCommand.Connection.Close();
return this.getInnovator().newError(res);

My guess is that you have some sort of domain permission issue with your Access DB. Try putting the MDB file on your server and connecting with a local path, then work your way up to the OnGet from there.

Rob

Brian - Friday, May 8, 2009 1:01 AM:

Hi Rob,

Thanks for the sample code. I tried it and still get file access issues.

I had just about decided that it must be a network permissions issue. This is the first time we have tried to connect a web app to a network database not on the web server.

At least I know that the issue is not with the basic code and can now start looking somewhere else.

Regards,

Brian.

Brian - Tuesday, May 12, 2009 1:28 AM:

I put a copy of the DB on the server and can access the data. Therefore you are correct and it was a permissions issue. I can work through that later.

I have created a Federated Item Type called "Part Inventory" and given it two visible properties part_number and on_hand_inventory. It is these that I want to display in the "Part Inventory" relationship tab of the Part form.

In the onGet method I can:

1) Retrieve the Item_number of the part to lookup in the remote database

2) Connect to the remote DB (finally)

3) Execute a simple Select query

4) Confirm that the result of the query has the data that I expect in it

5) Extract the data set from the OleDbDataReader

When I get to returning the Part Inventory item I get nothing back.

I suspect that I am not forming the Part Inventory item correctly.

This is a simple test that I am using to see what happens without all of the remote data access. I am just trying to hard code some values into the Part Inventory item to see what I am getting.

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

qry32.setProperty("part_number","000-0004");

qry32.setProperty("on_hand_inventory","1000");

Item myres3 = qry32.apply();

return myres3;

string result = myres3.getProperty("part_number");

return this.getInnovator().newError(result);

If I try the first "return" (return myres3;) then the grid gets nothing back and appears to be completely empty (not even a border).

If I try to force the issue to see what is in myres3 (2nd return) I get the error "Not a single item"

Do I need to fill in all of the "required" items for the Part Inventory item or is there something else blindingly obvious that I am not doing?

Thanks,

Brian.

Step by step. Inch by inch. At least I know that when I get there I will understand everything about it.

RobMcAveney - Tuesday, May 12, 2009 2:25 AM:

I can't look at this in detail at the moment, but on first glance it looks like the problem is probably that you're using apply(). That is what sends the request to the Aras Innovator server for processing, which you usually don't want to do in an OnGet. Instead, try something like this:

Item qry32 = this.newItem("Part Inventory");
qry32.setProperty("part_number","000-0004");
qry32.setProperty("on_hand_inventory","1000");
return qry32;

This should be enough to get an AML response, but I'm not sure what the minimum would be to have a row appear in the search grid. You' probably need an id, keyed_name, etc. but I'll have to look into what the minimum is. Try using the nash client (server/.../nash.aspx) to submit an AML request like this:

That should get you a response to build on. I will look into minimum to show up in the UI tomorrow.

Rob

SamsAn - Tuesday, May 12, 2009 2:44 AM:

OnGet method sample returned aml data is below.

Item res = this.getInnovator().newResult("");

XmlElement resultNd = (XmlElement) res.dom.SelectSingleNode("/*/*/*");

Item currItm = this.newItem();

currItm.loadAML("<Item type='Part Inventory'></Item>");

for (int i=0; i<2; i++)

{

//+++++ Form data to be returned to the Innovator Client

string id = this.getInnovator().getNewID();

currItm.setID(id);

currItm.setProperty("part_number", "Part Number from MS Access DB, item "+i.ToString());

currItm.setProperty("prop1_name", "Value for property 1, item "+i.ToString());

currItm.setProperty("prop2_name", "Value for property 2, item "+i.ToString());

resultNd.AppendChild(resultNd.OwnerDocument.ImportNode(currItm.node, true));

//----- Form data to be returned to the Innovator Client

}

return res;

SamsAn.
Original Mind Any Level Innovator Solutions Free-Lancer, http://sites.google.com/site/caraacc

Brian - Tuesday, May 12, 2009 7:52 AM:

Thanks for both responses.

The code that SamsAn provided does work and with it I can get the remote data to display in the search grid.

So it seems that the only other required field is the "id" for the record to be displayed.

I am unsure why the code the Rob supplied, when the "id" is added does not show the same result. (It seems I don't have the nash.aspx client available so I can't really see the aml as it is being formed.)

I am assuming that the Item res = newItem("Part Inventory"); code should provide well formed aml for a Part Inventory item type and that setting the properties for "id", "part_number" and "on_hand_inventory" should be sufficient.

Thanks for you assistance with this.

If you have time I would appreciate some followup on why the latter code does not work.

Regards,

Brian.

PeterSchroer - Tuesday, May 12, 2009 8:05 AM:

Brian,

Rob had a typo in the URL to NASH, try server/.../nash.aspx the Scripts folder was missing fro the url.

Peter

Brian - Tuesday, May 12, 2009 10:04 PM:

Hi All,

Thanks for the help over the last few days with this. I really do appreciate it.

As I reported earlier the code which SamsAn provided does work. It seemed a bit cumbersome to me and I thought that I should be albe to build an AML Item using the IOM code without resorting to directly manipulating XML.

Rob was on the right track with his code but we do need to call item.apply() to have the server create the new Item Type. Once that has been called we can the use the setProperty methods to further populate the AML before returning it to the search grid.

The complete code that I am using for this simple onGet is:

// 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;

string partnum;

partnum = res.getProperty("item_number");

// Connect to MS Access database. Database is located somewhere the InnovatorServer has

// access to. In this case on the InnovatorServer box itself.

System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source = d:db1.mdb;");

string sqltext = "Select * from table1 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 the new "Part Inventory" item which is to be returned to the search grid.

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

string newid = getNewID();

res5.setID(newid);

// Call .apply() now to create the item. Then we can further populate the other

// properties that we want to return to the search grid.

res5 = res5.apply();

res5.setProperty("part_number",partnum);

if (myDataReader.HasRows != true)

{

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

}

else

{

myDataReader.Read();

res5.setProperty("on_hand_inventory",Convert.ToString(myDataReader.GetInt32(2)));

};

myConnection.Close();

return res5;

This works just the way that I expect.

Thanks again for your assistance.

Regards,

Brian.

RobMcAveney - Tuesday, May 12, 2009 11:16 PM:

Hi Brian -

Good work! Glad to see you're making progress with the platform.

This is just a thought, but if you are only populating one property on Part Inventory, you might consider using a federated Property on Part instead of a federated ItemType. In this scenario, you would just create an Inventory property on the existing Part Item and populate it in much the same way you're doing here, only with an OnAfterGet instead of an OnGet. Your code would just loop through the Part items in the response, get the inventory for each from your Access db and set the property value on each item. Here is some sample code:

for (int i=0; i<this.getItemCount(); i++) {
string thisPartNum = this.getItemByIndex(i).getProperty("item_number","");
int inventory = i; // replace this line with the code to retrieve the value from the external db
this.getItemByIndex(i).setProperty("inventory",Convert.ToString(inventory));
}
return this;

There would of course be some performance implications to querying your inventory db every time someone searches for Parts, but there are ways to minimize the effects. The advantage is that inventories appear to be just another property on the Part form and not a separate query on another screen. Let me know if you're interested in this approach and we can work through some design options.

Rob

krish80 - Monday, December 13, 2010 5:48 AM:

Hi,

How to get the data's from Excel. i tried with the below modification. i am getting the error "No Value Given for one or more Required Parameters".

Regards,

string ConnectionString =@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:13dec2010.xls;Extended Properties=Excel 5.0";

System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);

string ExcelQuery = "Select A1 from [Sheet1$]";// where partnum='"+partnum+"'"; // from Sheet1";//where partnum='"+partnum+"'"

System.Data.OleDb.OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand(ExcelQuery,ExcelConnection);

ExcelConnection.Open();

System.Data.OleDb.OleDbDataReader myDataReader;

myDataReader = ExcelCommand.ExecuteReader();

Brian - Tuesday, May 12, 2009 11:41 PM:

Hi Rob,

Thanks for the suggestion. At this stage I am just exploring the mechanics of using Federated data as a means of exposing more information from diverse data sources in the Innovator UI.

I will keep your suggestion in mind as I can see some advantages in presenting data that way.

Among other things I can see building a "Request To Purchase" system including workflows/authorisations to dovetail with an ERP system. In order to do that I need to be able to use Federated Data.

Regards,

Brian.

Brian - Monday, December 13, 2010 5:57 PM:

Hi RK,

The problem seems to be with the Excel Query:

"Select A1 from [Sheet1$]" produces the error.

"Select * from [Sheet1$]" does not.

Using your code and changing the "A1" to "*" I was able to read all of the data in a test excel sheet.

Hope this helps.

Brian.

DEVELOPERS FORUM - Federated Data - remote data definition

Remote table definitions

Legal

Connect With Us