Handling Files in Blob

SQL for Handling files

Note: Fields `file_template_date_created`, `file_template_filename` , `file_template_filesize` are not to be included in datasource file ( file ds.xml) but to exist in SQL database table.

CREATE TABLE `templates` (
  `pk` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `file_template` longblob,
  `file_template_date_created` date DEFAULT NULL,
  `file_template_filename` varchar(255) DEFAULT NULL,
  `file_template_filesize` varchar(45) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Datasource file

Note: Fields `file_template_date_created`, `file_template_filename` , `file_template_filesize` are not to be included in datasource file ( file ds.xml)

<DataSource ID="prop_templates" serverType="sql" dataSourceVersion="1" dbName="proposal_system" tableName="templates">
 <fields>
 <field name="pk" type="sequence" primaryKey="true"/>
 <field name="file_template" type="binary" length=""/>
 <field name="product_id" type="integer"/>
 </fields>
 <allowAdvancedCriteria>true</allowAdvancedCriteria>
</DataSource>

ListGrid and DynamicForm XML

Note: ListGrid is not possible to upload file, after spending lots of hours of testing diagnosing what was going wrong I stopped and came to this conclusion.

<DynamicForm numCols="2" dataSource="prop_templates" ID="DF_product_templates" width="700" autoDraw="false">
 <fields>
  <FormItem name="file_template" title="File Template" constructor="FileItem"/>
  <FormItem name="product_id" title="Product Code" constructor="SelectItem">
        <showHover>true</showHover>
            <optionDataSource>prop_products</optionDataSource>
            <valueField>pk</valueField>
            <displayField>product_code</displayField>
            <detail>false</detail>
            <modalEditing>false</modalEditing>
            <autoFetchDisplayMap>true</autoFetchDisplayMap>
        </FormItem>
  <FormItem name="TI_upload_templates" width="200" constructor="ToolbarItem">
   <buttons>
     <ButtonItem name="BI_upload" title="Save">
              <click>
               <Action>
                   <target>DF_product_templates</target>
                   <name>saveData</name>
                   <title>Save Data</title>
               </Action>
             </click>
    </ButtonItem>
    <ButtonItem name="BI_new_upload" title="New">
             <click>
               <Action>
                  <target>DF_product_templates</target>
                  <name>editNewRecord</name>
                  <title>Edit New Record</title>
               </Action>
             </click>
    </ButtonItem>
  </buttons>
  </FormItem>
  </fields>
</DynamicForm>

<ListGrid dataSource="prop_templates" autoFetchData="true" ID="LG_templates" autoDraw="false">
 <fields>
  <ListGridField name="pk" title="ID">
    <canEdit>false</canEdit>
  </ListGridField>
  <ListGridField name="file_template" title="File Template">
    <canEdit>false</canEdit>
  </ListGridField>
  <ListGridField name="product_id" title="Product Code">
            <showHover>true</showHover>
            <optionDataSource>prop_products</optionDataSource>
            <valueField>pk</valueField>
            <displayField>product_code</displayField>
            <detail>false</detail>
            <modalEditing>false</modalEditing>
            <autoFetchDisplayMap>true</autoFetchDisplayMap>
   </ListGridField>
 </fields>
 <listEndEditAction>next</listEndEditAction>
 <showFilterEditor>true</showFilterEditor>
 <canEdit>true</canEdit>
 <canRemoveRecords>true</canRemoveRecords>
 <xsi:type>ListGrid</xsi:type>
</ListGrid>