Data Connections

Re: Combining multiple secondary data sources

knudde — Wed, 27 Feb 2008 07:26:04 GMT

Hi Greg,

you got a point, but my first oracle Web service is already doing a 'select' across multiple tables.

The reason i had to go with the extra column and joins etc is because the table, containing the information on current machine assignments for a given user, is hosted on a different Oracle Server /Instance. I'm not aware of any Select statement that allows me to join information from 2 different tables on 2 different servers.

It's indeed slow(er), and ran into issues getting the InfoPath timeout settings correctly (it can take over 15 min due to the complex parent-child cascading involved)...

Or rather, issues finding the right syntax to set the timeout settings correctly. :O

I've appreciated your time and effort to give feedback

Re: Combining multiple secondary data sources

Greg Collins — Tue, 26 Feb 2008 23:13:46 GMT

Glad you got it working... but it seems as though there is unnecessarily a lot of extra work going on that shouldn't need to be.

I'm not familiar with Oracle, but the database should have some sort of a JOIN command that would allow you to get all the information you need with a single Web service, where it queries both tables, joining them together (if it even needs to do a JOIN and not just get a count), and return all the necessary data from a single Web service call.

Look into it... could be much easier in the long run and save some time by only making a single call rather than two.

Re: Combining multiple secondary data sources

knudde — Tue, 26 Feb 2008 14:24:42 GMT

Exactly, i got it working with calling on the other webservice

OLD CODE

---snip-----

// Create the adapter and fill the DataSet

System.Data.OracleClient.OracleDataAdapter adapter =

new System.Data.OracleClient.OracleDataAdapter(comm);

DataSet dsRates = new DataSet();

adapter.Fill(dsRates);

// Convert the DataSet into XmlNode

return DataSetToXmlNode(dsRates);

}

-----snip------

NEW CODE

---snip-----

// Create the adapter and fill the DataSet

System.Data.OracleClient.OracleDataAdapter adapter =

new System.Data.OracleClient.OracleDataAdapter(comm);

DataSet dsRates = new DataSet();

adapter.Fill(dsRates);

//Now select the table from the dataset and add a column in which we'll store the other lookup information'

DataTable Dt = dsRates.Tables[0];

Dt.Columns.Add("machines",Type.GetType("System.String"));

DataRowCollection tableRows=Dt.Rows; // tablerows in a arraycollection

int colteller = Dt.Columns.Count; //count number of columns

for (int teller= 0; teller < tableRows.Count; teller++)

{

DataRow myrow = tableRows[teller];

Object[] rowItems = myrow.ItemArray;

string Corp = Dt.Rows[teller][3].ToString(); // my lookup field to pass to the other Webservice sits in the 4th columnstring x = retrieve_machines(Corp); //here i call my other webservices with the Corporate_ID as a parameter and store the output in a string

Dt.Rows[teller][colteller-1] = x; // assign the string to the last column

}

// Convert the DataSet into XmlNode

return DataSetToXmlNode(dsRates);

---snip-----

tnxs for the feedback

Re: Combining multiple secondary data sources

Greg Collins — Tue, 26 Feb 2008 00:52:28 GMT

What you need to do is to have your Web service return the combined data. It should query your Oracle database, joining the two tables appropriately, BEFORE returning the data back to your form. That way, you only make a single WS call and it returns the combined data that you need to fill your table in one fell swoop.

Combining multiple secondary data sources

knudde — Sat, 23 Feb 2008 16:40:52 GMT

Hi,

I've got a couple of webservices running that i use to send and receive information from.

The are all connecting to an oracle database.

1st web service (VP): fetching all VP's for a given function ( filtering on Corporate ID (corpid))

2nd (fetchall) service is running an oracle query to fetch all the siblings from a given VP ( SELECT corpid,CN FROM *a table* CONNECT BY PRIOR corpid=reportsto START WITH corpid= :Parameter )

so far so good, i can do a drop down list in Infopath with all the VP's and then retrieve their CorpID and use an input parameter for the 2nd webservice (fetchall) to retrieve all the siblings.

If i drag and drop the secondary datasource (fetchall) onto infopath , it gives me a repeating table that i can then fill with a click on a button.

works as well, i can get a filled out table of about 1000 items in some cases, see screenshot below

now comes the tricky part.

Each of these employees has also a corpID(not visible on the tabled) , from that filled out table i want to perform a lookup onto another webservice (machines) that will take the corpid as input and return the current machines this particular user is using.

The goal is to post this information in the field after the "Sum" field, which is a (meaningless) field coming from the original fetchall datasource

a) how can i combine this in this repeating table? so 2 differerent secondary datasources glued together?

b) how can i instruct infopath to take, once the table has been filled in, from eachline the CorpID field and use it to query the secondary datasource (machines)?

for reference, heres the secondary datasource , taking corpid as input and return type and model)

Perhaps what i want to do (combine 2 different data sources into 1 repeating table (that is completely filled automatically- no "add new item' please- )) not possible at all?.

I rather know then instead of looking for a solution in vein

tnxs