update function in logical data source does not get called. - Data Service Integrator

I created a logicial datasource that contains two functions, one read and one update. The test runs and returns the expected values, however the update is not actually updating the database. Any reason this might occur? Thanks. 

A common problem with this issue is that the datagraph that the client is sending does not indicate an update. Search for : testing update in ALDSP edocs, somewhere around the third search result there will be a document describing how to test updates - including how to capture datagraphs generated by the Test View. (search is broken at this moment, otherwise I'd give you the link ) Then you can make changes in the Test View, click on "Submit" and you will see the corresponding datagraph to make that change.
At the very least, the datagraph will need an 'old value' in the changeSummary. For instance, the datgraph to change a customers first name from John to Jonathan would look something like ...
<datagraph>
...
<changeSummary ... >
<FIRST_NAME>John</FIRST_NAME>
</changeSummary>
<CUSTOMER>
<LAST_NAME>Smith</LAST_NAME> <!-- all the new/current values -->
<FIRST_NAME>Jonathan</FIRST_NAME> <!-- the one that was changed - there is an 'old value' for it -->
...
</CUSTOMER>
</datagraph>

Related

QUERIES ON ODI XML

Dear Gurus,I have followed many ODI blogs regarding XML_to_RT or RT_To_XML interfaces (RT: Relational Table).Now some questions came into mind . could you please clarify that?1. For 'XML to RT' Interface we select 'In-Memory Engine:Sunopsis_Memory_Engine' as staging area and why not the same is required for 'RT_TO_XML' type interfaces?2. For 'XML to RT' to see the temp table (I$,C$ etc) how can we access the 'In-Memory Engine:Sunopsis_Memory_Engine' ? what type of DB it is?3. Most cases I have seen an extra attribute 'ORDER' (eg- CLIENT_ODERDER,NAME_ORDER). What is significance of this ORDER attribute?   why value of it is not unique?    because some times I have seen different ODRDER attributes has same value in mapping section (eg- CLIENT_ODERDER 2,NAME_ORDER 2).   How it is possible ?ThanksAnindya
Hi Anindya Chatterjee90, 
Anindya Chatterjee90 wrote:
 
1. For 'XML to RT' Interface we select 'In-Memory Engine:Sunopsis_Memory_Engine' as staging area and why not the same is required for 'RT_TO_XML' type interfaces?
 I never do that. I directly use an LKM SQL to Oracle from my XML technology to Oracle technology. I prefer to do all my transformation into the Oracle database instead of in the agent memory. 
Anindya Chatterjee90 wrote:
 
2. For 'XML to RT' to see the temp table (I$,C$ etc) how can we access the 'In-Memory Engine:Sunopsis_Memory_Engine' ? what type of DB it is?
 It is an HSQL database, in the memory of the ODI agent. I don't think you can easily see the content of these tables. 
Anindya Chatterjee90 wrote:
 
3. Most cases I have seen an extra attribute 'ORDER' (eg- CLIENT_ODERDER,NAME_ORDER). What is significance of this ORDER attribute?
  why value of it is not unique?
  because some times I have seen different ODRDER attributes has same value in mapping section (eg- CLIENT_ODERDER 2,NAME_ORDER 2).
  How it is possible ?
The order is reflecting in which position the element appeared in the XML  file.for instance if you have the following xml file, the bar elements will been ordered following their appearance. The element with text Hello will be order 1, World will be order 2 :
<foo fid=1>
  <bar>Hello</bar>
  <bar>World</bar>
</foo>
<foo fid=2>
  <bar>Not the same parent</bar>
  <bar>So we start counting from 1</bar>
</foo>
 The reason why you sometimes see the same order is because it's linked to different parent element.For instance here Not the same parent and So we start counting from 1 elements are linked to another foo element, so they will have another foreign key. Hope it helps. Regards,JeromeFr
Anindya Chatterjee90 wrote: 2. For 'XML to RT' to see the temp table (I$,C$ etc) how can we access the 'In-Memory Engine:Sunopsis_Memory_Engine' ? what type of DB it is?Many thanks Jerome it is really helpful However do you have reference guide to verify internal table in 'In-Memory Engine:Sunopsis_Memory_Engine'
Unfortunately I don't. And I don't think it's possible but I would love to be proven wrong on this one .
Hi Jerome, what i did to see those $ table in Memory Engine is created a physical schema for memory engine technology in topology and selected defaulted Memory engine logical schema and then created  a model and then reverse engineered..There I see all tables created in memory engine. RegardsASP
That's clever!Thanks for sharing
Here the screenshots
Wow great thanks ASP

have DS complexType definitions separated and xs:includable

Hi all,
We a have highly volatile domain schema that is expected to so continue. When we inevitably have to "Update Metadata" we are forced to change all the schemas for all the logical DSs affected by the physical DS schema update. Even if we merely include one DS's content into another. That hampers our development a lot.
Let me boil it down. Given a DS whose main read function is:
declare function tns:getAll() as element(v0_1:OurElement)*{
    for $AnotherElem in rec:getAll()
    where $AnotherElem/Code eq "whatever"
    return
<v0_1:OurElement>
        <Id>{fn:data($AnotherElem/Id)}</Id>
        <Titulo>{fn:data($AnotherElem/Titulo)}</Titulo>
         {
             for $YetAnother in liv:getAll()
             where $YetAnother/AnotherElemId eq $AnotherElem/Id
             return
                  $YetAnother
         }
</v0_1:OurElement>
};(OurElement is composed of (parts of) AnotherElem with one YetAnother mounted as is on.)
ODSI generates an XSD for OurElement that has inline (thus copied) the complexType definition for the YetAnother element of type (let's say) YetAnotherType.
What we want is to have a way for ODSI to produce the schema such that OurElement xs:includes YetAnother.xsd and the xs:element named YetAnother is of type="YetAnotherType" (the name of the complexType defined in YetAnother.xsd). So that when YetAnotherType inevitably changes we need not keep track of all its dependencies and change their schemas accordingly.
Seems too obvious a feature to be missing but we couldn't find anything in the documentation.
Help?
Thanks
(also as SR:7681365.992)
Edited by: vlf on Jul 10, 2009 9:53 AM 
we are forced to change all the schemas for all the logical DSs affected by the physical DS schema updateWhy don't you - one, time - for once and for all - modify the ODSI generated schemas that have in-lined the YetAnother definition to use an include? And then when you do "update Metadata", request that the schema not be automatically updated (I believe there are check-boxes for this).
- Mike 
Yes Mike, that would work.
In that scenario we're managing ourselves all the type definitions, and whenever we change something in the primary read function/underlying data source we lose the ability to just "Save and Associate the return type", having to edit the schema manually. That's just telling ODSI to don't even try to care about schema definitions (aside from validations & etc). We'd probably be better than now, but not as good as we would like.
If there is no support in ODSI for the complexTypes-separated "development model", we will probably do it as you suggest.
(Should I consider your input as an un-official "no, ODSI doesn't do that"?)
Thanks for the input. 
In that scenario we're managing ourselves all the type definitions,No, the schema referenced by the physical data service (say physical/schemas/YetAnother.xsd) would be updated by Update Metadata, and whatever included that schema would not need to be changed.
logical/schemas/Yet_Another.xsd
xs:include schemaLocation=physical/schemas/YetAnother.xsd
<element name="yan" type="YetAnother"/>
<element name="otherStuff" .../>
So any data service that uses logical/schemas/Yet_Another.xsd would not need to change.
The only time that you would need to "Save and Associate Return Type" is if you changed something outside of the modified xsd (like otherStuff). And there is no way to tell ODSI to not completely rewrite Yet_Another.xsd the way you want it rewritten. If you change otherStuff, you would have to manually edit Yet_Another.xsd 
>
No, the schema referenced by the physical data service (say physical/schemas/YetAnother.xsd) would be updated by Update Metadata, and whatever included that schema would not need to be changed.
>
Correct. That's the only case, however. And unfortunately of no use to us, as we we expand the mappings and translate UPPERCASE_TABLE_NAMES to CamelCase in the logical layer and thus cannot just include the physical ones.
>
The only time that you would need to "Save and Associate Return Type" is if you changed something outside of the modified xsd (like otherStuff). And there is no way to tell ODSI to not completely rewrite Yet_Another.xsd the way you want it rewritten. If you change otherStuff, you would have to manually edit Yet_Another.xsd
>
Exactly. This case is not that uncommon for us. And it was exactly this kind of awareness, this "not overwriting an XSD in the xs:include-style with one in the inline style" that we wish ODSI could do.
Thanks, Mike.
Closing the SR. 
And unfortunately of no use to us, as we we expand the mappings and translate UPPERCASE_TABLE_NAMES to CamelCase in the logical layer and thus cannot just include the physical ones.If you are translating the names to CamelCase, you will need a separate element with names in CamelCase. And if you define that element in a separate .xsd (created by Save And Associate Return Type), modification of the original YesAnother.xsd will not affect your xsd containing the CamelCase names.

Accessing data from schema when using ftp adapter

Hi all,
Below is an example of the data i receive from my ftp adapter, i read the entire file into my schema:
<Header>
<batch_filler1>*BATCH</batch_filler1>
<batch_number>08110</batch_number>
<batch_date>20090417</batch_date>
<batch_extra>KW1310</batch_extra>
</Header>
-<Detail>
<cost_centre_comment>W1310</cost_centre_comment>
<cost_centre_code_comment>01310</cost_centre_code_comment>
<amount>-3000.00</amount>
<filler1/>
<payment_method>T</payment_method>
<description>TSHWANE MULTI SKILL FEATURE CONTRACTORS</description>
<receipt_number>338516</receipt_number>
<date_comment>20090417</date_comment>
<bill_to_cust_id>11748885</bill_to_cust_id>
<ref_value>999999</ref_value>
<detail_extra/>
*</Detail>*
-<Detail>
<cost_centre_comment>W1310</cost_centre_comment>
<cost_centre_code_comment>01310</cost_centre_code_comment>
<amount>-1870.00</amount>
<filler1/>
<payment_method>T</payment_method>
<description>UNIVERSITY OF SOUTH UNIVERSITY SOOUTH AF</description>
<receipt_number>338517</receipt_number>
<date_comment>20090417</date_comment>
<bill_to_cust_id>10414916</bill_to_cust_id>
<ref_value>048630</ref_value>
<detail_extra/>
*</Detail>*
-<Detail>
<cost_centre_comment>W1310</cost_centre_comment>
<cost_centre_code_comment>01310</cost_centre_code_comment>
<amount>-300.00</amount>
<filler1/>
<payment_method>T</payment_method>
<description>VET & AGRIC CONSULTA RB JACKSON</description>
<receipt_number>338549</receipt_number>
<date_comment>20090417</date_comment>
<bill_to_cust_id>11462639</bill_to_cust_id>
<ref_value>048893</ref_value>
<detail_extra/>
*</Detail>*
-<Trailer>
<cost_centre>W801408014</cost_centre>
<amount>18467.13</amount>
<trailer_extra>GROOTTOTAAL VIR W8014/8014 OP 20090417 BONDEL 08110 08110 20090417</trailer_extra>
</Trailer>
As you can see i have multiple data for Detail, i need to loop through my schema for Detail and take the values and pass it on. How do you reference a particular Detail in bpel ?
An example would be if i want the 2nd Detail amount which is -300.00, do i reference it with a [1] as in arrays ?
Thanks 
it depends on what you want to do. Generally waht people do is map to a partner link, e.g. another service. In most situations the same structure is required for the target.
The transform activity is the best way to many multi nodes. You have the choose / if conditions that allow you to test conditions, if they match then you perform the map.
One thing that catches people out is the use of the for-each, if this is not specified only one row is mapped.
http://download-uk.oracle.com/docs/cd/B31017_01/integrate.1013/b28987/phase6.htm#sthref405
cheers
James 
Thanks,
But what i want to do is, for the Detail, i want to send the data as an instance for each Detail, so if i have 4 Details, then i want to have 4 instances in which i will pass the data for each Detail to be processed 
Simplest way to do this is in the FTP adapter wizard set the message count to 1. This will create a message / instance for every detail.
cheers
James

OSB - XML Transformation.

Hello guys...
Here again with new questions...
scenario
I have a WS request using this format...
-----
<myrequest>
<requestid>string</requestid>
<reason>string</reason>
<!-- 1 to 100 occurrences -->
<person>
<firstname>string</firstname>
<middlename>string</middlename>
<surname>string</surname>
</person>
</myrequest>
-----
and a response using this format.
-----
<myresponse>
<requestid>string</requestid>
<!-- 1 to 100 occurrences -->
<failed>
<firstname>string</firstname>
<middlename>string</middlename>
<surname>string</surname>
</failed>
</myresponse>
-----
What I'm doing is...
1- iterating my person list
2- validating the person list against my schema.
3- if the person is not valid for whatever reason
What I want to do
3- if the person is not valid for whatever reason (done)
3.1- remove from my list
3.2- add a <failed> block in my response.
So far, I am in my request pipeline inside an if else action with access to the body, a variable holding the current node.
I believe I have to create something (?!?!) to hold my failed list, and in my response pipeline, use this something (+_+) to change my response...
Thank you guys in advance... 
Hi Manoel,
Store your response in a variable called resp. Intialize it with value
<myresponse>
<requestid>string</requestid>
</myresponse>
Now,
3- if the person is not valid for whatever reason (done)
3.1- remove from my list
3.2- add a <failed> block in my response.
Above should be like -
3- if the person is not valid for whatever reason (done)
3.1- add a <failed> block in my response.
3.2- remove from my list
For 3.1 use insert action and insert <failed>{$currentNodeContent}</failed> as last child of $resp
For 3.2 use delete action to remove the current node from list
All above needs to be done inside loop only. To know more about insert and delete actions please refer -
http://download.oracle.com/docs/cd/E14571_01/doc.1111/e15867/proxy_actions.htm#CIHEFDFB
Regards,
Anuj 
in the request pipeline in the for-each you're currently doing
if the person fails concat/append it to $failed_list
if person validates ok concat/append it to $ok_list
at the end before the routing to a replace on the body with the $ok_list and in the response pipeline use the $failed_list to construct the response body.
something like this ? 
Ya, I've used the OSB documentation, but it's a bit helpless... I believe it's missing some examples...
I liked Eric's solution... much easier than remove from the request itself...
Anyway,
thank you guys...

Write back template error in obiee 12.2.1.0.0

Hello all, Logged in as weblogic and even tried with another obiee admin user. Write back template code<?xml version="1.0" encoding="utf-8" ?><WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1"><WebMessageTable lang="en-us" system="WriteBack" table="Messages">   <WebMessage name="control_table">      <XML>         <writeBack connectionPool="PPRD1">            <insert> </insert>            <update>update Table1 set name=‘#2’,Amount=Amount1,amount1=‘#3’ where id = #1 </update>         </writeBack>      </XML>   </WebMessage></WebMessageTable></WebMessageTables>        Presentation Services log
I would like you to check if you have ' or ’ in the XML file. When you copy paste sometime it happens.     <update>update Table1 set name=‘#2’,Amount=Amount1,amount1=‘#3’ where id = #1 </update> Thanks.
Also Check the following ..if  Execute direct request is set as "allow". Also need to add access from managed privilege screen.  User also need access to the following …  + Write back database subject area, if any… ThanksArijit
You have probably checked this, but just in case, can you also confirm that the user your service runs as as the necessary rights on the file itself.
Hi, as I saw your first image issue that is because that OBIEE doesnt regcognize the template, make you sure that you XML TEMPLATE IS  <obiee_home>/user_projects/domains/bi/bidata/service_instances/ssi/metadata/content/msgdb/l_en/   after it you should to provedide the proper privileges as the previous comments. there is a detail post about that in this link Configuring Write Back in OBIEE12c - Red Stack Tech 
I have used the redstck blog to configure it. But it is not resolved yet. Direct DB request is allowed. As Ceasar said error message says template not recognized. Any help will be highly appreciated ..
Try the following, reproduce your update code between the insert tags, I am sure I have experienced errors previously if I did not have both populated...
Fixed it, by recreating the script and somehow now it worked .... is it possible to add multiple update statements ? For example i have to update like below.  update Country set CountryName='#2',Country_Code=Country_Code_Og,ValueDollars=#3 where ID=#1Update Country_Master set CountryName='#2',Country_Code=Country_Code_Og,ValueDollars=#3 where ID=#1 Or the second statement like  update country_master a set countryname,country_code_og,valueDollars=(select countryname,country_code_og,valueDollars from country b where a.ID=b.ID) where a.ID=#1
Yes, you can even call a procedure to push the whole logic through an API equivalent, with writeback functionality being so limited I like this as an option partly for reasons of security, partly for data quality.
Or on the API thought, do what I have done previously and create a view with a trigger on it to handle the multiple table logic, makes it clean, safe, and keeps the DB logic in the DB.
I tried to update multiple columns and it is not working.
And I don't have a crystal ball to see the syntax of what you tried...
My question is I have two tables of similar structure .  Table 1 : Deal TableTable 2: Deal Corporate Table I need to write back to both the tables at the same time. Is it possible? two update statements in one write back template?
You have to wonder yourself if you can do that in the UPDATE SQL statement, since I know you cannot do that ..  "A template can contain SQL statements other than insert and update. The write-back function passes these statements to the database. However, Oracle does not support or recommend the use of any statements other than insert or update." https://docs.oracle.com/middleware/12211/biee/BIESG/GUID-6F16F7CA-E525-4569-A5A0-C1C0F851F57E.htm#GUID-82215685-B79C-4DF… 
Hi I think you should close this thread

Categories

Resources