B1if calling SQL atom while DI Object Transaction is processing

[cameron_fischer2]

Hi All,

I'm trying to insert a new business partner contact person into a BP record using a B1 Object call with method updateWithSubdeletion. I get a new user via a web call, insert the user and then I want to get the contact code of the person that was added so that I can return the code during the HTTP session.

The new contact person is added to B1 successfully, but when I add an SQL atom after the B1 object call the scenario processing fails, SQL locks up and B1 crashes. I have to restart the server to recover.

What I assume is happening is that the B1 object call method updateWithSubDeletion is asynchronous and is still in progress when the SQL call is executed which results in a deadlock.

I can't figure out any way to work around this without completely changing the data flow of the interfacing application. Does anyone have any ideas?

Regards

Cameron

[bastian_schaefer · Accepted solution]

Hi Cameron,

optionally, you can either use

• B1 RecordSet (B1 Object call atom with method B1 RecordSet) to retrieve the newly created users via DI API SQL within the same scenario step (it is much slower than JDBC SQL)

or much better

• you switch your integration step to "SAP Business One DI Single Transaction", so that each DI API Call get effective immediately.
  Be aware, that there will be no rollback at the end of the scenario processing in case of an DI API call error (for further information please check attached pdf document from SAPNote 1993784 - B1if_ Multiple DIAPI connections for one company)
  It also makes sense to combine "SAP Business One DI Single Transaction" with option "Stop processing if fails = false" for each B1 DI API Call and to handle the errors in atom0.

Best regards

Bastian

[former_member404576 · Accepted solution]

Hi Bastian,

Will the B1 Record Set retrieve only data actually committed?. In other words, I require to validate the creation of an B1 object even if the DIresult is success (I checked your suggestion in here: Update Item Problem - "success", but without ch... | SCN).

I'm using the B1 Record Set atom in my scenario (AddSalesOrder Web Service) not only to validate if it was really created, but also to retrieve the DocNum of the currently added Document.

Kind Regards,

Fernando

[bastian_schaefer · Accepted solution]

Hi Fernando,

if you define your integration scenario step not with option "SAP Business One Single Transaction" all B1 DI API calls are conditionally until the "commit" runs at the end of the scenario processing.

That means all operation might get rollbacked. Therefore please avoid to have two DI API "Insert/update" calls within one step and "SAP Business One Single Transaction" deactivated.

As B1 RecordSet relies on DI API connection, it's much slower than a JDBC SQL retrieval.

To retrieve the B1 object call result you could also work with B1 object call method "Synchronous Retrieval": From performance point of view it's not slower than the B1 ResultSet SQL.

Best regards

Bastian

[cameron_fischer2]

Thanks Bastian,

I tried enabling DI Single Transaction and that works perfectly. I actually thought I had already enabled it, I must have turned it off at some point.

@Jan, thanks I'll try that out and keep it in mind for future.

Regards

Cameron

[jan_polak]

Hi Cameron,

try

SELECT ... FROM ... WITH (READUNCOMMITTED)

Jan