Problem extracting Text field from SQL using ADO

iansilvers

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;

pduck

Does it have the same effect when using a "normal" text variable and not a BIGTEXT one?

iansilvers

Alexander Broz wrote:

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.

iansilvers

Problem solved. Apparently, if you are selecting text fields using ADO, they must appear at the end of the select statement.

nXqd

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

iansilvers

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.

nXqd

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 !