Editable reports

Commands

dbr.editable - Prepares a column so it is in-place editable
dbr.upload - Upload a file to the server's filesystem
dbr.upload.options - Set an upload option
dbr.selectable - Convert an ID column to a selectable row to be used with a report linked button
dbr.selectable.selected - Preselect selectable rows by passing the ID value as a parameter

Syntax

select 'dbr.editable', ColumnReference, procedure_name, [,parameter_name=paramref] [,type=input_type] [,select=query [[ColumnReference]] ] [,options={'jeditable_options'}]
select 'dbr.upload', path_to_directory, upload_sp [, parameters]
select 'dbr.upload.options', option, value
select 'dbr.selectable', ColumnReference
select 'dbr.selectable.selected', id

Where:

ColumnReference
The column which will be made editable
procedure_name
Editing report's procedure. Permissions to this report determine if the user is allowed to edit the column
parameter_name
Refers to a editing stored procedure's / URL's parameter name to which we want to pass value to
ColumnReference/ParameterReference
A column value or a parameter value to be passed on to the editing report as a value for parameter_name.
type
Allows you to choose from special input types:
  • 'textarea'
  • 'autosize' (autosizing textarea)
  • 'select' (standard selectlist)
  • 'select_find' (selectlist with find option)
  • 'autocomplete' (uses Ajax to fetch the data)
  • 'radiobutton'
  • 'checkbox'
  • 'date' / 'datepicker'
  • 'datetime' / 'datetimepicker'
  • 'time' / timepicker'
  • 'color' / 'colorpicker'
  • 'richtext'
  • 'file_rename'
  • code editing specific types 'html', 'php', 'javascript', 'css' and 'sql'
If no input type is specified, a normal input-field is displayed. Type 'select_find' is a selectlist with search functionality. For code editing specific types, enable the 'Code editing' report extension so the editor component will be included in the report.
select

Defines the selection query used when the type is 'select' or 'radiobutton.' The query should return the ID and the user-visible value. It can be a direct select query or a stored procedure call. If a stored procedure is used, it can leverage automatic parameters, and its parameters may refer to both the result set columns and report parameters.

You have the option to use the [ColumnReference] notation to reference the columns in the particular row in the query when utilizing a stored procedure.

If the type is set to 'select' or 'select_find,' you can employ opgroups by specifying the group as the third column in the select query.

checkbox_id
When using 'checkbox' type, defines the ColumnReference for the checkbox item IDs.
options
myDBR uses Jeditable to make cells editable and its parameters can be passed on with options

additional options for radiobutton
  • 'autosubmit': 1Does not display OK and Cancel buttons
  • 'display':'inline'Places items side-by-side
additional options for checkbox
  • 'scroll': 1Shows checkboxes in a scrollable list
  • 'find': 1Allows textual search
  • 'collapse': 1Allows collapsing the list
  • 'ui_height': 200Height of the scrollable list in px
path_to_directory
Full path to server directory where the uploaded files will be stored
upload_sp
The procedure that will be called once the upload is done
upload option
To configure the upload functionality
  • titleTitle the be shown in upload
  • noreplacePrevent overwriting files (value = 1)
  • accepted_filesA comma-separated list of accepted file extensions
  • maxfilesmaximum number of uploaded files
  • maxfilesizemaximum size of the uploaded file in megabytes
  • maxheightmaximum height of the uploaded image file in pixels
  • maxwidthmaximum width of the uploaded image file in pixels
  • qualityThe quality of the resized JPG/WebP images (0 to 1, default 0.8)

Explanation

A report column can be marked as editable (in-place editing) with the dbr.editable command. The command takes column number and parameter references as parameters. When the user clicks on the editable column, an in-place editor is shown and the edited value with specified parameters is sent to the procedure that was given as a parameter. The procedure can perform the update or reject the update and return the original value. If the editing procedure returns the value (single row, single column), myDBR will format the cell using the defined cell formatting.

Preventing editing certain cells

Sometimes not all rows in a column or columns in crosstab should be edited. You can prevent editing on selected cells by adding show_link-parameter to the dbr.editable command. You can also use 'no_edit'-class to the cell by using the dbr.cellclass command.

(If you wish to edit more than one column at a time see Creating editable reports for more info.)

Using options

With options you can:

  • Change the type of the editable field to something other than a basic input field.
    select 'dbr.editable', 'col', 'sp_DBR_budget_edit_comment', 'inID=is', 'inQuarter=Q', 'type=textarea', "options={'rows':6,'cols':20}";
  • Define the size of the editable field
    select 'dbr.editable', 'col', 'sp_DBR_budget_edit', 'inId=id', 'inQuarter=Q', "options={'height':20}";
  • Provide a callback javascript method to be called after the editing is done. myDBR provides two ready-made callback methods: autosum_int (integers) and autosum_float (decimal numbers) which will refresh the summary column for the edited column.

    Usage:
    select 'dbr.editable', 'col', 'sp_DBR_budget_edit', 'inId=id', 'inQuarter=Q', "options={'height':20, 'callback':autosum_int}";

    ยง A callback function is a JavaScript function (that you can define with dbr.javascript) that has two parameters. First parameter is the value or values JSON. If the editing report returns just single value the first parameter contains that value. If editing report returns multiple columns, the first parameter is a JSON string that you can parse in a callback with JSON.parse. An example of returning multiple columns would be that you want to change other columns based on the editing. (The second optional parameter contains the Jeditable settingsm which you probably do not need).
    myDBR also offers callback helper functions which allow you to make updates to other columns based on the user editable cell. Following list of javascript-functions are available:
    • col_value_get( obj[, column [, decimals [, value [, prefix [, suffix]]]]] )
      get a value for cell. With no decimals defined deals with integers. The column can be the column index or a class.
    • col_value_set( obj[, column [, decimals [, value [, prefix [, suffix]]]]] )
      set a value for cell. With no decimals defined deals with integers. The column can be the column index or a class.
    • ct_total( obj[, NULL [, decimals [, value [, prefix [, suffix]]]]] )
      Calculate horizontal aggregate from first parameter's cell. The first parameter should point to first data column.
    • col_value_text(obj [, column[, value]])
      get/set textual column value. The column can be the column index or a class.
    • col_sum( obj [, column [, decimals[, prefix[, suffix]]]] )
      Returns the sum of the column. column can be column index or a class. The column can be the column index or a class.
    • autosum_int( obj, [colindex [, prefix[, suffix]]] )
      Updates the sum of the column. If no column is defined the user editable column is updated. The column can be the column index or a class.
    • autosum_float( obj, [colindex [, decimals [, prefix[ , suffix]]]] )
      Updates the sum of the column as a decimal number. If no column is defined the user editable column is updated. The column can be the column index or a class.
    obj is reference to the edited cell (javascript 'this').

Basic example

In the example, we'll define the in-place editing to be available in the following cross-table report. We'll use the dbr.editable command to mark column 4 (Budget) to be updateable with the procedure sp_DBR_update_crosstable.

Parameters for the command in the example are:

  • id
    The column to be edited
  • sp_DBR_update_crosstable
    The myDBR report the edited value is passed to
  • inCategory=cat
    sp_DBR_update_crosstable's inCategory-parameter is passed the value for selected 'cat' column value (Category)
  • inWeek=Week
    sp_DBR_update_crosstable's inWeek-parameter is passed the value for selected 'Week' column value (payment_week)

create procedure sp_DBR_InPlaceEditing
begin

select 'dbr.editable', 'Budget', 'sp_DBR_update_crosstable', 'inCategory=cat', 'inWeek=week';

select 'dbr.crosstab', 'Week';
select 'dbr.count', 'cat';
select 'dbr.sum', 'Total';
select 'dbr.hsum', 'Total';
select 'dbr.hidecolumns', 1;

select  category as 'Film category[cat]', 
  payment_week 'Week', 
  amount as 'Total', 
  budget as 'Budget', 
  category_id
from film_budget;

end

The report that is doing the actual updating can either reject the update and return the original value or simply update the new value. The report gets all parameters specified in the dbr.editable-call and an additional parameter for the value.

The last parameter for the editing procedure is automatically populated by the value of the edited field. You can name the parameter as you like (inValue in the example).

create procedure sp_DBR_update_crosstable(
inCategory int,
inWeek int,
inValue float
)
begin

/* Check the data */
if (inValue>0) then
  /* Accept the update, no need to return a value */
  update film_budget
  set budget = inValue
  where category_id=inCategory and payment_week=inWeek;
else 
  /* Reject the update, return the original value */
  select budget
  from film_budget
  where category_id=inCategory and payment_week=inWeek;
end if;

end

In the example, we'll define the in-place editing to be available in the following cross-table report (Budget column under weeks).

Editing callback with multiple return values

When the editing procedure returns more than one column, the return values are sent back as a JSON string.

create procedure sp_DBR_edit_with_callback_JSON()
BEGIN

select 'dbr.javascript', '
function country_callback(val)
{
/* 
'this' is the edited cell
'val' is a JSON string as the editing report returns multiple columns
The JSON string can be parsed to a JSON object using JSON.parse
*/
var c = JSON.parse(val);
this.textContent = c.Name;
this.nextSibling.textContent = c.Code;
this.nextSibling.nextSibling.textContent = c.Continent;
}
';

select 'dbr.editable', 'Country', 'sp_DBR_country_edit', 'inPerson=Person', 'type=select_find', 'select=select Code, Name from demo_country', "options={'callback':country_callback}";

select p.id as 'PersonID', p.Person, c.Name as 'Country', c.Code as 'Country Code', c.Continent
from Persons p
 join Countries on c.Code=p.CountryCode;

end

The Country-column is editable, user chooses from the selectlist the value and the selected Country's code is passed on to the editing report

drop procedure sp_DBR_country_edit
$$
create procedure sp_DBR_country_edit(
inPerson int,
inCode varchar(3)
)
begin

update Persons
set CountryCode=inCode
where id=inPerson;

/*
 Return two columns -> will be sent back as a JSON string
*/

select inCode as Code, Name, Continent
from Countries
where Code=inCode;

end

Examples of input types

We'll use different input types for the following data: a plain input, a textarea (autosizing), a select list, radio buttons, a datepicker, and a colorpicker. Dates are shown as-is from the database.

For the radio button and selectlist the textual representation shown to the user will be fetched. myDBR will pass the ID to the editing report.

create procedure sp_DBR_update_inputtype()
begin
select 'dbr.pageview';

select 'dbr.javascript', '
function color_callback(settings, cell)
{
/* 
  Chosen (format #626262) value is in 'this'
  colorpicker.convert(this) will convert the color value into visible color
*/
colorpicker.convert(this);
}';

select 'dbr.editable', 'Basic', 'sp_DBR_input_edit', 'inId=id';
select 'dbr.editable', 'autosize', 'sp_DBR_text_edit', 'inId=id', 'type=autosize';
select 'dbr.editable', 'select', 'sp_DBR_varchar_edit', 'inId=id', 'type=select', "select=select id, value from char_values";
select 'dbr.editable', 'radio', 'sp_DBR_integer_edit', 'inId=id', 'type=radiobutton', "select=sp_radiovalues";
select 'dbr.editable', 'Date', 'sp_DBR_date_edit', 'inId=id', 'type=datepicker';
select 'dbr.editable', 'color', 'sp_DBR_color_edit', 'inId=id', 'type=colorpicker', "options={'callback':color_callback}";

select 'dbr.hidecolumn' 'id';

select  e.plain_column as 'Basic Input[Basic]',
        e.text_column as 'Textarea[autosize]',
        c.value as 'Select list[select]',
        i.value as 'Radio Button[radio]',
        e.date_column as 'Date', 
        e.color as 'Color[color]',  /* color format: #626262 */
        id
from mydb.editable e
  join int_values i on i.id = e.integer_column
  join char_values c on c.id = e.char_column;

end

With 'autocomplete'-editable type, one can serach and autocomplete from large amount of data as the suggestion based on user's input is done via Ajax. User edit the visible value (for example country name) and the editable passes the corresponding ID (for example country code for a country) into the editing procedure. If one passes allow_new option to the call ("options={'allow_new': true}";), user can enter value other than fetched via Ajax.

select 'dbr.hidecolumn', 'ID';
select 'dbr.editable', '[autoc]', 'sp_DBR_edit_country_ac', 'in_id=ID', 'type=autocomplete','select=sp_country_ac';

select e.id as 'ID', c.country as 'Country[autoc]'
from mydata e
 left join countries c on c.code=e.country_code
;

Where the editing report is:
create procedure sp_DBR_edit_country_ac(
in_id int,
in_value char(2)
)
begin

update mydata
set country_code = in_value
where id=in_id;

-- Return data for callback handling
select code, country, continent
from countries
where code = in_value;
end

The autocomplete Ajax-query returns three columns: the visible value and the optional secondary row (leave empty if not shown) and the id for the row. Limit the query to 20 rows (maximum shown at a time).

create procedure sp_country_ac(
in_search varchar(255)
)
begin

select country, concat('Capital: ', capital), code
from countries
where country like concat('%', in_search, '%')
limit 20;

end

Table int_values and its associated sp_radiovalues looks like this:

select * from int_values;

id   name
-----------
 1   'Asia'
 2   'Europe'
 3   'North America'
 4   'Africa'
 5   'Oceania'
 6   'South America'
 7   'Antartica'

create procedure sp_radiovalues()
begin

select id, name
from int_values;

end

Updating procedures get two parameters. First, the supplied 'id' parameter from the first column of the result set and the user-supplied value with the specific data type. The update procedures can include checks for the input, update the data, and optionally return a value that will replace the user input.

For the date column, we'll do a direct update

create procedure sp_DBR_date_edit (
inId int,
inUservalue date
)
begin

update mydb.editable
set date_column = inUservalue
where id=inId;

end

For the datetime we, for example, can check if the date belongs to the current year and accept/reject the update based on that.

create procedure sp_DBR_datetime_edit (
inId int,
inUservalue datetime
)
begin

/* Does the user date belong to current year? */
if ( year(inUservalue) = year(now()) ) then
  update mydb.editable
  set datetime_column = inUservalue
  where id=inId;
else
  /* Do not update and return the original data back to report */
  select datetime_column
  from mydb.editable
  where id=inId;
end if;

end

myDBR uses the IDs of the user-selectable radio buttons. If this update would be rejected and ID converted, myDBR will automatically convert the ID back into user-visible value.

create procedure sp_DBR_integer_edit (
inId int,
inUservalue int
)
begin

update mydb.editable
set integer_column = inUservalue
where id=inId;

end

Select works just like the radio button automating the conversion between ids and user-visible values.

create procedure sp_DBR_varchar_edit (
inId int,
inUservalue varchar(5)
)
begin

update mydb.editable
set varchar_column = inUservalue
where id=inId;

end

When using checkbox-type, one places the IDs (numeric) and the user-visible values in separate columns. The ID column is pinpointed with 'checkbox_id'-option. myDBR will return the selected values wrapped in DIV with class editable_cb_item and separated by DIV with class editable_cb_item_comma. This allows the formatting of the visible values either in the same line separated with a comma or in separate lines.

Checkbox-editing has also an option which allows using find/scroll/collapse options (similar to checkbox parameters). The html-option will preserve the HTML in the cell. The find-option allows searching for the choices, the scroll-option shows the list as a scrollable list, and the collapse-option (rarely used) allows collapsing the selection.

create procedure sp_DBR_editable_checkbox()
begin

SET @@group_concat_max_len = 100000; /* For MySQL group_concat_max_len has a default value of 1024, we'll prepare for more */ 

select 'dbr.editable', '[Categories]', 'sp_DBR_edit_film_category', 'in_film_id=film_id','checkbox_id=ids', 'type=checkbox', 'select=select category_id, name from category';


select fi.film_id, fi.title, ca.cat_ids as 'ids', ca.cat_names as 'Categories'
from film_samples fi
join (
  select f.film_id, group_concat(c.category_id) as cat_ids, group_concat(c.name) as cat_names
  from film_samples f
    left join film_category fc on fc.film_id=f.film_id
    left join category c on c.category_id=fc.category_id
  group by f.film_id
) ca on ca.film_id = fi.film_id;


select 'dbr.editable', '[Countries]', 'sp_DBR_edit_selected_demo_countries', 'in_id=id','checkbox_id=codes', 'type=checkbox', 
  'select=select c.Code, concat( c.flag , ' ',c.Name), c.Continent from country c order by c.Continent, c.Name',"options={'html':1, 'scroll':1, 'find':1}";

select 
  d.id, 
  group_concat(s.country_code) as 'codes,
  concat('dbr.html:',group_concat(concat('<div class="editable_cb_item">',c.flag, ' ',c.name,'</div>') SEPARATOR '<div class="editable_cb_item_comma">,</div>')) as Countries
from data d
  left join selected_demo_countries s on s.id=d.id
  left join country c on c.Code=s.country_code
group by d.id;


end

Passing parameters to a parameter query

When stored procedures are used in dbr.editable select parameters, you can pass parameters to it using normal column references.

create procedure sp_DBR_update_inputtype_parameter()
begin

select 'dbr.pageview';

select 'dbr.editable', 'Select', 'sp_DBR_integer_edit', 'inId=id', 'type=select', "select=sp_select_with_params [id]";

select  id, 
        date_column as 'Date', 
        datetime_column as 'Datetime', 
        i.value as 'Radio Button[radio]',
        c.value as 'Select'
from mydb.editable e, int_values i, char_values c
where e.integer_column = i.id and e.char_column = c.id;

end

Example of using file_rename-type dbr.editable

File renaming editable type is meant for situations where there is a table in the database containing the info which files the user can edit. The routine changes the actual file name and the reference in the database.

The actual path to the file is now shown to the user, the routine passes the ID of the table containing the actual filename.

create procedure sp_DBR_editable_rename()
begin

select 'dbr.editable', 'file', 'sp_DBR_editable_rename_do', 'type=file_rename', 'in_id=id';

select id, substring_index(file, '/', -1) as 'file'
from editable_rename
where id=1;

end;

The actual path to the file is now shown to the user, the routine passes the ID of the table containing the actual filename. The editable procedure for file rename has two automatically filled parameters (the last two). The penultimate parameter gets values of 'get' and 'set' and the last one the user-edited file name. The routine is called first with the parameter 'get' to get the full filename based on the ID. At this stage, the routine should return the full filename. After a successful rename, the routine is called a second time so that the file reference can be updated. The file will not be renamed if a) file does not exist in the filesystem b) there exists already a file with the new name or c) there are not enough filesystem permissions to the rename action.

create procedure sp_DBR_editable_rename_do(
in_id int,
in_action varchar(10),
in_file varchar(255)
)
begin

if (in_action='get') then
  select file
  from editable_rename
  where id=in_id;
else
  update editable_rename
  set file = in_file
  where id=in_id;
end if;

end

Example of basic callback usage

Auto-updating the summary columns with the supplied autosum_int / autosum_float requires you to just to define the predefined callback function. After the editing is done, a new summary value is calculated.

Setting the onblur option to empty makes the 'OK'- and 'Cancel'-buttons to appear below the enterable field.

select 'dbr.editable', 'value', 'sp_DBR_budget_edit', 'inAccount=account', "options={'onblur':'', 'callback':autosum_int}";
select 'dbr.sum', 2;

select account, value
from budget;

Example of own callback function and the onsubmit

In the example, we have an editable column and a column that holds the calculated value of other columns. When a user updates a column, we need to calculate a new calculated value and refresh the summary columns. In the example, we'll use myDBR built-in functions col_value_get and col_value_set which will get and set table cell value for a given cell.

The example also checks if the entered value is bigger than 100 and asks for confirmation from the user. This is done by setting its own handler for the onsubmit event.

The 'col_value_get'-function has the following format: col_value_get( obj, col ), where obj is the reference to the cell and col is the column class in the row if another column than the object's own column is referenced.

The 'col_value_set'-function has the following format: col_value_set( obj, col, decimals, val, prefix, suffix ), where 'obj' is the reference to the cell and col is the column class in the row if another column than the object's column is referenced. 'val' is the value to insert to set and prefix and suffix are additional text to be attached to the cell value.

The autosum-functions have the following formats:

autosum_int( obj, column, prefix, suffix )

autosum_float( obj, column, decimals, prefix, suffix )

When the first row's B column is updated following callback will calculate the cells marked in red.


select 'dbr.javascript', 'function mycallback()
{

// Value of A in edited row
var A = col_value_get( this, "Acol" );

// Value of B in edited cell
var B = col_value_get( this );

// Set the value of 4th A+B column
col_value_set( this, "ABcol", 0, A+B);

// Calculate sum of edited column
autosum_int(this);

// Calculate sum of A+B column
autosum_int(this,"ABcol");

}
function mysubmit(settings, cell)
{
  var edited_value;

  edited_value = Cell_data.get_value_raw( $(cell).find("input").val(), "int" );

  if ( (edited_value>100) && !confirm("Do you to set the value this high ("+$(cell).find("input").val()+")?")) {
    cell.reset();
    return false;
  }
  return true;
}

';

select 'dbr.sum', 'A', 'B', 'AB';
select 'dbr.editable', 'B', 'sp_DBR_edit_B', 'inKey=[ID]', "options={'callback':mycallback,'onsubmit':mysubmit}";

select 'dbr.colclass', 'A', 'Acol';
select 'dbr.colclass', 'A+B', 'ABcol';

select ID, A, B, A+B as 'A+B'
from mytable;

Example of using dbr.selectable

dbr.selectable converts ID columns of a result set to checkboxes, which can be used to mark the rows to be processed. The selected row IDs can be used in a subsequent linked report (usually with a combination of dbr.button). The result set for the selectable will be marked with the dbr.resultclass. The selected row IDs can later be referenced in the linked report using 'selectable.cssclass'-notation where the cssclass is the CSS class added with dbr.resultclass. Note that the selected IDs are user given values, so you should not trust the values and should validate the values in the linked report if needed.

Convert an integer ID column to checkboxes and mark the result with dbr.resultclass. The list of IDs is passed as a parameter to the report attached to a button via the selectable.resultclass syntax:

/* Convert the ID column to checkboxes */
select 'dbr.selectable', 'ID';
/* Give the result set a class which will be used later as parameter reference to the linked report */
select 'dbr.resultclass', 'films';

select  
  f.film_id as 'ID',
  f.title as 'Title',
  c.name as 'Category'
from films f 
  join category c on c.category_id = f.category_id;
  
select 'dbr.button', 'Set category';
select 'dbr.report', 'sp_DBR_film_category_set', 'dynamic_div[]', 'in_ids=selectable.films';

select 'dummy';

The IDs are passed in as a comma-separated string ("1,2,3,4"). At the end, we'll refresh the main report with dbr.refresh

create procedure sp_DBR_film_category_set(
in_ids text,
in_category_id int
)
begin

update films 
set category_id = in_category_id
where find_in_set(films.film_id, in_ids);

select 'dbr.refresh';

end

To refresh the list with selected values, one has multiple choices:

  1. Use dbr.refresh to reload the whole report
  2. Embed the element with dbr.report and use a hidden button to trigger the element refresh
  3. Use direct JavaScript to refresh the values (can be used when the values are not used in another dbr.report/dbr.url)

To use direct JavaScript to change the visible values

create procedure sp_DBR_film_category_set(
in_ids text,
in_category_id int
)
begin
declare v_category_name varchar(50);

update films 
set category_id = in_category_id
where find_in_set(films.film_id, in_ids);

-- Get the name
select name into v_category_name
from category
where category_id = in_category_id;

-- Escape the v_category_name if it can contain backslashes or '-characters, if not, you can skip this
set v_category_name = replace(v_category_name, "\\", "\\\\");
set v_category_name = replace(v_category_name, "'", "\\'");

select 'dbr.javascript', concat("selected.set_col_value('films', '",in_ids,"', 'category', '",v_category_name,"')");

end

The selected.set_col_value is a helper function which has a format of

selected.set_col_value(table_class, comma_separated_id_string, column_class, visible_value);.

It changes the value for column_class-columm for all rows whose ID matches with the supplied IDs and the table is identified by the table_class.

Rich text editing

By adding 'Rich text'-extension to the report from report parameters, one can use 'richtext' editing type which will bring up a full-fledged editor. The editor can be invoked from direct HTML code or from HTML code shown with dbr.html:.

myDBR uses TinyMCE for the Rich Text editor. TinyMCE options can be passed in as the 'richtext'-option.

To show the HTML code as rendered HTML, we'll use 'dbr.html:' in the query. To enable rich text editing, we'll use 'type=richtext' and use option "'html':1" to indicate that we'll be converting the HTML into an editable format.

select 'dbr.editable', 'rich_text',  'sp_DBR_edit_rich_text', 'in_id=id', 'type=richtext', "options={'html':1,'richtext':{'width': 600, 'height': 350}}";
select 'dbr.hidecolumn', 'id';
select 'dbr.colstyle', 'rich_text', '[white-space: pre;]';

select id, concat('dbr.html:', rich_text) as rich_text
from rich_text;

One can also use direct HTML code to invoke the editor. In this case, the code is shown as is in the query and no html-option is passed.

select 'dbr.colstyle', 'rich_text', '[white-space:pre]';
select 'dbr.hidecolumn', 'id';
select 'dbr.editable', 'rich_text',  'sp_DBR_edit_rich_text', 'in_id=id', 'type=richtext', "options={'richtext':{'width': 600, 'height': 350}}";

select id, rich_text
from rich_text;

Code editing

By adding 'Code editor'-extension to the report from report parameters, one can use code editing with 'html', 'php', 'javascript', 'css' and 'sql' types.

select 'dbr.hidecolumn', 'id';
select 'dbr.editable', 'code', 'sp_DBR_edit_code', 'inA=id', 'type=php';
select 'dbr.colstyle', 'code', '[white-space:pre;font-family:Courier]';

select id, code
from code_editing;

Example of using dbr.upload

With dbr.upload, one can offer file upload capability to the server.


select 'dbr.upload.options', 'noreplace', 1;
select 'dbr.upload.options', 'accepted_files', 'jpg,png';
select 'dbr.upload.options', 'maxheight', 300;
select 'dbr.upload.options', 'maxwidth', 200;
select 'dbr.upload.options', 'quality', 0.6;

select 'dbr.upload', '/var/www/upload', 'sp_DBR_upload_file', v_client_id;

select 'dummy';

The upload procedure takes three fixed parameters path, filename, and file size. One can also pass additional parameters to the procedure. The procedure will return the text that is shown to the user after a successful upload.

create procedure sp_DBR_upload_file(
in_path varchar(255),
in_file  varchar(255),
in_size int,
in_id int,
inLogin varchar(128)
)
begin


insert into uploads 
values (inLogin, in_path, in_file, in_size, in_id, now());

select concat('File ', in_file, ' uploaded (',format_bytes(in_size),')');

end