Options

How to pass Navision record fields to COM object?

mr.burnsmr.burns Member Posts: 39
Hello,

I want to write a COM object which should get fields from a Navision record. E.g: No., Description, etc. from Item table .

I started with a simple COM object, which just reads an integer as parameter. It works fine. I can access the integer in my COM object using C++.
Now I want send several fields (1-50) from a Navision record to the COM object. What is an efficient way?

If I just have a few fields lets say 1-5 I can create a COM function which reads 5 paramters, but what about 5-50 or even more fields?

I had an Idea to use BLOB field. I created a table with just a blob field inside and passed this BLOB field to my COM function.

But there I have a problem. What type of parameter I have to set in my COM function?
In the COM documentation of Navision there is a mapping of C/AL datatypes to COM datatypes:

OutStream --> VT_STREAM

But what is VT_STREAM?
I use Borland C++ Builder 6, and there I only can choose parameter types like:

BSTR, CURRENCY, DATE, DECIMAL, float, int, VARIANT, VARIANT* etc.

But no one matches VT_STREAM.

So what type of data is VT_STREAM? Is it a pointer to VARIANT?
Any help is highly appreciated!

Thanks!

Comments

  • Options
    SteveOSteveO Member Posts: 164
    Why not create an xml document and pass through an XMLDOM?
    This isn't a signature, I type this at the bottom of every message
  • Options
    mr.burnsmr.burns Member Posts: 39
    Hello SteveO,

    thanks for reply.
    I need to have a COM object due to abilities only a COM object has.
    To explain in detail it would take to long here, sorry!
    And the way over XMLDocument takes even more time each execution and I need a very fast running solution!

    Thanks anyway!

    Any further ideas?

    I just must know which datatype on COM side matches the OutStream datatype in Navision? VT_STREAM I cannot interpret, what is it?

    Is it a pointer? pointer to what? Which COM datatype is VT_STREAM?

    Thanks!
  • Options
    mr.burnsmr.burns Member Posts: 39
    Hello,

    I still need help!
    May someone has already had a similar problem and can give some experiences on how to pass data from Navision to mySQL using COM?

    Thanks a lot!
  • Options
    sabzamsabzam Member Posts: 1,149
    Hi,

    We're in the same situation and could not find any help in other posts. I know it is quite an old post but it seems to suit well our requirement.

    Can you kindly forward any views on the the above?

    Thanks in advance!
  • Options
    jjbravojjbravo Member Posts: 19
    Are you having problems with Blob fields like the guy who started this thread or all NAV fields in general?
  • Options
    sabzamsabzam Member Posts: 1,149
    It's all NAV fields. I managed to pass a field to a COM object but would like to pass an entire record and would like to avoid passing field by field.
  • Options
    jjbravojjbravo Member Posts: 19
    I don't see how you could pass a record to the COM objects, but I've resolved this in another way in our Fusion product which for years have been very reliable and fast.

    I have a QueryControl class, a Query class, a QueryColumns class and a QueryDataRow class.
    The QueryControl class orchestrates the interface with fusion.
    I call Query := QueryControl.NextQuery(); to get the next query object

    Then for each record in NAV that I want to return I create a new QueryColumns object :
    QueryColumns := Query.Columns;
    and I create a new QueryDataRow object :
    QueryDataRow := QueryControl.NewQueryRow();

    Then I call a method to fill the DataRow :
    FillRow(RecordReference,QueryColumns,QueryDataRow,FALSE);
    and add the QueryDataRow with the data back to my QueryControl:
    QueryControl.AddQueryRow(QueryDataRow);

    The FillRow looks like this:
    IF NOT ISCLEAR(QueryDataRow) THEN BEGIN
      FOR i := 0 TO (QueryColumnCollection.count - 1)  DO BEGIN
        QueryColumn := QueryColumnCollection.Item(i);
        IF NOT ISCLEAR(QueryColumn) THEN BEGIN
          IF RecordReference.FIELDEXIST(QueryColumn.No) THEN BEGIN
            FieldReference := RecordReference.FIELD(QueryColumn.No);
            IF FORMAT(FieldReference.CLASS) = 'FlowField' THEN BEGIN
              FieldReference.CALCFIELD;
              CASE FORMAT(FieldReference.TYPE) OF
                'Date' :
                  BEGIN
                    IF (FORMAT(FieldReference.VALUE) <> '') THEN BEGIN
                      VariantValue := ConvertDate(FORMAT(FieldReference.VALUE));
                      QueryDataRow.SetFieldValue(QueryColumn.GetUniqueName(),VariantValue);
                    END;
                  END;
                'DateTime' :
                  BEGIN
                    IF (FORMAT(FieldReference.VALUE) <> '') THEN BEGIN
                      VariantValue := ConvertDateTime(FORMAT(FieldReference.VALUE));
                      QueryDataRow.SetFieldValue(QueryColumn.GetUniqueName(),VariantValue);
                    END;
                  END;
                'Time' :
                  BEGIN
                    IF (FORMAT(FieldReference.VALUE) <> '') THEN BEGIN
                      //VariantValue := ConvertTime(FORMAT(FieldReference.VALUE));
                      VariantValue := FORMAT(FieldReference.VALUE);
                      QueryDataRow.SetFieldValue(QueryColumn.GetUniqueName(),VariantValue);
                    END;
                  END;
                'Option','DateFormula' :
                  QueryDataRow.SetFieldValue(QueryColumn.GetUniqueName(),FORMAT(FieldReference));
                'Decimal' :
                  BEGIN
                    QueryDataRow.SetFieldDecimalValue(QueryColumn.GetUniqueName(), FORMAT(FieldReference.VALUE));
                  END;
                ELSE
                  QueryDataRow.SetFieldValue(QueryColumn.GetUniqueName(),FieldReference.VALUE);
              END;
            END ELSE IF FORMAT(FieldReference.CLASS) = 'Normal' THEN BEGIN
              IF CalcSums THEN
                FieldReference.CALCSUM;
              CASE FORMAT(FieldReference.TYPE) OF
                'BLOB' :
                  FusionBlobMapping.GetValue(RecordReference,QueryColumn.GetUniqueName(),FieldReference.NUMBER,QueryDataRow);
                'Date' :
                  BEGIN
                    IF (FORMAT(FieldReference.VALUE) <> '') THEN BEGIN
                      VariantValue := ConvertDate(FORMAT(FieldReference.VALUE));
                      QueryDataRow.SetFieldValue(QueryColumn.GetUniqueName(),VariantValue);
                    END;
                  END;
                'DateTime' :
                  BEGIN
                    IF (FORMAT(FieldReference.VALUE) <> '') THEN BEGIN
                      VariantValue := ConvertDateTime(FORMAT(FieldReference.VALUE));
                      QueryDataRow.SetFieldValue(QueryColumn.GetUniqueName(),VariantValue);
                    END;
                  END;
                'Time' :
                  BEGIN
                    IF (FORMAT(FieldReference.VALUE) <> '') THEN BEGIN
                      VariantValue := ConvertTime(FORMAT(FieldReference.VALUE));
                      QueryDataRow.SetFieldValue(QueryColumn.GetUniqueName(),VariantValue);
                    END;
                  END;
                'Option','DateFormula' :
                  QueryDataRow.SetFieldValue(QueryColumn.GetUniqueName(),FORMAT(FieldReference));
                'Decimal' :
                  BEGIN
                    QueryDataRow.SetFieldDecimalValue(QueryColumn.GetUniqueName(), FORMAT(FieldReference.VALUE));
                  END;
                ELSE
                  QueryDataRow.SetFieldValue(QueryColumn.GetUniqueName(),FieldReference.VALUE);
              END
            END;
          END;
        END ELSE
          ERROR(Text003,QueryColumn.No,RecordReference.NUMBER);
      END;
    END;
    

    It works really well and has proven encredible reliable and fast.

    I hope this gives you some ideas to how you can solve this.

    John
  • Options
    sabzamsabzam Member Posts: 1,149
    Hi jjbravo,

    Thanks for your reply I would rather stick to the NAV objects though as I think it's more secure and reliable.
  • Options
    jjbravojjbravo Member Posts: 19
    I'm confused...this is NAV code using a com object written in .net.
    Thought this was your question. Just trying to help :)
  • Options
    ta5ta5 Member Posts: 1,164
    Hi Sabzam
    Just my 2 cents on that:
    You cannot pass a navision record to a com object because the com object (.NET, whatever) does not know this datatype. You either have to send the fields as separate parameters or you have to send the record as xml document, as mentioned earlier in this thread.
    If you don't want to build xml docs with xmldom you can use xml ports, search the help or this forum for xml ports. If your com object returns a record, it's the same, vice versa. This means you have to pass the fields separatly or you pass back a xml document.

    You'll may get some more information if you search for nav 2009 codeunit webservices. If a 3rd party program wants to use such a nav 2009 webservice, imho same set of questions have to be solved.

    Hope this helps.
    Thomas
  • Options
    sabzamsabzam Member Posts: 1,149
    Thanks you've been very helpful. I've been looking everywhere trying to find a way how to pass the whole record but to no avail. I'll resort to passing the record as xml as otherwise it would be too heavy.

    Thanks once again for helping me out.
  • Options
    dinhdinh Member Posts: 4
    hi sabzam,
    I also faced the same issue earlier.
    My solution for this was to pass TableNo as Integer and GETPOSITION(FALSE) as Text from C/AL. Since GETPOSITION(FALSE) shows the primary keys I can get to the whole record using CFrontDotNet.
    This is also a workaround but if you're familiar with CFrontDotNet, this is definitely taking less effort compared to xml.

    Chuong Dinh
  • Options
    jjbravojjbravo Member Posts: 19
    There is a conversation going on about accessing Records and fields using the ROT:
    viewtopic.php?t=42538

    Just FYI in case this could help you.

    John
  • Options
    [TweaK][TweaK] Member Posts: 14
    Hmmm ...

    I fully comprehend what "mrBurns" wants to achieve.. i want the same thing..
    Here's what i can say..

    There are 3 ways imo to pass data from navision to DotNet:
    1. Simple Parameters (text, integer, etc ...)
    2. COMStream Parameter (to pass a BLOB Outstream to the dotnet appi)
    3. Object Parameter

    1. Do not recommend, crappy solution
    2. Morfing a recordset into a blob and passing that blob to the dotnet component
    How: (C# DotNet Stream Parameter for function)
    - Download a stream from DotNet towards Navision: [In(), MarshalAs(UnmanagedType.AsAny)] object COMStreamIn
    - Upload a stream from Navision towards DotNet: [Out(), MarshalAs(UnmanagedType.AsAny)] object COMStreamOut
    => I use these stream parameters to Upload an Image BLOB from navision to dotnet, manipulate it there, then download it back to navision and store in the blob again
    => But then again, useless solution.. cuz what will you do with the data once in dotnet!?
    3. Create a function that takes an "Object" parameter.. Object is of type Variant, meaning you can pass in it whatever the f*ck you want! I assume you could just pass the Recordset to DotNet this way..
    The only real issue here is to what variable type to cast this recordset to, there is no DotNet variable that supports a Navision Recordset.

    So, hereby my question: Is there anyone on this plannet that has succeeded in creating a class that could represent a Navision Recordset or even better, a Navision Record Reference!!!

    Grtz
  • Options
    IsakssonMiIsakssonMi Member Posts: 77
    I think the best way to exchange entities is to create business entities in .NET that mirrors the table in NAV and implement the IDispatch interface (alt. IUnknown).
    Then you can populate it and then pass it to your automation. Pretty much what you do when working with WCF..
  • Options
    jannavjannav Member Posts: 15
    I used code parts from this: http://blogs.msdn.com/b/nav/archive/200 ... 1#comments and used parts from it to write xml records into the stream. In .net the xml is parsed into a class list
Sign In or Register to comment.