VARCHAR(Max) limited to 1024 characters with BigText

spider1269

I'm populating a BigText variable from a Stored proc. The output of the stored proc is VarChar(Max). For some reason the following statement doesn't work.

tmpText.ADDTEXT(lADORecordset.Fields.Item(0).Value);

If I add a FORMAT to the above statement it will work but only if the varchar is <= 1024 characters.

tmpText.ADDTEXT(FORMAT(ADORecordset.Fields.Item(0).Value));

Any idea how to get more than 1024 characters to work with BigText?

Thanks.

ppavuk

No way. when you doing assignment in C/AL, system put your varchar to text buffer. Text buffer is limited to 1024. The same with BSTR, and any other long text data type, even variant

spider1269

Is there any other way to do this other than parsing the data before it comes to NAV?

spider1269

I was able to solve this using ADO Stream. Works like a charm.

mdPartnerNL

Im always curious how and learning...want to share?

ppavuk

Basically, there is two options: ADOstream or third party dll. ADOstreams are better as they are part of system, and you don't need to install something on client machines.

spider1269

      ADOStream.Open;
      ADOStream.WriteText(lADORecordset.Fields.Item(0).Value);
      ADOStream.Position := 0;
      WHILE NOT ADOStream.EOS DO BEGIN
        TargetText.ADDTEXT(FORMAT(ADOStream.ReadText(1024)),y);
        y += 1024;
      END;
      ADOStream.Close;

The ADOStream variable is automation(Stream) and the TargetText is BigText. I'm updating the TargetText variable 1024 characters at a time but you could always use a smaller value.