Display mssql varbinary(max) pictures in form

[vogel61]

I am sure it must be possible to display pictures that are stored in a mssql database in sqlpage. Maybe someone may direct me in the right direction or show me a syntax example ...

[lovasoa]

Yes, it is possible! In what form are they stored in your database? The goal is to convert them to data URLs and pass the result as an image link.

[vogel61]

thanks a lot for your answer. Originally, they were jpgs, around 250x250, now residing inside a varbinary(max).

Do I have to encode it in the sql query? How could the code for displaying them in sqlpage look like?

BTW, deepseek r1 directed me to sqlpage as the preferred solution for my problem (I quickly needed a small meal voucher application with barcode scanning). Within one day it was up and running! It is a great tool for hassle-free web based frontends.

Now I got greedy :-) and tried to implement a very simple warehouse management for our stock items. I gloriously failed to be able to properly display the item thumbnails, let alone being able to swap pictures with updated ones on the local hard drive.

Perhaps you can direct me in the right direction to make this work?

Kind regards, Richard

[lovasoa]

Nice to see deepseek recommending us! What was your prompt, out of curiosity?

Here is a suggestion to generate the image data URLs from mssql:

-- Create a simple warehouse management table
CREATE TABLE WarehouseItems (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(100),
    Quantity INT,
    Thumbnail VARBINARY(MAX)  -- Store image data
);

-- Insert sample data with thumbnails
INSERT INTO WarehouseItems (Name, Quantity, Thumbnail)
VALUES 
  ('Box A', 50, 0x89504E470D0A1A0A),  -- Sample PNG header
  ('Pallet B', 20, 0xFFD8FFE000104A46), -- Sample JPEG header
  ('Container C', 5, 0x89504E470D0A1A0A); -- Another PNG

-- Define SQLPage card component
SELECT 'card' AS component, 'Warehouse Inventory' AS title, 2 AS columns;

-- Provide a row for each item
SELECT 
    Name AS title,
    'Quantity: ' + CAST(Quantity AS VARCHAR) AS description,
    'data:image/png;base64,' + 
    (SELECT Thumbnail FOR XML PATH(''), BINARY BASE64, TYPE).value('.', 'VARCHAR(MAX)') AS top_image
FROM WarehouseItems;

Replace image/png with
image/jpeg
image/gif
image/bmp
image/webp
image/tiff
image/svg+xml
etc. depending on the format of your images

[vogel61]

Wow! so fast, will try ASAP!

The prompt I made was in german, so I have had it translated to english by deepl:

I have a mssql database in which information about meal vouchers is stored (voucher number, who received the voucher). I need a simple web-based interface that can be used to issue and return vouchers to specific employees (by scanning a QR code). A stored procedure takes care of all the internals, on the interface only the mode (issue to employee X and central return of the used vouchers) has to be set and then the vouchers are scanned one after the other by inserting the barcode into a table field. That's all for now. What is the easiest way to connect the mssql database to a web-based interface and which freely available tools do I need for this?

Translated with DeepL.com (free version)

in the course of the conversation I uploaded the database create and deepseek did fairly well, but later started to hallucinate quite a bit and merged sqlpage and t-sql, but by going forth and back and reading your documention, it took me about 4 hrs until the app was finished ...

[lovasoa]

If you are on azure sql, there is this function https://learn.microsoft.com/en-us/sql/t-sql/functions/base64-encode-transact-sql?view=azuresqldb-current

[vogel61]

Ophir, I tried it out (adapted a little bit), the sql query works fine within sql server, but I get a syntax error if I issue the very same sql in sqlpage (see attached screendump). Any ideas about what I made wrong? kr Richard Am So., 9. Feb. 2025 um 10:05 Uhr schrieb Ophir LOJKINE < ***@***.***>:

…

Nice to see deepseek recommending us! What was your prompt, out of curiosity? Here is a suggestion to generate the image data URLs from mssql: -- Create a simple warehouse management tableCREATE TABLE WarehouseItems ( ID INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(100), Quantity INT, Thumbnail VARBINARY(MAX) -- Store image data ); -- Insert sample data with thumbnailsINSERT INTO WarehouseItems (Name, Quantity, Thumbnail)VALUES ('Box A', 50, 0x89504E470D0A1A0A), -- Sample PNG header ('Pallet B', 20, 0xFFD8FFE000104A46), -- Sample JPEG header ('Container C', 5, 0x89504E470D0A1A0A); -- Another PNG -- Define SQLPage card componentSELECT 'card' AS component, 'Warehouse Inventory' AS title, 2 AS columns; -- Provide a row for each itemSELECT Name AS title, 'Quantity: ' + CAST(Quantity AS VARCHAR) AS description, 'data:image/png;base64,' + (SELECT Thumbnail FOR XML PATH(''), BINARY BASE64, TYPE).value('.', 'VARCHAR(MAX)') AS top_imageFROM WarehouseItems; — Reply to this email directly, view it on GitHub <#803 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AYK22YAOOC37EGMTIHGEJDT2O4K7BAVCNFSM6AAAAABWYRNCI2VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTEMJQG42TAMI> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[vogel61]

to be exact, I used perplexity.ai and used r1 for reasoning ... Richard Vogel ***@***.***> schrieb am So., 9. Feb. 2025, 11:22:

…

Ophir, I tried it out (adapted a little bit), the sql query works fine within sql server, but I get a syntax error if I issue the very same sql in sqlpage (see attached screendump). Any ideas about what I made wrong? kr Richard Am So., 9. Feb. 2025 um 10:05 Uhr schrieb Ophir LOJKINE < ***@***.***>: > Nice to see deepseek recommending us! What was your prompt, out of > curiosity? > > Here is a suggestion to generate the image data URLs from mssql: > > -- Create a simple warehouse management tableCREATE TABLE WarehouseItems ( > ID INT IDENTITY(1,1) PRIMARY KEY, > Name VARCHAR(100), > Quantity INT, > Thumbnail VARBINARY(MAX) -- Store image data > ); > -- Insert sample data with thumbnailsINSERT INTO WarehouseItems (Name, Quantity, Thumbnail)VALUES > ('Box A', 50, 0x89504E470D0A1A0A), -- Sample PNG header > ('Pallet B', 20, 0xFFD8FFE000104A46), -- Sample JPEG header > ('Container C', 5, 0x89504E470D0A1A0A); -- Another PNG > -- Define SQLPage card componentSELECT 'card' AS component, 'Warehouse Inventory' AS title, 2 AS columns; > -- Provide a row for each itemSELECT > Name AS title, > 'Quantity: ' + CAST(Quantity AS VARCHAR) AS description, > 'data:image/png;base64,' + > (SELECT Thumbnail FOR XML PATH(''), BINARY BASE64, TYPE).value('.', 'VARCHAR(MAX)') AS top_imageFROM WarehouseItems; > > — > Reply to this email directly, view it on GitHub > <#803 (reply in thread)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AYK22YAOOC37EGMTIHGEJDT2O4K7BAVCNFSM6AAAAABWYRNCI2VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTEMJQG42TAMI> > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> >

[vogel61]

Thank you, I made a view with encoded pics, that works ... Am So., 9. Feb. 2025 um 12:01 Uhr schrieb Ophir LOJKINE < ***@***.***>:

…

If you are on azure sql, there is this function https://learn.microsoft.com/en-us/sql/t-sql/functions/base64-encode-transact-sql?view=azuresqldb-current — Reply to this email directly, view it on GitHub <#803 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AYK22YAVLIJFE2MNT54C7AT2O4YQZAVCNFSM6AAAAABWYRNCI2VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTEMJQHAYTANI> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[lovasoa]

great! can you share your code, and the initial incorrect syntax error so that we can fix it?

[vogel61]

The original code with your suggestion is the window in the middle, left top shows that it works if issued from mssql server directly, to the right you see the error:

https://drive.google.com/drive/folders/1VXJytWcc9ukiz_0Y2HBxNrOZKc_Ek-rp?usp=sharing (does not work directly here, need to copy)

Finally, I created a view using the below select and this view could be used directly to show pictures from the database on a card:

SELECT id, part, descr,
{fn CONCAT('data:image/jpeg;base64,', CAST('' AS XML ).value('xs:base64Binary(sql:column("pic"))', 'VARCHAR(MAX)'))} AS pic
FROM dbo.article

I wish I knew how to scale down the pic on the card, because it spreads all over the width of the card, or even better, if tables would also display pics, because there I can easily sort and filter. The thumbnail pics are just a visual aid to quickly identify the correct article.
I would be even happy to only show the pic once the form editing ist started, but this does not work also.

[lovasoa]

Could you copy and paste the contents of some of the values in the pic column here so that we can debug why they are not showing ?

[vogel61]

artikel.txt

I reduced the pic to a manageable size, card shows, table not. It seems that the markup code is not interpreted.

[vogel61]

Any new findings as why this does not woek as expected? Did I do anything wrong?

[lovasoa]

Hello and sorry for the delay !

I tracked down the issue to the markdown library we are using, which (incorrectly) considers data-URLs unsafe.

I'll add a configuration option to allow configuring the markdown renderer in the next version.

[lovasoa]

I also wrote a fix for the markdown library we use, we now have to get it accepted: wooorm/markdown-rs#164

[vogel61]

I am on a business trip today and have no access to this data. I will come back to you tomorrow. Ophir LOJKINE ***@***.***> schrieb am Mo., 10. Feb. 2025, 14:18:

…

Could you copy and paste the contents of some of the values in the pic column here so that we can debug why they are not showing ? — Reply to this email directly, view it on GitHub <#803 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AYK22YCIT4IBIKP4BE74CQL2PCRKZAVCNFSM6AAAAABWYRNCI2VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTEMJRHE2DKNY> . You are receiving this because you authored the thread.Message ID: ***@***.***>