Options

Problem extracting Text field from SQL using ADO

iansilversiansilvers Member Posts: 38
I hope someone is able to help.

I am using ADO to access information in an external SQL database, which I use to update a staging table in Navision for later processing. In the SQL database, all the fields that I required are varchar with the exception of one which is a text.

The SQL command I execute to retrieve the recordset is
select 
	isnull(tpc_type,'') as Type, 
	isnull(tpc_internal_reference,'') as ClientRef, 
	isnull(tpc_name,'') as ClientName, 
	isnull(tpc_notes,'') as Notes 
from [dbo].[topics]

The Notes field is the text field, and it contains an XML document which I need to get some information out of. I am attempting to write this to a Blob field on my staging table. I can easily process all the fields with the exception of Notes. Using the ActualSize property of the recordset field returns -1 which suggests that it is empty, despite the fact that if I run this command in SQL, it gives me what I expect.

I have tried everything that I can think of, and has been suggested on other topics, but have run into a brick wall. Anyone got any ideas as to what I am doing wrong?
WHILE NOT Rec.EOF DO BEGIN
  Staging.INIT;
  Staging."Entry No." := NextEntryNo;
  Staging."Unique Reference No." := Rec.Fields.Item('ClientRef').Value;
  Staging."Client Name" := COPYSTR(FORMAT(Rec.Fields.Item('ClientName').Value),1,MAXSTRLEN(IKENStaging."Client Name"));
  Staging."Last Updated Date" := GetDate(Rec.Fields.Item('LastUpdateDate').Value);

  // Status is held in the Notes field which is an XML file
  CLEAR(BigText);
  ADOStream.Open;
  ADOStream.WriteText(Rec.Fields.Item('Notes').Value);
  ADOStream.Position := 0;
  BigText.ADDTEXT(FORMAT(ADOStream.ReadText),1);
  ADOStream.Close;

  Staging.Notes.CREATEOUTSTREAM(WriteStream);
  BigText.WRITE(WriteStream);

  Staging.Processed := FALSE;
  Staging.INSERT;

  NextEntryNo := NextEntryNo + 1;
  Rec.MoveNext;
END;

Answers

  • Options
    pduckpduck Member Posts: 147
    Does it have the same effect when using a "normal" text variable and not a BIGTEXT one?
  • Options
    iansilversiansilvers Member Posts: 38
    Does it have the same effect when using a "normal" text variable and not a BIGTEXT one?

    I tried picking up the first 30 characters of the field into a normal text field, and it was still empty. Anyway, the normal text variable can be a maximum of 1024 characters, and this field in SQL will be much larger than that in most instances, so use of big text is necessary.
  • Options
    iansilversiansilvers Member Posts: 38
    Problem solved. Apparently, if you are selecting text fields using ADO, they must appear at the end of the select statement.
  • Options
    nXqdnXqd Member Posts: 39
    iansilvers wrote:
    Problem solved. Apparently, if you are selecting text fields using ADO, they must appear at the end of the select statement.

    Can you post your solution here, I have the same problem. I really don't understand your phrase that "fields must appear at the end of select statement " ?

    Thanks a lot
  • Options
    iansilversiansilvers Member Posts: 38
    nXqd wrote:
    iansilvers wrote:
    Problem solved. Apparently, if you are selecting text fields using ADO, they must appear at the end of the select statement.

    Can you post your solution here, I have the same problem. I really don't understand your phrase that "fields must appear at the end of select statement " ?

    Thanks a lot

    Sorry, but this was a couple of years ago, and I have moved on, so I don't have access to the solution anymore.
  • Options
    nXqdnXqd Member Posts: 39
    If you know and this thread reminds you of something you remember, it'd be great that you help me with my problem :)
    viewtopic.php?f=23&t=53382

    Thanks for reading this !
Sign In or Register to comment.