cancel
Showing results for 
Search instead for 
Did you mean: 

Capturing the result of an SQL Call in B1i

Former Member
0 Kudos
412

I have used a SQL Call atom in a B1i-package.

You can see the SQL-statement below :

- <Payload id="atom1" method="Query Statement(Query Statement)" Role="C" disable-output-escaping="false"

sql="SELECT TABLENAME, FIELDALIAS, PK FROM OMLT WHERE TRANENTRY=445" mode="single"

system="0010000135">

- <ResultSet xmlns="urn:com.sap.b1i.adapter:jdbcadapter" rowCount="1">

- <Row>

<TABLENAME>OITM</TABLENAME>

<FIELDALIAS>ItemName</FIELDALIAS>

<PK>Item</PK>

</Row>

</ResultSet

</Payload>

I have defined a local variable TEST with the following link :

/vpf:Msg/vpf:Body/vpf:Payload[./@id='atom1']/ResultSet/Row/PK

When I test this in debug I always get an empty value.

What could be wrong ? I donu2019t see it.

Thx,

Mario

Accepted Solutions (1)

Accepted Solutions (1)

marcus_schmalz2
Participant
0 Kudos

Hi Mario,

You have to use the jdbc namespace, since this is defined in the ResultSet tag.

For example like this:

/vpf:Msg/vpf:Body/vpf:Payload[./@id='atom1']/jdbc:ResultSet/jdbc:Row/jdbc:PK

I think the jdbc namespace must also be used for all following elements after ResultSet, but I did not test it.

Kind regards,

Marcus

Trinidad
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Mario,

Marcus is 100% right.

You have a sample how to read from a DB query result in the "B1if DB2B1 Scenario" sample available in the Integration Webinar PSD pages: https://psd.sap-ag.de/PEC/calendar/ . Just search for B1 recorded sessions on May-June 2011.

Regards,

Trinidad.

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Marcus, Hi Trinidad,

i have tried both syntaxes :

Local Variable VAR1 with the value

/vpf:Msg/vpf:Body/vpf:Payload[./@Role='T']/Event/b1e:b1events/b1e:b1event/b1e:keys/b1e:key/b1e:value

Local Variable VAR2 with

/vpf:Msg/vpf:Body/vpf:Payload[./@id='atom1']/jdbc:ResultSet/jdbc:Row/jdbc:PK

Local Variable VAR3 with

/vpf:Msg/vpf:Body/vpf:Variables/vpf:var[./@id='PK']/@value

I use a first Call SQL query and i'm using in my query VAR1.

This works fine and it gives me a result PK.

When I want to use this field PK in a second Call SQL query, the value of this field is empty.

In both cases the value is empty !

Former Member
0 Kudos

Trinidad, Marcus,

Oke, i have found now a solution.

I needed these information to write a scenario for synchronising the multilanguage table.

As you know this data consists of 2 tables OMLT/MLT1

The difficulty is that the key TRANENTRY can differ for the same field between the 2 databases.

Therefore yoou have to write a SQL-atom, to know what the key is in the receiving company.

So, you have several possibilities :

- the first time you add a translation for a field, you are in transactionmode 'A'

If you add several translations in once, no problem. My scenario or the standard scenario is working fine.

- if you modify/adapt the existing translations, you are in transactionmode 'U'

By retrieving the right key for the receiver, the scenario is also working fine.

- if you add a translation to the existing translations, you are also in transactionmode 'U'

By retrieving the right key for the receiver, the scenario is also working fine.

- if you have 5 existing translations on a field and you want to delete them all, you are in transactionmode 'D'

In this case, the only information that you receive from the sender is the key or TRANENTRY

But this is not enough to be able to search for the key of the receiver, when the keys are different between the

2 companies.

There is nog logfile on this table to be able to retrieve the TABLENAME, FIELDALIAS en PK information.

Do you have any idea how to solve this ?

The only solution that i see is preventing the user of deleting all translations in once.

marcus_schmalz2
Participant
0 Kudos

Hi Mario,

this really seems to be a problem to handle the deletion of objects when you don't know the object key.

But a workaround could be to fill a object key mapping table after each insert/update. This could be an own sql-table in any database or possibly even a global table in B1if.

Then you could do a lookup in this table to get the correct TranEntry for the deletion.

This is a bit tricky, but it should be a possible way.

Kind regards,

Marcus