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 local report to handle the results. Required.
dbr.remote.options - Allows setting extra options for transfer. Option includes to turn off certificate check on 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 local table.
local_report_parameters
Comma separated list of parameters for local report

Options include:

skip_ssl_verify
If set myDBR will connect to 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
How long will the local server wait for the remote server. The default is defined in /mydbr/defaults.php

Setup

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

  • Servername The nickname for the remote server. This is used in reports to identify the remote server.
  • URL Pointing to 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 Username used to connect to remote server.
  • Password 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 into a normal local table 'mylocaltable'. For the second remote call, the result will be put in to 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's basic access authentication, so make sure 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 exists
Report security hash does not match. Report execution aborted.
The security hash in the remote server definition does not match the one in 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