How can we insert relational data (e.g. complex data structures) into NAV from a web service in a single closed transaction?

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hi there,

I hope you can help :-)

We are working in a project were we want to export several web services in order to query and insert data into/from the Dynamics NAV data structures. In particular we have complex data structures which expose dependencies between multiple objects (tables). In the web services however we can only query these objects as "flat" structures. That is if we have a Page 'Application' and another 'ApplicationDetails' then we need to make a create requests followed by a create multiple request in order to insert an application together with the respective details of this application. Our problem is that we do not know how to handle this in a transaction save way since each request to a web service endpoint is handled in its own transaction.

Thus our question is as following:

How can we insert relational data (e.g. complex data structures) into NAV from a web service in a single closed transaction? Is this generally possible using SOAP based web services ob Dynamics NAV. We are using Dynamics NAV 2013 R2.

Other options we have discussed so far are:

* Writing directly to the MS SQL database that is used by Dynamics NAV.

The problem that we see here is that we cannot detect handle the 'lost'

update problem.

* Writing a code unit web service which revieves and returns an XMLPort. We am currently not sure if this is even a viable solution, our NAV developers have several concerns with this solution. As I understand it XMLPorts are intended for Bulk imports and the data is not checked for correctness and could possible overwrite data in Dynamics NAV if wrong values are sent.

Thanks in advance

*This post is locked for comments

All responses (4)

Answers (1)

Suggested answer

Jens Glathe 6,092 on at

Like (0)

Report

RE: How can we insert relational data (e.g. complex data structures) into NAV from a web service in a single closed transaction?

Hi Sebastian,

the short answer is: You can't. By design. What you can do, however, is to do a lock on a proxy record (reading and modifying it) before you access the real target records. We do this to avoid conflicts with our background and longer-running worker process. But this is only an option if you use new tables for your functionality, not standard NAV ones. This actually is our decoupling from NAV business logic: WS->lock the proxy record -> write to (and also lock) buffer tables, Other WS -> lock the proxy record -> lock the buffer records that need procesing -> do the processing and insert warehouse receipts.

The proxy record ensures exclusive access as long as you have successfully modified the proxy record in the first place. The catch is, you need all code accessing the desired tables to adhere to this protocol. But it successfully prevents critical external transfers from failing in the wrong way. It should always fail on gaining access to the proxy record, and since this isn't an exception, we can return a "false" or omething like this back to BizTalk and it doesnt count it as transferred for whatever strange reason.

The function to do the proxy lock is not trivial:

    PROCEDURE LockProcessingBuffer@1140009(VAR ProcessingBuffer@1140000 : Record <CENSORED>) : Boolean;
    VAR
      L_Session@1140002 : Record 2000000009;
      L_RetryCount@1140001 : Integer;
    BEGIN
      // write your own Session-ID - this locks the record for other sessions as long as we haven't committed the transaction.
      L_Session.SETRANGE("My Session",TRUE);
      L_Session.FINDFIRST;
      L_RetryCount := 0;
      REPEAT
        ProcessingBuffer.InUseBySessionID := L_Session."Connection ID";
        //Insert und auch Modify sind bereits ein implizites Locktable.
        IF NOT ProcessingBuffer.INSERT THEN BEGIN
          SELECTLATESTVERSION;
          IF NOT ProcessingBuffer.MODIFY THEN BEGIN
            SELECTLATESTVERSION;
            ProcessingBuffer.LOCKTABLE;
            ProcessingBuffer.GET(ProcessingBuffer.TableID);
            ProcessingBuffer.InUseBySessionID := L_Session."Connection ID";
            IF ProcessingBuffer.MODIFY THEN;
          END;
        END;
        IF NOT ProcessingBuffer.GET(ProcessingBuffer.TableID) THEN
          ProcessingBuffer.InUseBySessionID := 0;
        L_RetryCount += 1;
      UNTIL (ProcessingBuffer.InUseBySessionID = L_Session."Connection ID") OR (L_RetryCount > 10);
      EXIT(L_RetryCount <=10)
    END;

This function looks really strange, but it simply operates on the assumption that any of the C/AL instructions are not atomic and can be interrupted and some other process running C/AL gets what we wanted to achieve. The one true unique identifier is Connection ID - that doesn't change for the session. I'm not entirely sure if it works that way on NAV2013 and above. If not, use a GUID created in the function which is "sufficiently" unique.

The only true and verifiable lock you can get in C/SIDE is when you can successfully insert or modify a record (bulk inserts might be a problem, though).

with best regards

Jens
Lanoxx 5 on at

Like (0)

Report

RE: How can we insert relational data (e.g. complex data structures) into NAV from a web service in a single closed transaction?

Dear Jens Glathe,

I have a question about the XMLPorts, how do you solve the problem of concurrent modification. When using Web Services with Pages, then every page has an implicit key field that is uses to detect modifications of the record between load and update time. So if a user is modifying the record in the Dynamcis Nav Client and another one through the web service then one of them will recieve an error.

The problem with XMLPorts is that it solves the transaction issue we had, but now we have a new problem which is the concurrent modification? Is there any recommended way to detect this when using XMLPorts?

The following question on Stackoverflow also describes this issue, but the answer is not satisfiable at all:
http://stackoverflow.com/questions/23297430/can-i-use-the-key-field-in-nav-table-records-the-way-i-want-to-in-an-xmlport

Best Regards

Sebastian
Suggested answer

Jens Glathe 6,092 on at

Like (0)

Report

RE: How can we insert relational data (e.g. complex data structures) into NAV from a web service in a single closed transaction?

Hi,

we have done so using XMLPorts for document type data (header/lines/line details), this is transaction safe when you write a codeunit that has the XMLPorts as parameter. Also, what Jose recommends is a good technique. For the import part, the best practice solution seems to be to use an XMLPort with SourceTableTemporary. The code is spread out over codeunits and XMLPorts, but it is transaction safe.

with best regards

Jens
Verified answer

jcastro 2,245 on at

Like (0)

Report

RE: How can we insert relational data (e.g. complex data structures) into NAV from a web service in a single closed transaction?

It might not be the best way but what I can think of is directly writing a web service codeunit (no XMLPort). If you fear that you might have some issues regarding data consistency and replacing the wrong things, you could just write functions and retrieve the data as parameters maybe. After that, you have the necessary info to start coding and control everything you are fearful of.

Second option, if that does not work and you don't trust the XMLPort completely (although I think they are pretty cool), you can still use XMLPorts and set the table(s) in the XMLPorts themselves to temporary. After the importing is done (onpostxmlport trigger maybe) you can go through every record on the temporary table (no real change has been done yet) and write the changes yourself while making sure it does what you want it to and ignore the records you desire. I'm not sure I'm explaining myself very well. Let me know if so to try to word it differently.

In any case, when working with NAV you should try to avoid at all cost working directly with the MS SQL server (unless it's to get data, but not insert data).

How can we insert relational data (e.g. complex data structures) into NAV from a web service in a single closed transaction?

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates

How can we insert relational data (e.g. complex data structures) into NAV from a web service in a single closed transaction?

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Subscribe to

Leaderboard

Featured topics

Product updates