How to get the PictureURL from the Exact Online XML-feed to show the picture using Invantive Data Access Point?

Question

When using Data Access Point to generate an XML feed, using the following query:

select * from exactonlinerest..items

the resulting XML has a PictureURL:

https://start.exactonline.nl/docs/SysImage.aspx?Table=Items&ID=3332e277-ba03-420e-84ce-21ac174c5ced&ThumbSize=500&NoCache=1&OptimizeForWeb=1&_Division_=875673

But when trying to open the URL, an error occurs and the picture is not shown.

How would I go about and make that picture viewable?

Answer 1

You can retrieve the picture from Exact Online through the same secured channel used for the APIs as a blob by using:

select httpget(pictureurl) picture
from   me

This retrieves the picture, which is in the example below automatically rendered by Query Tool (50 shades of gray design):

Note that if you do a lot of httpget, each one occurs some overhead. Especially when downloading thousands of pictures, you will incur a lot of load on Exact Online. Do it only when necessary.

As an alternative, you can also use:

select *
from   exactonlinexml..items

The XML API of Exact Online returns the picture in the payload itself, reducing the number of round trips. Disadvantage is that if you have a 15.000 x 15.000 pixels x 32 depth image, you will get... exactly that, and it is huge and great and amazing. Please note that XML API uses HTTP compression (gzip).

Advantage of the URL used by the REST API is that it creates a thumbnail. That will generate some load on the Exact server (I don't know whether and how smart they cache), but reduces network constraints. Please note that REST API tables do NOT use HTTP compression, so compressible output still takes a lot of bandwidth. As you can see, there are a lot of design decisions to make and test, despite the ease of use of SQL, when doing large downloads and/or uploads.

You can probably fiddle with the thumbnail size as shown below:

Answer 2

The URL in the XML download of Data Access Point is XML encoded, which means some characters with special meaning has been replaced with their encoded variant. (like & -> &).

You have to decode the URL (there are plenty online tools available, and through some code it is possible too, depending on the programming language used) to its decoded variant. For your URL that is:

https://start.exactonline.nl/docs/SysImage.aspx?Table=Items&ID=3332e277-ba03-420e-84ce-21ac174c5ced&ThumbSize=500&NoCache=1&OptimizeForWeb=1&_Division_=875673

You have to be logged in on the web interface of Exact Online to be able to access that image by the way.

Answer 3

The picture is available when you are logged in to Exact Online.

Also you can retrieve the picture when you pass the oath Authorization Bearer in het header in the Get Request.

When you use the Exact Online Client API in C# it looks like this:

  using (var stream = client.GetAttachment(PictureUrl))
                        {
                            int ReadByte = stream.ReadByte();
                            List<byte> readlist = new List<byte>();

                            while (ReadByte >= 0)
                            {
                                readlist.Add((byte)ReadByte);
                                ReadByte = stream.ReadByte();
                            }
                            downloadData = readlist.ToArray();
                        }