Use uploaded images/icons in maps HTML pop-up template

Hi K.Bakkenes,

Would it be an option to store the images in the database, so you can include the Base64 image data in the HTML?

For example by using an expression column as the HTML Popup Column of the maps component, with a SQL expression like this:

'<div><h1>' + t1.title_column + '</h1>
 <img src="data:image/png;base64,'
 + cast('' as xml)
 .value('xs:base64Binary(xs:hexBinary(sql:column("t1.image_data")))', 'nvarchar(max)') 
 + '"/>
</div>'

Hi Jasper,

thank you for your suggestion. I tested it and everything seems to work fine. The logo is shown in the HTML pop-up template of the Maps component and is also visible in a form.|
 

Hi Jasper,

we've already applied the solution to an image upload domain but we also like to change multiple existing filesystem uploads to database storage.
Does the SF automatically convert filesystem files to a database storage varbinary field?
If not, is there an easy way to do this? I’m assuming that Thinkwise (PI) also applied a method to convert every SF image to database storage in the transition to SF 2020.2.

Hi K., 

For the SF and IAM we have added (dynamically generated) process flows to upload files into the database, using recursive Read file connectors.

The process flow looks like this:

The file content is saved to the database using the process flow procedure of the Read file actions and looks like this:

create procedure prc_flow_upload_meta_files_to_db_read_file_tab_icon
(
   -- Process variables
   @file_data      file_data      output,
   @file_path      file_path      output,
   @pk_col_1       pk_col         output,
   @pk_col_2       pk_col         output,
   @pk_col_3       pk_col         output,
   @pk_col_4       pk_col         output,
   @pk_col_5       pk_col         output,
   @pk_col_6       pk_col         output,
   @pk_col_7       pk_col         output,
   @project_id     project_id     output,
   @project_vrs_id project_vrs_id output,
   @row_no         row_no         output,
   @status_code    status_code    output,

   -- Next process actions
   @read_file_tab_icon_stop               int output, 
   @read_file_tab_icon_read_file_tab_icon int output  
)

as
begin 
    declare @temp_row_no int

    -- Reset file path variable
    set @file_path = null

    if @row_no is null
    begin
        set @row_no = 0
    end

    -- Replace the file path with the file name and upload data to the data col
    if @file_data is not null
    begin    
        update t
           set icon = case
                        when icon like '%\%' then right(icon, charindex('\', reverse(icon)) -1)
                        else icon
                      end,
               icon_data = @file_data
          from tab t
         where project_id = @pk_col_1
           and project_vrs_id = @pk_col_2
           and tab_id = @pk_col_3
    end
    -- If no file data was found the path could not be read. Show a message in the panel.
    else
    begin
        -- Don't show message for first run of the process action since the file data will always be empty
        if @row_no > 0
        begin
            declare @msg_param varchar(400)
            set @msg_param = '<tab tabid="tab" transl="standard"></tab>'
            exec tsf_send_message 'upload_file_to_db_unsuccessful', @msg_param, 0
        end
    end

    -- Reset file_data variable
    set @file_data = null

    -- Select the first row that still contains a file path
    select top 1
        @pk_col_1 = t.project_id,
        @pk_col_2 = t.project_vrs_id,
        @pk_col_3 = t.tab_id,
        @file_path = t.icon,
        @temp_row_no = t.row_no
    from
    (
        select
            project_id,
            project_vrs_id,
            tab_id,
            icon,
            row_number() over (order by
                project_id,
                project_vrs_id,
                tab_id
                ) as row_no
        from tab
        where icon is not null
          and project_id = @project_id
          and project_vrs_id = @project_vrs_id
    ) as t
    where t.row_no > @row_no
      and t.icon like '%\%'

    -- If the row number was not incremented we can assume the last row has been reached
    if @temp_row_no > @row_no
    begin
        set @row_no = @temp_row_no
    end
    else
    begin
        set @row_no = null
    end

    -- If the last row is reached go to the next process action and reset the row number variable
    if @row_no is null
    begin
        set @read_file_tab_icon_stop = 1
        set @read_file_tab_icon_read_file_tab_icon = 0
        set @row_no = 0
    end
    -- If a file path is found do the same action again for the next row
    else
    begin
        set @read_file_tab_icon_stop = 0
        set @read_file_tab_icon_read_file_tab_icon = 1
    end


end
go

This code recursively retrieves the first row from the specified table (tab) that contains a full path (backslash) in the icon name, reads the file and the saves the data into the icons data field.

I have also attached the dynamic model code to generate the Read file actions and the control procedure + template to generate the process flow procedures. You’ll have to create the process flow and add the other steps and required process variables by hand first. 

Hi Jasper,

I've recreated the process flow in my branch. With a few tweaks the process flow (and procedure) worked like a charm. 

Thanks for the help!

Kevin