Read data from nText in SQL server to bigText variable

nXqd

Hi guys,
At the moment, I try to read long string which is nText from SQL and then output to xml file. My strategy is get them in the bigText variable then write to xml file, but I'm stuck at the first step. So how do I input bigText variable with ADO Stream.

I've read the msdn documentation but I don't find any solution since I'm new to nav

Thanks for reading

Chinmoy

How is this problem related to NAV?

nXqd

Chinmoy wrote:

How is this problem related to NAV?

bigText is a variable in navision and I also use ADO in navision to query and get the result. Maybe my code will explain my problem clearer:

PROCEDURE ExportProdInfo@10();
  1     VAR
  2       shortDesc@1001 : Text[1000];
  3       longDesc@1002 : BigText;
  4       metaDesc@1003 : Text[1000];
  5       xmlText@1004 : Text[200];
  6       tmpText@1005 : Text[200];
  7     BEGIN
  8       //
  9       // Get the product information directly from database then export to xml file
 10       //
 11 
 12       IF ISCLEAR(ADOrs[1]) THEN
 13           CREATE(ADOrs[1]);
 14 
 15       QueryString :=
 16       'SELECT TOP 1 ProdOptProdNumber, ProdOptLongDescription, ProdOptShortDescription, ProdOptMetaDescription FROM ProductOption';
 17       MakeVars;
 18       ADOrs[1].Open(Ascii2Ansi(QueryString),ADOConn,OpenMethod,LockMethod);
 19       IF NOT ADOrs[1].EOF THEN REPEAT
 20       xmlText := '<"1.0" encoding="ISO-8859-1"?>';
 21       xmlText += 'SystemStatus';
 22       xmlNode := xmlDOM.createNode('element', 'PdfFile', 'SomeNamespace');
 23       xmlDOM.loadXML(xmlText);
 24 
 25       //XmlDOM.save('C:\testXML.xml');
 26         shortDesc := GetFieldStr('ProdOptShortDescription');
 27         //longDesc.ReadGetFieldStr('ProdOptLongDescription');
 28         metaDesc := GetFieldStr('ProdOptShortDescription');
 29 
 30         tmpBigText.GETSUBTEXT(tmpText, 0, 200);
 31         MESSAGE(tmpText);
 32         ADOrs[1].MoveNext;
 33       UNTIL ADOrs[1].EOF;
 34 
 35 
 36       CLEAR(ADOrs[1]);
 37     END;
 38 
 39     PROCEDURE GetFieldStr@13(FieldName@1048200 : Text[1024]) RetVal : Text[1024];
 40     BEGIN
 41       ADOStream.Type := 2; // text mode
 42       ADOStream.Open;
 43       ADOStream.WriteText(ADOrs[1].Fields.Item(FieldName).Value);
 44       XmlNode.nodeTypedValue := ADOStream.Read();
 45       tmpBigText.ADDTEXT(xmlNode.text); " Operation is not allowed in this context
 46       //tmpBigText.read(ADOStream);
 47       ADOStream.Close;
 48     END;

Chinmoy

Hey, I tried the following and it worked very well for me, however, execution time is too long:
Note: "i" needs to be defined as decimal

QueryString := 'SELECT Code, sText ' +'FROM [' + 'Chn] ';
ADOConn.Open(ADOConStr);
ADOrs.Open(QueryString, ADOConn, 3);

IF NOT ADOrs.EOF THEN
REPEAT
  longDesc.ADDTEXT(ADOrs.Fields.Item(1).Value);
  FOR i := 1 TO 2147483647 DO // (till the max position you can read)
  BEGIN
    tmpText := '';
    RetLength := longDesc.GETSUBTEXT(tmpText, i, 200);
    IF i + 200 <= 2147483647 THEN
      i += 200
    ELSE
      i += 2147483647 - i;

    IF RetLength > 0 THEN MESSAGE(tmpText);
  END;
  ADOrs.MoveNext;
UNTIL ADOrs.EOF;

Hope this helps..

Chn

nXqd

@Chn
Thanks you for taking your time to answer my question. I had another solution but it's quite ugly by using array of text , I will post the code for reference. I will take a look at your code, it seems to be better than mine