+16A lot of times the answer to resolve improved GUI experience is to use generated HTML in the Universal GUI in combination with the card-list as this one auto-sizes. However treating VARBINARY columns are troublesome especially with regards to performance. Are there any best practices or tips out there to treat for example uploaded icons for categories?
Best answer by Anne Buit
Improving the editor to embed images will go a long way, indeed.
For now, there are two ways to go about embedding a file upload in a html control. Base64 and API link.
The former is easier, more robust and the latter is more performant.
You can create a html-control column that uses an expression to embed the image.
Base64:
-- Assuming img_upload as image upload column and img_data as binary data column
-- Assuming subject_with_cardlist as table
select
concat('<img src="data:image/',
case
when t1.img_upload like '%.png' then 'png'
when t1.img_upload like '%.svg' then 'svg'
when t1.img_upload like '%.jpg' then 'jpg'
--etc
end, ';base64,', cast('' as xml).value('xs:base64Binary(sql:column("t1.img_data"))', 'varchar(max)'), '">') as base64_html
from subject_with_cardlist t1
API link:
-- Assuming subject_with_cardlist as table
-- Assuming ../indicium/iam/product/ as relative URL from universal
-- Assuming SOME_PROJECT is the project
-- Assuming id as singular primary key
-- Assuming img_upload as image upload column
select
concat('<img src=../indicium/iam/product/subject_with_cardlist(', t1.id, ')/SOME_PROJECT.download_img_upload(file_id=null)>') as api_link_html
from subject_with_cardlist t1The second one has a lot more variables that can break (for instance, when deploying to another environment.
The resulting html for base64 looks as following:
<img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAADAAAAAwCAYAAABXAvmHAAAABmJLR0QA/wD/AP+gvaeTAAAEFklEQVRYCe1Z20tUQRye2aOthZkp5F9ghQQJSWJJaong6rq7rgb1FkHUeyC9+BJI2EtQRBBCb0LmXnV9UfKhQCzDqOz2ENVLF6xNjbzs7vTN5uoZd8/Zs5d258Hl950zZ+b3m/m+OXPm7MwhZPu33QMZ9QDNKHo9uPFhY0HJj7LaiCnSTBg9TCirQtE+YNMo+0oZnUP5c/iN/97za3qyaTK06ZBeKiMBFo+t1kTIeZByovkyIBWbZ4w+MDE64He6nqQSqPZNS0C7234UvXmNUdakriyD9AQjpGfU4ZlJtQ6aSoDVb90VCSnXEXQRceh8HLNnYdR7u2itsGfo9NAfo9UixpirzWOrCjE6hAA+vo0Fpef1koaVbn/X8Bsj4eCT3M3qcpzEcPHCsxjIhQUxRK3+TvejZI0lFcDHOyoZB3YDubQFzFanAjbfU71GdQV0eDsORCKmx6igHMiHfSdh5fhI1/B7rcY1BbQGWkuUFTNXX6kVnKP8dyZTpMZn8y0mak9zJlFWd/QhIN/kQYHsxyi4yhOJkPAOWLwdNaaIaQoBCiCDhSOE1AQcntmtZAq2ZvBrJaz0Y9ZJm7zf7ubVxMHqccTlGcxQ0NP98G0BBIsbQm1u+xGQbxK8JLighDRb3PbqrVTiBMDhEiCjUQyJOG6CAMw8Zih1ysiec8L/JWf3/e4dPB2DIADTZj0KSgFZrXy5IFSnJicIQO83qAtlTOP5bCSqnyCAEVKtKpM1KXAUp1HKKrE4MURca6rUC9aLMTzFMiq8XIU7APLiMlCPTb7KKKtQNy0KIKRYXShpWvhXLAjAQwyTlLYGLUEAHuJFDT+ZshfUZAQB2A75pi6UMs2owFGchRjl69CDRojrzRpas41ejJE2oz6UcY7RJD8IdwAPwAueKTkEjoIATKMTkpMnWKeMqzkKAkJFy1MoDAKy2rw5rAi7eIKAMcvYCobRoKzswWsQm16rOG+YIIDnYuk2wM8SgiXiFidgfX9yTEIBI4bXxNhQ6sXD0gIRWAThmKJlZboU2+SL+l4x699V3B3g2dHdMMru8LQkuJWo9zm3hAJ4wdLPvZdxfgbk22bC5pUeLRKaAibP3VvGjthZBM4D+TK+tXiGz45aBDQF8ABs573F2QIsArk2vrlr0dsX5YR0BXCHEYdnmirhOqQ/A7myTwplddFnMUmLSQXweH+H/xVENCA9C/xvm8Fe6Amv3TtnpCFDAnhFEPFh51rhMbypb+I6BGTVUO8acGMpWFofcLo+Gq2cGnVU+7W5HIcoZX3IawfSqgNxMWOMEB/+SF4Z7XS/jmUaPWfUeHQXm9ELaNyJBsuAVCz6mRWLqLvrb/9UYjd8MxIQq0XjQ3cFyksBbkEQ/YLvXln/0M0r38Z2D2TQA38BlXkvFvnk0o0AAAAASUVORK5CYII=">The resulting html for the API link looks as following:
<img src=../indicium/iam/product/subject_with_cardlist(8)/SOME_PROJECT.download_img_upload(file_id=null)>Does this help?
+5Hi Freddy,
Using a VARBINARY column should not lead to performance degradation by default. The card list uses an image upload as the left-most segment and should load these via the APPLICATION.download_[file]() api call - asynchronously.
The raw binary image data should not be accessible to the end user when the roles are set up in a way that the binary content column for the image upload column is unauthorized. This is practically the default unless you make the content column visible to the user somehow (you can check this via the Explain-task in the roles) or when you use an all-rights role.
+16Hi Freddy,
Using a VARBINARY column should not lead to performance degradation by default. The card list uses an image upload as the left-most segment and should load these via the APPLICATION.download_[file]() api call - asynchronously.
The raw binary image data should not be accessible to the end user when the roles are set up in a way that the binary content column for the image upload column is unauthorized. This is practically the default unless you make the content column visible to the user somehow (you can check this via the Explain-task in the roles) or when you use an all-rights role.
Hi Anne,
Point is that you would want to show these images as an inline preview in for example an HTML field in the main card list and then you cannot use this API.
And as the HTML component is very limited, what would be the best solution to show a question with a screenshot preview?
+5As a reminder, making this available as Freddy describes would still be a work around in my poit of view for the use of a real html wysiwyg editor, as this one you use in the forum:
Do you have any information about the improvements of the html field in the universal?
+5Improving the editor to embed images will go a long way, indeed.
For now, there are two ways to go about embedding a file upload in a html control. Base64 and API link.
The former is easier, more robust and the latter is more performant.
You can create a html-control column that uses an expression to embed the image.
Base64:
-- Assuming img_upload as image upload column and img_data as binary data column
-- Assuming subject_with_cardlist as table
select
concat('<img src="data:image/',
case
when t1.img_upload like '%.png' then 'png'
when t1.img_upload like '%.svg' then 'svg'
when t1.img_upload like '%.jpg' then 'jpg'
--etc
end, ';base64,', cast('' as xml).value('xs:base64Binary(sql:column("t1.img_data"))', 'varchar(max)'), '">') as base64_html
from subject_with_cardlist t1
API link:
-- Assuming subject_with_cardlist as table
-- Assuming ../indicium/iam/product/ as relative URL from universal
-- Assuming SOME_PROJECT is the project
-- Assuming id as singular primary key
-- Assuming img_upload as image upload column
select
concat('<img src=../indicium/iam/product/subject_with_cardlist(', t1.id, ')/SOME_PROJECT.download_img_upload(file_id=null)>') as api_link_html
from subject_with_cardlist t1The second one has a lot more variables that can break (for instance, when deploying to another environment.
The resulting html for base64 looks as following:
<img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAADAAAAAwCAYAAABXAvmHAAAABmJLR0QA/wD/AP+gvaeTAAAEFklEQVRYCe1Z20tUQRye2aOthZkp5F9ghQQJSWJJaong6rq7rgb1FkHUeyC9+BJI2EtQRBBCb0LmXnV9UfKhQCzDqOz2ENVLF6xNjbzs7vTN5uoZd8/Zs5d258Hl950zZ+b3m/m+OXPm7MwhZPu33QMZ9QDNKHo9uPFhY0HJj7LaiCnSTBg9TCirQtE+YNMo+0oZnUP5c/iN/97za3qyaTK06ZBeKiMBFo+t1kTIeZByovkyIBWbZ4w+MDE64He6nqQSqPZNS0C7234UvXmNUdakriyD9AQjpGfU4ZlJtQ6aSoDVb90VCSnXEXQRceh8HLNnYdR7u2itsGfo9NAfo9UixpirzWOrCjE6hAA+vo0Fpef1koaVbn/X8Bsj4eCT3M3qcpzEcPHCsxjIhQUxRK3+TvejZI0lFcDHOyoZB3YDubQFzFanAjbfU71GdQV0eDsORCKmx6igHMiHfSdh5fhI1/B7rcY1BbQGWkuUFTNXX6kVnKP8dyZTpMZn8y0mak9zJlFWd/QhIN/kQYHsxyi4yhOJkPAOWLwdNaaIaQoBCiCDhSOE1AQcntmtZAq2ZvBrJaz0Y9ZJm7zf7ubVxMHqccTlGcxQ0NP98G0BBIsbQm1u+xGQbxK8JLighDRb3PbqrVTiBMDhEiCjUQyJOG6CAMw8Zih1ysiec8L/JWf3/e4dPB2DIADTZj0KSgFZrXy5IFSnJicIQO83qAtlTOP5bCSqnyCAEVKtKpM1KXAUp1HKKrE4MURca6rUC9aLMTzFMiq8XIU7APLiMlCPTb7KKKtQNy0KIKRYXShpWvhXLAjAQwyTlLYGLUEAHuJFDT+ZshfUZAQB2A75pi6UMs2owFGchRjl69CDRojrzRpas41ejJE2oz6UcY7RJD8IdwAPwAueKTkEjoIATKMTkpMnWKeMqzkKAkJFy1MoDAKy2rw5rAi7eIKAMcvYCobRoKzswWsQm16rOG+YIIDnYuk2wM8SgiXiFidgfX9yTEIBI4bXxNhQ6sXD0gIRWAThmKJlZboU2+SL+l4x699V3B3g2dHdMMru8LQkuJWo9zm3hAJ4wdLPvZdxfgbk22bC5pUeLRKaAibP3VvGjthZBM4D+TK+tXiGz45aBDQF8ABs573F2QIsArk2vrlr0dsX5YR0BXCHEYdnmirhOqQ/A7myTwplddFnMUmLSQXweH+H/xVENCA9C/xvm8Fe6Amv3TtnpCFDAnhFEPFh51rhMbypb+I6BGTVUO8acGMpWFofcLo+Gq2cGnVU+7W5HIcoZX3IawfSqgNxMWOMEB/+SF4Z7XS/jmUaPWfUeHQXm9ELaNyJBsuAVCz6mRWLqLvrb/9UYjd8MxIQq0XjQ3cFyksBbkEQ/YLvXln/0M0r38Z2D2TQA38BlXkvFvnk0o0AAAAASUVORK5CYII=">The resulting html for the API link looks as following:
<img src=../indicium/iam/product/subject_with_cardlist(8)/SOME_PROJECT.download_img_upload(file_id=null)>Does this help?
+16Thanks
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.