Remote calls to other myDBR servers

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.

Commands

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 turning off the certificate check on the remote server. Optional and needs to be called before 'dbr.remote.prepare'.

Syntax

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]

Where:

server: The nickname for the remote server defined in 'Remote servers'
localtable: Local table to receive the results (can be a temporary table). The format of the table must match the output of the remote report call
remote_report / remote_report_parameters: Remote server's myDBR report's stored procedure name with parameters
local_report: The local report to be called after the remote report is run and put into a local table.
local_report_parameters: A comma-separated list of parameters for a local report

Options include:

skip_ssl_verify: If set myDBR will connect to the remote server even if the SSL certificate is invalid.
use_proxy: When a proxy server is defined in myDBR environmental settings, this setting defines whether to use it or not.
use_local_authentication: If set myDBR will use local authentication with remote calls. For example, if myDBR is set to use Active Directory authentication, this would allow remote calls to use myDBR's own authentication.
timeout: Defines how long will the local server wait for the remote server. The default is defined in defaults.php

Setup

Remote myDBR servers are defined in Admin tools -> Remote servers. The following fields need to be defined:

Servername The nickname for the remote server. This is used in reports to identify the remote server.
URL Pointing to the base of remote myDBR. Example: https://myotherserver.com/mydbr/
Seed The URL seed for the remote server found in Environment settings -> Authentication / security -> URL hash seed
Username The username used to connect to remote server.
Password The password used to connect to remote server.

Examples

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 remains accessible 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 when 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 ensure you use HTTPS calls or limit what can be done with the username.

Troubleshooting

The most common error messages received when doing remote calls are the following:

No access privileges: The user does not have access privileges to the report or the report specified with dbr.remote.prepare does not exist
Report security hash does not match. Report execution aborted.: The security hash in the remote server definition does not match the one in the remote server's environmental settings URL hash seed value
Invalid login. Check the username and password for the remote server.: The username/password defined in the remote server definition is not correct