The form exetension allows creating multilingual questionnaire type custom forms. The form is posted to a myDBR linked report which receives the user entered valued as parameters (via HTTP POST).
dbr.form
- Create a form
select 'dbr.form', 'command', options..
Where command:
Enabling the extension includes a CSS file and a JS file from extensions/form. The CSS file styles the form with custom style. The JS file handles the optional multilingual aspects of the form: changing the form language keeps the user changes to the form and handles the required field settings on dependent elements.
The form report consist of:
The form definition consist of a linked report that handles the form submission and the creation of the form tag:
select 'dbr.report', 'sp_DBR_form_submit', '[form_action]', concat('append=&i=0',f_lang_append(GET_lang)), 'plain_URL=1'; select 'dbr.form', 'start', 'cr_form'; select '' as 'form_action';
The select 'dbr.form', 'start', 'cr_form';
-command creates the form-tag with name 'cr_form'.
The append
part the forallows submitting empty forms and optionally adds the GET lang-paramerter to the URL. The plain_URL
-option creates just the URL instead of a-tag.
The optional f_lang_append
-function checks the lang-parameter. If you do not need multilingual parameters, you can ignore this.
create function f_lang_append(lang char(5)) returns varchar(20) begin if (lang in ('en_US', 'de_DE', 'el_GR', 'es_ES', 'fi_FI', 'it_IT', 'nl_NL', 'sv_SE')) then return concat('&lang=',lang); end if; return ''; end
One can insert any elements into the form via dbr.html
-command. A specific select tag element with name 'lang' is reserved for creating multilingual reports. myDBR handles the form language change with defined translations while preserving the user choises in the form.
select 'dbr.html', '<div class="lang-choise">'; select 'dbr.form', 'select', 'lang', null, 'plain'; select 'fi_FI', 'Suomi' union select 'en_US', 'English' union select 'sv_SE', 'Svenska' union select 'el_GR', 'Ελληνικά' union select 'de_DE', 'Deutsche' union select 'es_ES', 'Española' union select 'it_IT', 'Italiana' union select 'nl_NL', 'Nederlandse' ;
The command select 'dbr.form', 'select', 'lang'
will generate the select tag with options where the first value from the result set is the option value and the second one is the name. The 'plain'-option will generate just the select tab and not any additional tags around it as it would do for actual form elements.
Sections start with section.start
or section.start.no_highlite
options for dbr.form
-command. The no_highlite
-definition disables the sections CSS hover (light blue highlite). The 'dummy' resultset after the command triggers the command to be effective (dbr.form is a myDBR extension).
select 'dbr.form', 'section.start.no_highlite'; select 'dummy';
Sections end with section.end
options for dbr.form
-command
select 'dbr.form', 'section.end'; select 'dummy';
One can combine the two commands into one when separating the options with comma:
select 'dbr.form', 'section.end,section.start'; select 'dummy';
Radio buttons consist of one or more choises for the same parameter. The name of the parameter in the example will be 'how_long_used' with 'in_'-added in front of it i.e 'in_how_long_used'. The parameter for the submit linked report's parameter will be 'in_how_long_used'.
select 'dbr.form', 'radio', 'how_long_used', 'How long have you used myDBR?'; select 0, 'Have not yet used it' union select 1, '0 - 1 years' union select 2, '1 - 5 years' union select 3, '5 - 10 years' union select 4, 'Over 10 years';
This will create a element like this:
One can add additional fields for radion button selection with dbr.record. This is useful when you need to ask user clarification or select a value from select list for individual radio button selection.
select 'dbr.record', 'begin', 'other_products'; select 'dbr.form', 'text', 'other_product', null, 30, 'required'; select 'dummy'; select 'dbr.record', 'end'; select 'dbr.record', 'begin', 'other_products_choose'; select 'dbr.form', 'select', 'other_products_chosen', null, 'required'; select null, '' union select 'prod_a', 'Product A' union select 'prod_b', 'Product B'; select 'dbr.record', 'end'; select 'dbr.form', 'radio', 'how_long_used', 'Have you used other products?'; select 0, 'No', null union select 1, 'Choose, what', '{#other_products_choose}' union select 2, 'Other, what', '{#other_products}';
The required
-attribute means that the field in question is a required field if the corresponding radio button is selected.
Note that the select and radio elements not not need the 'dummy'-selection as the result set does already that.
This will create a element like this:
The select list can be defined as:
select 'dbr.form', 'select', 'database', 'Primary database'; select null, '' union select 'mysql', 'MySQL' union select 'mariadb', 'MariaDB' union select 'mssql', 'Microsoft SQL Server' union select 'sybase', 'Sybase';
This will create a element like this:
A table element consist of multiple parameters with number of values:
select 'dbr.form', 'table', null, 'Overall evaluation', 'Excellent', 'Good','Fairly good', 'Satisfactory', 'Poor'; select 'overall_evaluation', 'Overall evaluation' union select 'product_quality', 'Product quality' union select 'documentation', 'Documentation' union select 'support', 'Support' union select 'value_for_money', 'Value for money';
This will create a element like this:
The values for the parameters (like 'overall_evaluation') will be from 5 to 1 (Excellent=5, Good=4 etc)
Checkboxes can be grouped together and arranged into columns:
select 'dbr.form', 'checkbox', 3, ' Authentication'; select 'google', 'Google' union select 'ad', 'Active Directory' union select 'custom_login', 'Custom login' union select '2fa', 'Two-factor authentication';
This will create a element like this:
Checkboxes like radio buttons can have additional elemenrs (dropdown, text) attached into individual choises. See the full example below.
The textarea allows multiline input whilas the rext is a simple text field
select 'dbr.form', 'textarea', 'comment', 'Comments / improvement ideas'; select 'dbr.form', 'text', 'contact_info', 'Contact info', 50; select 'dummy';
The form-extension relies on the HTML required-attribute. Fields that are parameters to the submit linked report get their required-attribute status from parameter's 'Optional' attribute. If the 'Optional'-attribute is unchecked, the required attribute is added to the field in submit.
The form submit report (see sp_DBR_form_submit above) will get the parameters from form's POST). The submit report should do a redirect to another report after processing the values so that if the user refreshes the page the submit will not be repeated.
CREATE PROCEDURE `sp_DBR_feedback_doc`( GET_lang varchar(30) ) BEGIN /* We do not need the title */ select 'dbr.title', ''; /* The submit report &i=0 will run the submit report even with empty form f_lang_append(GET_lang) will pass the 'lang' URL parameter to the submit report plain_URL places the plain URL to the report to form's action attribute */ select 'dbr.report', 'sp_DBR_feedback_doc_submit', '[form_action]', concat('append=&i=0',f_lang_append(GET_lang)), 'plain_URL=1'; /* Create a form with id cr_form */ select 'dbr.form', 'start', 'cr_form'; select 'form_action'; /* Create a section wihtout background highlite for the logo and and the language selection */ select 'dbr.form', 'section.start.no_highlite'; select 'dummy'; select 'dbr.html', '<div style="text-align:center"><img src="user/images/myDBR.svg" style="width:400px"></div>'; /* Create optional language select tag and center it */ select 'dbr.html', '<div class="lang-choise" style="text-align:center">'; select 'dbr.form', 'select', 'lang', null, 'plain'; select 'fi_FI', 'Suomi' union select 'en_US', 'English' union select 'sv_SE', 'Svenska' union select 'el_GR', 'Greek' union select 'de_DE', 'German' union select 'es_ES', 'Spanish' union select 'nl_NL', 'Dutch' ; select 'dbr.html', '</div>'; select 'dbr.form', 'section.end,section.start'; select 'dummy'; /* Record the other_products_choose record with a select element Note the required option (see below) */ select 'dbr.record', 'begin', 'other_products_choose'; select 'dbr.form', 'select', 'other_products_chosen', null, 'required'; select null, '' union select 'prod_a', 'Product A' union select 'prod_b', 'Product B'; select 'dbr.record', 'end'; /* Create a radio selection and use the recorded elements The exrta fields (other_products_choose and other_products) were marked as required. If the respective radio button is chosen, the extra field needs to be filled */ select 'dbr.form', 'radio', 'used_another_product', 'Have you used other products?'; select 0, 'No', null union select 1, 'Choose, what', '{#other_products_choose}' union select 2, 'Other, what', null; select 'dbr.form', 'section.end,section.start'; /* Create a select field */ select 'dbr.form', 'select', 'database', 'Primary database'; select null, '' union select 'mysql', 'MySQL' union select 'mariadb', 'MariaDB' union select 'mssql', 'Microsoft SQL Server' union select 'sybase', 'Sybase'; select 'dbr.form', 'section.end,section.start'; /* Create a table element with values from 5 to 1 */ select 'dbr.form', 'table', null, 'Overall evaluation', 'Excellent', 'Good','Fairly good', 'Satisfactory', 'Poor'; select 'overall_evaluation', 'Overall evaluation' union select 'product_quality', 'Product quality' union select 'documentation', 'Documentation' union select 'support', 'Support' union select 'value_for_money', 'Value for money' ; select 'dbr.form', 'section.end,section.start'; /* Create a checkbox element in 3 columns */ select 'dbr.form', 'checkbox', 3, ' Authentication'; select 'google', 'Google' union select 'ad', 'Active Directory' union select 'custom_login', 'Custom login' union select '2fa', 'Two-factor authentication' ; select 'dbr.form', 'section.end,section.start'; select 'dummy'; /* Create a textarea */ select 'dbr.form', 'textarea', 'comment', 'Comments / improvement ideas'; /* Create an input field */ select 'dbr.form', 'text', 'contact_info', 'Contact info', 50; select 'dummy'; select 'dbr.form', 'section.end'; /* The submit button */ select 'dbr.form', 'submit', 'Submit'; select 'dbr.form', 'end'; select 'dummy'; END
This will produce a form like this:
CREATE PROCEDURE `sp_DBR_feedback_doc_submit`( in_lang varchar(10), in_used_another_product int, in_other_products_chosen varchar(20), in_database varchar(20), in_overall_evaluation tinyint, in_product_quality tinyint, in_documentation tinyint, in_support tinyint, in_value_for_money tinyint, in_google int, in_ad int, in_custom_login int, in_2fa int, in_comment text, in_contact_info text ) begin insert into doc_feedback2 ( language, used_another_product, other_products_chosen, db, overall_evaluation, product_quality, documentation, support, value_for_money, google, ad, custom_login, auth2fa, comment, contact_info ) values ( in_lang, in_used_another_product, in_other_products_chosen, in_database, in_overall_evaluation, in_product_quality, in_documentation, in_support, in_value_for_money, in_google, in_ad, in_custom_login, in_2fa, in_comment, in_contact_info ); select 'dbr.redirect', 'sp_DBR_questionnaire_thank_you', concat('append=&m=0&hdr=0',f_lang_append(in_lang)); select 'dummy'; end
The parameters that are not mandatory are set as 'Optional'. The select list 'other_products_chosen' is set to be 'required' in the report code and myDBR will make it required if the 'used_another_product' parameter is chosen.