Tips for Working with Binary Columns¶

For performance sake with Vinyl apps, only add a binary column to a Business Object if you are going to be using that column in a file control. If you are building a Business Object with a table that includes binary columns, do not select the All button to add all columns unless that object will be used to display a file control on a panel. It is worth it to create Business Objects specifically to use on panels where a file is uploaded/downloaded. Also, consider whether you may have added one to a user/param table, and could have added it to other Business Objects then selected all columns.

If you have an Multi-row Panel (MRP) with 25 records returning and there is a binary field selected on the Business Object behind that panel, it will retrieve all 25 files every time that panel is rendered. If you have 4 binary files on that Business Object, it will retrieve 100 files every time that panel is rendered.

If you want a file to be downloadable from an MRP, consider calling the download button in a hidden page so it doesn't retrieve the file as part of the MRP panel rendering.

Below is a query which you can run on your server that will find SOME of the files on a Business Object behind a panel where the file is not used in a control:

SELECT        TOP (100) PERCENT Ui_Control_1.Name AS Page, dbo.Ui_Control.Name AS Panel, dbo.Db_Table.Name AS DataObjectName, dbo.Db_Column.Name AS ColumnName
FROM            dbo.Ui_Control INNER JOIN
                         dbo.Ui_Control AS Ui_Control_1 ON dbo.Ui_Control.RootControlId = Ui_Control_1.ControlId INNER JOIN
                         dbo.Db_Table ON dbo.Ui_Control.SourceTableId = dbo.Db_Table.TableId INNER JOIN
                         dbo.Db_Column ON dbo.Db_Table.TableId = dbo.Db_Column.TableId INNER JOIN
                         dbo.Db_Column AS Db_Column_1 ON dbo.Db_Column.TargetColumnId = Db_Column_1.ColumnId INNER JOIN
                         dbo.Db_StorageDataType ON Db_Column_1.StorageDataTypeId = dbo.Db_StorageDataType.StorageDataTypeId LEFT OUTER JOIN
                         dbo.Ui_Control AS Ui_Control_2 ON dbo.Ui_Control.ControlId = Ui_Control_2.PanelControlId AND dbo.Db_Column.ColumnId = Ui_Control_2.SourceColumnId
WHERE        (dbo.Ui_Control.ControlTypeId = '81d20b00-15b2-f8c3-7fe1-d889d9c7e95f') AND (dbo.Db_StorageDataType.Name = N'binary') AND (Ui_Control_2.ControlId IS NULL)
ORDER BY DataObjectName