{"id":20399,"date":"2024-02-16T16:00:08","date_gmt":"2024-02-16T15:00:08","guid":{"rendered":"https:\/\/help.openbee.com\/open-bee-portal\/knowledge-base\/how-to-guides\/synchronize-list-values-from-an-external-database\/"},"modified":"2024-02-16T16:00:08","modified_gmt":"2024-02-16T15:00:08","slug":"synchronize-list-values-from-an-external-database","status":"publish","type":"page","link":"https:\/\/help.openbee.com\/en\/open-bee-portal\/knowledge-base\/how-to-guides\/synchronize-list-values-from-an-external-database\/","title":{"rendered":"Synchronize list values from an external database"},"content":{"rendered":"<div id=\"main-content\" class=\"wiki-content group\">\n<p>Open Bee Portal includes a script that allows you to retrieve values from an external database and add them to an Open Bee\u2122\u2122 Portal list.<\/p>\n<style type=\"text\/css\"><![CDATA[\/*<![CDATA[*\/ div.rbtoc1648215991645 {padding: 0px;} div.rbtoc1648215991645 ul {list-style: disc;margin-left: 0px;} div.rbtoc1648215991645 li {margin-left: 0px;padding-left: 0px;} \/*]]]]><![CDATA[>*\/]]><\/style>\n<div class=\"toc-macro rbtoc1648215991645\">\n<ul class=\"toc-indentation\">\n<li><a href=\"#Synchroniserlesvaleursd%E2%80%99uneliste%C3%A0partird'unebasededonn%C3%A9esexterne-Pr%C3%A9-requis\">Prerequisite  <\/a><\/li>\n<li><a href=\"#Synchroniserlesvaleursd%E2%80%99uneliste%C3%A0partird'unebasededonn%C3%A9esexterne-Donn%C3%A9esdetests\">Test data  <\/a><\/li>\n<li><a href=\"#Synchroniserlesvaleursd%E2%80%99uneliste%C3%A0partird'unebasededonn%C3%A9esexterne-Configurationettestduscript\">Setting up and testing the script  <\/a><\/li>\n<li><a href=\"#Synchroniserlesvaleursd%E2%80%99uneliste%C3%A0partird'unebasededonn%C3%A9esexterne-Ex%C3%A9cutionautomatique\">Autoplay<\/a><\/li>\n<li><a href=\"#Synchroniserlesvaleursd%E2%80%99uneliste%C3%A0partird'unebasededonn%C3%A9esexterne-Logs\">Logs  <\/a><\/li>\n<li><a href=\"#Synchroniserlesvaleursd%E2%80%99uneliste%C3%A0partird'unebasededonn%C3%A9esexterne-Installationencloudousansacc%C3%A8s%C3%A0labasededonn%C3%A9es\">Install in the cloud or without database access<\/a><\/li>\n<li><a href=\"#Synchroniserlesvaleursd%E2%80%99uneliste%C3%A0partird'unebasededonn%C3%A9esexterne-InstallationdesdriverspourunebaseMSSQL\">Installing drivers for an MSSQL database  <\/a><\/li>\n<li><a href=\"#Synchroniserlesvaleursd%E2%80%99uneliste%C3%A0partird'unebasededonn%C3%A9esexterne-InstallationdesdriverspourunebaseOracle\">Installing Drivers for an Oracle Base<\/a><\/li>\n<\/ul>\n<\/div>\n<h2 id=\"Synchroniserlesvaleursd\u2019uneliste\u00e0partird'unebasededonn\u00e9esexterne-Pr\u00e9-requis\">Prerequisite<\/h2>\n<ul>\n<li>An external database for which there is a PDO driver (e.g. Oracle, MS SQL or MySql &#8230;). The full list is available here: <a class=\"external-link\" href=\"http:\/\/php.net\/manual\/fr\/pdo.drivers.php\" rel=\"nofollow\">http:\/\/php.net\/manual\/fr\/pdo.drivers.php<\/a>.<\/li>\n<li>A network configuration that allows the server hosting Open Bee\u2122 Portal to connect to the external database.<\/li>\n<li>The login information on the external database.<\/li>\n<\/ul>\n<div class=\"aui-message hint shadowed information-macro\">\n<div class=\"message-content\">\n<p>This tool is included as of version 6.5.3 of Open Bee\u2122 Portal. However, it is possible to install it on version 6.5.2 by downloading <a class=\"external-link\" href=\"http:\/\/download.myopenbee.com\/releases\/OpenBee\/OpenBeePortal\/6.5.3\/sql2ListTask.class.php.zip\" rel=\"nofollow\">this ZIP file<\/a> and unzipping it into the &#8220;lib\/task&#8221; folder in the Open Bee\u2122 Portal installation folder.<\/p>\n<\/div>\n<\/div>\n<h2 id=\"Synchroniserlesvaleursd\u2019uneliste\u00e0partird'unebasededonn\u00e9esexterne-Donn\u00e9esdetests\">Test data<\/h2>\n<p>To illustrate this article, we will take the example of an external MySQL database named &#8220;test&#8221; containing a &#8220;customers&#8221; table. We will synchronize the &#8220;name&#8221; column of the &#8220;customers&#8221; table with a list from Open Bee\u2122 Portal.<\/p>\n<div class=\"code panel pdl\" style=\"border-width: 1px;\">\n<div class=\"codeContent panelContent pdl\">\n<pre class=\"theme: Confluence; brush: sql; gutter: false\" style=\"font-size: 12px;\">SET NAMES \"UTF8\"; \n \nCREATE DATABASE test; \nUSE test; \n \nCREATE TABLE IF NOT EXISTS `customers` (\n`id_customer` int(11) NOT NULL AUTO_INCREMENT,\n`name` varchar(255), \nPRIMARY KEY (`id_customer`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;\n \nINSERT INTO customers VALUES (NULL, \"Bijou\"), (NULL, \"Carat\"), (NULL, \"Topaz\");<\/pre>\n<\/div>\n<\/div>\n<h2 id=\"Synchroniserlesvaleursd\u2019uneliste\u00e0partird'unebasededonn\u00e9esexterne-Configurationettestduscript\">Setting up and testing the script<\/h2>\n<p>The script does the following:<\/p>\n<ul style=\"list-style-type: square;\">\n<li>Reading data from the external database.<\/li>\n<li>Write the data to the appropriate list in the Open Bee\u2122 Portal.<\/li>\n<\/ul>\n<p>To do this, it requires 4 arguments:<\/p>\n<ul style=\"list-style-type: square;\">\n<li>dbDsn: A DNS (Data Source Name) PDO (Php Data Objects) of the external database. Example: <em>mysql:dbname=test; host=localhost<\/em> For more details, see the <a class=\"external-link\" href=\"http:\/\/php.net\/manual\/fr\/class.pdo.php\" rel=\"nofollow\">PHP documentation on the subject<\/a>.<\/li>\n<li>dbLogin: The user of the external database.<\/li>\n<li>dbPassword: Password for accessing the external database.<\/li>\n<li>dbQuery: The query to be executed to retrieve the values from the list.  <img decoding=\"async\" class=\"emoticon emoticon-warning\" src=\"https:\/\/help.openbee.com\/wp-content\/uploads\/2022\/03\/warning.png\" alt=\"(warning)\" data-emoticon-name=\"warning\">  This query should return only one column. Example: &#8220;SELECT <strong>name<\/strong> FROM customers&#8221;.<\/li>\n<li>listId: the identifier of the Open Bee\u2122 Portal list in which the data will be inserted. This value can be retrieved from the ranking rules management interface from the Open Bee\u2122 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 &#8220;<span style=\"color: #000000;\">var idList<\/span>&#8220;. You will find the ID of the list:<\/li>\n<\/ul>\n<p style=\"margin-left: 30.0px;\"><img decoding=\"async\" class=\"confluence-embedded-image\" src=\"https:\/\/help.openbee.com\/wp-content\/uploads\/2022\/03\/245399554.png\" data-image-src=\"https:\/\/help.openbee.com\/wp-content\/uploads\/2022\/03\/245399554.png\"><\/p>\n<p style=\"margin-left: 30.0px;\">\n<p style=\"margin-left: 30.0px;\">\n<p>Then run the script as in the following example:<\/p>\n<div class=\"code panel pdl\" style=\"border-width: 1px;\">\n<div class=\"codeContent panelContent pdl\">\n<pre class=\"theme: Confluence; brush: java; gutter: false\" style=\"font-size: 12px;\">php symfony sql2List --dbDsn=\"mysql:dbname=test;host=localhost\" --dbLogin=user --dbPassword=MyPass --dbQuery=\"SELECT name FROM customers\" --listId=11001<\/pre>\n<\/div>\n<\/div>\n<p>Note: It may be useful, especially in the case of Oracle, to specify the charset type. Example: &#8220;oci:dbname=test; host=localhost; charset=UTF-8&#8221;, if an accent problem occurs.<\/p>\n<p>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\u2122 Portal list.<\/p>\n<h2 id=\"Synchroniserlesvaleursd\u2019uneliste\u00e0partird'unebasededonn\u00e9esexterne-Ex\u00e9cutionautomatique\">Autoplay<\/h2>\n<p>Once your query has been tested, you can save the script as a scheduled task on the Open Bee\u2122 Portal installation server.<\/p>\n<p>Adding new values to the external database will automatically sync with Open Bee\u2122 Portal.<\/p>\n<h2 id=\"Synchroniserlesvaleursd\u2019uneliste\u00e0partird'unebasededonn\u00e9esexterne-Logs\">Logs<\/h2>\n<p>The logs of this task are available in the &#8220;sql2List.log&#8221; file located in the &#8220;log&#8221; folder of the Open Bee\u2122 Portal installation.<\/p>\n<h2 id=\"Synchroniserlesvaleursd\u2019uneliste\u00e0partird'unebasededonn\u00e9esexterne-Installationencloudousansacc\u00e8s\u00e0labasededonn\u00e9es\">Install in the cloud or without database access<\/h2>\n<p>From a Windows or Linux server:<\/p>\n<ul style=\"list-style-type: square;\">\n<li>Install PHP v5.6 with extensions: CURL, PDO. Plus other PHP extensions\/drivers that may be needed depending on your database (MSSQL for example)<\/li>\n<li>From the command line, call the following script: <a href=\"https:\/\/ftp.myopenbee.com\/releases\/Tools\/sql2ListWithApi\/sql2ListWithApi.php\">sql2ListWithApi.php<\/a>.<br \/>\nOn the same principle described in the previous paragraphs of this page, and taking into account the new options:<\/p>\n<ul style=\"list-style-type: square;\">\n<li>dbDsn: A DNS (Data Source Name) PDO (Php Data Objects) of the external database. Example: <em>mysql:dbname=test; host=localhost<\/em> For more details, see the <a class=\"external-link\" href=\"http:\/\/php.net\/manual\/fr\/class.pdo.php\" rel=\"nofollow\">PHP documentation on the subject<\/a>.<\/li>\n<li>dbLogin: The user of the external database.<\/li>\n<li>dbPassword: Password for accessing the external database.<\/li>\n<li>dmsURL: Open Bee Portal address (port included if 80 or 443). Example: &#8216;<a class=\"external-link\" href=\"http:\/\/127.0.0.1:8000\/\" rel=\"nofollow\">http:\/\/127.0.0.1:8000<\/a>&#8216;<\/li>\n<li>dmsUser: Open Bee Portal user. Must have administrative permissions to update the values in a list.<\/li>\n<li>dmsPassword: Open Bee Portal User Password<\/li>\n<li>deleteOldValues: Allows the deletion of old values that already exist in the list. Possible values (&#8216;true&#8217;, &#8216;false&#8217;, &#8216;0&#8217;, &#8216;1&#8217;)<\/li>\n<li>alphabeticSort: Allows you to sort the values in a list alphabetically. Possible values (&#8216;Asc&#8217;, &#8216;Desc&#8217;)<\/li>\n<li>sourceCharset: Allows the encoding of special characters. The possible values (&#8216;windows-1252&#8217; or &#8216;CP1252&#8217;, and for other codes follow the link <a class=\"external-link\" href=\"https:\/\/www.php.net\/manual\/fr\/mbstring.supported-encodings.php\" rel=\"nofollow\">https:\/\/www.php.net\/manual\/fr\/mbstring.supported-encodings.php<\/a>).<\/li>\n<li>listId: The ID of the list.<\/li>\n<li>Query: The query to be executed to retrieve the values from the list.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<div class=\"code panel pdl\" style=\"border-width: 1px;\">\n<div class=\"codeContent panelContent pdl\">\n<pre class=\"theme: Confluence; brush: java; gutter: false\" style=\"font-size: 12px;\">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\"<\/pre>\n<\/div>\n<\/div>\n<ul>\n<li>Save this processing to a scheduled task<\/li>\n<\/ul>\n<h2 id=\"Synchroniserlesvaleursd\u2019uneliste\u00e0partird'unebasededonn\u00e9esexterne-InstallationdesdriverspourunebaseMSSQL\">Installing drivers for an MSSQL database<\/h2>\n<ul>\n<li>Download &amp; Install:<br \/>\nVisual C++ Redistributable for Visual Studio 2012 Update 4: <a class=\"external-link\" href=\"https:\/\/www.microsoft.com\/fr-fr\/download\/details.aspx?id=30679\" rel=\"nofollow\">https:\/\/www.microsoft.com\/fr-fr\/download\/details.aspx?id=30679<\/a>(vcredist_x64 + vcredist_x86)<br \/>\nMicrosoft Drivers for PHP for SQL Server: <a class=\"external-link\" href=\"https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=20098\" rel=\"nofollow\">https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=20098<\/a> (SQLSRV32.EXE)<br \/>\nMicrosoft\u00ae ODBC Driver 11 for SQL Server: <a class=\"external-link\" href=\"https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=36434\" rel=\"nofollow\">https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=36434<\/a> (ENUx64msodbcsql.msi)<\/li>\n<li>Copy the following extensions to the &#8220;apache24Pathmodulesphp5ext&#8221; folder<br \/>\nphp_sqlsrv_56_ts.dll<br \/>\nphp_pdo_sqlsrv_56_ts.dll<\/li>\n<li>Add these 2 extensions to php.ini<\/li>\n<li>Restart the openbeePortalApache2 service<\/li>\n<\/ul>\n<h2 id=\"Synchroniserlesvaleursd\u2019uneliste\u00e0partird'unebasededonn\u00e9esexterne-InstallationdesdriverspourunebaseOracle\">Installing Drivers for an Oracle Base<\/h2>\n<p>See this article: <a href=\"https:\/\/help.openbee.com\/en\/open-bee-portal\/installers-guide\/installing-oracle-pdo-and-ms-sql-extensions\/\">Installing Oracle and MS SQL PDO Extensions<\/a><\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Open Bee Portal includes a script that allows you to retrieve values from an external database and add them to an Open Bee\u2122\u2122 Portal list. Prerequisite Test data Setting up and testing the script Autoplay Logs Install in the cloud or without database access Installing drivers for an MSSQL database Installing Drivers for an Oracle [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":20163,"menu_order":7,"comment_status":"closed","ping_status":"closed","template":"templates\/ob-help-products.php","meta":{"footnotes":""},"class_list":["post-20399","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages\/20399","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/comments?post=20399"}],"version-history":[{"count":0,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages\/20399\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/pages\/20163"}],"wp:attachment":[{"href":"https:\/\/help.openbee.com\/en\/wp-json\/wp\/v2\/media?parent=20399"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}