Synchronize list values from an external database

Open Bee Portal includes a script that allows you to retrieve values from an external database and add them to an Open Bee™™ Portal list.

Prerequisite

  • An external database for which there is a PDO driver (e.g. Oracle, MS SQL or MySql …). The full list is available here: http://php.net/manual/fr/pdo.drivers.php.
  • A network configuration that allows the server hosting Open Bee™ Portal to connect to the external database.
  • The login information on the external database.

This tool is included as of version 6.5.3 of Open Bee™ Portal. However, it is possible to install it on version 6.5.2 by downloading this ZIP file and unzipping it into the “lib/task” folder in the Open Bee™ Portal installation folder.

Test data

To illustrate this article, we will take the example of an external MySQL database named “test” containing a “customers” table. We will synchronize the “name” column of the “customers” table with a list from Open Bee™ Portal.

SET NAMES "UTF8"; 
 
CREATE DATABASE test; 
USE test; 
 
CREATE TABLE IF NOT EXISTS `customers` (
`id_customer` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255), 
PRIMARY KEY (`id_customer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
INSERT INTO customers VALUES (NULL, "Bijou"), (NULL, "Carat"), (NULL, "Topaz");

Setting up and testing the script

The script does the following:

  • Reading data from the external database.
  • Write the data to the appropriate list in the Open Bee™ Portal.

To do this, it requires 4 arguments:

  • dbDsn: A DNS (Data Source Name) PDO (Php Data Objects) of the external database. Example: mysql:dbname=test; host=localhost For more details, see the PHP documentation on the subject.
  • dbLogin: The user of the external database.
  • dbPassword: Password for accessing the external database.
  • dbQuery: The query to be executed to retrieve the values from the list. (warning) This query should return only one column. Example: “SELECT name FROM customers”.
  • listId: the identifier of the Open Bee™ Portal list in which the data will be inserted. This value can be retrieved from the ranking rules management interface from the Open Bee™ Portal administration. To do this, select a ranking rule and then select a metadata associated with the list. Then open the source code of the page (Ctrl + U) and search (Ctrl +F) for the value “var idList“. You will find the ID of the list:

Then run the script as in the following example:

php symfony sql2List --dbDsn="mysql:dbname=test;host=localhost" --dbLogin=user --dbPassword=MyPass --dbQuery="SELECT name FROM customers" --listId=11001

Note: It may be useful, especially in the case of Oracle, to specify the charset type. Example: “oci:dbname=test; host=localhost; charset=UTF-8”, if an accent problem occurs.

A message is displayed with the number of added values in the list. Values extracted from the external database are now available in the Open Bee™ Portal list.

Autoplay

Once your query has been tested, you can save the script as a scheduled task on the Open Bee™ Portal installation server.

Adding new values to the external database will automatically sync with Open Bee™ Portal.

Logs

The logs of this task are available in the “sql2List.log” file located in the “log” folder of the Open Bee™ Portal installation.

Install in the cloud or without database access

From a Windows or Linux server:

  • Install PHP v5.6 with extensions: CURL, PDO. Plus other PHP extensions/drivers that may be needed depending on your database (MSSQL for example)
  • From the command line, call the following script: sql2ListWithApi.php.
    On the same principle described in the previous paragraphs of this page, and taking into account the new options:

    • dbDsn: A DNS (Data Source Name) PDO (Php Data Objects) of the external database. Example: mysql:dbname=test; host=localhost For more details, see the PHP documentation on the subject.
    • dbLogin: The user of the external database.
    • dbPassword: Password for accessing the external database.
    • dmsURL: Open Bee Portal address (port included if 80 or 443). Example: ‘http://127.0.0.1:8000‘
    • dmsUser: Open Bee Portal user. Must have administrative permissions to update the values in a list.
    • dmsPassword: Open Bee Portal User Password
    • deleteOldValues: Allows the deletion of old values that already exist in the list. Possible values (‘true’, ‘false’, ‘0’, ‘1’)
    • alphabeticSort: Allows you to sort the values in a list alphabetically. Possible values (‘Asc’, ‘Desc’)
    • sourceCharset: Allows the encoding of special characters. The possible values (‘windows-1252’ or ‘CP1252’, and for other codes follow the link https://www.php.net/manual/fr/mbstring.supported-encodings.php).
    • listId: The ID of the list.
    • Query: The query to be executed to retrieve the values from the list.
php sql2ListWithApi.php --dbDsn="odbc:DataSourceName"  --dbLogin= -dbPassword= --dmsURL="http://localhost:8000" --dmsUser=userName --dmsPassword=userPassword --deleteOldValues=true --alphabeticSort=DESC --sourceCharset= --listId=11310 --query="select XXX from TABLE1"
  • Save this processing to a scheduled task

Installing drivers for an MSSQL database

Installing Drivers for an Oracle Base

See this article: Installing Oracle and MS SQL PDO Extensions