Remote Calls to Other myDBR Servers

myDBR installations can query other myDBR installations. This capability enables reports to consolidate data from multiple databases, even if these databases are not interconnected.

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 of Remote Calls

Let's demonstrate querying two different remote myDBR servers: my_remote_mydbr and my_other_mydbr.

  1. The first remote call will return its data directly into an existing local table.

  2. For the second remote call, we'll create a temporary table to store the data fetched from the remote server.

You can include multiple remote calls before executing the actual report.

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 'my_remote_mydbr' as defined in the settings and calls the report 'sp_DBR_remote_first' on that server, passing 'inDate' as a parameter. The result is stored in a regular local table named 'mylocaltable'. For the second remote call, the result will be placed into the recently created temporary table 'remote_data_tmp'. Remote calls can contain only one result set, and the format must match that of the local table.

Note that temporary tables can be used with MySQL. In MySQL, temporary tables remain accessible after the procedure has completed (they persist until the connection is closed). In other databases, temporary tables are removed once the procedure completes, necessitating the use of regular tables.

After the remote calls have been executed, myDBR calls the local report 'sp_DBR_show_remote', which displays the data and drops any temporary tables 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 using HTTP Basic Access Authentication. Therefore, ensure that you use HTTPS calls to secure the transmission of credentials, or limit the actions that can be performed with the provided 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