myDBR installation can make queries to other myDBR installations. This allows reports to combine data from multiple databases even if the databases are not connected to each other.
dbr.remote.prepare - Defines the server to be called, call to be made and local table to receive the results. Required, needs to be called before 'dbr.remote.call'
dbr.remote.call - Executes the defined remote call and runs a local report to handle the results. Required.
dbr.remote.options - Allows setting extra options for transfer. The option includes to turn off certificate check on the remote server. Optional and needs to be called before 'dbr.remote.prepare'.
select 'dbr.remote.prepare', 'server', 'localtable', remote_report[, remote_report_parameters]
select 'dbr.remote.call', 'local_report'[, local_report_parameters]
select 'dbr.remote.options', 'option'[, value]
Remote myDBR servers are defined in Admin tools -> Remote servers. The following fields needs to be defined:
ServernameThe nickname for the remote server. This is used in reports to identify the remote server.
URLPointing to base of remote myDBR. Example: https://myotherserver.com/mydbr/
SeedThe URL seed for the remote server found in Environment settings -> Authentication / security -> URL hash seed
UsernameUsername used to connect to remote server.
PasswordPassword used to connect to remote server.
We'll make a query to two different remote myDBR servers (my_remote_mydbr and my_other_mydbr). The first remote call returns its data to an existing local table. For the second call, we'll create a temporary table to hold the remote call's data. You can have multiple remote calls before the actual report is executed.
DROP PROCEDURE IF EXISTS sp_DBR_remote_call $$ CREATE PROCEDURE `sp_DBR_remote_call`(inDate date) BEGIN /* mylocaltable is a normal table defined in database */ drop temporary table if exists remote_data_tmp; create temporary table remote_data_tmp ( title varchar(255), description text, release_year year(4), filmdate date ); select 'dbr.remote.prepare', 'my_remote_mydbr', 'mylocaltable', 'sp_DBR_remote_first', inDate; select 'dbr.remote.prepare', 'my_other_mydbr', 'remote_data_tmp', 'sp_DBR_remote_other', inDate; select 'dbr.remote.call', 'sp_DBR_show_remote', inDate, 2; END $$
myDBR looks for the server's 'my_remote_mydbr' definition from the settings and calls a report 'sp_DBR_remote_first' in that server. while passing the inDate as a parameter. The result is stored in a normal local table 'mylocaltable'. For the second remote call, the result will be put into the recently created 'remote_data_tmp' temporary table. The remote call can contain only one result set and the format must match the local table.
Note that you can use temporary tables with MySQL. In MySQL temporary tables live remain after the procedure has been completed (it lives until the connection is closed). In other databases, temporary tables are removed once the procedure completes. In other databases, one needs to use normal tables.
After the remote calls have been made, myDBR calls the local report (sp_DBR_show_remote) which shows the data and drops the temporary table if created.
DROP PROCEDURE IF EXISTS `sp_DBR_show_remote` $$ CREATE PROCEDURE `sp_DBR_show_remote`(inDate date, inNum int) BEGIN select * from mylocaltable; select * from remote_data_tmp; drop temporary table if exists remote_data_tmp; END $$
myDBR authenticates through HTTP basic access authentication, so make sure you use https calls or limit what can be done with the username.
The most common error messages received when doing remote calls are the following: