How to pass Navision record fields to COM object?

mr.burns

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!

SteveO

Why not create an xml document and pass through an XMLDOM?

mr.burns

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!

mr.burns

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!

sabzam

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!

jjbravo

Are you having problems with Blob fields like the guy who started this thread or all NAV fields in general?

sabzam

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.

jjbravo

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

sabzam

Hi jjbravo,

Thanks for your reply I would rather stick to the NAV objects though as I think it's more secure and reliable.

jjbravo

I'm confused...this is NAV code using a com object written in .net.
Thought this was your question. Just trying to help

ta5

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

sabzam

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.

dinh

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

jjbravo

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

[TweaK]

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

IsakssonMi

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..

jannav

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