Using BLOB and BigText

Business Central/NAV Developers Forum
1

Hi, I am trying to use a single field in Navision to store the text for the body of an email when using reporting services (SQL). Using Client 4.02.23099.

What I have tried so far: Use BigText (addtext) to create the lines. Then outstream it to the BLOB field.

Now I would like to extract the text out of the BLOB in SQL so to get the body text. I have tried the CONVERT funciton but SQL says that “Image” blob cannot be converted. I checked the setting of the blob in Navision and change to Memo sub-type and also set compression = no. Still no luck [:(]

Anyone tried anything similar?

Thanks,Nic

2

Not sure but I think if you search mibuso for STREAM & BLOB there are some posts.

I’m not familiar with this, Kamil postsed a download perhaps this can point you in the right direction

http://www.mibuso.com/dlinfo.asp?FileID=343

3

Thanks Savatage, I am fine with Navision’s handling of the Blob.

E.g.

BigText.ADDTEXT(‘Line 1’);
BigText.ADDTEXT(‘Line 2’);
BigText.ADDTEXT(‘Line 3’);

Rec.BLOB.CREATEOUTSTREAM(OutStream);

BigText.WRITE(OutStream);

It’s just that I would like to get the Text out of the from SQL’s point of view.

Nic

4

Thought I would update and close my posting because I came up with a solution:

It is possible to get the text out of a BLOB as I was trying to do.

The SQL code to convert Navision BLOB into something SQL can use as text is:

CONVERT( varchar(2000), CONVERT (binary, BLOB)) AS BLOB

Then you can use the field in Reporting services etc.

5

Hey Nic, very much appreciated tht you took the time to post this, thanks [:D]