6

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?

2
  • What does the error say? Also are you sure its an image and now a webpage? You can check that by using F12 browser developers tools and in the network tab. Feb 23 '17 at 9:59
  • I get an 'Oops'-error in return. But with the solution in the answer below I'm seeing the image now!
    – RH1985
    Feb 23 '17 at 10:15
2

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):

Retrieve picture blob

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:

Changing thumbnail size

1
  • It worked, with the IMAGE_BINARYDATA field from exactonlinexml..items. Thanks! It's a huge data-field, but it's the only one that doesn't requires to be logged in.
    – RH1985
    Mar 3 '17 at 11:54
4

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.

8
  • I got it working now, but only because I'm logged in indeed. Is there a function/API to retrieve the image without being logged in?
    – RH1985
    Feb 23 '17 at 10:19
  • No, it is a security feature, which is okay in my opinion. Let me see if you can retrieve it using Invantive SQL. Feb 23 '17 at 10:21
  • It's very okay that it's a security feature, but I hoped there would be an exception for the images to display them on a website with the Exact Online products. Thanks!
    – RH1985
    Feb 23 '17 at 10:23
  • No, that would mean anyone could download your images, also the user images, etc. Not okay! :) Feb 23 '17 at 10:24
  • True, didn't think about that :)
    – RH1985
    Feb 23 '17 at 10:31
0

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();
                        }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.