Reading long strings using ADO

Business Central/NAV Developers Forum
1

I’m about to import some data from a table in different SQL database into Navision.

Some of the fields are of the type Ntext and basically rich text fields.

I want to the first 100 chars into a simple text field in NAV and the full field into a BLOB field.

Getting first 100 chars into the field is not the biggest problem.

If I use the ADOStream collection can I then transfer it directly to a BLOB field?

Or have you tried something similar?

2

Ntext is varchar(4096), actually 8192 double-byte Unicode chars, that for the"N" stands - so be careful with LEFT(blabla,100)…

Regarding ADOstream - if noone else answers first, I’ll check with my father tomorrow, he knows .NET better than I, but right now it’s 3 am [;)]

3

Yes it’s a rather long field.

I can say what I*m trying to do, and it’s to integrate Navision with Umbraco, the open source CMS software I’m using to run http://DynamicsExperts.net. I’m trying to import the members into the CRM section of Navision, to make it much easier for us to handle replies and emails etc. to the candidates.

4

Hey Modris,

Did you find out anything?

5

Why not you are using Linkserver connection to the database and import data by sql queries? or i am talking nonsense?

6

And what is linkeserver?

7

I am sorry its linked server. You probably know it. It allows connection from one sql database to other database residing on other server and then you can write sql queries to import the data or do what ever you want. This was just a suggestion. But in these case if you will import data directly to navision tables in sql , it may cause problem during upgrade of navision. This was just one suggestion.

8

Yes but I don’t really think that this will help. Or how will a linked table in NAV react if getting a long text string longer than 250 chars?

9

hi all,

Erik so far i have seen a lot of post and yours happen to be more and you have a load of experince too, still you have doubts? sorry if i am being rude, i just wanted to know. Because i am not from Dynamics Background, i Just Found this field very interesting. so How long do i have to go before i can solve errors and query without others guides?

I apologie if i spoke anything out of line.!

10

You are right about it may not help as you have special requirement. I just suggested it as i have been using it and it is flexible too. Well longer than 250 string wont fit in the NAV table, i didnt know that you were having problems with long text which is longer than 250. Just i had a suggestion thats all.

11

Badly, of course… But, as you know the structure of “linked” tables --I suppose you yourself have designed them-- and thus know possible problematic fields, you can create a View, which cuts longer fields and remaps them to a bunch of allowed 250. Then in the importing code you can manipulate the View, not linked table, further to gain what you need without fair to get overflow error.
Rather weird method, of course, and would not help with BLOBs, but Ntext theoretically could be managed that way… The question is, that MS almost in every next SQL version extends Ntext - it was 4096 Unicode (double-byte) chars, now AFAIK its already twice as long, so it’s not a “clean” method.

12

If you never have doubts or goes where you have never been before, then you will never learn new. You have to learn your whole life, otherwise you will never be able to stay on top.

And knowing this community, then this is my primary location to ask if I have a thing that I don’t understand.

You learn by asking question, but you also learn by answering questions.

13

So your answer is: STAY AWAY FROM NTEXT - if you can?

14

So your answer is: STAY AWAY FROM NTEXT - if you can?

15

Erik,

I’m coming into this late but ran into the same problem. I fixed it with an ADO stream function. Thanx for pointing me in the right direction.

Function: ConvertLongText

Local Vars:

Name DataType

adoStream Automation ‘Microsoft ActiveX Data Objects 2.7 Library’.Stream

Passed in Parameter:

adoField: Automation “‘Microsoft ActiveX Data Objects 2.7 Library’.Field”)

Return Parameter:

ReturnText: Text[30]

Code:

IF FORMAT(adoField.ActualSize) = FORMAT(0) THEN

EXIT;

CREATE(adoStream);

adoStream.Open;

adoStream.WriteText(adoField.Value);

WHILE NOT adoStream.EOS DO

CreateNewDescriptionLine(adoStream.ReadText(50));

adoStream.Position := 0;

IF EVALUATE(ReturnText,adoStream.ReadText(30)) THEN ;

adoStream.Close;

What method did you find to fix yours?